Knex for Node.js — Complete Guide
In this tutorial, you will learn about Knex for Node.js. We cover key concepts, practical examples, and best practices to help you master this topic.
Learn Knex for Node.js database migrations: install and configure Knex, write schema-building migrations, seed databases, use query builder, and integrate Knex migrations into Node.js applications.
What You Learn
You will learn how to use Knex for database migrations in Node.js applications: install and configure Knex with different databases, write schema-building migrations using the JavaScript API, seed databases with test data, use the query builder for data operations, and integrate migrations into deployment.
Why It Matters
Knex is the most popular migration tool for Node.js. It provides a database-agnostic schema builder, query builder, and migration framework. JavaScript/TypeScript developers can manage their entire database lifecycle without writing raw SQL.
Real-World Use
DodaTech's Node.js notification service uses Knex with PostgreSQL. Migrations are written in JavaScript, leveraging the schema builder for database-agnostic operations. The same migration files work in development with SQLite and in production with PostgreSQL.
Installation and Setup
# Install Knex and a database driver
npm install knex
npm install pg # PostgreSQL
# npm install mysql2 # MySQL
# npm install sqlite3 # SQLite
# Initialize Knex configuration
npx knex init
# Creates knexfile.js
// knexfile.js - Knex configuration
module.exports = {
development: {
client: 'sqlite3',
connection: {
filename: './dev.sqlite3',
},
migrations: {
directory: './db/migrations',
},
seeds: {
directory: './db/seeds',
},
useNullAsDefault: true, // Required for SQLite
},
production: {
client: 'pg',
connection: {
host: process.env.DB_HOST,
port: process.env.DB_PORT || 5432,
database: process.env.DB_NAME,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
ssl: { rejectUnauthorized: false },
},
pool: {
min: 2,
max: 10,
},
migrations: {
tableName: 'knex_migrations',
},
},
};
Expected output: knex init creates the configuration file. Multiple environments are configured with different databases. Development uses SQLite for simplicity. Production uses PostgreSQL with connection pooling.
Creating Migrations
# Create a new migration
npx knex migrate:make create_users_table
# Output:
# Created Migration: db/migrations/20260628120000_create_users_table.js
// db/migrations/20260628120000_create_users_table.js
exports.up = function(knex) {
return knex.schema.createTable('users', (table) => {
table.increments('id').primary();
table.string('name', 100).notNullable();
table.string('email', 255).notNullable().unique();
table.string('phone', 20).nullable();
table.boolean('is_active').defaultTo(true);
table.timestamps(true, true); // created_at, updated_at
table.index(['email', 'is_active'], 'idx_users_email_active');
});
};
exports.down = function(knex) {
return knex.schema.dropTableIfExists('users');
};
Expected output: Migration file exports up and down functions. The schema builder methods chain to define columns, constraints, and indexes. Timestamps are created automatically with timestamps(true, true).
Schema Building
// Comprehensive schema building examples
exports.up = function(knex) {
return knex.schema
.createTable('orders', (table) => {
table.increments('id');
table.integer('user_id').unsigned().notNullable();
table.decimal('total', 10, 2).notNullable();
table.enu('status', ['pending', 'paid', 'shipped', 'delivered'])
.defaultTo('pending');
table.text('notes').nullable();
table.jsonb('metadata').nullable();
table.specificType('tags', 'text[]').nullable();
table.timestamps(true, true);
// Constraints
table.foreign('user_id')
.references('id')
.inTable('users')
.onDelete('CASCADE');
// Indexes
table.index('user_id', 'idx_orders_user');
table.index('status', 'idx_orders_status');
})
.createTable('order_items', (table) => {
table.increments('id');
table.integer('order_id').unsigned().notNullable();
table.integer('product_id').unsigned().notNullable();
table.integer('quantity').notNullable().defaultTo(1);
table.decimal('price', 10, 2).notNullable();
table.foreign('order_id')
.references('id')
.inTable('orders')
.onDelete('CASCADE');
table.unique(['order_id', 'product_id'], 'uq_order_product');
});
};
exports.down = function(knex) {
return knex.schema
.dropTableIfExists('order_items')
.dropTableIfExists('orders');
};
Expected output: Knex schema builder supports all common column types, constraints, foreign keys, indexes, and Composite keys. Tables are created in dependency order. Down drops in reverse.
Data Migrations
// Data migration - backfill data
exports.up = async function(knex) {
// Add column
await knex.schema.table('users', (table) => {
table.string('full_name').nullable();
});
// Backfill data in batches
const batchSize = 500;
let processed = 0;
while (true) {
const rows = await knex('users')
.whereNull('full_name')
.limit(batchSize);
if (rows.length === 0) break;
const updates = rows.map(user => ({
id: user.id,
full_name: `${user.first_name || ''} ${user.last_name || ''}`.trim(),
}));
await knex.transaction(async (trx) => {
for (const user of updates) {
if (user.full_name) {
await trx('users')
.where('id', user.id)
.update({ full_name: user.full_name });
}
}
});
processed += rows.length;
console.log(`Backfilled ${processed} users`);
}
// Make NOT NULL after backfill
await knex.schema.table('users', (table) => {
table.string('full_name').notNullable().alter();
});
};
exports.down = function(knex) {
return knex.schema.table('users', (table) => {
table.dropColumn('full_name');
});
};
Expected output: Data migrations use the Knex query builder for backfilling. Batch processing prevents long transactions. The column is made NOT NULL only after all existing rows have data.
Seeding
// db/seeds/01_users.js
exports.seed = async function(knex) {
// Delete existing entries
await knex('users').del();
// Insert seed data
await knex('users').insert([
{
name: 'Alice Johnson',
email: 'alice@example.com',
is_active: true,
},
{
name: 'Bob Smith',
email: 'bob@example.com',
is_active: true,
},
{
name: 'Charlie Brown',
email: 'charlie@example.com',
is_active: false,
},
]);
};
// db/seeds/02_orders.js
exports.seed = async function(knex) {
await knex('orders').del();
const users = await knex('users').select('id');
const orders = users.flatMap(user => [
{ user_id: user.id, total: 59.99, status: 'delivered' },
{ user_id: user.id, total: 29.99, status: 'pending' },
]);
await knex('orders').insert(orders);
};
Expected output: Seed files populate databases with test data. Seeds are ordered (01, 02) for dependency management. Run seeds with npx knex seed:run. Seeds are separate from migrations.
Running Migrations
# Run all pending migrations
npx knex migrate:latest
# Rollback the last batch
npx knex migrate:rollback
# Rollback all migrations
npx knex migrate:rollback --all
# View migration status
npx knex migrate:status
# Run seeds
npx knex seed:run
# Get current migration version
npx knex migrate:current_version
Expected output: Knex CLI manages migration lifecycle. migrate:latest applies all pending migrations. migrate:rollback reverts the last batch. migrate:status shows which migrations are applied or pending.
Integration with Express
// db.js - Database connection module
const knex = require('knex');
const config = require('./knexfile');
const environment = process.env.NODE_ENV || 'development';
const db = knex(config[environment]);
module.exports = db;
// app.js - Application startup
const express = require('express');
const db = require('./db');
const app = express();
// Health check that verifies database connection
app.get('/health', async (req, res) => {
try {
await db.raw('SELECT 1');
res.json({ status: 'healthy', database: 'connected' });
} catch (err) {
res.status(503).json({ status: 'unhealthy', database: 'disconnected' });
}
});
// Start server
const PORT = process.env.PORT || 3000;
async function start() {
try {
// Run migrations on startup
await db.migrate.latest();
console.log('Migrations applied successfully');
app.listen(PORT, () => {
console.log(`Server running on port ${PORT}`);
});
} catch (err) {
console.error('Failed to start:', err);
process.exit(1);
}
}
start();
Expected output: Knex migrations run on application startup before the server accepts requests. Health check verifies database connectivity. Failed migrations prevent application start.
Common Mistakes
1. Forgetting useNullAsDefault for SQLite
SQLite requires useNullAsDefault: true in configuration. Without it, Knex throws an error when inserting rows without all column values. This is a common pitfall when using SQLite for development.
2. Not Returning the Promise
Knex migration functions must return a promise or use async/await. Forgetting the return causes Knex to advance the migration before it completes. Always return the schema builder or query result.
3. Mixing Schema and Data Changes
Schema and data changes have different failure modes. Schema changes are fast. Data changes can take minutes. Separate them into different migration files. Run data migrations only after schema is applied.
4. No Down Migration
Every migration needs a down function. Without it, rollback is impossible. Even simple add-column migrations should have a drop-column down. Down functions are critical for safe deployments.
5. Hardcoding Database Credentials
Database credentials in knexfile.js are committed to git. Use environment variables. Read config from Process.env. Never commit production credentials.
Practice Questions
1. How do you create a migration in Knex?
Run npx knex migrate:make migration_name. This creates a timestamped file in the migrations directory with exports.up and exports.down functions.
2. What is the purpose of the Knex schema builder?
The schema builder provides a JavaScript API for creating, altering, and dropping database objects. It abstracts database-specific SQL syntax, making migrations portable across databases.
3. How do you handle migration rollback in Knex?
Run npx knex migrate:rollback. This reverts the last batch of migrations by calling their down functions. Use --all to rollback all migrations.
4. How do you integrate Knex migrations with application startup?
Call db.migrate.latest() before the application starts listening. This ensures the database schema is up-to-date before accepting requests.
Challenge
Set up Knex for a Node.js application with: PostgreSQL for production, SQLite for development, migrations for users and orders tables with foreign keys, data migration for backfilling, seed files for test data, and application startup integration that runs migrations before listening.
FAQ
Mini Project: Knex Migration Setup
Set up Knex for a Node.js application with: Knexfile configured for development (SQLite) and production (PostgreSQL), migrations for users, posts, and comments tables with foreign keys and indexes, data migration for backfilling post slugs, seed files for all tables, and Express server that runs migrations on startup.
What's Next
Now that you understand Knex, explore Sequelize Migrations for the Sequelize ORM migration approach.
Built by the developers of DodaTech
Doda Browser, DodaZIP & Durga Antivirus Pro