Recently I started work on a Node project that was built using Sequelize with MySQL. It was chosen to ease the transition from an earlier version written with Ruby on Rails. The original's ActiveRecord models mapped quite closely to their Sequelize equivalents, which got things started smoothly enough.
Although Sequelize had some API quirks that didn't feel very idiomatic alongside other Node code, the developers have hungrily accepted pull requests and it's emerging as a reasonable ORM solution. However, like many others in the Node community I feel uncomfortable with ORM.
Why? Well, some of us have learned how to use relational databases correctly. Joining an established project that uses ORM only to find there's no referential integrity or sensible indexes is to be expected these days, as programmers have moved away from caring about databases to application-level schemas. I've had my head down in MongoDB/Mongoose and Redis code for the last few years, but relational databases aren't going away any time soon so either programmers need to get the hang of them or we need better database modules.
This all prompted me to look at alternative solutions to relational databases in Node. First, I broke down the problem into separate areas:
- Driver: The module that manages database connections, sends queries, and responds with data
- Abstraction layer: Provide tools for escaping queries to avoid SQL injection attacks, and wrap multiple drivers so it's easy to port applications between MySQL/PostgreSQL/SQLite
- Validator: Validates data against a schema prior to sending it to the database. Aids with the generation of human-readable error messages
- Schema management: Keep schema up-to-date when fields are added or removed
Some projects won't need to support all of these areas -- you can mix and match them as needed. I prefer to create simple veneer-like "model" classes that wrap more low-level database operations. This works well in a web application where it can be make sense to decouple the HTTP layer from the database.
The mysql and pg modules are actively maintained, and are usually required by "abstraction layer" modules and ORM solutions.
A note about configuration: when it comes to connecting to the database, I strongly prefer modules that support connection URIs. It makes it a lot easier to deploy web applications to services like Heroku, because a single environmental variable can be set that contains the connection details for your production database.
EventEmitter descendent that allows operations to be pushed to an internal stack.
The author of the pg module, Brian Carlson, who occasionally stops by the
#dailyjs IRC channel on Freenode, recently mentioned his new relational project that aims to provide a saner approach to ORM in Node. This module feels more like an abstraction layer API, but it's gunning to be a formidable new ORM solution.
There are some popular libraries that tackle the abstraction layer, including any-db and massive.
I usually find myself dealing with errors in web forms, so anything that makes error handling easier is an advantage. Validation and schemas are closely related, which is why ORM libraries usually combine them.
Validator has some simple tools for validating data types, but it also has optional Express middleware that makes it easy to drop into a web application. Another similar project is conform by Oleg Korobenko.
user .select(user.id) .from(user) .where( user.name.equals('boom').and(user.id.equals(1)) ).or( user.name.equals('bang').and(user.id.equals(2)) ).toQuery();
He's using this to build the previously mentioned relational module as well.
Sequelize has an API for managing database migrations. It can migrate to a given version and back, and it can also "sync" a model's schema to the database (creating the table if it doesn't exist).
There are also dedicated migration modules, like db-migrate by Jeff Kunkle.
The Node community has created a rich set of modules for working with relational databases, and although there's a strong anti-ORM sentiment interesting projects like relational are appearing.
Although these modules don't address my concerns about the way in which ORM gets used with apathy towards best practices, it's promising to see lower-level modules that can be used as building blocks for more application-specific solutions.
All of this has come at a time when relational database projects are adapting, changing, and even growing in popularity despite the recent attention the NoSQL movement has been given. PostgreSQL is going from strength to strength, and Heroku provides it as default. MariaDB is a drop-in replacement for MySQL that has a non-blocking Node module. SQLite is probably technically growing in usage as it backs Core Data in iCloud applications -- Android developers also use SQLite.
Let other readers know how you deal with SQL-backed Node projects in the comments!