knex_dart 1.2.0

A Dart SQL query builder inspired by Knex.js. Use driver packages (knex_dart_postgres, knex_dart_mysql, knex_dart_sqlite) to connect to databases.

knex_dart

A faithful port of Knex.js to Dart — a powerful, fluent SQL query builder supporting 9 databases.

Packages

Package	Description	Version
knex_dart	Core query builder (this package)
knex_dart_postgres	PostgreSQL driver
knex_dart_mysql	MySQL driver
knex_dart_sqlite	SQLite driver
knex_dart_duckdb	DuckDB driver (OLAP + WASM)
knex_dart_mssql	Microsoft SQL Server driver
knex_dart_bigquery	Google BigQuery driver
knex_dart_snowflake	Snowflake driver
knex_dart_turso	Turso (libSQL) driver
knex_dart_d1	Cloudflare D1 driver
knex_dart_capabilities	Shared dialect capability matrix
knex_dart_lint	Optional static dialect lint plugin

knex_dart is the core package — query builder, schema builder, and compiler with no database connectivity. Add the driver for your database.

Documentation

Full documentation: https://docs.knex.mahawarkartikey.in/

• Database Support — all 9 databases with connection examples
• WHERE Clauses — 29 filtering methods
• Joins — INNER, LEFT, RIGHT, FULL OUTER, LATERAL
• Window Functions — RANK, LEAD, LAG, frame clauses
• Transactions — atomic operations + nested savepoints
• Streaming — memory-efficient large result sets
• Migrations — code-first and SQL-directory sources
• Dialect Lint — optional static analysis plugin

Installation

Add the driver for your database — it pulls in knex_dart automatically:

dependencies:
  knex_dart_postgres: ^0.1.0   # PostgreSQL
  # knex_dart_mysql: ^0.1.0    # MySQL
  # knex_dart_sqlite: ^0.1.0   # SQLite
  # knex_dart_duckdb: ^0.1.0   # DuckDB (OLAP / browser WASM)

For SQL generation only (no live connection):

dependencies:
  knex_dart: ^0.1.0
  knex_dart_capabilities: ^0.1.0

Quick Start

PostgreSQL

import 'package:knex_dart_postgres/knex_dart_postgres.dart';

final db = await KnexPostgres.connect(
  host: 'localhost',
  database: 'mydb',
  username: 'user',
  password: 'pass',
);

final users = await db.select(
  db('users').where('active', '=', true).limit(10),
);

await db.destroy();

SQLite

import 'package:knex_dart_sqlite/knex_dart_sqlite.dart';

final db = await KnexSQLite.connect(filename: ':memory:');

await db.executeSchema(
  db.schema.createTable('users', (t) {
    t.increments('id');
    t.string('name');
  }),
);

await db.insert(db('users').insert({'name': 'Alice'}));
await db.destroy();

DuckDB (OLAP / Browser WASM)

import 'package:knex_dart_duckdb/knex_dart_duckdb.dart';

final db = await KnexDuckDB.memory();  // or KnexDuckDB.file('path.db')

final result = await db.select(
  db.queryBuilder()
    .from('sales')
    .sum('amount as total')
    .groupBy('region'),
);

await db.close();

DuckDB runs natively on macOS/Linux/Windows and in the browser via WASM — same API on both platforms.

Query Builder Only (No Connection)

Generate dialect-correct SQL without any driver installed:

import 'package:knex_dart/knex_dart.dart';
import 'package:knex_dart_capabilities/knex_dart_capabilities.dart';

// PostgreSQL — double-quoted identifiers, $1 placeholders
final q = KnexQuery.forDialect(KnexDialect.postgres);
print(q.from('users').where('active', '=', true).toSQL().sql);
// select * from "users" where "active" = $1

// MySQL — backtick identifiers, ? placeholders
final q2 = KnexQuery.forClient('mysql2');
print(q2.from('users').where('active', '=', true).toSQL().sql);
// select * from `users` where `active` = ?

Supported dialects: pg, mysql2, sqlite3, duckdb, snowflake, bigquery, turso, d1, mariadb, redshift.

Query Builder

SELECT

db('users').select(['id', 'name']).where('active', '=', true);

// Joins
db('users')
  .join('orders', 'users.id', '=', 'orders.user_id')
  .select(['users.name', 'orders.total'])
  .where('orders.status', '=', 'completed');

// Aggregates
db('sales')
  .count('* as total')
  .sum('amount as revenue')
  .where('status', '=', 'completed');

// LATERAL join (PostgreSQL / MySQL 8+)
db('users').leftJoinLateral('latest', (sub) {
  sub.table('orders')
    .where('orders.user_id', db.raw('"users"."id"'))
    .orderBy('created_at', 'desc')
    .limit(1);
});

INSERT / UPDATE / DELETE

db('users').insert({'name': 'Alice', 'email': '[e-mail]'});

db('users').where('id', '=', 1).update({'name': 'Bob'});

db('users').where('id', '=', 1).delete();

// Upsert
db('users')
  .insert({'email': '[e-mail]', 'name': 'Alice'})
  .onConflict('email')
  .merge();

Advanced

// CTEs
db('active_users')
  .withRecursive('active_users', db('users').where('active', '=', true))
  .select(['*']);

// Window functions
db.queryBuilder()
  .table('employees')
  .select(['id', 'department', 'salary'])
  .rowNumber('row_num', (a) => a.partitionBy('department').orderBy('salary', 'desc'))
  .lead('next_salary', 'salary', 'salary', 'department');

// Raw
db.raw('select * from users where id = ?', [1]);

Schema Builder

await db.executeSchema(
  db.schema.createTable('posts', (t) {
    t.increments('id');
    t.string('title').notNullable();
    t.text('body');
    t.integer('user_id').references('id').inTable('users');
    t.timestamps();
  }),
);

Transactions

await db.trx((trx) async {
  await trx.insert(trx('accounts').insert({'balance': 100}));
  await trx.update(trx('accounts').where('id', '=', 1).update({'balance': 0}));
});

Nested transactions use savepoints automatically:

await db.trx((outer) async {
  await outer.insert(outer('accounts').insert({'owner': 'Alice', 'balance': 1000}));

  try {
    await outer.trx((inner) async {
      await inner.insert(inner('accounts').insert({'owner': 'Bob', 'balance': 500}));
      throw Exception('rollback inner only');
    });
  } catch (_) {}

  // Alice's row committed; Bob's row rolled back
});

Streaming

final stream = db.streamQuery(
  db('events').where('processed', '=', false).orderBy('id'),
);

await for (final row in stream) {
  await handleEvent(row);
}

Supported on PostgreSQL, MySQL, SQLite, and DuckDB.

Migrations

// Code-first
await db.migrate.fromCode([
  const SqlMigration(
    name: '001_create_users',
    upSql: ['create table users (id integer primary key, email varchar(255))'],
    downSql: ['drop table users'],
  ),
]).latest();

// SQL directory (*.up.sql / *.down.sql files)
await db.migrate.fromSqlDir('./migrations').latest();

Optional Dialect Lint Plugin

knex_dart_lint provides static diagnostics for dialect-incompatible API usage:

dev_dependencies:
  custom_lint: ^0.8.1
  knex_dart_lint: ^0.1.0

# analysis_options.yaml
analyzer:
  plugins:
    - custom_lint

Catches: .returning() on MySQL/SQLite, fullOuterJoin() on MySQL/SQLite, joinLateral() on SQLite, .onConflict().merge() on unsupported dialects.

Features

• SELECT, INSERT, UPDATE, DELETE
• 29 WHERE methods — basic, IN, NULL, BETWEEN, EXISTS, OR, JSON, full-text
• JOINs — INNER, LEFT, RIGHT, FULL OUTER, CROSS, LATERAL
• Aggregates — COUNT, SUM, AVG, MIN, MAX with DISTINCT variants
• Window functions — rowNumber, rank, denseRank, lead, lag, firstValue, lastValue, nthValue + frame clauses
• CTEs — WITH and WITH RECURSIVE
• UNION, INTERSECT, EXCEPT (with ALL variants)
• Subqueries in WHERE IN, FROM, and SELECT
• Upserts — onConflict().merge()
• Schema builder — createTable, alterTable, dropTable, foreign keys, indexes
• Transactions + nested savepoints on PostgreSQL, MySQL, SQLite, DuckDB
• Streaming — streamQuery() for memory-efficient large result sets
• Connection pooling — PoolConfig for PostgreSQL and MySQL
• Migrations — code-first, SQL-directory, and JSON-schema sources
• Dialect-aware SQL — correct quoting and placeholders per database
• KnexQuery.forDialect() — generate SQL for any dialect without a driver
• Web/WASM — DuckDB runs in Chrome/headless browser
• 591+ tests, >85% line coverage

Side-by-Side: Knex.js vs knex_dart

Knex.js

knex('users')
  .select('name', 'email')
  .where('age', '>', 18)
  .orderBy('created_at', 'desc')
  .limit(10);

knex_dart

db('users')
  .select(['name', 'email'])
  .where('age', '>', 18)
  .orderBy('created_at', 'desc')
  .limit(10);

Acknowledgments

This project is a port of Knex.js, created by Tim Griesser and contributors.

License

MIT — see LICENSE.