Skip to content
Discord Get Started

Knex.js

Knex connects to DB9 using the pg (node-postgres) driver over pgwire. The fluent query builder, schema builder, transaction support, and raw SQL all work without modification.

DB9 passes every test in its Knex compatibility suite, covering CRUD, transactions, advanced SQL, and vector operations — and the Knex schema builder works too, so Knex needs no schema workaround on DB9. (TypeORM’s migration runner also works; its synchronize, and Prisma’s and Drizzle’s schema tools, do not.)

Prerequisites

Section titled “Prerequisites”
  • A DB9 database (create one)
  • Node.js 18+
  • Knex 3.1+

Create a DB9 Database

Section titled “Create a DB9 Database”
Terminal
db9 create --name knex-app

db9 create prints the connection string, with the admin password, once — copy it from the Connection String: line. It has this shape:

Output
postgresql://YOUR_TENANT_ID.admin:YOUR_PASSWORD@pg.d0000.db9.io:5432/postgres

YOUR_TENANT_ID is the tenant ID prefix in the generated connection string. Copy the username from the Connection String: line printed by db9 create or from db9 db connect. The examples below use pg.db9.io:5433, which reaches the same server; either host and port works. db9 db status knex-app shows the connection string without the password; if you lose it, db9 db reset-password knex-app sets a new password and prints a new connection string.

Settings that take the password on its own need it unencoded: the connection string URL-encodes it (%2B is +, %3D is =), while db9 create --show-password and db9 db reset-password also print it as plain text on the Admin Pass: line.

Project Setup

Section titled “Project Setup”
Terminal
mkdir knex-db9 && cd knex-db9
npm init -y
npm install knex pg
npm install -D typescript @types/pg @types/node

Connection

Section titled “Connection”
db.ts
import Knex from 'knex';


const db = Knex({
  client: 'pg',
  connection: {
    host: 'pg.db9.io',
    port: 5433,
    user: 'YOUR_TENANT_ID.admin',
    password: 'YOUR_PASSWORD',
    database: 'postgres',
    ssl: { rejectUnauthorized: false },
  },
  pool: {
    min: 0,
    max: 10,
  },
});


export default db;

Test the connection:

TypeScript
const result = await db.raw('SELECT 1 AS value');
console.log(result.rows); // [{ value: 1 }]

Create Tables

Section titled “Create Tables”

Use the Knex schema builder or raw SQL:

setup.ts
import db from './db';


async function setup() {
  await db.schema.createTable('users', (table) => {
    table.increments('id').primary();
    table.string('email', 255).notNullable().unique();
    table.string('name', 100).notNullable();
    table.integer('age').defaultTo(0);
    table.boolean('is_active').defaultTo(true);
    table.text('bio');
    table.jsonb('metadata');
    table.uuid('external_id');
    table.timestamp('created_at', { useTz: true }).defaultTo(db.fn.now());
    table.timestamp('updated_at', { useTz: true }).defaultTo(db.fn.now());
    table.index('email');
  });


  await db.schema.createTable('posts', (table) => {
    table.increments('id').primary();
    table.string('title', 500).notNullable();
    table.text('content').notNullable();
    table.boolean('published').defaultTo(false);
    table.integer('author_id').notNullable().references('id').inTable('users').onDelete('CASCADE');
    table.timestamp('created_at', { useTz: true }).defaultTo(db.fn.now());
  });


  await db.schema.createTable('tags', (table) => {
    table.increments('id').primary();
    table.string('name', 100).notNullable().unique();
  });


  await db.schema.createTable('post_tags', (table) => {
    table.integer('post_id').notNullable().references('id').inTable('posts').onDelete('CASCADE');
    table.integer('tag_id').notNullable().references('id').inTable('tags').onDelete('CASCADE');
    table.primary(['post_id', 'tag_id']);
  });


  console.log('Tables created');
  await db.destroy();
}


setup();

CRUD Operations

Section titled “CRUD Operations”
TypeScript
import db from './db';


// Insert with RETURNING
const [user] = await db('users')
  .insert({ email: 'alice@example.com', name: 'Alice', age: 30, metadata: { role: 'admin' } })
  .returning('*');


// Bulk insert
const users = await db('users')
  .insert([
    { email: 'bob@example.com', name: 'Bob', age: 25 },
    { email: 'carol@example.com', name: 'Carol', age: 35 },
  ])
  .returning('*');


// Select with filters
const results = await db('users')
  .select('id', 'name', 'email')
  .where('age', '>=', 25)
  .orderBy('name', 'asc')
  .limit(10)
  .offset(0);


// Get single row
const alice = await db('users')
  .where({ email: 'alice@example.com' })
  .first();


// Aggregates
const count = await db('users').count('* as count').first();
const totalAge = await db('users').sum('age as total').first();


// Update with RETURNING
const [updated] = await db('users')
  .where({ id: user.id })
  .update({ name: 'Alice Updated' })
  .returning(['id', 'name', 'updated_at']);


// Upsert (INSERT ... ON CONFLICT)
await db('users')
  .insert({ email: 'alice@example.com', name: 'Alice V2', age: 31 })
  .onConflict('email')
  .merge(['name', 'age']);


// Delete
await db('users').where({ id: user.id }).del();

WHERE operators

Section titled “WHERE operators”
TypeScript
// Comparison
await db('users').where('age', '>', 25);
await db('users').where('age', '<=', 30);
await db('users').whereIn('name', ['Alice', 'Bob']);
await db('users').whereNotNull('bio');
await db('users').whereNull('bio');


// Pattern matching
await db('users').where('name', 'like', '%li%');
await db('users').where('name', 'ilike', '%alice%');


// Compound conditions
await db('users')
  .where('age', '>=', 30)
  .whereNotNull('bio')
  .orWhere(function () {
    this.where('age', '<', 25).whereNull('bio');
  })
  .orderBy('age', 'desc');

Joins

Section titled “Joins”
TypeScript
// INNER JOIN
const postsWithAuthors = await db('users')
  .innerJoin('posts', 'users.id', 'posts.author_id')
  .select('users.name', 'posts.title');


// LEFT JOIN
const allUsersWithPosts = await db('users')
  .leftJoin('posts', 'users.id', 'posts.author_id')
  .select('users.name', 'posts.title')
  .orderBy('users.name');


// GROUP BY with HAVING
const activeAuthors = await db('users')
  .innerJoin('posts', 'users.id', 'posts.author_id')
  .select('users.name')
  .count('posts.id as post_count')
  .groupBy('users.name')
  .having(db.raw('COUNT(posts.id) > 1'))
  .orderBy('post_count', 'desc');

Transactions

Section titled “Transactions”

Callback-based (auto-commit/rollback)

Section titled “Callback-based (auto-commit/rollback)”
TypeScript
await db.transaction(async (trx) => {
  const [user] = await trx('users')
    .insert({ email: 'txn@example.com', name: 'Txn User', age: 30 })
    .returning('*');


  await trx('posts')
    .insert({ title: 'Atomic Post', content: 'Created in a transaction.', author_id: user.id });
  // Auto-commits on success, auto-rolls back on error
});

Manual control

Section titled “Manual control”
TypeScript
const trx = await db.transaction();


try {
  await trx('users')
    .insert({ email: 'manual@example.com', name: 'Manual', age: 28 });
  await trx.commit();
} catch (err) {
  await trx.rollback();
  throw err;
}

Isolation levels

Section titled “Isolation levels”
TypeScript
const trx = await db.transaction();


try {
  await trx.raw('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ');
  const users = await trx('users').select('*');
  await trx.commit();
} catch (err) {
  await trx.rollback();
}

Supported: READ COMMITTED, REPEATABLE READ. SERIALIZABLE is not implemented — it does not error, it is silently downgraded to REPEATABLE READ with a server WARNING. Set REPEATABLE READ explicitly so the intent is visible in your code.

CTEs and Subqueries

Section titled “CTEs and Subqueries”
TypeScript
// CTE with .with()
const olderUsers = await db
  .with('older', db('users').where('age', '>=', 30))
  .select('name', 'age')
  .from('older')
  .orderBy('age');


// Subquery in WHERE
const aboveAvg = await db('users')
  .where('age', '>', db('users').avg('age'));


// Subquery in SELECT
const ranked = await db('users')
  .select('name')
  .select(
    db('users')
      .count('*')
      .where('age', '<=', db.ref('users.age'))
      .as('rank')
  )
  .orderBy('age');

Raw SQL for Advanced Features

Section titled “Raw SQL for Advanced Features”
TypeScript
// Window functions
const ranked = await db.raw(`
  SELECT name, age,
    ROW_NUMBER() OVER (ORDER BY age DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY age DESC) AS dense_rank
  FROM users ORDER BY rank
`);


// Recursive CTE
const hierarchy = await db.raw(`
  WITH RECURSIVE tree AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, t.level + 1
    FROM employees e JOIN tree t ON e.manager_id = t.id
  )
  SELECT * FROM tree ORDER BY level, name
`);


// DISTINCT ON
const firstPerDept = await db.raw(`
  SELECT DISTINCT ON (department_id) name, department_id, salary
  FROM employees
  ORDER BY department_id, salary DESC
`);


// Parameterized queries
const filtered = await db.raw(
  'SELECT * FROM users WHERE age > ? AND age < ?',
  [20, 35]
);
Section titled “Vector Search”

Knex does not have a native vector type. Use raw SQL for vector operations:

TypeScript
// Create vector table and index
await db.raw(`
  CREATE EXTENSION IF NOT EXISTS vector;
  CREATE TABLE IF NOT EXISTS embeddings (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    embedding vector(3) NOT NULL
  );
  CREATE INDEX IF NOT EXISTS idx_embeddings
    ON embeddings USING hnsw (embedding vector_l2_ops);
`);


// Insert vectors
const [doc] = await db('embeddings')
  .insert({ name: 'doc-1', embedding: '[1.0, 2.0, 3.0]' })
  .returning('*');


// Cosine distance search
const results = await db('embeddings')
  .select('name')
  .select(db.raw("cosine_distance(embedding, '[1.0, 1.0, 1.0]') AS distance"))
  .orderBy('distance', 'asc')
  .limit(5);


// L2 distance with operator
const nearest = await db.raw(`
  SELECT name FROM embeddings
  WHERE embedding <-> '[1.0, 0.0, 0.0]' < 1.0
  ORDER BY embedding <-> '[1.0, 0.0, 0.0]'
`);

DB9 supports HNSW indexes with vector_l2_ops, vector_cosine_ops, and vector_ip_ops; IVFFlat is not available at all. A CREATE INDEX ... USING hnsw builds a real index over both the wire protocol and the HTTP SQL API. The planner only uses it when the query’s distance metric matches the index’s opclass, a LIMIT is present, and the query has no WHERE clause; a bound query vector is fine. The examples above build a vector_l2_ops index and then query with cosine_distance or without a LIMIT, so they run as exact scans. See When the index is used. If your code uses CREATE INDEX ... USING ivfflat, change it to USING hnsw.

Schema Changes

Section titled “Schema Changes”

Use the Knex schema builder or raw SQL for migrations:

TypeScript
// Add a column
await db.schema.alterTable('users', (table) => {
  table.text('phone');
});


// Create an index
await db.raw('CREATE INDEX IF NOT EXISTS idx_users_age ON users (age)');


// Drop a column
await db.schema.alterTable('users', (table) => {
  table.dropColumn('phone');
});

For teams, use Knex’s built-in migration system with raw SQL inside migration files for full control over DDL.

Production Notes

Section titled “Production Notes”
  • Driver: Knex uses pg (node-postgres). Set client: 'pg' in the configuration.
  • Connection pooling: Configure pool.min and pool.max. Knex manages the pool internally; DB9 supports multiple concurrent connections per tenant.
  • RETURNING: Knex’s .returning() works with DB9 for INSERT, UPDATE, and DELETE.
  • JSONB: Knex passes JSONB values through to PostgreSQL. Use db.raw() for JSONB operators (->, ->>, @>), and cast any binding used as a key, path or operand — see JSONB queries.
  • Connection string: Use the complete <tenant_id>.admin username from the generated connection string or db9 db connect, with host pg.db9.io and port 5433.

Troubleshooting

Section titled “Troubleshooting”

Connection timeout

Section titled “Connection timeout”

Verify the host (pg.db9.io), port (5433), and complete username from the generated connection string. DB9 routes pgwire connections by the tenant ID prefix in that username.

Schema builder operations

Section titled “Schema builder operations”

The Knex schema builder works against DB9, with no workaround needed. createTable, alterTable (add, drop and rename column), renameTable, dropTableIfExists, index create/drop, hasTable, hasColumn and columnInfo() all run normally, and columnInfo() is pure information_schema introspection.

If you are porting advice from another ORM’s page, note that it does not apply here. The DB9 catalog gap that breaks drizzle-kit push is the missing pg_stat_user_indexes view, and Prisma’s db push trips on how DB9 spells a serial column’s default — Knex’s schema builder depends on neither.

JSONB queries

Section titled “JSONB queries”

For JSONB operations beyond simple value storage, use db.raw() with PostgreSQL’s native JSONB operators:

TypeScript
const admins = await db('users')
  .whereRaw("metadata @> ?", [JSON.stringify({ role: 'admin' })]);

Knex sends ? as a bind parameter. As the key, path or operand of ->, ->>, #>, #>>, #-, @>, <@, ? and - (delete key) it needs no cast — db.raw("metadata ->> ?", [key]) binds normally. The any/all key-existence operators ?| and ?&, and jsonb_extract_path() / jsonb_extract_path_text(), do need one: DB9 cannot infer the parameter’s type there, and the query fails with 42P18 could not determine data type of parameter $1. Use ::text[] for ?| and ?&, and ::text for the jsonb_extract_path key.

The key-existence operators need one more step. In db.raw() and whereRaw(), ? is also the binding placeholder, so ?, ?| and ?& must be escaped as \? — in JavaScript source that is a double backslash. Unescaped, Knex fails before reaching DB9 (with one binding it throws Expected 1 bindings, saw 2; other combinations reach the server as malformed SQL), so no cast can help. Passing ? as a builder operator — .where('metadata', '?', key) — does not work either: it emits where "metadata" 'role' $1. Use the escaped raw form:

TypeScript
// Throws client-side: Knex reads the operator as a second placeholder
await db('users').whereRaw("metadata ? ?::text", [key]);


// Works: escape the operator (for `?` the ::text cast is optional)
await db('users').whereRaw("metadata \\? ?::text", [key]);
await db('users').whereRaw("metadata \\?| ?::text[]", [keys]);

The cast rules from the compatibility matrix still apply: ::text[] for ?| and ?&.

Verified Compatibility

Section titled “Verified Compatibility”

Tested with Knex 3.1 against DB9. Every test passes, covering:

CategoryStatus
Connection and poolingPass
CRUD with RETURNINGPass
WHERE operators and pattern matchingPass
Joins (INNER, LEFT)Pass
Transactions and isolation levelsPass
CTEs and subqueriesPass
Window functionsPass
Aggregates with GROUP BY / HAVINGPass
Vector operations and HNSW indexingPass
DDL operationsPass

Next Pages

Section titled “Next Pages”