ORM & Migrations

bloom_db and bloom_migrate provide a unified, chainable QuerySet API modeled on Django's ORM and djangors-orm, with symmetric SQLite and PostgreSQL support and transactional DDL migrations.

Defining Models with bloom_db

Models subclass Model and declare field metadata via ModelMeta and @BloomModel annotations. All SQL query values use bound parameters to prevent SQL injection:

lib/apps/blog/models.dart

dart

// lib/apps/blog/models.dart
import 'package:bloom_db/bloom_db.dart';

@BloomModel(app: 'blog', tableName: 'blog_posts')
class Post extends Model {
  @BloomField(primaryKey: true, auto: true, kind: FieldKind.bigInt)
  final int id;

  @BloomField(kind: FieldKind.char, maxLength: 255)
  final String title;

  @BloomField(kind: FieldKind.text)
  final String content;

  @BloomField(column: 'created_at', kind: FieldKind.dateTime)
  final DateTime createdAt;

  Post({
    this.id = 0,
    required this.title,
    required this.content,
    DateTime? createdAt,
  }) : createdAt = createdAt ?? DateTime.now().toUtc();

  static final meta = ModelMeta(
    structName: 'Post',
    appLabel: 'blog',
    tableName: 'blog_posts',
    fields: [
      FieldMeta(name: 'id', columnName: 'id', kind: FieldKind.bigInt, primaryKey: true, auto: true),
      FieldMeta(name: 'title', columnName: 'title', kind: FieldKind.char, maxLength: 255),
      FieldMeta(name: 'content', columnName: 'content', kind: FieldKind.text),
      FieldMeta(name: 'createdAt', columnName: 'created_at', kind: FieldKind.dateTime),
    ],
    ordering: ['-createdAt', 'id'],
  );

  @override
  ModelMeta get modelMeta => meta;

  @override
  List<(String, BloomValue)> fieldValues() => [
        ('id', BloomValue.i64(id)),
        ('title', BloomValue.text(title)),
        ('content', BloomValue.text(content)),
        ('createdAt', BloomValue.dateTime(createdAt)),
      ];

  static Post fromRow(DbRow row) => Post(
        id: row.tryIntByName('id') ?? 0,
        title: row.tryStringByName('title') ?? '',
        content: row.tryStringByName('content') ?? '',
        createdAt: row.tryDateTimeByName('created_at') ?? DateTime.now().toUtc(),
      );

  static QuerySet<Post> objects() => QuerySet<Post>(meta: meta, fromRow: fromRow);
}

QuerySet API & Q() Expressions

The QuerySet<T> API provides chainable querying methods: filter, exclude, orderBy, limit, offset, get, first, all, exists, count, update, delete, bulkCreate, getOrCreate, updateOrCreate, values, and valuesList:

QuerySet Usage

dart

// QuerySet queries and expressions
final db = await PostgresDbExecutor.connect(
  host: '127.0.0.1',
  port: 5432,
  username: 'postgres',
  password: 'postgres',
  database: 'bloom_dev',
);

// 1. Filtering with Q() expressions (&, |, ~)
final recentPosts = await Post.objects()
    .filter(Q('title__icontains', 'bloom') & ~Q('content', 'draft'))
    .orderBy('-createdAt')
    .limit(20)
    .all(db);

// 2. Exact lookup
final post = await Post.objects().filter(Q('id', 42)).get(db);

// 3. Update with F() expression
await Post.objects()
    .filter(Q('id', 42))
    .update(db, {'view_count': F('view_count') + 1});

// 4. Checking existence and counts
final exists = await Post.objects().filter(Q('title', 'Launch')).exists(db);
final total = await Post.objects().count(db);

// 5. Atomic getOrCreate
final (user, created) = await User.objects().getOrCreate(
  db,
  lookup: {'email': 'alice@example.com'},
  defaults: {'name': 'Alice', 'created_at': DateTime.now().toUtc()},
);

Optional Codegen with bloom_db_generator

If you prefer not to write ModelMeta, fieldValues(), and fromRow by hand, bloom_db_generator uses build_runner to generate the mapping mixins automatically:

pubspec.yaml

yaml

// pubspec.yaml
dev_dependencies:
  build_runner: ^2.4.9
  bloom_db_generator: ^0.1.0

// Run generator
dart run build_runner build

Database Migrations with bloom_migrate

Migration files live under migrations/<app>/NNNN_name.sql and contain structured -- up and -- down SQL blocks:

migrations/blog/0001_initial.sql

yaml

-- migrations/blog/0001_initial.sql
-- up
CREATE TABLE IF NOT EXISTS blog_posts (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS blog_posts_created_at_idx ON blog_posts(created_at);

-- down
DROP TABLE IF EXISTS blog_posts;

Manage migrations using the bloom_migrate CLI:

Terminal

bash

# 1. Generate migration SQL from registered models
dart run bloom_migrate makemigrations blog --dialect=postgres --name=initial

# 2. Apply pending migrations against PostgreSQL or SQLite
dart run bloom_migrate migrate --url=postgres://postgres:postgres@localhost:5432/bloom_dev
dart run bloom_migrate migrate --url=sqlite:bloom.db

# 3. Check migration status
dart run bloom_migrate status --url=sqlite:bloom.db

# 4. Roll back applied migrations
dart run bloom_migrate rollback --url=sqlite:bloom.db --count=1

Programmatic MigrationRunner

Execute pending migrations programmatically during server boot:

lib/boot.dart

dart

import 'package:bloom_db/bloom_db.dart';
import 'package:bloom_migrate/bloom_migrate.dart';

Future<void> runStartupMigrations(DbExecutor db) async {
  final runner = MigrationRunner(
    db: db,
    migrationsDirectory: 'migrations',
  );

  final applied = await runner.migrate();
  for (final m in applied) {
    print('Applied migration: ${m.app}/${m.name}');
  }
}

Previous: Scaffolding a Server Next: Authentication