| name | next-model-knex-connector |
| description | SQL connector for `@next-model/core` backed by Knex 3, supporting sqlite3, Postgres, MySQL, MariaDB, Oracle, and MSSQL through any Knex client. Triggers include "Knex connector", "multi-dialect", "Knex pool", and "schema migrations through Knex" — reach for this when you want one connector that targets multiple SQL dialects through Knex rather than a native single-dialect connector. |
| license | MIT |
| metadata | {"author":"tamino-martinius","version":"1.0.0"} |
@next-model/knex-connector
@next-model/knex-connector is a SQL connector for @next-model/core, backed by Knex 3. It speaks the same Connector interface as the native single-dialect connectors but delegates query building, pooling, transactions, and DDL to a Knex instance — which means any Knex client works (sqlite3, Postgres, MySQL, MariaDB, Oracle, MSSQL, Redshift). CI exercises sqlite3, PostgreSQL 17, and MySQL 8 on every push; the rest "should also work" but are not part of the matrix. Prefer this connector when you need multi-dialect portability or already have a Knex investment; prefer the native connectors otherwise (smaller dep graph, tighter dialect-specific behaviour).
When to use
- You want one connector that targets multiple SQL dialects (e.g. sqlite3 in dev/test, Postgres in prod) without swapping packages.
- You already have a Knex setup (config, pool tuning, migrations) and want NextModel to participate in the same instance.
- You need a dialect that does not have a native connector — MariaDB, Oracle, MSSQL, Redshift — but is supported by Knex.
When not to use
- You are committed to a single dialect — use the corresponding native connector for fewer deps and more direct semantics:
@next-model/postgres-connector (see next-model-postgres-connector)@next-model/sqlite-connector (see next-model-sqlite-connector)@next-model/mysql-connector (see next-model-mysql-connector)@next-model/mariadb-connector (see next-model-mariadb-connector)
Install
pnpm add @next-model/knex-connector knex
pnpm add -D sqlite3
knex is a runtime dependency; the actual driver (pg, mysql2, sqlite3, tedious, oracledb, …) you choose is yours to install per the database you target.
Setup
The constructor takes the same options object as knex({...}):
import { KnexConnector } from '@next-model/knex-connector';
const sqlite = new KnexConnector({
client: 'sqlite3',
connection: { filename: ':memory:' },
useNullAsDefault: true,
});
const pg = new KnexConnector({
client: 'pg',
connection: process.env.DATABASE_URL,
});
const mysql = new KnexConnector({
client: 'mysql2',
connection: 'mysql://root:secret@127.0.0.1:3306/myapp',
pool: { min: 1, max: 10 },
});
The underlying knex instance is exposed as connector.knex if you need to drop down to raw query-building or call connector.knex.destroy() on shutdown.
Optionally pass a DatabaseSchema (from @next-model/core's defineSchema(...)) as the second argument so Model({ connector, tableName: 'users' }) can infer per-table props at the type level:
import { defineSchema } from '@next-model/core';
const schema = defineSchema({
users: { columns: { id: { type: 'integer', primary: true }, email: { type: 'string' } } },
});
const connector = new KnexConnector({ client: 'pg', connection: '...' }, { schema });
Existing call sites without { schema } keep working unchanged.
Quick start
import { defineSchema, Model } from '@next-model/core';
import { KnexConnector } from '@next-model/knex-connector';
const schema = defineSchema({
users: {
columns: {
id: { type: 'integer', primary: true, autoIncrement: true },
name: { type: 'string' },
age: { type: 'integer' },
},
},
});
const connector = new KnexConnector({
client: 'pg',
connection: process.env.DATABASE_URL,
}, { schema });
class User extends Model({
connector,
tableName: 'users',
}) {}
Every Model feature (filters, aggregates, transactions, soft deletes, associations, schema DSL) flows through the Connector interface — so the rest of your code is identical regardless of which driver you picked.
Schema mutations
connector.createTable(name, t => …) accepts the core schema DSL. The connector translates each column kind to the matching knex schema-builder method:
| Core DSL | Knex call |
|---|
t.integer('id', { autoIncrement: true }) | table.increments('id') |
t.string('name') | table.string('name', limit ?? 255) |
t.text('body') | table.text('body') |
t.bigint('count') | table.bigInteger('count') |
t.float('rate') | table.float('rate') |
t.decimal('price', { precision, scale }) | table.decimal('price', precision, scale) |
t.boolean('active') | table.boolean('active') |
t.date('day') | table.date('day') |
t.datetime / t.timestamp(...) | table.timestamp(name) |
t.json('payload') | table.json('payload') |
t.timestamps() | two timestamp columns + now() defaults |
t.index(cols, { unique }) | table.index / table.unique |
{ default: 'currentTimestamp' } becomes defaultTo(knex.fn.now()). Every other default: value is passed through to defaultTo() verbatim. dropTable / alterTable map to the corresponding knex schema builder calls and emit full ALTER TABLE DDL through Knex.
Transactions
connector.transaction(fn) opens a real knex.transaction and pins it to activeTransaction so every nested call (including schema operations and execute) participates. Re-entrant calls join the outer transaction rather than nesting savepoints — the inner callback runs inside the outer one and a thrown error rolls the entire outer back. Errors thrown inside fn propagate after the rollback.
Gotchas
- Peer-dep selection.
knex is a regular dependency, but the dialect driver (pg, mysql2, sqlite3, tedious, oracledb, …) is yours to install. Picking the wrong client string vs. the installed package is the most common boot-time error.
batchInsert strategy varies per driver because not every driver supports RETURNING *:
- sqlite3 — inserts items one-by-one, then re-fetches each by primary key.
- pg —
INSERT … RETURNING * returns the full inserted rows in one round-trip.
- mysql / mysql2 — bulk
INSERT returns only the first auto-increment id; the connector expands it to consecutive ids (safe under InnoDB's contiguous lock for a single statement) and re-fetches all rows in one whereIn(id) query.
KeyType.manual — short-circuits the re-fetch and echoes the items back as-is.
execute(query, bindings) result shape is normalised to a flat Dict<any>[] per driver (sqlite3 → result, pg → result.rows, mysql/mysql2 → result[0]). MariaDB rides the mysql path; behaviour matches what knex returns for [rows, fields].updateAll / deleteAll build the WHERE clause directly from scope.filter and ignore scope.limit / scope.skip, which sqlite rejects (limit has no effect on a delete/update). The current row set is captured by an explicit query() call before the mutation so the methods can return the affected rows even when the driver lacks RETURNING.$like case sensitivity follows the driver — Postgres is case-sensitive, MySQL/SQLite default to insensitive.$raw translates ? placeholders to driver-specific positional markers (Postgres $1, MySQL ?).reflectSchema dispatches by Knex client: sqlite3 / better-sqlite3 use PRAGMA queries against sqlite_master, pg / postgres query information_schema + pg_index / pg_class, and mysql / mysql2 / mariadb query MySQL information_schema. Unknown clients throw PersistenceError — reflectSchema is optional, so leaving it unimplemented is safe.
See also
next-model-core — the Connector interface and schema DSL this connector implements.next-model-postgres-connector — native Postgres connector (prefer for single-dialect Postgres projects).next-model-sqlite-connector — native sqlite connector.next-model-mysql-connector — native MySQL connector.next-model-mariadb-connector — native MariaDB connector.next-model-migrations — migration runner that drives createTable / alterTable / dropTable through whichever connector you pick.
