Dart 79 🎯 Object-Relational Mapping (ORM) and Database Connectors in Pure Dart - KandZ

⏱️ Estimated read: 14 min | |

Dart’s server-side ecosystem has no single ORM that dominates the way Hibernate dominates Java or ActiveRecord dominates Ruby. Instead, the landscape is a spectrum: raw drivers that speak the wire protocol, query builders that generate dialect-correct SQL, and ORMs that map tables to typed Dart classes with code generation. The choice between them is a choice about how much abstraction the application needs and how much control it is willing to give up. A driver gives you the SQL and the connection; an ORM gives you models, migrations, and relationships, at the cost of a code generation step and a layer of indirection.

The two families that matter for pure Dart are the SQLite-centric tools and the PostgreSQL-centric tools. SQLite is the embedded database, and Drift is its most mature ORM, built on sqlite3 with reactive streams, type-safe queries, and cross-platform support . PostgreSQL is the server database, and the options range from raw drivers like aim_postgres to query builders like knex_dart to ORMs like orm and duxt_orm . The decision is not about which is “best”—it is about whether the application can tolerate code generation, whether it needs reactive queries, and whether it needs to support multiple database backends.

Key point: A raw driver (sqlite3, aim_postgres) gives full SQL control with no code generation; a query builder (knex_dart) generates dialect-correct SQL with a fluent API but no models; an ORM (drift, duxt_orm, orm) maps tables to typed classes with migrations, relationships, and code generation. Choose the lowest layer that solves the problem.

Why the ORM landscape is fragmented in Dart

The Flutter problem. Dart’s primary use case is Flutter, and Flutter apps need local persistence, not server database access. This is why the most mature ORM, Drift, is SQLite-focused and designed for the mobile and desktop client, with reactive streams that rebuild widgets when the underlying data changes . The server-side PostgreSQL ORMs are younger and less battle-tested because the demand is smaller.

The code generation problem. Dart’s build system is build_runner, which is slow and has a reputation for complicating the development loop. An ORM that requires code generation adds a step to every model change: modify the class, run build_runner, wait, then use the generated code. Some developers accept this for the type safety and the relationship mapping; others prefer the query builder or the raw driver because the iteration loop is faster .

The dialect problem. A query builder like knex_dart generates SQL for nine dialects, including PostgreSQL, MySQL, SQLite, and DuckDB, from a single Dart API . An ORM that is tied to one database, like Drift to SQLite, cannot be used for a PostgreSQL service without switching to a different tool. The fragmented landscape is partly a consequence of the fragmented database support: no single Dart ORM covers every engine with the same maturity.

The migration problem. An ORM without migrations is a model layer without a schema management strategy. Drift has migrations , duxt_orm has auto-migrations , orm has reviewed migration definitions , and dormql has a migration runner with createTable, addColumn, and dropTable helpers . The migration story is often the deciding factor, because a model layer that cannot evolve the schema is not usable beyond a prototype.

a. The raw driver: `sqlite3` and `aim_postgres`

The lowest layer is the driver that speaks the database’s wire protocol or C API. The sqlite3 package wraps the SQLite C library, and aim_postgres implements the PostgreSQL wire protocol in pure Dart . These packages do not generate code, do not map models, and do not manage migrations. They execute SQL and return result sets.

// Import the sqlite3 package for the SQLite C library
import 'package:sqlite3/sqlite3.dart';

void main() {
  // Open an in-memory database
  final db = sqlite3.openInMemory();

  // Execute a schema statement
  db.execute('''
    CREATE TABLE artists (
      id INTEGER NOT NULL PRIMARY KEY,
      name TEXT NOT NULL
    )
  ''');

  // Prepare an insert statement for repeated execution
  final stmt = db.prepare('INSERT INTO artists (name) VALUES (?)');
  // Execute the statement with each parameter set
  stmt.execute(['The Beatles']);
  stmt.execute(['Led Zeppelin']);
  // Dispose the statement to release resources
  stmt.dispose();

  // Run a select with a parameterized LIKE clause
  final results = db.select(
    'SELECT * FROM artists WHERE name LIKE ?',
    ['The %'],
  );

  // Iterate the result set and print each row
  for (final row in results) {
    print('Artist: ${row['name']}');
  }

  // Dispose the database to avoid leaks
  db.dispose();
}

The sqlite3 package is synchronous. The query blocks the isolate until it returns. For a CLI tool or a server with a small database, this is acceptable. For a Flutter app with a large database, the synchronous call on the main isolate causes jank, which is why packages like resqlite and drift move the work to a worker isolate .

aim_postgres is the asynchronous equivalent for PostgreSQL. It supports named and positional parameters, transactions, and SSL/TLS . The query method returns a Future that completes with the result set, and the transaction method accepts a callback that receives a transaction object.

b. The query builder: `knex_dart`

A query builder sits above the driver and below the ORM. It provides a fluent API for constructing SQL, and it generates the correct dialect for the target database. knex_dart is a port of the JavaScript Knex library, and it supports PostgreSQL, MySQL, SQLite, DuckDB, and several others .

// Import the PostgreSQL driver, which pulls in knex_dart
import 'package:knex_dart_postgres/knex_dart_postgres.dart';

Future<void> main() async {
  // Connect to the PostgreSQL database
  final db = await KnexPostgres.connect(
    host: 'localhost',
    database: 'mydb',
    username: 'user',
    password: 'pass',
  );

  // Build a query: select from users where active is true, limit 10
  final query = db('users')
      .where('active', '=', true)
      .limit(10);

  // Execute the query and get the rows
  final users = await db.select(query);

  // Print the first user's name
  print(users.first['name']);

  // Close the connection
  await db.destroy();
}

The query builder does not generate typed models. The result is a List<Map<String, dynamic>>, and the application is responsible for converting the maps to domain objects. The advantage is that the builder is not tied to a schema definition and does not require code generation. The disadvantage is that a column name typo is a runtime error, not a compile-time error, and the relationships between tables are not modeled.

The knex_dart query builder supports joins, window functions, transactions, and streaming . The optional knex_dart_lint plugin provides static diagnostics for dialect-incompatible API usage, such as .returning() on MySQL . The builder is the correct choice when the application wants SQL control and cross-dialect support without the code generation of an ORM.

c. The ORM: `drift`, `duxt_orm`, and `orm`

An ORM maps tables to Dart classes and generates the code that converts between the two. The three ORMs that matter for pure Dart are Drift for SQLite, duxt_orm for PostgreSQL, MySQL, and SQLite, and orm for SQLite and PostgreSQL .

Drift is the most mature. It is built on sqlite3, and it provides type-safe queries, reactive streams, migrations, and cross-platform support . The table definitions are Dart classes, and the code generator produces the data classes and the query API. The reactive streams are the feature that distinguishes Drift: a query can be turned into a stream that emits a new result whenever the underlying table changes, which is what a Flutter widget needs to rebuild automatically .

duxt_orm is an ActiveRecord-style ORM with auto-migrations. A model extends Entity, and the schema is defined in the model’s register method. The DuxtOrm.migrate() call creates the tables automatically, and the Model<T> class provides all(), find(), where(), and save() . The ORM supports soft deletes by default, with destroy() setting a deleted_at column and withTrashed() including the deleted rows in a query . The trade-off is that the ActiveRecord pattern couples the model to the database, which is convenient for small applications but harder to test in isolation.

orm is a newer beta ORM that generates typed tables and query clients from plain Dart models. It requires the dart run orm generate command to produce the client code, and it supports SQLite and PostgreSQL . The ORM’s query API includes typed filters (eq, gte, startsWith), whereAny for OR conditions, and orderBy and limit . The migration system uses reviewed migration definitions with fingerprints, which is more rigorous than the auto-migration of duxt_orm but requires more manual work.

The choice between them is determined by the database and the application. A Flutter app with local SQLite storage should use Drift, because it is the only one with production-grade reactive streams and cross-platform support. A Dart server with PostgreSQL should consider orm for its typed queries and migration verification, or duxt_orm for its lower ceremony. The orm package is still beta, so a production service should weigh the stability risk against the type safety benefit.

Complete Example Session

The following session demonstrates the three layers: a raw driver query with sqlite3, a query builder query with knex_dart, and an ORM query with Drift.

// ============================================
// PART 1: THE RAW DRIVER (sqlite3)
// ============================================
import 'package:sqlite3/sqlite3.dart';

void rawDriverExample() {
  // Open the in-memory database
  final db = sqlite3.openInMemory();
  // Create the schema
  db.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)');
  // Insert a row with a parameterized statement
  db.execute('INSERT INTO users (name) VALUES (?)', ['Alice']);
  // Query the row
  final rows = db.select('SELECT * FROM users');
  // Print the name
  print('Raw: ${rows.first['name']}');
  // Dispose the database
  db.dispose();
}

// ============================================
// PART 2: THE QUERY BUILDER (knex_dart)
// ============================================
import 'package:knex_dart_sqlite/knex_dart_sqlite.dart';

Future<void> queryBuilderExample() async {
  // Connect to an in-memory SQLite database
  final db = await KnexSQLite.connect(filename: ':memory:');
  // Create the table with the schema builder
  await db.executeSchema((schema) {
    schema.createTable('users', (t) {
      t.increments('id');
      t.string('name');
    });
  });
  // Insert a row through the builder
  await db.insert(db('users').insert({'name': 'Alice'}));
  // Select the row through the builder
  final rows = await db.select(db('users').select(['name']));
  // Print the name
  print('Builder: ${rows.first['name']}');
  // Close the connection
  await db.close();
}

// ============================================
// PART 3: THE ORM (Drift)
// ============================================
import 'package:drift/drift.dart';
import 'package:drift/native.dart';

// The table definition is a Dart class
class Users extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text()();
}

// The database class generates the query API
@DriftDatabase(tables: [Users])
class AppDatabase extends _$AppDatabase {
  AppDatabase() : super(NativeDatabase.memory());
  @override
  int get schemaVersion => 1;
}

Future<void> ormExample() async {
  // Create the database
  final db = AppDatabase();
  // Insert a row through the generated API
  await db.into(db.users).insert(UsersCompanion.insert(name: 'Alice'));
  // Query the row through the generated API
  final users = await db.select(db.users).get();
  // Print the name
  print('ORM: ${users.first.name}');
  // Close the database
  await db.close();
}

The three parts cover the raw driver, the query builder, and the ORM. What the session demonstrates is that the raw driver gives the most control with the least abstraction, the query builder adds cross-dialect SQL generation without models, and the ORM adds typed models and generated queries at the cost of code generation.

Quick Reference

Layer Comparison

Layer	Package	Code Gen	Typed Models	Migrations
Driver	`sqlite3`, `aim_postgres`	No	No	No
Query builder	`knex_dart`	No	No	Yes
ORM (SQLite)	`drift`	Yes	Yes	Yes
ORM (PG/MySQL)	`duxt_orm`	No	Yes	Auto
ORM (PG/SQLite)	`orm`	Yes	Yes	Reviewed

Database Support

Package	PostgreSQL	MySQL	SQLite	DuckDB
`sqlite3`	No	No	Yes	No
`aim_postgres`	Yes	No	No	No
`knex_dart`	Yes	Yes	Yes	Yes
`drift`	No	No	Yes	No
`duxt_orm`	Yes	Yes	Yes	No
`orm`	Yes	No	Yes	No

Key Features

Feature	`drift`	`duxt_orm`	`orm`
Reactive streams	Yes	No	No
Auto-migrations	Yes	Yes	No
Soft deletes	No	Yes	No
Type-safe queries	Yes	Partial	Yes
Cross-platform	Yes	No	No

Best Practices

✅ Do This:

// Use the raw driver for maximum control
db.execute('SELECT * FROM users WHERE id = ?', [id]);

// Use the query builder for cross-dialect SQL
final q = db('users').where('active', '=', true);

// Use an ORM when the model mapping and relationships justify the code generation
final users = await db.select(db.users).get();

❌ Don’t Do This:

// Use an ORM for a single query
// The code generation overhead is not justified

// Use a raw driver for a large application with many models
// The manual mapping becomes unmaintainable

// Ignore the database engine when choosing an ORM
// Drift is SQLite-only; it cannot connect to PostgreSQL

Common Pitfalls

Pitfall	Why It Happens	Fix
ORM cannot connect to database	Wrong engine (Drift is SQLite-only)	Use a package that supports the engine
Code generation slow	`build_runner` on every change	Use the query builder or accept the wait
Column typo at runtime	Raw driver has no type checking	Use an ORM or query builder with typed fields
Migration not applied	Forgot to run the migration command	Check the ORM’s migration documentation
Synchronous driver blocks UI	`sqlite3` on the main isolate	Use Drift or a worker-isolate wrapper

Real-World Examples

1. Raw SQLite query

final db = sqlite3.openInMemory();
db.execute('CREATE TABLE t (id INTEGER)');
final rows = db.select('SELECT * FROM t');

2. Raw PostgreSQL query

final db = await PostgresDatabase.connect('postgresql://...');
final result = await db.query('SELECT * FROM users');

3. Query builder select

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

4. Query builder insert

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

5. Drift table

class Users extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text()();
}

6. Drift insert

await db.into(db.users).insert(UsersCompanion.insert(name: 'Alice'));

7. Drift select

final users = await db.select(db.users).get();

8. duxt_orm model

class User extends Entity {
  String? name;
  static void register() { Entity.registerModel<User>(...); }
}

9. duxt_orm save

final user = User(name: 'Alice');
await user.save();

10. orm query

final users = await db.users.where(active: eq(true)).select<UserCard>();

Visual

The Abstraction Layers

┌──────────────────────────────────────────────┐
│  ORM (drift, duxt_orm, orm)                  │
│  models, relationships, migrations           │
│         │                                    │
│         ▼                                    │
│  Query Builder (knex_dart)                   │
│  dialect-correct SQL, fluent API             │
│         │                                    │
│         ▼                                    │
│  Driver (sqlite3, aim_postgres)              │
│  wire protocol, result sets                  │
└──────────────────────────────────────────────┘

Drift Reactive Flow

┌──────────────────────────────────────────────┐
│  Widget watches a Drift query                │
│         │                                    │
│         ▼                                    │
│  Database change (insert, update, delete)    │
│         │                                    │
│         ▼                                    │
│  Stream emits new result                     │
│         │                                    │
│         ▼                                    │
│  Widget rebuilds                             │
└──────────────────────────────────────────────┘

Database Support

┌──────────────────────────────────────────────┐
│  SQLite: sqlite3, knex_dart, drift,         │
│          duxt_orm, orm                       │
│                                              │
│  PostgreSQL: aim_postgres, knex_dart,       │
│              duxt_orm, orm                   │
│                                              │
│  MySQL: knex_dart, duxt_orm                 │
│                                              │
│  DuckDB: knex_dart                          │
└──────────────────────────────────────────────┘

Summary

Item	Value
Raw driver (SQLite)	`sqlite3`
Raw driver (PostgreSQL)	`aim_postgres`
Query builder	`knex_dart`
ORM (SQLite)	`drift`
ORM (PG/MySQL)	`duxt_orm`
ORM (PG/SQLite)	`orm`
Code generation	Drift, `orm`
Reactive streams	Drift
Cross-dialect SQL	`knex_dart`

Key takeaways:

The layers are driver, query builder, and ORM. A driver executes SQL; a query builder generates dialect-correct SQL; an ORM maps tables to typed classes with migrations and relationships. Choose the lowest layer that solves the problem.
sqlite3 is the synchronous SQLite driver. It is the foundation that Drift and resqlite build on. It is fast for small databases but blocks the isolate on the main thread .
knex_dart is the cross-dialect query builder. It supports PostgreSQL, MySQL, SQLite, DuckDB, and several others from a single API. It does not generate models, but it does generate the correct SQL for the target engine .
Drift is the SQLite ORM with reactive streams. It generates type-safe queries and turns them into streams that emit when the underlying data changes, which is what a Flutter widget needs to rebuild .
duxt_orm is the ActiveRecord ORM. It supports PostgreSQL, MySQL, and SQLite, with auto-migrations and soft deletes. It is lower ceremony than Drift but does not have reactive streams .
orm is the typed ORM with reviewed migrations. It generates a typed client from plain Dart models and supports SQLite and PostgreSQL. It is still beta, so production use requires weighing the stability risk .

Remember: The Dart ORM landscape is not a single tool but a set of layers. The raw driver gives control, the query builder gives cross-dialect SQL, and the ORM gives models and migrations. The choice is determined by the database engine, the need for reactive queries, the tolerance for code generation, and the maturity of the package. Drift is the mature choice for SQLite and Flutter. knex_dart is the portable choice for a server that might switch engines. orm is the typed choice for a PostgreSQL service that values compile-time safety, with the caveat that it is still beta. There is no single “best” ORM because there is no single Dart application.

Was this helpful?

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!

Why the ORM landscape is fragmented in Dart

a. The raw driver: sqlite3 and aim_postgres

b. The query builder: knex_dart

c. The ORM: drift, duxt_orm, and orm

Complete Example Session

Quick Reference

Layer Comparison

Database Support

Key Features

Best Practices

Common Pitfalls

Real-World Examples

1. Raw SQLite query

2. Raw PostgreSQL query

3. Query builder select

4. Query builder insert

5. Drift table

6. Drift insert

7. Drift select

8. duxt_orm model

9. duxt_orm save

10. orm query

Visual

The Abstraction Layers

Drift Reactive Flow

Database Support

Summary

a. The raw driver: `sqlite3` and `aim_postgres`

b. The query builder: `knex_dart`

c. The ORM: `drift`, `duxt_orm`, and `orm`