SQLEasy

A dialect-aware SQL builder for Postgres, MySQL, SQL Server & SQLite — pure Dart, Flutter-ready, bring your own connection.

pub pub points license

SQLEasy is a lightweight, zero-dependency SQL query builder. It composes dialect-correct SELECT / INSERT / UPDATE / DELETE — plus CTEs, unions, and batched transactions — with a fluent API, and hands you the SQL string and its bound parameters.

It is not a driver or an ORM. You bring your own connection (postgres, mysql_client, sqflite, drift, …) and run what SQLEasy generates. That focus is the point: correct SQL for four dialects — identifier quoting, placeholder style, default schemas, and transaction wrappers — and nothing you have to wire around.

Pure Dart. No Flutter SDK dependency, no dart:io, no dart:html — it runs on Flutter mobile, desktop and web, and on plain Dart servers. It is the Dart port of @deebeetech/sqleasy, held to that implementation byte-for-byte by a shared golden corpus.

Part of the DeeBee ecosystem.

Installation

dart pub add sqleasy
# or, in a Flutter app:
flutter pub add sqleasy

Quick Start

import 'package:sqleasy/sqleasy.dart';

final builder = PostgresQuery().newBuilder()
  ..selectColumn('u', 'id')
  ..selectColumn('u', 'name', alias: 'userName')
  ..fromTable('users', alias: 'u')
  ..where('u', 'active', WhereOperator.equals, true);

final prepared = builder.parsePrepared();
// prepared.sql:    SELECT "u"."id", "u"."name" AS "userName" FROM "public"."users" AS "u" WHERE "u"."active" = $1;
// prepared.params: [true]   ← hand straight to your driver: conn.execute(prepared.sql, prepared.params)

Every mutator returns the builder, so cascades (..) and plain chaining both read cleanly. "No alias" and "no owner" are optional named parameters — there are no empty-string sentinels.

Database Support

Each dialect has its own entry point that handles identifier quoting, placeholder syntax, default schemas, and transaction delimiters automatically.

final mssql = MssqlQuery(); // [dbo].[table], sp_executesql (values inlined, params empty), BEGIN/COMMIT TRANSACTION
final mysql = MysqlQuery(); // `table`, ? placeholders, START TRANSACTION/COMMIT
final postgres = PostgresQuery(); // "public"."table", $1 placeholders, BEGIN/COMMIT
final sqlite = SqliteQuery(); // "table", ? placeholders, BEGIN/COMMIT

Query Examples

SELECT

final builder = PostgresQuery().newBuilder();

// Select all columns
builder..selectAll()..fromTable('users', alias: 'u');
// SELECT * FROM "public"."users" AS "u";

// Specific columns (alias is an optional named parameter)
builder.clearAll();
builder
  ..selectColumn('u', 'id')
  ..selectColumn('u', 'name', alias: 'userName')
  ..fromTable('users', alias: 'u');
// SELECT "u"."id", "u"."name" AS "userName" FROM "public"."users" AS "u";

// DISTINCT
builder.clearAll();
builder..distinct()..selectColumn('u', 'name')..fromTable('users', alias: 'u');
// SELECT DISTINCT "u"."name" FROM "public"."users" AS "u";

// Raw expression
builder.clearAll();
builder..selectRaw('COUNT(*) AS total')..fromTable('users', alias: 'u');
// SELECT COUNT(*) AS total FROM "public"."users" AS "u";

// Scalar sub-query in SELECT
builder.clearAll();
builder
  ..selectAll()
  ..selectWithBuilder('orderCount', (sb) => sb
    ..selectRaw('COUNT(*)')
    ..fromTable('orders', alias: 'o'))
  ..fromTable('users', alias: 'u');
// SELECT *, (SELECT COUNT(*) FROM "public"."orders" AS "o") AS "orderCount" FROM "public"."users" AS "u";

WHERE

final builder = PostgresQuery().newBuilder();

// Comparison operators
builder
  ..selectAll()
  ..fromTable('users', alias: 'u')
  ..where('u', 'age', WhereOperator.greaterThanOrEquals, 18);

// AND / OR
builder.clearAll();
builder
  ..selectAll()
  ..fromTable('users', alias: 'u')
  ..where('u', 'active', WhereOperator.equals, true)
  ..and()
  ..where('u', 'age', WhereOperator.greaterThan, 21);
// ... WHERE "u"."active" = $1 AND "u"."age" > $2;   params: [true, 21]

// BETWEEN
builder.clearAll();
builder..selectAll()..fromTable('users', alias: 'u')..whereBetween('u', 'age', 18, 65);

// IS NULL / IS NOT NULL
builder.clearAll();
builder..selectAll()..fromTable('users', alias: 'u')..whereNotNull('u', 'email');

// IN (values)
builder.clearAll();
builder..selectAll()..fromTable('users', alias: 'u')..whereInValues('u', 'role', ['admin', 'moderator']);
// ... WHERE "u"."role" IN ($1, $2);   params: [admin, moderator]

// IN (sub-query)
builder.clearAll();
builder
  ..selectAll()
  ..fromTable('users', alias: 'u')
  ..whereInWithBuilder('u', 'id', (sb) => sb
    ..selectColumn('o', 'user_id')
    ..fromTable('orders', alias: 'o'));

// Grouped conditions
builder.clearAll();
builder
  ..selectAll()
  ..fromTable('users', alias: 'u')
  ..where('u', 'active', WhereOperator.equals, true)
  ..and()
  ..whereGroup((gb) => gb
    ..where('u', 'role', WhereOperator.equals, 'admin')
    ..or()
    ..where('u', 'role', WhereOperator.equals, 'moderator'));
// ... WHERE "u"."active" = $1 AND ("u"."role" = $2 OR "u"."role" = $3);

JOIN

joinTable takes the ON-condition callback as its third argument; the table alias is an optional named parameter.

final builder = PostgresQuery().newBuilder();

builder
  ..selectAll()
  ..fromTable('users', alias: 'u')
  ..joinTable(JoinType.inner, 'orders', (jb) {
    jb.on('u', 'id', JoinOperator.equals, 'o', 'user_id');
  }, alias: 'o');
// SELECT * FROM "public"."users" AS "u"
//   INNER JOIN "public"."orders" AS "o" ON "u"."id" = "o"."user_id";

// Multiple ON conditions
builder.clearAll();
builder
  ..selectAll()
  ..fromTable('users', alias: 'u')
  ..joinTable(JoinType.inner, 'orders', (jb) {
    jb
      ..on('u', 'id', JoinOperator.equals, 'o', 'user_id')
      ..and()
      ..on('u', 'tenant_id', JoinOperator.equals, 'o', 'tenant_id');
  }, alias: 'o');

// Join to a sub-query
builder.clearAll();
builder
  ..selectAll()
  ..fromTable('users', alias: 'u')
  ..joinWithBuilder(
    JoinType.inner,
    'recent_orders',
    (sb) => sb
      ..selectAll()
      ..fromTable('orders', alias: 'o')
      ..where('o', 'created_at', WhereOperator.greaterThan, '2024-01-01'),
    (jb) => jb.on('u', 'id', JoinOperator.equals, 'recent_orders', 'user_id'),
  );

INSERT

final builder = PostgresQuery().newBuilder()
  ..insertInto('users')
  ..insertColumns(['name', 'email', 'age'])
  ..insertValues(['John', 'john@example.com', 30]);
// INSERT INTO "public"."users" ("name", "email", "age") VALUES ($1, $2, $3);
// params: [John, john@example.com, 30]

// Multi-row insert: call insertValues once per row.

UPDATE

final builder = PostgresQuery().newBuilder()
  ..updateTable('users', alias: 'u')
  ..set('name', 'John Updated')
  ..set('age', 31)
  ..where('u', 'id', WhereOperator.equals, 1);
// UPDATE "public"."users" AS "u" SET "name" = $1, "age" = $2 WHERE "u"."id" = $3;

// Raw SET expression: ..setRaw('"login_count" = "login_count" + 1')

DELETE

final builder = PostgresQuery().newBuilder()
  ..deleteFrom('users', alias: 'u')
  ..where('u', 'id', WhereOperator.equals, 1);
// DELETE FROM "public"."users" AS "u" WHERE "u"."id" = $1;

ORDER BY / LIMIT / OFFSET

final builder = PostgresQuery().newBuilder()
  ..selectAll()
  ..fromTable('users', alias: 'u')
  ..orderByColumn('u', 'name', OrderByDirection.ascending)
  ..limit(10)
  ..offset(20);

limit() is pagination: on MSSQL it renders as OFFSET … ROWS FETCH NEXT … ROWS ONLY, which T-SQL accepts only alongside an ORDER BY — so paginating without one throws rather than emitting SQL the server would reject. top(n) is the separate, SQL-Server-only manual row cap, and the tool to reach for when you want TOP (n) and no ordering. The two are not interchangeable, and limit() never silently becomes a TOP.

GROUP BY / HAVING

final builder = PostgresQuery().newBuilder()
  ..selectColumn('u', 'role')
  ..selectRaw('COUNT(*) AS cnt')
  ..fromTable('users', alias: 'u')
  ..groupByColumn('u', 'role')
  ..having('u', 'role', WhereOperator.notEquals, 'guest');

Common Table Expressions (CTEs)

final builder = PostgresQuery().newBuilder()
  ..cte('active_users', (cb) => cb
    ..selectAll()
    ..fromTable('users', alias: 'u')
    ..where('u', 'active', WhereOperator.equals, true))
  ..selectAll()
  ..fromRaw('"active_users" AS "au"');
// WITH "active_users" AS (SELECT * FROM "public"."users" AS "u" WHERE "u"."active" = $1)
//   SELECT * FROM "active_users" AS "au";

// Recursive: ..cteRecursive('hierarchy', (cb) { ... })

UNION / INTERSECT / EXCEPT

final builder = PostgresQuery().newBuilder()
  ..selectColumn('u', 'name')
  ..fromTable('users', alias: 'u')
  ..union((ub) => ub
    ..selectColumn('c', 'name')
    ..fromTable('customers', alias: 'c'));

// Also available: unionAll(), intersect(), except()

Multi-Builder (Batched Statements)

Compose multiple statements into a single SQL string, optionally wrapped in a transaction.

final multi = PostgresQuery().newMultiBuilder();

multi.addBuilder('insert_user')
  ..insertInto('users')
  ..insertColumns(['name', 'email'])
  ..insertValues(['John', 'john@example.com']);

multi.addBuilder('update_count')
  ..updateTable('stats', alias: 's')
  ..set('user_count', 100)
  ..where('s', 'id', WhereOperator.equals, 1);

print(multi.parseRaw());
// BEGIN; INSERT INTO "public"."users" ("name", "email") VALUES (John, john@example.com);UPDATE "public"."stats" AS "s" SET "user_count" = 100 WHERE "s"."id" = 1;COMMIT;

// Named builders can be removed or reordered before rendering.
multi.reorderBuilders(['update_count', 'insert_user']);

// Disable transaction wrapping (statements emitted back-to-back, no BEGIN/COMMIT).
multi.setTransactionState(MultiBuilderTransactionState.transactionOff);

Executing a batch

parse() / parseRaw() render the batch as one string for display or logging — they carry no bound parameters, and placeholder numbering restarts per statement, so on Postgres, MySQL, and SQLite that single string is not an execution-safe prepared call.

To run a batch, use preparedStatements() — the executable unit — and execute each in order inside a transaction on your own connection:

await conn.execute('BEGIN');
try {
  for (final stmt in multi.preparedStatements()) {
    await conn.execute(stmt.sql, stmt.params);
  }
  await conn.execute('COMMIT');
} catch (_) {
  await conn.execute('ROLLBACK');
  rethrow;
}

Prepared Statements vs Raw SQL

Every builder offers three renderings:

  • parsePrepared() — the execution-safe one. Returns a PreparedSql (.sql, .params), whose shape is dialect-specific:
    • Postgres — $1/$2/… placeholders + the ordered params.
    • MySQL / SQLite — positional ? placeholders + the ordered params.
    • MSSQL — a self-contained exec sp_executesql … batch with the values inlined as escaped arguments, so params is empty. It is still injection-safe: values are escaped and passed as sp_executesql arguments, never concatenated into the statement text.
  • parse() — the SQL string with placeholders, without the values (handy for logging the shape).
  • parseRaw() — values inlined into the SQL. Debug / display only — not escaped, not execution-safe. Never run parseRaw() output against a database.
final builder = PostgresQuery().newBuilder()
  ..selectAll()
  ..fromTable('users', alias: 'u')
  ..where('u', 'id', WhereOperator.equals, 42);

builder.parsePrepared(); // .sql: '... WHERE "u"."id" = $1;'  .params: [42]
builder.parse();         // SELECT * FROM "public"."users" AS "u" WHERE "u"."id" = $1;
builder.parseRaw();      // SELECT * FROM "public"."users" AS "u" WHERE "u"."id" = 42;   (debug only)

Configuration

Pass a RuntimeConfiguration to customize behavior:

final rc = RuntimeConfiguration()
  ..maxRowsReturned = 500
  ..customConfiguration = {'timeout': 30};

final query = PostgresQuery(rc);

Correctness across Flutter web and mobile

Every query this package produces is verified against a shared golden corpus — 189 cases across all four dialects — that both this package and the TypeScript original must reproduce byte-for-byte. That is not decoration; it defends against a real, silent trap.

JavaScript has one number type. Dart has int and double — and Dart does not agree with itself across platforms:

expression Dart VM (Flutter mobile/desktop) dart2js (Flutter web)
5.0 is int false true
(5.0).toString() "5.0" "5"
double.infinity is int false true

So a naive builder emits different SQL on Flutter web than on Flutter mobile, from the same source, with nothing thrown and nothing logged: 5.0 binds as @p0 tinyint / = 5 on the web and @p0 float / = 5.0 on mobile. Every value here passes through one platform-independent rendering layer, and the test suite runs on both platforms — dart test (the VM) and dart test -p chrome (dart2js) — so the two can never diverge. See goldens/README.md.

Development

dart pub get
dart analyze
dart test               # the Dart VM — Flutter mobile and desktop
dart test -p chrome     # dart2js — Flutter web. Not redundant, not optional.

dart run tool/fetch_goldens.dart    # pull the pinned corpus from the TypeScript repo's tag
dart run tool/embed_goldens.dart    # re-embed it for the dart2js test run

License

MIT © DeeBee Tech

Libraries

sqleasy
A dialect-aware SQL builder for Postgres, MySQL, SQL Server and SQLite.