Knex.js is a batteries-included, multi-dialect SQL query builder for Node.js (PostgreSQL, MySQL, SQLite3, CockroachDB, and Oracle).
Initialization & Connection
const knex = require('knex')({
client: 'pg', // 'mysql2', 'sqlite3'
connection: {
host: '127.0.0.1',
user: 'dbuser',
password: 'secretpassword',
database: 'myapp'
},
pool: { min: 2, max: 10 }
});
Select Queries
// Basic Select
const users = await knex('users')
.select('id', 'name', 'email')
.where('active', true)
.orderBy('created_at', 'desc')
.limit(10);
// Where Conditions
knex('users')
.where({ first_name: 'Alice', last_name: 'Smith' })
.where('votes', '>', 100)
.whereIn('id', [1, 2, 3])
.whereNull('deleted_at')
.whereBetween('age', [18, 65]);
// Raw Expressions
knex('orders')
.select(knex.raw('count(*) as total, status'))
.groupBy('status');
Joins
knex('users')
.join('contacts', 'users.id', '=', 'contacts.user_id')
.select('users.id', 'contacts.phone');
knex('users')
.leftJoin('accounts', 'users.id', 'accounts.user_id')
.select('users.name', 'accounts.account_name');
Mutations: Insert, Update, Delete
// Insert (returns inserted IDs in Postgres via returning)
const [id] = await knex('users')
.insert({ name: 'Bob', email: '[email protected]' })
.returning('id');
// Update
await knex('users')
.where('id', 1)
.update({ name: 'Robert', updated_at: knex.fn.now() });
// Delete
await knex('users')
.where('active', false)
.del();
Schema Builder & Migrations
Creating Tables
await knex.schema.createTable('users', (table) => {
table.increments('id').primary();
table.string('name', 100).notNullable();
table.string('email').unique().notNullable();
table.boolean('is_active').defaultTo(true);
table.timestamps(true, true); // created_at and updated_at
});
Migration File (migrations/20261108_create_users.js)
exports.up = function (knex) {
return knex.schema.createTable('posts', (table) => {
table.increments('id');
table.integer('user_id').unsigned().references('id').inTable('users').onDelete('CASCADE');
table.string('title').notNullable();
table.text('content');
table.timestamps(true, true);
});
};
exports.down = function (knex) {
return knex.schema.dropTableIfExists('posts');
};
Running Migrations CLI
knex migrate:latest
knex migrate:rollback
knex seed:run