Choosing the Right Database Layer for Node.js Applications
knex is a SQL query builder that provides a flexible interface for constructing database queries without enforcing an object-relational mapping (ORM) structure. sequelize, typeorm, and bookshelf are full ORMs that map database tables to JavaScript classes or objects, handling relationships and validations automatically. orange-orm is a lesser-known alternative in this space. These tools help developers interact with databases like PostgreSQL, MySQL, and SQLite using JavaScript or TypeScript, abstracting away raw SQL while offering varying levels of control and convention.
Npm Package Weekly Downloads Trend
Github Stars Ranking
Stat Detail
Choosing the Right Database Layer for Node.js Applications
When building Node.js backends, selecting the right database layer is a critical architectural decision. The options range from raw query builders to full-featured Object-Relational Mappers (ORMs). Each tool offers a different balance of control, convenience, and type safety. Let's examine how bookshelf, knex, orange-orm, sequelize, and typeorm handle common development tasks.
π Query Construction: Raw SQL vs. Builders vs. ORM Methods
knex focuses on building queries fluently without mapping results to models. You chain methods to construct SQL.
// knex: Fluent query builder
const users = await knex('users')
.where('active', true)
.select('id', 'name');
sequelize uses model methods to generate queries behind the scenes.
// sequelize: Model-based query
const users = await User.findAll({
where: { active: true },
attributes: ['id', 'name']
});
typeorm provides a Repository pattern or Entity Manager to handle queries.
// typeorm: Repository pattern
const users = await userRepository.find({
where: { active: true },
select: ['id', 'name']
});
bookshelf relies on models that wrap Knex queries internally.
// bookshelf: Model query
const users = await User.where('active', true)
.fetchAll({ columns: ['id', 'name'] });
orange-orm typically follows a standard model query approach similar to other ORMs, though documentation is sparser.
// orange-orm: Model query (generalized based on typical ORM patterns)
const users = await OrangeModel.find({
where: { active: true },
fields: ['id', 'name']
});
π Handling Relationships: Joins and Associations
knex requires you to manually write joins. It does not understand relationships between tables.
// knex: Manual join
const posts = await knex('posts')
.join('users', 'posts.user_id', 'users.id')
.where('users.active', true);
sequelize defines associations in the model setup and handles joins automatically.
// sequelize: Defined association
const posts = await Post.findAll({
include: [{ model: User, where: { active: true } }]
});
typeorm uses relations defined in entity classes with decorators.
// typeorm: Entity relation
const posts = await postRepository.find({
relations: ['user'],
where: { user: { active: true } }
});
bookshelf uses the withRelated option to eager load relationships defined on the model.
// bookshelf: Eager loading
const posts = await Post.where('user_id', '!=', null)
.fetchAll({ withRelated: ['user'] });
orange-orm generally supports relationship definitions, but implementation details vary by version.
// orange-orm: Relation loading (generalized)
const posts = await OrangePost.find({
populate: ['user']
});
π Migrations: Managing Schema Changes
knex has a built-in migration system that is widely used even by projects not using Knex for queries.
// knex: Migration file
exports.up = function(knex) {
return knex.schema.createTable('users', (table) => {
table.increments('id');
table.string('name');
});
};
sequelize includes a CLI for generating and running migration files.
// sequelize: Migration file
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('users', {
id: { type: Sequelize.INTEGER, primaryKey: true },
name: { type: Sequelize.STRING }
});
}
};
typeorm can generate migrations automatically from entity changes or be written manually.
// typeorm: Migration class
export class CreateUsers1234567890123 implements MigrationInterface {
public async up(queryRunner: QueryRunner): Promise<void> {
await queryRunner.createTable(new Table({ name: 'users', columns: [...] }));
}
}
bookshelf does not have its own migration tool; it relies on Knex for migrations.
// bookshelf: Uses Knex migrations
// Same as knex example above
exports.up = function(knex) {
return knex.schema.createTable('users', (table) => {
table.increments('id');
});
};
orange-orm migration support depends on the specific version and may require external tools.
// orange-orm: Migration (varies by implementation)
// Often requires manual SQL or external migration runners
await orm.migrate('create_users_table');
π TypeScript Support: Static Typing Integration
knex supports TypeScript but queries are dynamic. You often need to define interfaces for results.
// knex: TypeScript interface
interface User { id: number; name: string; }
const users = await knex<User>('users').where('id', 1);
sequelize added TypeScript support later. It requires defining models with generic types.
// sequelize: TypeScript model
const User = sequelize.define<User>('User', {
name: { type: DataTypes.STRING }
});
typeorm was built with TypeScript in mind. Entities are classes with decorators.
// typeorm: TypeScript entity
@Entity()
export class User {
@PrimaryGeneratedColumn()
id: number;
@Column()
name: string;
}
bookshelf has community type definitions but is primarily JavaScript-focused.
// bookshelf: TypeScript definition
interface User extends Bookshelf.Model<User> {
id: number;
name: string;
}
orange-orm TypeScript support is less documented and may vary.
// orange-orm: TypeScript (if supported)
interface OrangeUser {
id: number;
name: string;
}
π Lifecycle Hooks: Before Save and After Fetch
knex does not have model lifecycle hooks because it has no models.
// knex: No hooks
// Must handle logic manually before/after query execution
await knex('users').insert({ name: 'Alice' });
console.log('Inserted manually');
sequelize supports hooks like beforeCreate, afterUpdate, etc.
// sequelize: Hooks
User.addHook('beforeCreate', (user) => {
user.name = user.name.toUpperCase();
});
typeorm uses listeners and subscribers on entities.
// typeorm: Listeners
@BeforeInsert()
capitalizeName() {
this.name = this.name.toUpperCase();
}
bookshelf is known for its robust event-based lifecycle.
// bookshelf: Events
const User = bookshelf.Model.extend({
tableName: 'users',
initialize: function() {
this.on('creating', this.capitalizeName);
}
});
orange-orm may support hooks, but consistency varies.
// orange-orm: Hooks (generalized)
OrangeModel.on('beforeSave', (data) => {
// modify data
});
π Summary: Key Differences
| Feature | knex | sequelize | typeorm | bookshelf | orange-orm |
|---|---|---|---|---|---|
| Type | Query Builder | ORM | ORM | ORM | ORM |
| TS Support | Good | Moderate | Excellent | Limited | Variable |
| Migrations | Built-in | Built-in | Built-in | Via Knex | Varies |
| Relationships | Manual | Auto | Auto | Auto | Auto |
| Maintenance | High | High | High | Low | Low |
π‘ The Big Picture
knex is the foundation. It gives you control. Use it when you want to write SQL without the magic of an ORM getting in your way. It is also the engine behind bookshelf.
sequelize is the veteran. It has been around for a long time and supports almost every SQL database. It is a safe choice for JavaScript projects that need reliability and extensive features.
typeorm is the modern TypeScript choice. If your team lives in TypeScript, this integrates best with your workflow. It enforces structure through classes and decorators.
bookshelf is the legacy option. It is built on Knex but adds an ORM layer. Unless you are maintaining an older codebase, there are more active communities elsewhere.
orange-orm is the niche option. It lacks the ecosystem of the others. Use it only if you have a specific reason that the larger libraries cannot meet.
Final Thought: For new projects, typeorm is best for TypeScript stacks, while sequelize remains strong for JavaScript. If you prefer writing SQL, knex is the industry standard for query building.
How to Choose: bookshelf vs knex vs orange-orm vs sequelize vs typeorm
- bookshelf:
Choose
bookshelfif you are maintaining a legacy application that already relies on its specific model patterns built on top of Knex. It is generally not recommended for new projects due to lower activity compared to modern alternatives. Stick with this only if you need its specific event-driven model lifecycle without migrating to a more active ecosystem. - knex:
Choose
knexif you want maximum control over your SQL queries without the overhead of an ORM. It is ideal for teams that prefer writing explicit queries over managing complex model relationships. This is the best fit for microservices or applications where performance and query precision matter more than developer convenience features like auto-mapping. - orange-orm:
Choose
orange-ormonly after careful verification of its current maintenance status, as it lacks the widespread adoption of other options. It may suit niche requirements where specific, lightweight behavior is needed, but community support will be limited. For most production scenarios, established libraries like Sequelize or TypeORM are safer bets due to their long-term stability. - sequelize:
Choose
sequelizeif you need a battle-tested ORM with support for a wide variety of SQL dialects and extensive documentation. It works well for teams that want a balance between convention and configuration, with strong support for transactions and relationships. This is a solid default choice for JavaScript-heavy teams that do not require strict TypeScript typing out of the box. - typeorm:
Choose
typeormif your project is built with TypeScript and you want strong typing integrated directly into your database models. It uses decorators and the Active Record or Data Mapper patterns to keep code organized and type-safe. This is the preferred option for modern NestJS applications or any stack where compile-time checks on database structures are a priority.
Popular Comparisons
README for bookshelf
bookshelf.js
Bookshelf is a JavaScript ORM for Node.js, built on the Knex SQL query builder. It features both Promise-based and traditional callback interfaces, transaction support, eager/nested-eager relation loading, polymorphic associations, and support for one-to-one, one-to-many, and many-to-many relations.
It is designed to work with PostgreSQL, MySQL, and SQLite3.
Website and documentation. The project is hosted on GitHub, and has a comprehensive test suite.
Introduction
Bookshelf aims to provide a simple library for common tasks when querying databases in JavaScript, and forming relations between these objects, taking a lot of ideas from the Data Mapper Pattern.
With a concise, literate codebase, Bookshelf is simple to read, understand, and extend. It doesn't force you to use any specific validation scheme, and provides flexible, efficient relation/nested-relation loading and first-class transaction support.
It's a lean object-relational mapper, allowing you to drop down to the raw Knex interface whenever you need a custom query that doesn't quite fit with the stock conventions.
Installation
You'll need to install a copy of Knex, and either mysql, pg, or sqlite3 from npm.
$ npm install knex
$ npm install bookshelf
# Then add one of the following:
$ npm install pg
$ npm install mysql
$ npm install sqlite3
The Bookshelf library is initialized by passing an initialized Knex client instance. The Knex documentation provides a number of examples for different databases.
// Setting up the database connection
const knex = require('knex')({
client: 'mysql',
connection: {
host : '127.0.0.1',
user : 'your_database_user',
password : 'your_database_password',
database : 'myapp_test',
charset : 'utf8'
}
})
const bookshelf = require('bookshelf')(knex)
// Defining models
const User = bookshelf.model('User', {
tableName: 'users'
})
This initialization should likely only ever happen once in your application. As it creates a connection pool for the current database, you should use the bookshelf instance returned throughout your library. You'll need to store this instance created by the initialize somewhere in the application so you can reference it. A common pattern to follow is to initialize the client in a module so you can easily reference it later:
// In a file named, e.g. bookshelf.js
const knex = require('knex')(dbConfig)
module.exports = require('bookshelf')(knex)
// elsewhere, to use the bookshelf client:
const bookshelf = require('./bookshelf')
const Post = bookshelf.model('Post', {
// ...
})
Examples
Here is an example to get you started:
const knex = require('knex')({
client: 'mysql',
connection: process.env.MYSQL_DATABASE_CONNECTION
})
const bookshelf = require('bookshelf')(knex)
const User = bookshelf.model('User', {
tableName: 'users',
posts() {
return this.hasMany(Posts)
}
})
const Post = bookshelf.model('Post', {
tableName: 'posts',
tags() {
return this.belongsToMany(Tag)
}
})
const Tag = bookshelf.model('Tag', {
tableName: 'tags'
})
new User({id: 1}).fetch({withRelated: ['posts.tags']}).then((user) => {
console.log(user.related('posts').toJSON())
}).catch((error) => {
console.error(error)
})
Official Plugins
- Virtuals: Define virtual properties on your model to compute new values.
- Case Converter: Handles the conversion between the database's snake_cased and a model's camelCased properties automatically.
- Processor: Allows defining custom processor functions that handle transformation of values whenever they are
.set()on a model.
Community plugins
- bookshelf-cascade-delete - Cascade delete related models on destroy.
- bookshelf-json-columns - Parse and stringify JSON columns on save and fetch instead of manually define hooks for each model (PostgreSQL and SQLite).
- bookshelf-mask - Similar to the functionality of the {@link Model#visible} attribute but supporting multiple scopes, masking models and collections using the json-mask API.
- bookshelf-schema - A plugin for handling fields, relations, scopes and more.
- bookshelf-signals - A plugin that translates Bookshelf events to a central hub.
- bookshelf-paranoia - Protect your database from data loss by soft deleting your rows.
- bookshelf-uuid - Automatically generates UUIDs for your models.
- bookshelf-modelbase - An alternative to extend
Model, adding timestamps, attribute validation and some native CRUD methods. - bookshelf-advanced-serialization - A more powerful visibility plugin, supporting serializing models and collections according to access permissions, application context, and after ensuring relations have been loaded.
- bookshelf-plugin-mode - Plugin inspired by the functionality of the {@link Model#visible} attribute, allowing to specify different modes with corresponding visible/hidden fields of model.
- bookshelf-secure-password - A plugin for easily securing passwords using bcrypt.
- bookshelf-default-select - Enables default column selection for models. Inspired by the functionality of the {@link Model#visible} attribute, but operates on the database level.
- bookshelf-ez-fetch - Convenient fetching methods which allow for compact filtering, relation selection and error handling.
- bookshelf-manager - Model & Collection manager to make it easy to create & save deep, nested JSON structures from API requests.
Support
Have questions about the library? Come join us in the #bookshelf freenode IRC channel for support on knex.js and bookshelf.js, or post an issue on Stack Overflow.
Contributing
If you want to contribute to Bookshelf you'll usually want to report an issue or submit a pull-request. For this purpose the online repository is available on GitHub.
For further help setting up your local development environment or learning how you can contribute to Bookshelf you should read the Contributing document available on GitHub.
F.A.Q.
Can I use standard node.js style callbacks?
Yes, you can call .asCallback(function(err, resp) { on any database operation method and use the standard (err, result) style callback interface if you prefer.
My relations don't seem to be loading, what's up?
Make sure to check that the type is correct for the initial parameters passed to the initial model being fetched. For example new Model({id: '1'}).load([relations...]) will not return the same as new Model({id: 1}).load([relations...]) - notice that the id is a string in one case and a number in the other. This can be a common mistake if retrieving the id from a url parameter.
This is only an issue if you're eager loading data with load without first fetching the original model. new Model({id: '1'}).fetch({withRelated: [relations...]}) should work just fine.
My process won't exit after my script is finished, why?
The issue here is that Knex, the database abstraction layer used by Bookshelf, uses connection pooling and thus keeps the database connection open. If you want your process to exit after your script has finished, you will have to call .destroy(cb) on the knex property of your Bookshelf instance or on the Knex instance passed during initialization. More information about connection pooling can be found over at the Knex docs.
How do I debug?
If you pass debug: true in the options object to your knex initialize call, you can see all of the query calls being made. You can also pass that same option to all methods that access the database, like model.fetch() or model.destroy(). Examples:
// Turning on debug mode for all queries
const knex = require('knex')({
debug: true,
client: 'mysql',
connection: process.env.MYSQL_DATABASE_CONNECTION
})
const bookshelf = require('bookshelf')(knex)
// Debugging a single query
new User({id: 1}).fetch({debug: true, withRelated: ['posts.tags']}).then(user => {
// ...
})
Sometimes you need to dive a bit further into the various calls and see what all is going on behind the scenes. You can use node-inspector, which allows you to debug code with debugger statements like you would in the browser.
Bookshelf uses its own copy of the bluebird Promise library. You can read up here for more on debugging Promises.
Adding the following block at the start of your application code will catch any errors not otherwise caught in the normal Promise chain handlers, which is very helpful in debugging:
process.stderr.on('data', (data) => {
console.log(data)
})
How do I run the test suite?
See the CONTRIBUTING document on GitHub.
Can I use Bookshelf outside of Node.js?
While it primarily targets Node.js, all dependencies are browser compatible, and it could be adapted to work with other javascript environments supporting a sqlite3 database, by providing a custom Knex adapter. No such adapter exists though.
Which open-source projects are using Bookshelf?
We found the following projects using Bookshelf, but there can be more:
- Ghost (A blogging platform) uses bookshelf. [Link]
- Soapee (Soap Making Community and Resources) uses bookshelf. [Link]
- NodeZA (Node.js social platform for developers in South Africa) uses bookshelf. [Link]
- Sunday Cook (A social cooking event platform) uses bookshelf. [Link]
- FlyptoX (Open-source Node.js cryptocurrency exchange) uses bookshelf. [Link]
- And of course, everything on here use bookshelf too.