DEV Community

Objection + Knex = Painless PostgreSQL in your Node App

Ali Spittel on February 20, 2018

It's no secret that I'm a total PostgreSQL fangirl -- I rarely see a use case for using a different database, especially with the support for array...

Read full post

Sami Koskimäki • Apr 3 '18

Great post, thanks for writing it! You have a small error in the usage of allowInsert method though. It can only be used to limit relations when inserting/upserting graphs using insertGraph and upsertGraph methods. It has no effect with the normal insert method.

Daniel HB • Feb 21 '18

How does Knex compare to Sequelize?

Dian Fay • Feb 22 '18 • Edited

Like apples and oranges, basically. Sequelize, like its peers Objection and Bookshelf, is a full-fledged O/RM which gives you a well-defined, database-agnostic API at the cost of maintaining parallel data models, somewhat restricted flexibility (the stateful entity metaphor O/RMs encourage is a very limited view of what your database can actually do), lowest-common-denominator feature sets, and winding up writing SQL anyway when things get complicated, which they always do. As @rhymes mentioned below, I run a competing project with a different approach to data access, so take my opinion for what it's worth :)

Knex, meanwhile, is a query builder which generates SQL statements. It's lower-level and doesn't care about your data model; it's strictly a way to build SQL in JavaScript.

Sami Koskimäki • Nov 25 '18

Based on this comment, I'm guessing you haven't actually used objection. The main design goal of objection is to NOT restrict flexibility or limit access to DB features.

Dian Fay • Nov 25 '18 • Edited

I have to admit I have not; the last time I had to search for a new data access framework for Node was a matter of days before you released 0.1.0 :) I did at least do the bare minimum of reading your documentation and looking at examples to see how it addressed the fundamental weaknesses of object-relational mapping before I mentioned it, though!

I consider models inherently limiting. To be completely fair to Objection, a minimally functional model is maybe half a dozen lines long, and patch takes care of one of the major restrictions of the active record pattern, but recapitulating your database architecture before you can use it necessarily adds a structural rigidity (if badly done, it can be outright brittle) and a certain inertia. This isn't something that can be laid at your or anyone else's doorstep; it's the price of working with record graphs in application code, as implicit in the object-relational mapper pattern. You can argue that it is or can be worth it, but that doesn't mean you don't have to pay it.

Sami Koskimäki • Apr 11 '19 • Edited

First of all, objection is built on knex and every possible operation you can start with it return a query builder that you can modify in any way. You are always 100% in control of what SQL gets executed. There's absolutely no restricting "common-denominator" API. With objection you can always do everything you can do with a query builder, but also a lot of other things. Objection's API is a superset of SQL instead of common-denominator.

Your other point was that models are limiting. Let's test that theory. Let's say you have this model in objection

class Person extends Model {
  static tableName = 'persons'
}

Then you have a bunch of queries all over your database

const { Person }  = require('./models/person')

...

Person.query().select('...').where('...')

How is that any different from using a query builder? Instead of mentioning the name of the table in the query, you use the model. The table name and the model class reference are just as easy to change.

Let's say the name of the table changes. All you need to do is to change the tableName property. What if the columns names change? You change the queries, just like you would with a query builder. What if you want to change the model name? Well, search and replace the model name. How about relations? Again, change the model. If you use a plain query builder, you need to update the foreign keys in million places instead.

Using objection's models adds no overhead on top of using a query builder whatsoever. If anything, it removes a great deal of it.

I really don't see what you mean here. You still seem to be talking about your experiences with other ORMs.

krckyboy • May 30 '19

I have to say that Objection.js has by far the best docs that I've ever seen so far. Good job!

Antonin J. (they/them) • Feb 21 '18

This is really cool! I actually use Postgres/Knex/Objection at work in production, and have for about a year and a half and it's an amazing tool. One of my favorite features of Objection is the use of the get and set in the class which you mentioned.

Combined with lodash, it's really easy to do something like:

res.send(_.pick(post, [
  ...post,
  computedProperty,
  computedProperty2,
  whateverElse
]);

I wanted to ask, why did you end up trying out Objection over, let's say, Bookshelf which was built by the same author as Knex and at around the same time as Knex? We migrated from Bookshelf to Objection so I think Objection is the right choice, just wondering though.

Ali Spittel • Mar 2 '18

Ah awesome! I had a friend recommend Objection, so that's why I went with that one! I hadn't heard great things about Bookshelf.

Lars Wächter • Feb 20 '18

Perfect timing! I like to add ORM to my node apps in the next days as well. Thanks

rhymes • Feb 20 '18 • Edited

Now that you tested an ORM library I guess you have to test @dmfay Massive.js based on the Data Mapper pattern as well :-D

Great post, as usual!

Ali Spittel • Mar 2 '18

Ah! Will look into that!

Sholto Maud • Feb 21 '18

Nice Post Ali,

Do you have a link to how best integrate "auth"?

Ali Spittel • Mar 2 '18

Ah that would be awesome! I looked a little bit but didn't see anything. I think you could use Passport, though (kinda like this: code.tutsplus.com/tutorials/using-...)

Belhassen Chelbi • Feb 20 '18

loved it

Gleb Bahmutov • Feb 20 '18

I highly recommend using "knex-migrate" github.com/sheerun/knex-migrate#re... to do Knex migrations - a very nice tool with better commands for running / rolling / seeing migrations.

Ali Spittel • Mar 2 '18

Oh cool! Didn't know this existed!

Manuel Romero • Feb 21 '18

Great post, Ali!

Arlei F. Farnetani Junior • Nov 6 '18

Excellent post! Thanks a lot!

ilya • Mar 20 '21

Hello
Great and useful post...
I am going to import database with nodejs.
If you know, Please teach me.
Thank you very much.
And i am following you.
want to meet on cha.

Prashant Nirgun • Jul 1 '21

Need help when we have Objection and its handling crud methods why do we overwride it here manually ?

Where and how to write custom end point for contacts should we write in class or service ?

Lazarus Lazaridis • Mar 7 '18

Great post, thank you!

MrScX • Aug 10 '19

BTW in exports.up and exports.down you pass in Promise as an argument. That throws an error. Removing that fixes it. Also, knex migrate:make doesn't pass that argument in its auto-generated file.

Kofi Oghenerukevwe H. • Dec 14 '20 • Edited

Saw the title and just came here to like it and say YESSSSSSSSSS!!!!!!!!!!!!! as someone who's used Objection and Knex before.

Sajal Shrestha • Dec 25 '20

Really helpful to start off with ORM. Thank you