Dart ORM #

ORM (Object-Relational Mapping) is an abstraction layer that maps database rows to Dart objects and vice versa — letting you interact with a database using type-safe Dart code without writing raw SQL. The Dart ORM ecosystem isn’t as large as Python’s (SQLAlchemy) or JavaScript’s (Prisma, TypeORM), but there are several solid choices for various needs. This article covers the Dart ORM landscape, the two most mature libraries (Drift and Stormberry), and when a manual query builder approach beats an ORM.

The Dart ORM Landscape #

flowchart TD
    A{Database target?} --> B{SQLite\nlocal mobile/desktop}
    A --> C{PostgreSQL\nserver-side}
    A --> D{Multi-database}
    B --> E["Drift\nmost mature\ntype-safe\ncode gen"]
    C --> F["Stormberry\ncode gen\nrelation support"]
    C --> G["Manual query builder\n+ postgres package\nfull control"]
    D --> H["Conduit ORM\npart of the framework"]
    D --> I["Angel ORM\npart of the framework"]

Library	Database	Status	Key Features
Drift	SQLite	✅ Active, very mature	Type-safe, reactive, code gen, migrations
Stormberry	PostgreSQL	✅ Active	Code gen, relations, annotation-based
Conduit ORM	PostgreSQL	✅ (part of Conduit)	Integrated with the framework
Angel ORM	PostgreSQL, MySQL	✅ (part of Angel3)	Integrated with the framework
Manual query builder	All	—	Full control, no abstraction

Drift — the ORM for SQLite #

Drift (formerly called Moor) is the most mature ORM in the Dart ecosystem — specifically for SQLite and widely used in Flutter apps for local storage. Drift uses code generation and provides genuinely type-safe queries.

Setup #

dart pub add drift drift_flutter
dart pub add dev:drift_dev build_runner

# pubspec.yaml
dependencies:
  drift: ^2.14.0
  drift_flutter: ^0.1.0      # for Flutter (native SQLite)
  # sqlite3_flutter_libs: ^0.5.0  # native SQLite for mobile
  # drift/native.dart: for pure Dart (server/CLI)

dev_dependencies:
  drift_dev: ^2.14.0
  build_runner: ^2.4.0

Table and Database Definitions #

// lib/database/database.dart
import 'package:drift/drift.dart';
import 'package:drift_flutter/drift_flutter.dart';

// The part generated by build_runner
part 'database.g.dart';

// Table definitions — like an SQL schema but in Dart
class Produk extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get nama => text().withLength(min: 1, max: 200)();
  TextColumn get deskripsi => text().nullable()();
  RealColumn get harga => real()();
  IntColumn get stok => integer().withDefault(const Constant(0))();
  TextColumn get kategori => text()();
  BoolColumn get aktif => boolean().withDefault(const Constant(true))();
  DateTimeColumn get dibuatPada => dateTime().withDefault(currentDateAndTime)();
  DateTimeColumn get diubahPada => dateTime().nullable()();
}

class Kategori extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get nama => text().unique()();
  TextColumn get deskripsi => text().nullable()();
}

// Database definition — lists all tables
@DriftDatabase(tables: [Produk, Kategori])
class AppDatabase extends _$AppDatabase {
  AppDatabase() : super(_openConnection());

  // Schema version — increment when tables change
  @override
  int get schemaVersion => 1;

  // Migration when the schema changes
  @override
  MigrationStrategy get migration {
    return MigrationStrategy(
      onCreate: (Migrator m) async {
        await m.createAll(); // create all tables for fresh installs
      },
      onUpgrade: (Migrator m, int from, int to) async {
        if (from < 2) {
          // Upgrade from v1 to v2 — add a new column
          await m.addColumn(produk, produk.diubahPada);
        }
        if (from < 3) {
          // Upgrade from v2 to v3
          await m.createTable(kategori);
        }
      },
    );
  }
}

// Open the SQLite connection
LazyDatabase _openConnection() {
  return LazyDatabase(() async {
    final dbFolder = await getApplicationDocumentsDirectory();
    final file = File(path.join(dbFolder.path, 'app.sqlite'));
    return NativeDatabase.createInBackground(file);
  });
}

# Generate code from the table definitions
dart run build_runner build

# or watch mode
dart run build_runner watch

CRUD with Drift #

// All operations are type-safe — the ProdukData class is generated automatically
class ProdukRepository {
  final AppDatabase _db;

  ProdukRepository(this._db);

  // SELECT all active products
  Future<List<ProdukData>> ambilSemua() {
    return (_db.select(_db.produk)
          ..where((p) => p.aktif.equals(true))
          ..orderBy([(p) => OrderingTerm.desc(p.dibuatPada)]))
        .get();
  }

  // SELECT one product
  Future<ProdukData?> ambilById(int id) {
    return (_db.select(_db.produk)
          ..where((p) => p.id.equals(id)))
        .getSingleOrNull();
  }

  // Reactive query — a Stream that updates automatically when data changes
  Stream<List<ProdukData>> watchProdukAktif() {
    return (_db.select(_db.produk)
          ..where((p) => p.aktif.equals(true))
          ..orderBy([(p) => OrderingTerm.asc(p.nama)]))
        .watch();
  }

  // INSERT
  Future<int> tambah(ProdukCompanion produk) {
    return _db.into(_db.produk).insert(produk);
  }

  Future<int> tambahProduk({
    required String nama,
    required double harga,
    required int stok,
    required String kategori,
  }) {
    return _db.into(_db.produk).insert(
          ProdukCompanion.insert(
            nama: nama,
            harga: harga,
            stok: stok,
            kategori: kategori,
          ),
        );
  }

  // UPDATE
  Future<bool> perbarui(ProdukData produk) {
    return _db.update(_db.produk).replace(produk);
  }

  Future<int> updateHarga(int id, double hargaBaru) {
    return (_db.update(_db.produk)
          ..where((p) => p.id.equals(id)))
        .write(ProdukCompanion(
          harga: Value(hargaBaru),
          diubahPada: Value(DateTime.now()),
        ));
  }

  // DELETE
  Future<int> hapus(int id) {
    return (_db.delete(_db.produk)..where((p) => p.id.equals(id))).go();
  }

  // TRANSACTION
  Future<void> pindahStok(int dari, int ke, int jumlah) async {
    await _db.transaction(() async {
      // Decrease the source stock
      final sumber = await ambilById(dari);
      if (sumber == null || sumber.stok < jumlah) {
        throw Exception('Insufficient stock');
      }
      await updateStok(dari, sumber.stok - jumlah);

      // Increase the destination stock
      final tujuan = await ambilById(ke);
      if (tujuan == null) throw Exception('Destination product not found');
      await updateStok(ke, tujuan.stok + jumlah);
    });
  }

  Future<int> updateStok(int id, int stokBaru) {
    return (_db.update(_db.produk)..where((p) => p.id.equals(id)))
        .write(ProdukCompanion(stok: Value(stokBaru)));
  }
}

Custom Queries with SQL in Drift #

// Sometimes custom SQL is needed for complex queries
class ProdukRepository {
  // Custom SELECT with a join
  Future<List<ProdukDenganKategori>> ambilDenganKategori() {
    final query = _db.select(_db.produk).join([
      innerJoin(_db.kategori, _db.kategori.id.equalsExp(_db.produk.id)),
    ]);

    return query.map((row) => ProdukDenganKategori(
          produk: row.readTable(_db.produk),
          kategori: row.readTable(_db.kategori),
        )).get();
  }

  // Raw SQL query — for cases that can't be expressed with the builder
  Future<List<Map<String, Object?>>> statistikPerKategori() {
    return _db.customSelect(
      'SELECT kategori, COUNT(*) as total, AVG(harga) as rata_harga '
      'FROM produk WHERE aktif = 1 GROUP BY kategori',
    ).get().then((rows) => rows.map((row) => row.data).toList());
  }
}

Stormberry — the ORM for PostgreSQL #

Stormberry is a PostgreSQL-specific ORM with an annotation-based, code-generation approach, suitable for Dart server-side development.

Setup #

dart pub add stormberry
dart pub add dev:stormberry_generator build_runner

# pubspec.yaml
dependencies:
  stormberry: ^0.17.0
  postgres: ^3.4.0   # Stormberry uses the postgres package

dev_dependencies:
  stormberry_generator: ^0.17.0
  build_runner: ^2.4.0

Model Definitions #

// lib/model/produk.dart
import 'package:stormberry/stormberry.dart';

// The @Model annotation marks the class as a database entity
@Model()
abstract class Produk {
  @PrimaryKey()
  int get id;

  String get nama;
  String? get deskripsi;
  double get harga;
  int get stok;
  String get kategori;
  bool get aktif;

  @DateTimeColumn()
  DateTime get dibuatPada;

  // Many-to-one relationship to Kategori
  @BelongsTo()
  KategoriModel? get kategoriObj;
}

@Model()
abstract class KategoriModel {
  @PrimaryKey()
  int get id;

  String get nama;

  // One-to-many relationship — one category has many products
  @HasMany()
  List<ProdukView>? get produk;
}

// A view for responses different from the full model
@ModelView(Produk)
abstract class ProdukRingkasan {
  int get id;
  String get nama;
  double get harga;
}

# Generate code
dart run build_runner build

Using Stormberry #

import 'package:stormberry/stormberry.dart';
import 'package:postgres/postgres.dart';

Future<void> main() async {
  // Create the database connection
  final db = Database(
    host: 'localhost',
    port: 5432,
    database: 'toko',
    user: 'postgres',
    password: 'password',
  );

  // CRUD via the generated repository
  final repo = db.produk;

  // INSERT
  await repo.insertOne(ProdukInsertRequest(
    nama: 'Laptop Gaming',
    harga: 15000000,
    stok: 10,
    kategori: 'elektronik',
    aktif: true,
    dibuatPada: DateTime.now().toUtc(),
  ));

  // SELECT all
  final semuaProduk = await repo.queryProduk(
    ProdukQuery(where: 'aktif = true', orderBy: 'dibuat_pada DESC'),
  );

  // SELECT with a summary view (field subset)
  final ringkasan = await repo.queryProdukRingkasan();

  // SELECT one
  final produk = await repo.queryProduk(
    ProdukQuery(where: 'id = 1'),
  );

  // UPDATE
  await repo.updateOne(ProdukUpdateRequest(
    id: 1,
    harga: 14000000,
  ));

  // DELETE
  await repo.deleteOne(1);

  await db.close();
}

Manual Query Builders — When They Beat an ORM #

An ORM isn’t always the best choice. There are situations where a manual query builder is more appropriate:

// lib/repository/produk_repository.dart
import 'package:postgres/postgres.dart';

class ProdukRepository {
  final Pool _pool;

  ProdukRepository(this._pool);

  // Complex queries that ORMs struggle to express
  Future<List<Map<String, dynamic>>> laporanPenjualan({
    required DateTime dari,
    required DateTime hingga,
  }) async {
    final result = await _pool.execute(
      '''
      SELECT
        p.id,
        p.nama AS nama_produk,
        p.kategori,
        COUNT(oi.id) AS jumlah_transaksi,
        SUM(oi.qty) AS total_terjual,
        SUM(oi.subtotal) AS total_pendapatan,
        AVG(oi.harga) AS harga_rata_rata,
        MIN(o.dibuat_pada) AS transaksi_pertama,
        MAX(o.dibuat_pada) AS transaksi_terakhir
      FROM produk p
      INNER JOIN order_items oi ON oi.id_produk = p.id
      INNER JOIN orders o ON o.id = oi.id_order
      WHERE
        o.status = 'selesai'
        AND o.dibuat_pada BETWEEN $1 AND $2
      GROUP BY p.id, p.nama, p.kategori
      ORDER BY total_pendapatan DESC
      ''',
      parameters: [dari, hingga],
    );
    return result.map((row) => row.toColumnMap()).toList();
  }
}

USE an ORM (Drift/Stormberry) when:
  ✓ Simple CRUD without complex queries
  ✓ You need type-safety and IDE autocomplete
  ✓ You need framework-managed migrations
  ✓ The team isn't familiar with SQL
  ✓ Drift: Flutter apps with local SQLite storage
  ✓ Stormberry: server-side PostgreSQL with simple relations

USE a manual query builder when:
  ✓ Queries are very complex (nested JOINs, window functions, CTEs)
  ✓ You need full control over the generated SQL
  ✓ Performance-critical — you want to make sure there's no N+1 or bad queries
  ✓ The team has strong SQL knowledge
  ✓ Non-standard databases or database-specific features

Drift vs Stormberry #

Aspect	Drift	Stormberry
Database	SQLite	PostgreSQL
Target	Mobile/Desktop (Flutter)	Server-side
Reactive	✓ `watch()` → Stream	✗
Migration	✓ Built-in manual	✓ Auto-generated
Relations	✓ Join support	✓ BelongsTo/HasMany
Raw SQL	✓ `customSelect()`	✓ Via the postgres package
Maturity	⭐⭐⭐⭐⭐ Very mature	⭐⭐⭐ Developing
Documentation	Very complete	Fairly complete

Summary #

Drift is the best choice for SQLite — very mature, type-safe, supports reactive queries (watch()) that are extremely useful in Flutter, and managed migrations.
Stormberry for server-side PostgreSQL with annotation-based models — simpler than Conduit ORM but not tied to any specific framework.
Conduit ORM / Angel ORM are the right choice if you’re already using those frameworks — tightly integrated with no extra configuration needed.
Manual query builders with the postgres package are better for complex analytical queries, reports, and cases where performance is the top priority.
Drift’s watch() returns a Stream that updates automatically when database data changes — a killer feature for Flutter apps needing reactive UI.
Code generation is the foundation of all mature Dart ORMs — dart run build_runner build is mandatory after changing table or model definitions.
Drift migrations must be written manually in the onUpgrade callback — increment schemaVersion and add upgrade logic. More verbose but gives full control.
Stormberry Views let you define different field subsets for various query contexts — without always fetching every column.
ORMs aren’t a silver bullet — for very complex queries (reports, nested aggregations), direct SQL is easier to read and maintain than trying to express it through an ORM API.
The N+1 query problem applies to all ORMs — always check the generated queries and make sure relations are fetched with JOINs, not separate queries in a loop.

← Previous: Angel Next: Web Driver →