Selecting Database Tools for Node.js Backends
knex, mysql2, pg, sequelize, and sqlite3 are essential tools for managing data in Node.js applications, but they operate at different layers of abstraction. pg and mysql2 are low-level drivers that speak directly to PostgreSQL and MySQL databases, offering maximum control. sqlite3 provides similar low-level access for local file-based databases. knex sits in the middle as a query builder, adding a consistent syntax across different SQL dialects. sequelize is a full Object-Relational Mapper (ORM) that abstracts SQL entirely into JavaScript objects and classes. Choosing the right one depends on how much control you need versus how much convenience you want.
Npm Package Weekly Downloads Trend
Github Stars Ranking
Stat Detail
Choosing Database Tools for Node.js Backends
When building server-side JavaScript applications, selecting the right database tool is a critical architectural decision. The packages knex, mysql2, pg, sequelize, and sqlite3 all solve the problem of data persistence, but they approach it from different angles. Some are direct drivers, some are query builders, and one is a full ORM. Understanding these layers helps you avoid over-engineering or painting yourself into a corner. Let's compare how they handle connection, queries, modeling, and maintenance.
š Connecting to the Database
Setting up a connection is the first step, and the complexity varies significantly between raw drivers and higher-level tools.
pg requires you to create a pool manager to handle connections efficiently.
- You must configure host, user, and database name explicitly.
- The pool handles opening and closing connections for you.
// pg: Connection Pool
const { Pool } = require('pg');
const pool = new Pool({
user: 'dbuser',
host: 'localhost',
database: 'mydb',
password: 'secretpassword',
port: 5432,
});
mysql2 also uses a pool but offers a promise wrapper for cleaner async code.
- Configuration is similar to
pgbut specific to MySQL ports. - You access the promise interface to avoid callback hell.
// mysql2: Promise Pool
const mysql = require('mysql2/promise');
const pool = mysql.createPool({
host: 'localhost',
user: 'dbuser',
password: 'secretpassword',
database: 'mydb',
});
sqlite3 connects to a file path instead of a network host.
- It does not use a pool by default in the basic package.
- Connections are often single-threaded and file-locked.
// sqlite3: File Connection
const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('./mydb.sqlite');
knex abstracts the connection config behind a client selector.
- You specify the client type (e.g., 'pg', 'mysql', 'sqlite3').
- It creates the pool internally based on your configuration.
// knex: Unified Config
const knex = require('knex')({
client: 'pg',
connection: {
host: 'localhost',
user: 'dbuser',
password: 'secretpassword',
database: 'mydb',
},
});
sequelize uses a single connection URI or object to initialize the ORM.
- It sets up pooling and authentication automatically.
- You define the dialect to match your database engine.
// sequelize: ORM Initialization
const { Sequelize } = require('sequelize');
const sequelize = new Sequelize('postgres://dbuser:secretpassword@localhost:5432/mydb');
š Running Queries
The way you fetch data defines your daily developer experience. Raw drivers give you SQL strings, while ORMs give you methods.
pg executes raw SQL strings with parameterized values.
- You write standard SQL syntax directly in your code.
- This gives you full access to Postgres-specific features.
// pg: Raw Query
const result = await pool.query('SELECT * FROM users WHERE id = $1', [1]);
const rows = result.rows;
mysql2 uses question marks for parameter placeholders instead of dollar signs.
- The syntax is nearly identical to
pgbut follows MySQL standards. - You still manage the SQL string structure manually.
// mysql2: Raw Query
const [rows] = await pool.execute('SELECT * FROM users WHERE id = ?', [1]);
sqlite3 traditionally uses callbacks, though promises can be wrapped.
- The API is older and less modern than
pgormysql2. - You must handle errors in the callback function.
// sqlite3: Callback Query
db.all('SELECT * FROM users WHERE id = ?', [1], (err, rows) => {
if (err) throw err;
console.log(rows);
});
knex chains methods to build queries programmatically.
- You do not write SQL strings directly for basic operations.
- The library translates chains into safe SQL for your dialect.
// knex: Query Builder
const rows = await knex('users').where({ id: 1 }).select('*');
sequelize calls methods on defined models instead of tables.
- You interact with JavaScript classes, not database tables.
- The library generates the SQL behind the scenes.
// sequelize: Model Query
const users = await User.findAll({ where: { id: 1 } });
šļø Data Modeling
Defining what your data looks like is where ORMs and drivers diverge the most.
pg has no built-in modeling.
- You define schemas in SQL migration files.
- Your code assumes the structure exists in the database.
// pg: No Model (Raw SQL Assumption)
// You must ensure table exists via SQL:
// CREATE TABLE users (id INT, name TEXT);
mysql2 also lacks built-in modeling.
- Structure is managed entirely via database scripts.
- Code relies on documentation to know column names.
// mysql2: No Model (Raw SQL Assumption)
// You must ensure table exists via SQL:
// CREATE TABLE users (id INT, name VARCHAR(255));
sqlite3 requires manual schema management.
- You run
CREATE TABLEcommands on initialization. - There is no enforcement of types in the JavaScript layer.
// sqlite3: Manual Schema
db.run(`CREATE TABLE IF NOT EXISTS users (id INT, name TEXT)`);
knex provides schema building tools but not data models.
- You can define table structure in JavaScript migration files.
- It does not create JavaScript classes for rows.
// knex: Schema Builder
await knex.schema.createTable('users', (table) => {
table.increments('id');
table.string('name');
});
sequelize defines models as JavaScript classes.
- You specify types and validation rules in code.
- The library can sync these models to the database automatically.
// sequelize: Model Definition
const User = sequelize.define('User', {
name: { type: DataTypes.STRING },
});
š Migrations
Changing your database structure over time requires a migration strategy.
pg does not include a migration system.
- You must use external tools like
node-pg-migrate. - This adds complexity to your build pipeline.
// pg: External Tool Required
// Requires separate package installation and CLI setup
mysql2 lacks built-in migrations.
- You need to script your own SQL file execution.
- Common to use tools like
db-migratealongside it.
// mysql2: External Tool Required
// Requires separate package installation and CLI setup
sqlite3 has no migration tooling included.
- Developers often write custom scripts to alter tables.
- This can lead to inconsistent states across environments.
// sqlite3: Custom Scripts
// Manual execution of ALTER TABLE commands in code
knex includes a robust migration CLI out of the box.
- You create files for up and down changes.
- It tracks which migrations have run in a special table.
// knex: Built-in Migration
// knex migrate:make create_users_table
// Creates a file with export.up and export.down functions
sequelize provides a CLI for model-based migrations.
- It generates migration files based on your model changes.
- This keeps your code and database schema in sync.
// sequelize: CLI Migration
// npx sequelize-cli migration:generate --name create-users
// Generates a file with queryInterface.createTable
š Summary Table
| Feature | pg / mysql2 | sqlite3 | knex | sequelize |
|---|---|---|---|---|
| Type | Database Driver | Database Driver | Query Builder | ORM |
| Query Style | Raw SQL Strings | Raw SQL Strings | Method Chaining | Model Methods |
| Modeling | None (SQL Only) | None (SQL Only) | Schema Builder | JavaScript Classes |
| Migrations | External Tools Needed | Manual Scripts | Built-in CLI | Built-in CLI |
| Learning Curve | Low (Just SQL) | Low (Just SQL) | Medium | High |
| Best For | Full Control | Local / Testing | Flexibility | Rapid Development |
š” Final Recommendation
pg and mysql2 are the foundation layers ā choose them if you want zero abstraction and maximum performance. They are perfect when your team knows SQL well and wants to avoid the magic of an ORM. sqlite3 serves a niche role ā use it for local testing or small tools, but avoid it for high-traffic web servers due to locking constraints.
knex is the sweet spot for many teams ā it gives you structure without hiding the SQL. It is ideal when you want migrations and a query builder but still need to write complex joins or raw queries occasionally. sequelize is the productivity engine ā pick it when you want to model data as objects and let the library handle the relationships. It speeds up initial development but can become complex to debug when things go wrong.
Final Thought: If you are building a simple API, start with knex for balance. If you are building a complex enterprise system with strict data rules, sequelize might save you time. If you need raw speed and specific database features, stick with pg or mysql2.
How to Choose: knex vs mysql2 vs pg vs sequelize vs sqlite3
- knex:
Choose
knexif you want a balance between raw SQL control and developer convenience. It is ideal when you need to support multiple database types without rewriting queries, or when you want a robust migration system without the overhead of a full ORM. It works well for teams that prefer writing SQL-like logic but want it wrapped in JavaScript functions. - mysql2:
Choose
mysql2if your project relies exclusively on MySQL and you need high performance with minimal abstraction. It is suitable for developers who want to write raw SQL queries directly and manage connections manually. This package is a strong fit for microservices where each service owns its specific database schema. - pg:
Choose
pgif you are building on PostgreSQL and want the standard, battle-tested client library. It is best for teams that value stability and direct access to Postgres-specific features like JSONB or full-text search. Use this when you do not need an ORM and prefer managing data structures through raw SQL or external schema tools. - sequelize:
Choose
sequelizeif you prefer defining data models as JavaScript classes and want the library to handle SQL generation for you. It is ideal for complex applications where relationships between data tables are intricate and need strict enforcement. This tool saves time on boilerplate but requires learning its specific API and lifecycle hooks. - sqlite3:
Choose
sqlite3if you need a lightweight, file-based database for local development, testing, or small single-user applications. It is perfect for prototypes or edge cases where setting up a full database server is impractical. Avoid this for high-concurrency production web apps due to file-locking limitations and native binding requirements.
Popular Comparisons
README for knex
knex.js
A SQL query builder that is flexible, portable, and fun to use!
A batteries-included, multi-dialect (PostgreSQL, MariaDB, MySQL, CockroachDB, MSSQL, SQLite3, Oracle (including Oracle Wallet Authentication)) query builder for Node.js, featuring:
- transactions
- connection pooling
- streaming queries
- both a promise and callback API
- a thorough test suite
Node.js versions 16+ are supported.
- Take a look at the full documentation to get started!
- Browse the list of plugins and tools built for knex
- Check out our recipes wiki to search for solutions to some specific problems
- In case of upgrading from an older version, see migration guide
You can report bugs and discuss features on the GitHub issues page or send tweets to @kibertoad.
For support and questions, join our Gitter channel.
For knex-based Object Relational Mapper, see:
- https://github.com/Vincit/objection.js
- https://github.com/mikro-orm/mikro-orm
- https://bookshelfjs.org
To see the SQL that Knex will generate for a given query, you can use Knex Query Lab
Local Development Setup
Prerequisites
-
Node.js 16+
-
Python 3.x with
setuptoolsinstalled (required for building native dependencies likebetter-sqlite3)Python 3.12+ removed the built-in
distutilsmodule. If you encounter aModuleNotFoundError: No module named 'distutils'error duringnpm install, installsetuptoolsfor the Python version used by node-gyp:pip install setuptools -
Windows only: Visual Studio Build Tools with the "Desktop development with C++" workload
Install dependencies
npm install
Examples
We have several examples on the website. Here is the first one to get you started:
const knex = require('knex')({
client: 'sqlite3',
connection: {
filename: './data.db',
},
});
try {
// Create a table
await knex.schema
.createTable('users', (table) => {
table.increments('id');
table.string('user_name');
})
// ...and another
.createTable('accounts', (table) => {
table.increments('id');
table.string('account_name');
table.integer('user_id').unsigned().references('users.id');
});
// Then query the table...
const insertedRows = await knex('users').insert({ user_name: 'Tim' });
// ...and using the insert id, insert into the other table.
await knex('accounts').insert({
account_name: 'knex',
user_id: insertedRows[0],
});
// Query both of the rows.
const selectedRows = await knex('users')
.join('accounts', 'users.id', 'accounts.user_id')
.select('users.user_name as user', 'accounts.account_name as account');
// map over the results
const enrichedRows = selectedRows.map((row) => ({ ...row, active: true }));
// Finally, add a catch statement
} catch (e) {
console.error(e);
}
TypeScript example
import { Knex, knex } from 'knex';
interface User {
id: number;
age: number;
name: string;
active: boolean;
departmentId: number;
}
const config: Knex.Config = {
client: 'sqlite3',
connection: {
filename: './data.db',
},
useNullAsDefault: true,
};
const knexInstance = knex(config);
knexInstance<User>('users')
.select()
.then((users) => {
console.log(users);
})
.catch((err) => {
console.error(err);
})
.finally(() => {
knexInstance.destroy();
});
Usage as ESM module
If you are launching your Node application with --experimental-modules, knex.mjs should be picked up automatically and named ESM import should work out-of-the-box.
Otherwise, if you want to use named imports, you'll have to import knex like this:
import { knex } from 'knex/knex.mjs';
You can also just do the default import:
import knex from 'knex';
If you are not using TypeScript and would like the IntelliSense of your IDE to work correctly, it is recommended to set the type explicitly:
/**
* @type {Knex}
*/
const database = knex({
client: 'mysql',
connection: {
host: '127.0.0.1',
user: 'your_database_user',
password: 'your_database_password',
database: 'myapp_test',
},
});
database.migrate.latest();