Sequelize, Postgres, JSON & JSONB

Here’s a few gotchas I’ve run into while working with sequelize. Writing them down to hopefully save someone else a few minutes.

Sequelize is a pretty awesome ORM for node, it’s giving me strong active record vibes and I like it!

It features

• seeds & migrations.
• Eager & lazy loading of associated models.
• Hooks (lifecycle methods / callbacks)
• Generators (npx sequelize-cli)

Sync your models

One feature which sequelize has over active record is the capability to ‘sync’ your models to the db. Effectively it generates a schema & applies it to the db (tho it’d be cool if a schema file was generated as well, maybe I haven’t found that yet).

Why’s that interesting? Well in the rails world you’d generate models & migrations and migrate the database as you go. And that’s the right way to do it, in production. But with a new projects where you’re iterating quickly on the basics, it’s a little overkill. Once you get to a v1 or earlier, you’ll want to start using migrations. But while your project only exists on localhost, it’s nice to have one spot to make changes (models) and have the changes propagate through.

And that’s what sync does.

Docs: https://sequelize.org/master/manual/model-basics.html#model-synchronization

There are options to force, alter or only create missing tables. So far I’ve used force, but gated the feature behind an env variable so that it doesn’t wipe the database on every code change (using nodemon for reloading).

Wouldn’t put this anywhere near my production code, but in early stage dev I love it. It makes the imagination to models process that little bit more instantaneous.

Possible Improvements:

While sync is great in dev, it would be awesome if there was a clearer path to switching from sync to migrations. As mentioned earlier, if sync could generate a schema (or just migrations directly) at a point in time (when you’re transitioning to production, versioning or bringing on more devs) that would make for a great workflow.

Presently it feels like one of the other proposition when it comes to migrations & sync, and there will inevitably be a transition point between them. Using generators to generate models (which generate a corresponding migration) immediately fall out of ‘sync’ when you change the model, and sync it. Where as I’d like to generate my models, work on them, and then generate migrations from the those models at a later stage.

Seeds feel similarly disconnected, in that the generated seed syntax utilises SQL queries to create & drop seed data, where as I’d prefer to use my models to do so, during the initial dev and then generate seeds / fixtures at a later stage. Definitely depends on the volume of data, and personal preferences, so mileage will vary there.

Someone has probably solved these already and I’ve just not found it yet, if you have a better way ping me on twitter.

Postgres JSON vs JSONB

But while sync is cool, JSON vs JSONB was downright annoying until I worked out what I was doing wrong.

Take note: When using sequelize, postgres & DataTypes.JSON your data is actually stored as plaintext, not json.

This leads to all sorts of annoying parsing & stringifying json in and out of the db.

Before I realised what was actually going on, thinking ‘why the hell did they decide this was a good idea?’, I’d setup get() & set() methods to parse & stringify the json.

I hadn’t chosen JSONB because I didn’t think I’d need to query these fields directly, so it seemed overkill. And I figured I could switch it at any stage later pretty quickly.

Upon consulting the docs again, I see:

Note for PostgreSQL

The JSON data type in PostgreSQL stores the value as plain text, as opposed to binary representation. If you simply want to store and retrieve a JSON representation, using JSON will take less disk space and less time to build from its input representation. However, if you want to do any operations on the JSON value, you should prefer the JSONB data type described below.

Docs: https://sequelize.org/master/manual/other-data-types.html

Also note: JSONB is only available with Postgres

So, it sounds like there are some performance benefits for using JSON, if you don’t need to query the data within the JSON directly. But if you choose to use JSON, you’ll need to manage serialising the data in & our of the database (which is only a few lines of code).