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 100% of Knex compatibility tests (97/97) covering CRUD, transactions, advanced SQL, and vector operations.

Prerequisites

A DB9 database (create one)
Node.js 18+
Knex 3.1+

Create a DB9 Database

db9 create --name knex-app

Get the connection string:

db9 db status knex-app

Project Setup

mkdir knex-db9 && cd knex-db9
npm init -y
npm install knex pg
npm install -D typescript @types/pg @types/node

Connection

import Knex from 'knex';

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

export default db;

Test the connection:

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

Create Tables

Use the Knex schema builder or raw SQL:

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

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

// 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

// 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

Callback-based (auto-commit/rollback)

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

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

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

// 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

// 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]
);

Vector Search

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

// 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, the probe is an inlined literal, a LIMIT is present, and the query has no WHERE clause — 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

Use the Knex schema builder or raw SQL for migrations:

// 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

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 knex-app.admin as the username, with host pg.db9.io and port 5433.

Troubleshooting

Connection timeout

Verify the host (pg.db9.io), port (5433), and username format ({database-name}.admin). DB9 routes connections by parsing the tenant from the username.

Schema builder operations fail

DB9’s information_schema support is limited. If db.schema.alterTable doesn’t work as expected, use db.raw() with direct DDL statements.

JSONB queries

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

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 <@ it needs no cast — db.raw("metadata ->> ?", [key]) binds normally. The key-existence operators ?, ?| and ?&, the - (delete key) operator, 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 (42883 for -). Use ::text for ? and -, and ::text[] for ?| and ?&.

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:

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

// Works: escape the operator, and still cast the binding
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 ?, ::text[] for ?| and ?&.

Verified Compatibility

Tested with Knex 3.1+ against DB9. All 97 tests pass covering:

Category	Status
Connection and pooling	Pass
CRUD with RETURNING	Pass
WHERE operators and pattern matching	Pass
Joins (INNER, LEFT)	Pass
Transactions and isolation levels	Pass
CTEs and subqueries	Pass
Window functions	Pass
Aggregates with GROUP BY / HAVING	Pass
Vector operations and HNSW indexing	Pass
DDL operations	Pass

Sequelize — Sequelize ORM with DB9
Prisma — Prisma ORM with DB9
Connect — connection strings and authentication
Vector Extension — HNSW indexes and distance operators
Compatibility Matrix — full PostgreSQL compatibility surface