Node.js 44 🟢 SQL Query Building with Knex.js
Knex.js is a SQL query builder for Node.js. It is not an ORM, and it does not hide the SQL. It provides a fluent JavaScript API that constructs SQL queries, and the queries it produces are the SQL a developer would write by hand — but with the values parameterized, the identifiers quoted, and the dialect-specific syntax handled by the library . The developer writes knex('users').where({ name: 'Tim' }).select('*'), and Knex produces the SQL for the target database. The result is the control of raw SQL with the safety of parameter binding and the portability of a dialect-agnostic API.
The builder is the core abstraction. A query is built by chaining methods: select, from, where, join, orderBy, and the rest. Each method adds a clause to the internal state, and the query is executed when the chain is awaited or a terminal method like then is called . The chain is lazy: building the query does not execute it, and the same chain can be built incrementally before it is run. This makes the builder composable: a function can return a partially built query, and the caller can add the where clause and the orderBy before executing it.
Knex supports the major relational databases: PostgreSQL, MySQL, MariaDB, SQLite3, MSSQL, Oracle, and CockroachDB . The same query builder API works across all of them, and the dialect-specific differences — the quoting, the limit syntax, the returning clause — are handled by the client layer . A query written for SQLite can run against PostgreSQL with the same JavaScript code, and the SQL that is generated is the correct SQL for each database. The trade-off is the abstraction: the developer cannot use the full power of a database-specific feature without the raw SQL escape hatch.
This chapter covers three areas. First, why Knex exists — the raw-SQL-string problem, the parameter-binding problem, and the dialect-portability problem. Second, how to build queries — the connection configuration, the select, insert, update, and delete builders, the where clauses, the joins, and the raw expressions. Third, the lifecycle features — the migrations, the seeds, the transactions, the connection pool, and the streaming. The chapter ends with a complete example session, a quick reference, best practices, common pitfalls, real-world examples, and diagrams showing the query-building flow.
Key point: Knex.js is a SQL query builder, not an ORM. It provides a fluent JavaScript API that constructs parameterized SQL for the target dialect. The query chain is lazy and composable. The builder supports the major relational databases, and the dialect-specific syntax is handled by the client layer. Migrations, seeds, transactions, connection pooling, and streaming are built in.
Why Knex exists
The raw-SQL-string problem. Writing SQL as a string in JavaScript is fragile. The string concatenation, the quoting, and the parameter binding are all manual, and each one is an opportunity for a bug. A missing quote or a forgotten parameter produces a syntax error or, worse, an SQL injection. Knex replaces the string with a chain of method calls. Each method is a function, the arguments are values, and the library handles the quoting and the binding. The result is the same SQL, but the construction is type-checked by the JavaScript runtime and the binding is automatic .
The parameter-binding problem. SQL injection is prevented by parameter binding, and parameter binding is easy to forget when the SQL is a string. Knex binds the values automatically: knex('users').where('name', 'Tim') produces the SQL select * from "users" where "name" = ? with the value Tim bound as a parameter . The developer does not write the ? and the binding array; the builder does. The injection is structurally impossible for the values, and the raw escape hatch is the only place where the developer must be careful .
The dialect-portability problem. A query written for PostgreSQL may not run against MySQL. The quoting characters differ, the limit syntax differs, and the returning clause is PostgreSQL-specific. Knex abstracts these differences. The same JavaScript query runs against both databases, and the library generates the correct SQL for each. The portability is useful for the applications that support multiple databases, for the testing environments that use SQLite and the production environments that use PostgreSQL, and for the teams that want to delay the database choice .
The migration problem. A database schema changes over time, and the changes must be versioned and applied in order. Knex provides a migration system: the up function applies the change, the down function reverses it, and the knex_migrations table tracks which migrations have been run . The migrations are JavaScript files, and they use the same schema builder as the application code. The seed system populates the database with the initial data, and the two together are the standard workflow for the development and the deployment .
The composition problem. A query is often built from several parts. The base query is defined in a function, and the caller adds the filters, the sort, and the pagination. With raw SQL, the parts are strings that must be concatenated, and the order of the concatenation matters. With Knex, the parts are method calls on the same chain, and the order is determined by the builder. The query is composable, and the composition is safe.
The trade-off. Knex adds a layer of abstraction between the application and the database. The SQL is not visible in the source code, and the generated SQL must be inspected with .toSQL() or the debug mode. The abstraction can hide the performance characteristics of a query, and a complex query built with the builder may not use the indexes the way a hand-written query would. The trade-off is between the safety and the portability of the builder and the directness and the control of the raw SQL. For the common queries, the builder is the right choice. For the complex, performance-critical queries, the raw SQL is the escape hatch.
a. The connection configuration
Knex is initialized with a configuration object. The client key specifies the database, and the connection key specifies the connection details.
const knex = require('knex')({
client: 'pg',
connection: {
host: '127.0.0.1',
port: 5432,
user: 'app',
password: 'secret',
database: 'myapp',
},
pool: {
min: 2,
max: 10,
},
});The client can be a database name or a driver name: pg for PostgreSQL, mysql2 for MySQL, sqlite3 for SQLite, and mssql for SQL Server . The connection can be an object, a connection string, or a function that returns the connection details. The pool key configures the connection pool: the min and the max are the minimum and maximum connections .
The connection can also be a URI:
const knex = require('knex')({
client: 'pg',
connection: process.env.DATABASE_URL,
});The pool has the acquireConnectionTimeout option, which controls how long Knex waits for a connection before throwing a timeout error. The default is 60000 milliseconds, and the most common cause of the timeout is the pool exhaustion from the transactions that are not released .
The afterCreate callback is called when a new connection is created, and it can run the initialization queries:
const knex = require('knex')({
client: 'pg',
connection: { /* ... */ },
pool: {
afterCreate: (conn, done) => {
conn.query('SET timezone="UTC";', (err) => {
done(err, conn);
});
},
},
});The callback runs the SET timezone query on the new connection and calls the done callback. If the query fails, the error is passed to the done callback, and the connection is discarded .
b. Building queries
The builder starts with knex(tableName) or knex.select().from(tableName). The chain is lazy, and the query is executed when the chain is awaited.
Select.
const users = await knex('users').select('*');
const names = await knex.select('name').from('users');
const user = await knex('users').where({ id: 1 }).first();The first() method adds the limit 1 and returns the first row or undefined . The select method takes the column names, and the from method takes the table name.
Where clauses.
await knex('users').where('id', '>', 20);
await knex('users').where({ name: 'Tim', role: 'admin' });
await knex('users').where('name', 'like', '%Tim%');
await knex('users').whereIn('id', [1, 2, 3]);
await knex('users').whereNull('deleted_at');
await knex('users').where('age', '>=', 18).andWhere('age', '<=', 65);The where method accepts the column, the operator, and the value, or an object of the key-value pairs. The whereIn method takes an array, and the whereNull method checks for the null value. The andWhere and orWhere methods combine the conditions .
Insert.
const [id] = await knex('users').insert({ name: 'Tim', email: 'tim@example.com' });
const ids = await knex('users').insert([
{ name: 'Alice', email: 'alice@example.com' },
{ name: 'Bob', email: 'bob@example.com' },
]);The insert method returns the inserted IDs. For PostgreSQL, the returning method returns the full row:
const [user] = await knex('users')
.insert({ name: 'Tim', email: 'tim@example.com' })
.returning(['id', 'name', 'email']);The returning method is PostgreSQL-specific, and it is the standard way to get the inserted row .
Update.
await knex('users').where({ id: 1 }).update({ name: 'Tim' });
const count = await knex('users').where('age', '<', 18).update({ role: 'minor' });The update method returns the number of the affected rows. The returning method can be used to return the updated rows.
Delete.
await knex('users').where({ id: 1 }).del();
await knex('users').where('role', 'guest').del();The del method deletes the matching rows and returns the number of the affected rows.
Joins.
const rows = await knex('users')
.join('accounts', 'users.id', 'accounts.user_id')
.select('users.name as user', 'accounts.account_name as account');
const rows = await knex('users')
.leftJoin('posts', 'users.id', 'posts.author_id')
.select('users.name', 'posts.title');The join method takes the table, the first column, and the second column. The leftJoin and rightJoin methods are the outer joins .
Raw expressions.
const rows = await knex('users')
.select(knex.raw('count(*) as user_count, status'))
.groupBy('status');
const result = await knex.raw(
'SELECT users.*, count(posts.id)::int as post_count FROM users LEFT JOIN posts ON posts.author_id = users.id GROUP BY users.id',
);The knex.raw method is the escape hatch for the expressions the builder cannot express. The bindings are passed as the second argument, and the ? placeholders are bound as parameters .
c. Migrations, seeds, and transactions
Migrations. A migration is a JavaScript file with an up function and a down function. The up function applies the change, and the down function reverses it.
exports.up = function (knex) {
return knex.schema.createTable('users', (table) => {
table.increments('id');
table.string('name').notNullable();
table.string('email').unique().notNullable();
table.timestamps(true, true);
});
};
exports.down = function (knex) {
return knex.schema.dropTable('users');
};The migration is created with knex migrate:make create_users, and it is run with knex migrate:latest . The knex_migrations table tracks the applied migrations, and the knex migrate:rollback reverses the last batch. The migration files are the version control for the schema, and the recommendation is to never modify a migration that has been deployed — create a new one instead .
Seeds. A seed is a JavaScript file that populates the database with the initial data.
exports.seed = async function (knex) {
await knex('users').del();
await knex('users').insert([
{ name: 'Alice', email: 'alice@example.com' },
{ name: 'Bob', email: 'bob@example.com' },
]);
};The seed is created with knex seed:make users, and it is run with knex seed:run . The seeds are for the development and the testing, and they are not run in the production.
Transactions. A transaction is a series of queries that are committed or rolled back as a unit. The knex.transaction method takes a callback, and the callback receives a transaction object that is used in place of the knex object.
await knex.transaction(async (trx) => {
const [userId] = await trx('users').insert({ name: 'Tim' });
await trx('accounts').insert({ user_id: userId, name: 'knex' });
});The transaction commits when the callback resolves, and it rolls back when the callback throws. The trx object has the same query builder API as the knex object, and the queries run on the same connection .
Complete Example Session
// ============================================
// PART 1: THE CONNECTION
// ============================================
const knex = require('knex')({
client: 'pg',
connection: {
host: process.env.DB_HOST || 'localhost',
port: parseInt(process.env.DB_PORT || '5432', 10),
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_NAME,
},
pool: { min: 2, max: 10 },
});
// ============================================
// PART 2: THE SCHEMA MIGRATION
// ============================================
exports.up = function (knex) {
return knex.schema.createTable('users', (table) => {
table.increments('id');
table.string('name').notNullable();
table.string('email').unique().notNullable();
table.string('role').defaultTo('user');
table.timestamps(true, true);
});
};
exports.down = function (knex) {
return knex.schema.dropTable('users');
};
// ============================================
// PART 3: THE SELECT
// ============================================
async function findAllUsers() {
return knex('users').select('*');
}
async function findUserById(id) {
return knex('users').where({ id }).first();
}
async function findAdmins() {
return knex('users').where({ role: 'admin' });
}
// ============================================
// PART 4: THE INSERT
// ============================================
async function createUser(name, email) {
const [user] = await knex('users')
.insert({ name, email })
.returning(['id', 'name', 'email']);
return user;
}
// ============================================
// PART 5: THE UPDATE
// ============================================
async function updateUser(id, data) {
const count = await knex('users').where({ id }).update(data);
return count > 0;
}
// ============================================
// PART 6: THE DELETE
// ============================================
async function deleteUser(id) {
const count = await knex('users').where({ id }).del();
return count > 0;
}
// ============================================
// PART 7: THE JOIN
// ============================================
async function findUsersWithAccounts() {
return knex('users')
.join('accounts', 'users.id', 'accounts.user_id')
.select('users.name as user', 'accounts.account_name as account');
}
// ============================================
// PART 8: THE TRANSACTION
// ============================================
async function transferFunds(fromId, toId, amount) {
await knex.transaction(async (trx) => {
await trx('accounts').where({ id: fromId }).decrement('balance', amount);
await trx('accounts').where({ id: toId }).increment('balance', amount);
});
}
// ============================================
// PART 9: THE RAW EXPRESSION
// ============================================
async function getUserStats() {
const result = await knex.raw(
'SELECT users.name, count(posts.id)::int as post_count ' +
'FROM users LEFT JOIN posts ON posts.author_id = users.id ' +
'GROUP BY users.id, users.name ' +
'HAVING count(posts.id) > ?',
[0],
);
return result.rows;
}
// ============================================
// PART 10: THE COMPLETE APPLICATION
// ============================================
const express = require('express');
const app = express();
app.use(express.json());
app.get('/users', async (req, res) => {
const users = await knex('users').select('id', 'name', 'email');
res.json(users);
});
app.get('/users/:id', async (req, res) => {
const user = await knex('users').where({ id: req.params.id }).first();
if (!user) return res.status(404).json({ error: 'Not found' });
res.json(user);
});
app.post('/users', async (req, res) => {
const [user] = await knex('users')
.insert(req.body)
.returning(['id', 'name', 'email']);
res.status(201).json(user);
});
app.patch('/users/:id', async (req, res) => {
const count = await knex('users').where({ id: req.params.id }).update(req.body);
if (count === 0) return res.status(404).json({ error: 'Not found' });
const user = await knex('users').where({ id: req.params.id }).first();
res.json(user);
});
app.delete('/users/:id', async (req, res) => {
const count = await knex('users').where({ id: req.params.id }).del();
if (count === 0) return res.status(404).json({ error: 'Not found' });
res.status(204).send();
});
app.listen(3000);The ten parts show the connection, the schema migration, the select, the insert, the update, the delete, the join, the transaction, the raw expression, and the complete application.
Quick Reference
The Configuration
| Key | Purpose |
|---|---|
client | The database driver (pg, mysql2, sqlite3) |
connection | The connection details or URI |
pool.min / pool.max | The connection pool size |
pool.acquireConnectionTimeout | The wait time for a connection |
pool.afterCreate | The callback after a new connection |
The Query Methods
| Method | Purpose |
|---|---|
knex(table).select(cols) | Select the columns |
knex(table).where(cond) | Filter the rows |
knex(table).first() | Return the first row |
knex(table).insert(data) | Insert the rows |
knex(table).update(data) | Update the rows |
knex(table).del() | Delete the rows |
knex(table).join(...) | Join the tables |
knex.raw(sql, bindings) | The raw SQL |
The Where Clauses
| Method | Purpose |
|---|---|
.where('id', '>', 20) | The comparison |
.where({ name: 'Tim' }) | The equality |
.whereIn('id', [1, 2]) | The IN clause |
.whereNull('deleted_at') | The IS NULL |
.andWhere(...) | The AND condition |
.orWhere(...) | The OR condition |
The Lifecycle
| Command | Purpose |
|---|---|
knex migrate:make name | Create a migration |
knex migrate:latest | Run the pending migrations |
knex migrate:rollback | Roll back the last batch |
knex seed:make name | Create a seed |
knex seed:run | Run the seeds |
knex.transaction(cb) | Run a transaction |
The Dialects
| Client | Database |
|---|---|
pg | PostgreSQL |
mysql2 | MySQL |
sqlite3 | SQLite |
mssql | SQL Server |
oracledb | Oracle |
cockroachdb | CockroachDB |
Best Practices
✅ Do This:
// Use the builder for the parameter binding
knex('users').where('name', 'Tim'); // ✅
// Use the returning clause for the inserted rows
knex('users').insert(data).returning(['id', 'name']); // ✅
// Use the transactions for the multi-step operations
knex.transaction(async (trx) => { ... }); // ✅
// Use the migrations for the schema changes
exports.up = (knex) => knex.schema.createTable(...); // ✅
// Use the pool configuration
pool: { min: 2, max: 10 } // ✅
// Use the raw expressions sparingly
knex.raw('count(*)::int as count') // ✅❌ Don’t Do This:
// Don't concatenate the values into the raw SQL
knex.raw(`SELECT * FROM users WHERE name = '${name}'`); // ❌
// Don't forget the returning clause for the insert
knex('users').insert(data); // no ID returned // ❌
// Don't use the transactions without the trx object
knex.transaction(async (trx) => { await knex('users').insert(data); }); // ❌
// Don't modify a deployed migration
// Create a new migration instead // ❌
// Don't set the pool max too high
pool: { max: 1000 } // exhausts the database // ❌
// Don't use the builder for the complex window functions
// Use the raw SQL // ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| The query does not execute | The chain is not awaited | Await the chain or call .then |
| The values are not bound | The raw SQL is used | Use the builder or the bindings |
| The insert returns no ID | No returning clause | Add .returning('id') |
| The transaction does not roll back | The wrong knex object is used | Use the trx object |
| The pool is exhausted | The transactions are not released | Check the acquireConnectionTimeout |
| The migration fails | The up or down is missing | Implement both |
| The dialect mismatch | The wrong client | Set the correct client |
Real-World Examples
1. The Select
const users = await knex('users').select('*');2. The Where
const admins = await knex('users').where({ role: 'admin' });3. The Insert
const [id] = await knex('users').insert({ name: 'Tim' });4. The Update
await knex('users').where({ id: 1 }).update({ name: 'Tim' });5. The Delete
await knex('users').where({ id: 1 }).del();6. The Join
knex('users').join('accounts', 'users.id', 'accounts.user_id');7. The Transaction
await knex.transaction(async (trx) => { ... });8. The Migration
exports.up = (knex) => knex.schema.createTable('users', ...);9. The Raw
knex.raw('SELECT count(*) FROM users WHERE age > ?', [18]);10. The Connection
const knex = require('knex')({ client: 'pg', connection: { ... } });Visual
The Query-Building Flow
┌──────────────────────────────────────────────────────────────┐
│ THE QUERY-BUILDING FLOW │
│ │
│ knex('users') │
│ │ │
│ │ Start the query builder for the users table. │
│ ▼ │
│ .select('name', 'email') │
│ │ │
│ │ Add the select clause. │
│ ▼ │
│ .where('age', '>', 18) │
│ │ │
│ │ Add the where clause. │
│ ▼ │
│ .orderBy('name') │
│ │ │
│ │ Add the order by clause. │
│ ▼ │
│ .limit(10) │
│ │ │
│ │ Add the limit clause. │
│ ▼ │
│ await (or .then) │
│ │ │
│ │ Execute the query. │
│ ▼ │
│ The result rows │
│ │
│ The chain is lazy. The query is built incrementally │
│ and executed when awaited. │
│ │
└──────────────────────────────────────────────────────────────┘The Dialect Abstraction
┌──────────────────────────────────────────────────────────────┐
│ THE DIALECT ABSTRACTION │
│ │
│ The same JavaScript query: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ knex('users').where('id', 1).select('*') │ │
│ └──────────────────────────────────────────────────────┘ │
│ │ │
│ ├── PostgreSQL: │
│ │ SELECT * FROM "users" WHERE "id" = ? │
│ │ │
│ ├── MySQL: │
│ │ SELECT * FROM `users` WHERE `id` = ? │
│ │ │
│ ├── SQLite: │
│ │ SELECT * FROM "users" WHERE "id" = ? │
│ │ │
│ └── MSSQL: │
│ SELECT * FROM [users] WHERE [id] = @p0 │
│ │
│ The quoting, the parameter syntax, and the dialect-specific │
│ details are handled by the client layer. │
│ │
└──────────────────────────────────────────────────────────────┘The Transaction
┌──────────────────────────────────────────────────────────────┐
│ THE TRANSACTION │
│ │
│ await knex.transaction(async (trx) => { │
│ │ │
│ │ The trx object is a scoped query builder. │
│ │ │
│ ├── await trx('users').insert({ name: 'Tim' }) │
│ │ │
│ ├── await trx('accounts').insert({ user_id: 1 }) │
│ │ │
│ └── The callback resolves. │
│ │ │
│ ▼ │
│ The transaction is committed. │
│ │
│ If the callback throws: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The transaction is rolled back. │ │
│ │ The queries are undone. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ The queries in the transaction run on the SAME connection. │
│ The trx object must be used, not the knex object. │
│ │
└──────────────────────────────────────────────────────────────┘The Migrations
┌──────────────────────────────────────────────────────────────┐
│ THE MIGRATIONS │
│ │
│ migrations/ │
│ ├── 20240101_create_users.js │
│ ├── 20240102_create_accounts.js │
│ └── 20240103_add_role_to_users.js │
│ │
│ Each migration has: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ exports.up = (knex) => { ... } │ │
│ │ exports.down = (knex) => { ... } │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ The knex_migrations table tracks the applied migrations: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ name batch migration_time │ │
│ │ 20240101_create_users.js 1 2024-01-01 │ │
│ │ 20240102_create_accounts.js 1 2024-01-01 │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ knex migrate:latest → run the pending migrations │
│ knex migrate:rollback → roll back the last batch │
│ │
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Knex.js | SQL query builder for Node.js |
| Client | pg, mysql2, sqlite3, mssql |
| Query chain | Lazy; executed when awaited |
| Parameter binding | Automatic |
returning | Return the inserted/updated rows |
knex.raw | The escape hatch for the complex SQL |
knex.transaction | The transaction wrapper |
| Migrations | up and down functions |
| Seeds | The initial data |
| Pool | The connection pool |
Key takeaways:
- Knex.js is a SQL query builder, not an ORM. It provides a fluent JavaScript API that constructs parameterized SQL for the target dialect. The queries it produces are the SQL a developer would write by hand, with the values bound and the identifiers quoted .
- The query chain is lazy and composable. The chain is built by calling methods, and the query is executed when the chain is awaited or a terminal method is called. The same chain can be built incrementally and returned from a function .
- The parameter binding is automatic. The values are bound as parameters, and the SQL injection is structurally impossible for the values. The
knex.rawmethod is the escape hatch, and the bindings must be passed explicitly . - The dialect abstraction handles the database differences. The same JavaScript query runs against PostgreSQL, MySQL, SQLite, and the other supported databases, and the library generates the correct SQL for each .
- The migrations version the schema. The
upfunction applies the change, thedownfunction reverses it, and theknex_migrationstable tracks the applied migrations. The recommendation is to never modify a deployed migration . - The transactions are scoped to a connection. The
knex.transactionmethod takes a callback, and the callback receives atrxobject that is used in place of theknexobject. The queries in the transaction run on the same connection . - The connection pool is configured with the
poolkey. Theminandmaxcontrol the pool size, and theacquireConnectionTimeoutcontrols the wait time. The pool exhaustion is the most common cause of the timeout . - The
returningclause returns the inserted or updated rows. It is PostgreSQL-specific, and it is the standard way to get the row after an insert .
Remember: Knex.js is the SQL query builder that gives the developer the control of raw SQL with the safety of parameter binding and the portability of a dialect-agnostic API. The query chain is lazy and composable, and the queries are executed when awaited. The migrations version the schema, the seeds populate the data, and the transactions scope the multi-step operations. The knex.raw method is the escape hatch for the complex SQL, and the bindings must be passed explicitly. The result is a data access layer that is flexible, portable, and safe.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!