sql_lite_builder 🏗️ #

Type-safe, fluent SQL query builder for Flutter + sqflite.
Write database queries without raw strings. No code generation. No ORM overhead.

The Problem #

// ❌ Dangerous — SQL Injection if `id` comes from user input
db.rawQuery("SELECT * FROM users WHERE id = $id");

// ❌ Runtime crash — typo in column name, missing comma, wrong quote
db.rawQuery("SELECT name age FROM users WHRE active = 1");

Every raw SQL string in your codebase is a potential:

• SQL Injection vulnerability
• Runtime crash from a typo discovered too late

The Solution #

// ✅ Safe — value is always a '?' bound parameter
final users = await db.table('users')
  .where('id', WhereOp.eq, id)
  .get();

// ✅ IDE-autocomplete, type-checked, readable
final result = await db.table('users')
  .select(['id', 'name', 'email'])
  .where('age', WhereOp.gte, 18)
  .where('is_active', WhereOp.eq, true)
  .orderBy('name')
  .limit(20)
  .get();

Why sql_lite_builder? #

sql_lite_builder sits in the sweet spot: safer than raw sqflite, lighter than Drift.

Installation #

# pubspec.yaml
dependencies:
  sql_lite_builder: ^1.0.0
  sqflite: ^2.3.3+1
  path: ^1.9.0

import 'package:sql_lite_builder/sql_lite_builder.dart';

Quick Start #

import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import 'package:sql_lite_builder/sql_lite_builder.dart';

Future<void> main() async {
  // Open your database as usual
  final db = await openDatabase(join(await getDatabasesPath(), 'app.db'));

  // Now use the fluent API — db.table() is added via Extension Methods
  final users = await db.table('users')
    .where('age', WhereOp.gte, 18)
    .where('is_active', WhereOp.eq, true)
    .orderBy('name')
    .limit(10)
    .get();
}

API Reference #

Entry Point #

db.table('users')   // → QueryBuilder

SELECT #

// All columns (default)
db.table('users').get()

// Specific columns
db.table('users').select(['id', 'name', 'email']).get()

// DISTINCT
db.table('users').selectDistinct(['country']).get()

// Raw expression
db.table('users').selectRaw('COUNT(*) AS cnt, AVG(age) AS avg_age').get()

WHERE — All Variants #

// Basic comparison (operators: =, !=, >, <, >=, <=, LIKE, NOT LIKE, GLOB)
.where('age', WhereOp.gt, 18)
.where('name', WhereOp.like, '%ahmed%')
.where('is_active', WhereOp.eq, true)   // bool → 1

// OR condition
.where('role', WhereOp.eq, 'admin')
.orWhere('role', WhereOp.eq, 'moderator')

// IN / NOT IN
.whereIn('status', ['active', 'pending', 'verified'])
.whereNotIn('role', ['banned', 'suspended'])

// BETWEEN / NOT BETWEEN
.whereBetween('age', 18, 65)
.whereNotBetween('score', 0.0, 50.0)

// IS NULL / IS NOT NULL
.whereNull('deleted_at')
.whereNotNull('email')

// Nested groups (parentheses)
.whereNested((q) => q
  .where('country', WhereOp.eq, 'SA')
  .orWhere('country', WhereOp.eq, 'AE')
)
// → WHERE (`country` = ? OR `country` = ?)

// OR nested group
.orWhereNested((q) => q
  .where('role', WhereOp.eq, 'admin')
  .where('verified', WhereOp.eq, true)
)

// Raw SQL (bindings required for values)
.whereRaw("strftime('%Y', created_at) = ?", ['2024'])

JOIN #

// INNER JOIN
db.table('orders')
  .join('users', 'orders.user_id', '=', 'users.id')

// LEFT JOIN
db.table('users')
  .leftJoin('posts', 'users.id', '=', 'posts.user_id')

// CROSS JOIN
db.table('colors').crossJoin('sizes')

ORDER BY #

.orderBy('name')                          // ASC (default)
.orderBy('created_at', descending: true)  // DESC
.orderByDesc('views')                     // shorthand DESC
.inRandomOrder()                          // ORDER BY RANDOM()

// Multiple columns
.orderBy('age', descending: true).orderBy('name')

GROUP BY / HAVING #

db.table('posts')
  .selectRaw('user_id, COUNT(*) AS cnt, SUM(views) AS total')
  .groupBy('user_id')
  .having('COUNT(*)', '>', 2)
  .orderByDesc('total')
  .get()

LIMIT / OFFSET / PAGINATION #

.limit(20)
.offset(40)
.paginate(page: 3, perPage: 10)   // LIMIT 10 OFFSET 20
.forPage(page: 3, perPage: 10)    // alias for paginate

Terminal — Read #

// All rows
await db.table('users').get()    // → List<Map<String, dynamic>>

// Typed all rows
await db.table('users').getAs(User.fromMap)   // → List<User>

// First row or null
await db.table('users').first()

// First row typed or null
await db.table('users').firstAs(User.fromMap)

// First row or throws RecordNotFoundException
await db.table('users').firstOrFail()

// First row typed or throws
await db.table('users').firstOrFailAs(User.fromMap)

// Single column value
await db.table('users').where('id', '=', 5).value('name')

// Flat list of one column
await db.table('users').pluck('email')   // → ['a@t.com', 'b@t.com']

// Chunked processing (memory-safe for large tables)
await db.table('users').chunk(100, (rows) async {
  await processRows(rows);
  return true;  // return false to stop
});

// Paginated result with metadata
final page = await db.table('users')
  .orderBy('name')
  .paginateAs(page: 1, perPage: 20, mapper: User.fromMap);

print(page.total);      // total matching rows
print(page.lastPage);   // total number of pages
print(page.hasNextPage);

Terminal — Aggregates #

await db.table('users').count()           // → int
await db.table('users').count('id')       // COUNT(id)
await db.table('users').max('age')        // → num?
await db.table('users').min('age')        // → num?
await db.table('users').avg('score')      // → num?
await db.table('users').sum('points')     // → num?
await db.table('users').exists()          // → bool
await db.table('users').doesntExist()     // → bool

Terminal — Write #

// INSERT — returns new row id
final id = await db.table('users').insert({
  'name': 'Ahmed',
  'email': 'ahmed@example.com',
  'age': 25,
  'is_active': true,          // bool → 1 automatically
  'created_at': DateTime.now(), // DateTime → ISO 8601 automatically
});

// INSERT OR IGNORE (silently ignores unique violations)
await db.table('users').insertOrIgnore({'name': '...', 'email': '...'});

// INSERT OR REPLACE (upsert — deletes conflicting row and re-inserts)
await db.table('users').upsert({'name': '...', 'email': '...'});

// Batch insert
await db.table('products').insertMany([
  {'name': 'iPhone',  'price': 999},
  {'name': 'Samsung', 'price': 799},
]);

// UPDATE — requires WHERE (throws DangerousOperationException if missing)
await db.table('users')
  .where('id', WhereOp.eq, userId)
  .update({'name': 'New Name', 'age': 30});

// updateAll — intentionally update every row (no WHERE required)
await db.table('users').updateAll({'is_active': true});

// INCREMENT / DECREMENT
await db.table('posts').where('id', WhereOp.eq, postId).increment('views');
await db.table('accounts').where('id', WhereOp.eq, id).decrement('balance', by: 100);

// DELETE — requires WHERE (throws DangerousOperationException if missing)
await db.table('users')
  .where('is_active', WhereOp.eq, false)
  .delete();

// TRUNCATE — intentionally delete all rows
await db.table('sessions').truncate();

Transactions #

await db.transactionSafe((txn) async {
  await txn.table('accounts')
    .where('id', WhereOp.eq, fromId)
    .decrement('balance', by: amount);

  await txn.table('accounts')
    .where('id', WhereOp.eq, toId)
    .increment('balance', by: amount);

  // If anything throws here, both operations are rolled back
});

Schema Definition (Optional) #

class UsersTable extends TableSchema {
  @override
  String get name => 'users';

  @override
  List<ColumnDefinition> get columns => [
    ColumnDefinition.integer('id').primaryKey().autoIncrement(),
    ColumnDefinition.text('name').notNull(),
    ColumnDefinition.text('email').notNull().unique(),
    ColumnDefinition.integer('age').nullable(),
    ColumnDefinition.boolean('is_active').notNull().withDefault(true),
    ColumnDefinition.dateTime('created_at').defaultNow(),
    ColumnDefinition.integer('score').withDefault(0),
    ColumnDefinition.integer('user_id')
      .references('users', 'id', onDelete: 'CASCADE'),
  ];

  @override
  List<IndexDefinition> get indexes => [
    IndexDefinition.on(name, ['email']),
    IndexDefinition.unique(name, ['email']),
    IndexDefinition.on(name, ['created_at']),
  ];
}

// Generate SQL
print(UsersTable().toCreateSql());
await db.execute(UsersTable().toCreateSql());

// All statements (CREATE TABLE + CREATE INDEX)
for (final sql in UsersTable().allCreateStatements()) {
  await db.execute(sql);
}

Migrations #

// Define all migrations (never delete or reorder — only append)
final migrations = [
  Migration(
    version: 1,
    description: 'Create users table',
    up: (db) async => db.execute(UsersTable().toCreateSql()),
    down: (db) async => db.execute('DROP TABLE IF EXISTS users'),
  ),
  Migration(
    version: 2,
    description: 'Add phone column',
    up: (db) async =>
        db.execute('ALTER TABLE users ADD COLUMN phone TEXT'),
  ),
];

// Run in onCreate / onUpgrade:
final db = await openDatabase(
  path,
  version: 1,
  onCreate: (db, _) => db.migrations.runAll(migrations),
  onOpen:   (db)    => db.migrations.runAll(migrations),
);

Type Conversion #

// Reading back from the database:
final isActive = TypeConverter.toBool(row['is_active']);
final createdAt = TypeConverter.toDateTime(row['created_at']);
final role = TypeConverter.toEnum(row['role'], Role.values);

Safety Guards #

// ❌ DangerousOperationException — protect against accidental full-table ops
await db.table('users').delete();           // must add .where(...)
await db.table('users').update({'age': 0}); // must add .where(...)

// ✅ Explicit intentional operations
await db.table('users').truncate();         // deletes all
await db.table('users').updateAll({'flag': 0}); // updates all

Debug #

// Inspect generated SQL without executing
print(
  db.table('users')
    .where('age', WhereOp.gt, 18)
    .orderBy('name')
    .limit(10)
    .toSqlWithBindings()
);
// Output:
// SELECT * FROM `users` WHERE `age` > ? ORDER BY name ASC LIMIT 10 | bindings: [18]

Logging Configuration #

// In main():
SqlLiteBuilderConfig.configure(
  logging: true,          // enable/disable all logging
  showBindings: false,    // hide values in production
  showDuration: true,     // show query execution time
  logSink: (msg) => myLogger.debug(msg),  // custom sink
);

Database Helpers #

await db.tableExists('users')           // → bool
await db.columnExists('users', 'phone') // → bool
await db.rowCount('users')              // → int
await db.setForeignKeys(enabled: true)  // enable FK enforcement
await db.vacuum()                       // reclaim disk space
await db.getUserVersion()               // → int
await db.setUserVersion(3)

Contributing #

Contributions, issues and feature requests are welcome!
See CONTRIBUTING.md or open an issue.

License #

MIT © 2026 sql_lite_builder contributors