Knex is an open-source SQL query builder for Node.js that targets PostgreSQL, MariaDB, MySQL, CockroachDB, Microsoft SQL Server, SQLite3 and Oracle, and it is aimed at JavaScript and TypeScript developers who need one query API across several database engines instead of hand-written SQL per dialect.

What it is

Knex is a batteries-included, multi-dialect query builder for Node.js, distributed as the knex package on npm under the MIT licence. It supports Node.js 16 and later and offers both a promise API and a callback API, with TypeScript definitions such as import { Knex, knex } from 'knex'. The project lives in the JavaScript and npm ecosystem, keeps its documentation at knexjs.org, and lists its topic set as javascript, knex, mysql, postgresql, sql, and sqlite3. It also covers Oracle Wallet Authentication alongside the more common engines, and it ships a thorough test suite run through GitHub Actions.

The concrete problem it solves is dialect drift. Application code written against one database engine usually has to be rewritten, or wrapped in conditional string building, when the same application must also run on another engine. Knex replaces that hand-assembled SQL with a single builder API that emits the correct SQL for the configured client, so the same calls to knex('users').insert(...), .join('accounts', 'users.id', 'accounts.user_id') and .select('users.user_name as user', 'accounts.account_name as account') produce engine-appropriate statements. To inspect what a given query will emit, the project points to Knex Query Lab.

Key capabilities

• Multi-dialect support across PostgreSQL, MariaDB, MySQL, CockroachDB, MSSQL, SQLite3 and Oracle, including Oracle Wallet Authentication.
• Transactions, documented in the guide at knexjs.org/guide/transactions.html.
• Connection pooling, configured through the pool option in the guide.
• Streaming queries, exposed through the interfaces documented under streams.
• Both a promise API and a callback API for the same query builder.
• Schema building through knex.schema.createTable('users', (table) => { ... }), including column helpers such as table.increments('id') and foreign keys via table.integer('user_id').unsigned().references('users.id').
• A published plugin and tools list in ECOSYSTEM.md, a recipes wiki for common problems, an UPGRADING.md migration guide for older versions, and support through the GitHub issues page and a Gitter channel.

Who uses it and how

• Node.js backend teams that must serve more than one database engine, using one builder layer rather than separate query code per engine.
• Teams that want a model layer above the query layer, through the Knex-based ORMs the README names: objection.js, mikro-orm and bookshelfjs.
• Developers prototyping against a local file database, as in the README example that configures client: 'sqlite3' with connection: { filename: './data.db' } before creating tables and inserting rows.
• Contributors building from source, who need Node.js 16 or later, Python 3.x with setuptools installed for native dependencies such as better-sqlite3, and, on Windows, Visual Studio Build Tools with the "Desktop development with C++" workload.

Getting started

Install the knex package from npm together with a driver for the target engine, then create an instance from a config object such as { client: 'sqlite3', connection: { filename: './data.db' } }. Full documentation and further examples are published at knexjs.org.

How it compares

The tools the facts place nearest to Knex are objection.js, mikro-orm and bookshelfjs, which are Object Relational Mappers built on top of Knex rather than alternatives to it, so Knex sits beneath them as the query layer. For verifying the generated SQL directly, Knex Query Lab occupies a neighbouring role as an inspection tool for the same queries.

When to use it — and when not to

Knex is a library, not a hosted service, so a team adopting it must already run whichever database engine it points at and manage that engine, its credentials and its backups. Teams that want a full model layer with entities and relations should look at the ORMs named above, and teams outside Node.js cannot use it at all. The costs are visible in the project's own setup notes, which require a Python 3.x toolchain with setuptools and, on Windows, C++ build tools to compile native dependencies, plus a repository that carries 751 open issues.

npm install

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);
}

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();
  });

import { knex } from 'knex/knex.mjs';

import knex from 'knex';

/**
 * @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();