Seed data

You’ll write a seed script that can run any number of times without creating duplicates, plus a bulk generator for local load testing.

Prerequisites

The blog schema from Define a schema, migrated.

Steps

1. Decide where the data belongs

Data	Put it in
Rows the app can’t run without (lookup values, a system user)	A hand-written migration, so every environment gets it exactly once.
Demo and development data	A seed script you run on demand.
Test fixtures	The test itself, inserted inside `withTestDb` (see Testing).

Reference data in a migration:

-- lib/models/migrations/1730480000000_seed_tags.up.sql
INSERT INTO "tags" ("name") VALUES ('dart'), ('postgres'), ('release')
ON CONFLICT ("name") DO NOTHING;

-- lib/models/migrations/1730480000000_seed_tags.down.sql
DELETE FROM "tags" WHERE "name" IN ('dart', 'postgres', 'release');

2. Write an idempotent seed script

tool/seed.dart:

import 'package:blog/models/blog.gisila.g.dart';
import 'package:gisila_orm/gisila.dart';

Future<void> main() async {
  final db = await Database.connect(await DatabaseConfig.fromEnvironment());
  try {
    await db.transaction((tx) async {
      final ada = await upsertAuthor(tx, 'ada@example.com', 'Ada Lovelace');
      final grace = await upsertAuthor(tx, 'grace@example.com', 'Grace Hopper');

      await ensurePost(tx, ada, 'Notes on the Analytical Engine');
      await ensurePost(tx, grace, 'Compilers for everyone');
    });
    print('seeded');
  } finally {
    await db.close();
  }
}

Future<Author> upsertAuthor(DbContext db, String email, String name) => authors()
    .insert({'email': email, 'name': name})
    .onConflictDoUpdate(['email'])
    .one(db);

Future<Post> ensurePost(DbContext db, Author author, String title) async {
  final existing = await posts()
      .where(PostTable.authorId.eq(author.id!))
      .where(PostTable.title.eq(title))
      .first(db);
  return existing ??
      await posts().insert({'author_id': author.id, 'title': title}).one(db);
}

Upserts on a unique key (email) make re-runs update rather than duplicate.
Where there’s no unique key, look the row up first, as ensurePost does.
The transaction means a failure halfway leaves nothing behind.

Run it:

dart run tool/seed.dart

3. Generate bulk data

For thousands of rows, insert in multi-row batches instead of one statement per row:

import 'dart:math';

Future<void> seedBulk(DbContext db, {int authorCount = 100, int postsPerAuthor = 50}) async {
  final rnd = Random(42); // fixed seed: the same data every run

  final authorRows = [
    for (var i = 0; i < authorCount; i++) {'name': 'Author $i', 'email': 'author$i@example.test'},
  ];
  final q = authors().insert(authorRows.first).onConflictDoNothing(['email']);
  for (final r in authorRows.skip(1)) {
    q.values(r);
  }
  await q.returning(false).run(db);

  final ids = [
    for (final a in await authors().where(AuthorTable.email.like('%@example.test')).all(db)) a.id!,
  ];

  const batchSize = 1000; // 1000 rows × 3 columns stays far below the 65,535 parameter limit
  final rows = [
    for (final id in ids)
      for (var j = 0; j < postsPerAuthor; j++)
        {'author_id': id, 'title': 'Post $j', 'body': 'Lorem ${rnd.nextInt(1 << 20)}'},
  ];
  for (var i = 0; i < rows.length; i += batchSize) {
    final batch = rows.sublist(i, min(i + batchSize, rows.length));
    final insert = posts().insert(batch.first);
    for (final r in batch.skip(1)) {
      insert.values(r);
    }
    await insert.returning(false).run(db);
  }
}

returning(false) skips sending the inserted rows back, which matters at this volume.

4. Reset local data

await db.execute('TRUNCATE "comments", "posts", "authors" RESTART IDENTITY CASCADE');

Never point this at a shared database. Guard it, for example by checking that DATABASE_URL is unset or names localhost.

Verify it works

dart run tool/seed.dart
dart run tool/seed.dart

Both runs succeed, and counting rows shows two authors and two posts, not four:

print(await authors().count(db.context())); // 2