knex_dart 1.1.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 for Dart backends.

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_capabilities	Shared dialect capability matrix
knex_dart_lint	Optional static dialect lint plugin

knex_dart is the core package — it contains the query builder, schema builder, and compiler logic but no database connectivity. Pick the driver package for your database.

Documentation

Full documentation is available at:

• https://docs.knex.mahawarkartikey.in/
• Migrations: https://docs.knex.mahawarkartikey.in/migration/migrations
• Dialect Lint (optional): https://docs.knex.mahawarkartikey.in/tooling/dialect-lint
• Transactions: https://docs.knex.mahawarkartikey.in/query-building/transactions
• Schema Builder: https://docs.knex.mahawarkartikey.in/query-building/schema-builder

Installation

dependencies:
  knex_dart_postgres: ^0.1.1  # or mysql / sqlite

The driver package pulls in knex_dart automatically.

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'}));

MySQL

import 'package:knex_dart_mysql/knex_dart_mysql.dart';

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

Query Builder

All driver packages expose the same Knex query builder API.

SELECT

// Basic
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');

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();

Advanced

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

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

// 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 are supported via savepoints (SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT).

Migrations

Knex Dart supports explicit migration source styles:

• fromCode(...) for in-code migration units
• fromSqlDir(...) for filesystem *.up.sql / *.down.sql migrations
• fromConfig() to read MigrationConfig.directory (default ./migrations)
• fromSchema(...) for external schema input mapped to KnexSchemaAst

// 1) Code-first (SQL migration unit)
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();

// 2) SQL directory
await db.migrate.fromSqlDir('./migrations').latest();

// 3) From config.migrations.directory
await db.migrate.fromConfig().latest();

Schema builder style is also supported by implementing a migration unit:

class CreateUsersMigration implements MigrationUnit {
  @override
  String get name => '002_create_users_with_builder';

  @override
  Future<void> up(Knex db) async {
    final schema = db.schema;
    schema.createTable('users', (t) {
      t.increments('id');
      t.string('email', 255).notNullable().unique();
    });
    await schema.execute();
  }

  @override
  Future<void> down(Knex db) async {
    final schema = db.schema;
    schema.dropTableIfExists('users');
    await schema.execute();
  }
}

Optional Dialect Lint Plugin

knex_dart_lint is optional and provides static diagnostics for dialect-incompatible query APIs.

Example warnings:

• .returning() on MySQL/SQLite
• fullOuterJoin() on SQLite/MySQL
• joinLateral() on SQLite

Setup:

dev_dependencies:
  custom_lint: ^0.8.1
  knex_dart_lint: ^0.1.0

# analysis_options.yaml
analyzer:
  plugins:
    - custom_lint

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

Features

• SELECT, INSERT, UPDATE, DELETE
• WHERE — basic, IN, NULL, BETWEEN, EXISTS, OR, raw
• JOINs — INNER, LEFT, RIGHT, FULL OUTER, CROSS, with callback builder
• Aggregates — COUNT, SUM, AVG, MIN, MAX with DISTINCT variants
• ORDER BY, GROUP BY, HAVING, LIMIT, OFFSET
• Raw queries with ?, :name, ?? binding formats
• RETURNING clause (PostgreSQL)
• CTEs (WITH / WITH RECURSIVE)
• UNIONs, INTERSECTs, EXCEPTs
• Subqueries
• JSON operators (whereJsonPath, whereJsonSupersetOf, etc.)
• Full-text search (whereFullText)
• Upserts (onConflict().merge())
• Schema builder — createTable, alterTable, dropTable, foreign keys, indexes
• Migrations — code-first, SQL-directory, and external-schema sources
• Dialect-aware SQL (PostgreSQL $1, MySQL/SQLite ?)

Acknowledgments

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

License

MIT — see LICENSE.