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

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

Get the connection string:

Terminal
db9 db status knex-app

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: 'knex-app.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 (runs as REPEATABLE READ on DB9).

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 supported.

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 (->, ->>, @>).
  • Connection string: Use knex-app.admin as the username, 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 username format ({database-name}.admin). DB9 routes connections by parsing the tenant from the username.

Schema builder operations fail

Section titled “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

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 @> ?::jsonb", [JSON.stringify({ role: 'admin' })]);

Verified Compatibility

Section titled “Verified Compatibility”

Tested with Knex 3.1+ against DB9. All 97 tests pass 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 HNSWPass
DDL operationsPass

Next Pages

Section titled “Next Pages”