knex vs sequelize vs pg-promise vs bookshelf

Package	Downloads	Stars	Size	Issues	Publish	License

knex	2,661,008	20,169	874 kB	702	2 years ago	MIT
sequelize	2,147,690	30,311	2.91 MB	1,003	9 months ago	MIT
pg-promise	561,821	3,547	417 kB	1	2 months ago	MIT
bookshelf	81,067	6,365	-	237	6 years ago	MIT

Database Support

knex:
knex supports a wide range of SQL databases, including PostgreSQL, MySQL, SQLite, and Oracle. It is highly versatile and can be used with any SQL-compliant database.
sequelize:
sequelize supports multiple SQL databases, including PostgreSQL, MySQL, MariaDB, SQLite, and Microsoft SQL Server. It provides a unified API for working with different database dialects.
pg-promise:
pg-promise is specifically designed for PostgreSQL, offering deep integration with PostgreSQL features and capabilities. It is not suitable for other database types.
bookshelf:
bookshelf supports multiple databases through Knex.js, including PostgreSQL, MySQL, and SQLite. However, it does not provide native support for NoSQL databases.

Query Building

knex:
knex offers a powerful and flexible query building API that allows for programmatic construction of SQL queries. It supports chaining, raw queries, and provides fine-grained control over the generated SQL.
sequelize:
sequelize provides a high-level query API that abstracts away much of the SQL complexity. It supports chaining, raw queries, and includes features like pagination and filtering out of the box.
pg-promise:
pg-promise allows for both programmatic query building and raw SQL execution. It provides a flexible API for creating dynamic queries, but it does not include a built-in query builder like Knex.
bookshelf:
bookshelf provides a simple and intuitive query building interface, leveraging Knex.js under the hood. It allows for easy creation of complex queries, but it is not as flexible as Knex for low-level query manipulation.

Migrations

knex:
knex includes a powerful migration and seeding tool as part of the package. It provides a CLI for creating and running migrations, making it easy to manage database schema changes.
sequelize:
sequelize includes a built-in migration and seeding system, complete with a CLI for creating and running migrations. It also supports migration rollback and versioning.
pg-promise:
pg-promise does not provide built-in migration tools, but it can be integrated with third-party migration libraries like node-pg-migrate or db-migrate for schema management.
bookshelf:
bookshelf does not include a built-in migration tool, but it can be easily integrated with Knex.js migrations, allowing for schema management and versioning.

Associations

knex:
knex does not provide built-in support for associations, as it is primarily a query builder. However, associations can be implemented manually in the application logic or by using additional libraries.
sequelize:
sequelize provides robust support for associations, including one-to-one, one-to-many, and many-to-many relationships. It offers a rich API for defining and managing associations, including eager and lazy loading.
pg-promise:
pg-promise does not have built-in support for associations, but it allows for manual implementation of relationships in the database and application code. It provides flexibility for handling related data as needed.
bookshelf:
bookshelf supports various types of associations, including one-to-one, one-to-many, and many-to-many. It provides a simple API for defining and working with relationships between models.

Ease of Use: Code Examples

knex:

knex Example:

const knex = require('knex')({
  client: 'pg',
  connection: {
    host: 'localhost',
    user: 'user',
    password: 'password',
    database: 'mydb'
  }
});

// Query example
knex('users').where({ id: 1 }).first().then(user => {
  console.log(user);
});

sequelize:

sequelize Example:

const { Sequelize, DataTypes } = require('sequelize');
const sequelize = new Sequelize('postgres://user:password@localhost:5432/mydb');

// Define a model
const User = sequelize.define('User', {
  name: { type: DataTypes.STRING },
  email: { type: DataTypes.STRING },
});

// Fetch a user
User.findByPk(1).then(user => {
  console.log(user.toJSON());
});

pg-promise:

pg-promise Example:

const pgp = require('pg-promise')();
const db = pgp({
  host: 'localhost',
  user: 'user',
  password: 'password',
  database: 'mydb'
});

// Query example
db.one('SELECT * FROM users WHERE id = $1', 1)
  .then(user => {
    console.log(user);
  });

bookshelf:

bookshelf Example:

const Bookshelf = require('bookshelf');
const knex = require('knex')({
  client: 'pg',
  connection: {
    host: 'localhost',
    user: 'user',
    password: 'password',
    database: 'mydb'
  }
});

const bookshelf = Bookshelf(knex);

// Define a model
const User = bookshelf.model('User', {
  tableName: 'users',
});

// Fetch a user
User.where({ id: 1 }).fetch().then(user => {
  console.log(user.toJSON());
});

Database Support

Query Building

Migrations

Associations

Ease of Use: Code Examples

knex.js

Examples

TypeScript example

Usage as ESM module