Database "Stuff" I Wished I Knew About For NodeJS - Part 2- Postgres

So now that we have covered the use-cases for MongoDB and Postgres in Part 1, let's talk about how to use Postgres with NodeJS.

First off, you don't need to know SQL to use anything SQL, like PostgreSQL. I have written less SQL in my life than I have written poems to my wife. And I am not romantic.

Also I promise this is not one of those "10/20/30 best modules" articles, which looked like a lot of copy, and paste, and paraphrasing, and even still recommending modules deprecated in 2019 as one of the best in 2020.

This here, is my many years of experience, scribed.

Why Postgres?

Postgres has been around for a very long time since its inception in 1996, developed at the University of California, Berkeley. It was commonly seen as "more advanced" than MySQL. On the other hand, MySQL focused on simplicity but compromised performance. It caught on with the mainstream. It didn't mattered back in those days of of pre-2000, data didn't scale at the rate that it is now.

Postgres is anything but ancient, because it is constantly being updated. In fact it is so powerful and reliable, very big corporations like banks uses it for its consistency and speed. And the benefits are immense:

1. Very mature, time tested and proven.
2. Very constantly updated.
3. Which also means it always gets new features.
4. Very fast.

Apples are red and oranges are orange.

I won't go into the details of what makes Postgres faster than MongoDB, as there are many articles out there on this.

In general, where data is relational, Postgres is almost always faster than MongoDB -- which makes it most of the time true because most data are relational. For non-relational data, even though Postgres also support schema-less storage with JSON fields, this is where MongoDB wins hands down.

Suffice to say, I have read many of these articles/researches, and concluded that you just have to use the right database for the right purpose -- apples are apples and cannot be compared to oranges. People who say that Postgres is also great for NoSQL with JSON fields, are as baseless as those who say MongoDB are great for relational data.

The worst reason to use MongDB

So you can write JavaScipt, but Structured Query Language (SQL) looks completely alien. This is not the reason to choose MongoDB.

Whatever SQL database you use, you don't have to write any SQL, period.

To do away with writing clunky SQL, there are JavaScript query builders and ORMs to access SQL databases using modern and simple Javascript syntax, much like MongoDB's syntax (not convinced? browse through my tutorial to build instagram, you will not see any SQL).

Query-builders and ORM

So what are query builders and Object Relational Mapper?

Query Builders - Knex

Query builders allows you to use Javascript syntax to write your queries. The best one out there is Knex:

knex('users').where({
  first_name: 'Test',
  last_name:  'User'
}).select('id')

Which is essentially in SQL like this:

select `id` from `users` where `first_name` = 'Test' and `last_name` = 'User'

And if you are a speed demon, you can choose to write raw SQLs. But with such thin overheads that Knex has, it makes perfect sense to use Knex to drastically improved readability.

In limited cases, raw SQLs makes sense where libraries have not kept up with new high-level SQL features, or included those that are seldom used. If not, for most cases, debugging a tower of SQL codes doesn't differentiate well with other tortious activities such as burning in hell or watching Cats.

ORM

ORM is essentially one abstraction-level above query builders. It allows you to define your database schema, and unlocks are more intuitive syntax. Using an ORM, one doesn't need to think in traditional terms of tables, rows and columns. It would be more intuitive, in terms of relationships between entities. Hold this thought, which you will understand through reading the syntax of ORMs.

1. SequelizeJS

This is by far the most complete ORM. It uses a await/async/defer/promise-styled syntax that is very readable and maintainable.

I personally prefer the promise-styled syntax (but if you check out their v6 documentation, they have transited to async/await style):

// User table
const User = sequelize.define('User', {
  firstName: { type: DataTypes.STRING },
  lastName: { type: DataTypes.STRING }
})

// Post table
const Post = sequelize.define('Post', {
  title: { type: DataTypes.STRING },
  caption: { type: DataTypes.STRING }
})

// association
User.hasMany(Post)
Post.belongsTo(User)

// query
User.find({ 
    where: { firstName: 'John' },
    include: { model: Post }
}).then(user => {
    // `user` will contain information about John, and all his posts.

    // send data back to requestor
})

Notice how, like I mentioned, you think of data in terms of entities like User and Post, and not in tables, columns and rows.

All the features you need, like transactions

I have used Sequelize extensively, and it really has all the features and customisations that any applications will need. For advanced users, check out how transactions are easily implemented, say when you need to create a user at the same time with a new post:

sequelize.transaction((t) => {

    return User.create({
        firstName: 'Abraham',
        lastName: 'Lincoln'
    }, { transaction: t }).then(user => {

        user.addPost({
           title: 'My first post',
           caption: 'I just joined!'
        }, { transaction: t })

    })

}).catch(error => {
    // handle the error
})

So if something downstream, like the creation of a Post has errors, the creation of User will be rolled back. This is a critical feature, such as for building a bank application: if you run a query to update multiple bank account balances, failure to update any account balance should roll back all the updates, else you will end up with missing money.

Documentation

SequelizeJS has an extensive documentation, but can be difficult to understand sometimes. This is where its customisability does itself in -- there are just too many use-cases to cover. This is addressed partially by using its own Command Line Interface, which generates default codes for connecting to databases, and defining models. And then for more advanced usage, the better way I find is to look for github examples or stackoverflow answers.

Protip: Or like one of the main maintainers Mick Hanson likes to tell me, to read the tests. I have grown to find that this is a very useful way of figuring out the syntax not just for sequelize, but for all other modules.

Common complains about ORM - slow

Yes, feature-rich one like Sequelize adds a lot of overheads and can become very slow. But because it is very customisable; you can switch off some of it features, or even use raw queries for speed.

Augment ORM with query builders

Also, this is where Knex comes in if you do not want to use raw SQL, even though most ORMs will allow raw queries with very thin overheads. But then again, I already addressed the point about raw SQLs and burning in hell.

2. Bookshelf

Bookshelf is based on Knex, and came on as an early competitor to SequelizeJS. It values speed and simplicity, but compromises on features. It also supports major features like eager loading (just means loading a model with its associated models) and transactions. But in general, I don't find any real reasons to use Bookshelf over Sequelize.

3. TypeORM

(Note: @philschonholzer commented that TypeORM development appears to have stalled. It looks like a potential TypeScript replacement could be Mikro ORM. Having only browsed through the documentation, I think it’s okay but similarly not as mature as Sequelize.)

TypeORM is much newer than Sequelize and hence naturally has a more modern internal syntax (which really isn't something you need to bother). Also, as its name suggest, it is written in TypeScript, which attracted many collaborators from the TypeScript bandwagon.

Code Reliability

I am tempted to say that TypeScript codebases should usually be more reliable. But for TypeORM, at time of this writing it has less than half the amount of dependents compared to SequelizeJS (based on npmjs, TypeORM has 1.5k dependents, and SequelizeJS has 3.7k). But in terms of issues, TypeORM has more than double that of Sequelize (1.8k vs 800). However, suffice to say, both libraries are very heavily assailed by tests and are reliable enough.

Also, if you are developing TypeScript, then TypeORM is a viable piece so that you won't have a JS module disturbing "homogeneity", albeit I find this hardly a reason.

Some say, if you use TypeScript, choose TypeORM

You may have come across this reason if you read comments and articles online. I just don't think there is much logic in this reasoning.

Logically, beyond what is already abstracted away, you would still write TS normally when querying the models. Yes, you are missing out on some autocompletes. But for an ORM it hardly matters, and this is exactly my practical experience. Plus TypeScript can sometimes be quite inflexible and an overkill.

It is also more clunky to define models in TypeORM, which doesn't look very readable, and makes Javascript look like C#/Java:

import {Entity, PrimaryGeneratedColumn, Column} from "typeorm";

@Entity()
export class User {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    firstName: string;

    @Column()
    lastName: string;

}

Features and Documentation

Also, being newer, it has less features compared to Sequelize, and will take time to catch up. Likewise, its documentation also needs a lot more work. Nonetheless, the protip mentioned earlier remains true, which is you can always read the tests to get hints of how to use certain features.

Overall I think TypeORM can be selected over Sequelize given it has potential to improve over time. But to be sure, I will advise that you study their documentations before deciding if you can survive without some features that Sequelize has, but missing from TypeORM.

4. ObjectionJS

ObjectionJS is quite a stellar effort to simplify the syntax, where it could have been slightly more complicated in Sequelize or TypeORM. However, it is much, much leaner in terms of features, and provided no real advantages over Sequelize or TypeORM, except perhaps some performance gains with much less overheads.

Conclusion

I think in terms of the value proposition for Postgres, it is clear that in my experience, it will be one major component of your database mix. The main point is to use a database, whether SQL or not, for the right purpose. And that please don't shun away from Postgres (or any relational database for that matter) because you cannot write SQL -- because there are query builders and ORMs. And in terms of comparing what is out there, Sequelize and TypeORM are very viable choices that will cover what you need to build a fast, maintainable and modern application.