Best ORM for NodeJS Project with MySQL DB

[justin hyland]

I'm somewhat new to NodeJS, I come from the traditional LAMP stack world, most of my apps were in CodeIgniter, and I was beginning to work with Laravel, which has an amazing ORM, Eloquent.

I'm switching my primary project over from PHP/Apache to NodeJS/ExpressJS, and since I'm early enough in my project, I wanted to be sure I am choosing the best ORM for the job.

The ones I have found so far, are SequelizeJS, BookshelfJS, ORM, and ORM2  

Currently, I'm tinkering around with SequelizeJS, and thats the only one I've messed with so far. I was told that it's the best one out there, but the people that have told me that, haven't tried anything else, or researched anything else either, lol.

I like SequelizeJS, it seems pretty good, but I've ran into a few limitations, all of which could be solved if it was setup so I could write plugins or extensions for it, but its just not setup like that. I do like the 'paranoid' feature (adds a timestamp to the deletedOn column, and excludes it from future searches, unless paranoid is disabled), which unless I overlooked it, I didn't see that in the other ORM's.

Any topics or articles I've found online about any of these ORM's are pretty dated and review very old versions of the ORM's, so I was just hoping to get some input on what others use in their projects.

My Preferences (But not required):

• Good documentation
• Plugin support
• Something similar to the paranoid setting found in SequelizeJS
• Very good support for relationships
• Support for Regex searching (RLIKE, REGEXP)
• Support for both ENUM and SET column types

SequelizeJS seems to follow most of those, but I have to mess with some of the queries or models to get it to work right, if theres an ORM that does all of the above out of the box, that would be great! 

Thank you

-J

[justin hyland]

One more thing for the Preferences (I knew I was forgetting something)

• SQL Injection prevention

[justin hyland]

Well, incase anyone wanted to know (which it doesn't seem so).. I ended up picking BookmarkJS/KnexJS, Reasons:

1. The documentation is much better, lol
2. I like how the queries are structured. In Sequelize, it's just a large object, using the query operators as keys. In BookshelfJS, You actually chain KnexJS methods together... Just seems a lot easier to work with.
3. Bookshelf allows plugins, (and includes some by default). The only real plus I had on the side of Sequelize, was I liked the "paranoid" feature (which is soft deleting of rows), However, since BookshelfJS allows for plugins, theres an awesome plugin for it! Which supports delete AND restore! 
4. BookshelfJS/KnexJS is modeled after Laravels Eloquent ORM, and since I'm coming from the PHP world and have a little experience with laravel, its much easier to pick up
5. It has better support for more column types by default, without having to override some stuff or add some hacked JS code
6. And for the last one (which I thought was actually a negative for BookshelfJS at first, and positive for SequelizeJS, but eventually I realized its the opposite), you don't have to define the columns/attributes of the table within the model. In SequelizeJS, when you define the model, you have to define the attributes/columns as well, which is useful if you want to use that model to create the table as well. But in my experience, you hardly ever have to create the table, its typically already created before the script is running (via installations, imports, restores, etc). In KnexJS, you can create a migration file, which can create/drop the tables, and thats where you would define the table columns, but you don't actually need to define them in the model used in BookshelfJS.. and when I started to think about it, that really makes sense.. I mean, why would you need to do that anyways? Maybe someone can think of a reason behind it and prove me wrong on this one, but I couldn't think of one.

Well, if anyone else has any input, id be glad to hear it! Thanks!

-J

On Sunday, December 20, 2015 at 3:18:00 PM UTC-7, justin hyland wrote:

[Miguel Coquet]

Thanks for the replying back with what you found and your thought process for your decision. This may seem trivial to some, but contributions like this always help.

Good luck

Miguel Coquet

--
Job board: http://jobs.nodejs.org/
New group rules: https://gist.github.com/othiym23/9886289#file-moderation-policy-md
Old group rules: https://github.com/joyent/node/wiki/Mailing-List-Posting-Guidelines
---
You received this message because you are subscribed to the Google Groups "nodejs" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nodejs+un...@googlegroups.com.
To post to this group, send email to nod...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/nodejs/885d2eca-3e50-4424-956e-eeda353e038d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Tim Davis]

Been using Knex for a while and keep finding powerful features. After using bookshelf for a while I'm back to just Knex. Love the chainable api too. The transaction support is also rad. At first I liked bookshelf. Still do :-). The relations loading is very convenient and the backbone ish models are familiar and intuitive. But so far just using views and chained queries is convenient enough for me.

One thing that was a pain for bookshelf models was dealing with denormalised data. I guess you could parse payloads into db rows but by then, you may as well just call knex.insert instead of forging models.

That along with object key formatting difficulties (my_column in db myColumn in js) made me switch to Knex alone. It is simple enough to just pass rows through a function before/after data access.

Let us know how it goes!

[justin hyland]

Thanks for the replying back with what you found and your thought process for your decision. This may seem trivial to some, but contributions like this always help.

 

For sure, I wish I found something like this when I was comparing the two, haha. Im going to learn a little more about BookshelfJS/KnexJS, and leave a review on my blog, ill post a link in here when its done!

 

One thing that was a pain for bookshelf models was dealing with denormalised data. I guess you could parse payloads into db rows but by then, you may as well just call knex.insert instead of forging models.

Not quite sure what you mean by 'denormalised data'? 

That along with object key formatting difficulties (my_column in db myColumn in js) made me switch to Knex alone. It is simple enough to just pass rows through a function before/after data access.

 I can agree with that one, I was going to write a plugin that uses lodashes camelCase and snakeCase to convert the DB columns and tables from snake to camel, and let you refer to them in the camel case form when updating/searching/inserting/deleting rows.

 

[Tim Davis]

Not quite sure what you mean by 'denormalised data'? 

Well this could be more of a Tim problem than a Bookshelf problem so please bear with me :-P

I have "customers" and "suppliers". In some cases a supplier is also a customer, blah blah. Their shared properties like name, phone, email have been factored out into "parties". Specifics like "customers"."sellers_permit" and "suppliers"."account_number" are factored into their own tables. Customers and suppliers are read and populated into forms including their party data, so when JSON is sent to my server, the various properties need to be picked and inserted into the correct tables.

This is just one data management thing I'm offloading to the server. Anyway, the Modeling in my app happens on the client and my API just does persistence, not much behavior.