Defining Tables

Schemas define the structure of your database. They are Dart files in lib/src/schemas/ that describe tables and their columns. When you change a schema file, Zonai generates the SQL migration to keep the database in sync.

The table() Function#

table('table_name', TableClass.new) registers a table with Zonai. The first argument is the table name as it appears in the database (snake_case, plural). The second is the constructor of the Dart class that defines the columns. The return value is a table reference used in rules, operations, extensions, and rate limits.

Typed IDs#

Every table has its own ID type, so a tasks ID can't be passed where a users ID is expected. They all live together in lib/src/ids.dart, under a sealed base class that supplies the shared value field:

import 'package:zonai_schema/zonai_schema.dart' as z;

sealed class Id implements z.Id {
  const Id(this.value);

  @override
  final String value;

  @override
  bool operator ==(Object other) => other is z.Id && other.value == value;

  @override
  int get hashCode => value.hashCode;

  @override
  String toString() => value;
}

class TasksId extends Id {
  const TasksId(super.value);

  factory TasksId.generate() => TasksId(z.Id.generate(_suffix));

  static const _suffix = 'tk';  // a short, per-table suffix
}

zonai_schema is imported as z because its Id is an abstract interface class — your base class implements it and declares value itself, rather than extending it. Making the base sealed is what lets a single Id.fromJson switch over every ID in the project exhaustively; see Quick Start for that full file.

The suffix appears at the end of generated IDs (e.g. abc123_tk), making debugging easier.

Column Types#

Each column maps a Dart type to a SQLite type:

A column is nullable when the accessor you hand the builder returns a nullable type — there is no isNullable argument. $.text('bio', (s) => s.bio) is a TextColumn when bio is a String and a ColumnType<String?> when it is a String?.

$.updatedAt(...) follows the same rule. A nullable one is NULL until the row is first updated. A non-nullable one is stamped on insert as well, so every row carries a value from the moment it exists. That's the one to use when something pages or syncs by updated_at, because a NULL never compares greater than a cursor.

Server-Generated Columns#

Use $.serverGenerated(...) for a text column whose value the server always sets — a generated API key, a computed checksum — in an insert override:

apiKey = $.serverGenerated('api_key', (s) => s.apiKey),

no-analyze: a single initializer, shown out of its table class.

It is a TEXT column that stays non-nullable, but when a create payload omits it, Zonai fills in a blank placeholder. That matters because rules run before operations, against a row built from the raw request: with a plain non-nullable $.text column the client never sends, building that row fails and the request errors before your insert override runs.

Unlike $.password, the value is returned in responses, so use it for values callers may see once set — not secrets. The admin dashboard shows it read-only.

Revisions#

$.revision(...) is a counter the server maintains: 0 when the row is created, and one higher on every update, whatever the update changed.

rev = $.revision('rev', (s) => s.rev),

no-analyze: a single initializer, shown out of its table class.

Use it to update a row only if nobody else changed it since you read it:

UpdateOneBody(
  table: 'posts',
  where: Eq('id', id),
  updates: [Update.column('title', UpdateValue.literal('New'))],
  expect: Eq('rev', 3), // the revision you read
)

no-analyze: shows the body alone; see the Dart client for the call around it.

A create or update that sets the revision itself is refused with a 400, not quietly ignored. A client that believed it wrote the value is exactly the one that needs to be told it didn't. The column is INTEGER NOT NULL DEFAULT 0, so adding it to a table that already has rows migrates them to revision 0. The admin dashboard shows it read-only.

What the revision does not see:

• A custom operation that writes with raindrop directly. The increment is part of TableOperations.update(...). A custom override that builds its own statement with db.update(...) skips it, and skips updatedAt too. Build the write with update(updates, where: ...) instead, and it gets both.
• A row deleted and created again with the same id. The new row starts at 0. A client that remembers revision 0 of the old row can't tell the two apart, so a sync that allows reused ids should key on something besides the revision, such as createdAt.

Indexes#

Indexes are defined in a callback passed as the third argument to table(). Use index() for a regular index and uniqueIndex() for a unique index. Both take a name and then call .on() with the column(s) to index:

final posts = table('posts', PostTable.new, (t) {
  index('posts_title_index').on(t.title);
  uniqueIndex('posts_title_unique').on(t.title);
});

Composite indexes list multiple columns in .on():

final posts = table('posts', PostTable.new, (t) {
  index('posts_author_created_index').on(t.authorId, t.createdAt);
});

Naming Conventions#

• Table name: snake_case, plural (tasks, user_profiles, blog_posts)
• Dart class: PascalCase, singular (TaskTable, UserProfileTable, BlogPostTable)
• Row type: PascalCase, singular (Task, UserProfile, BlogPost)
• Column names: snake_case

Complete Example#

import 'package:my_app/src/ids.dart';
import 'package:zonai_schema/zonai_schema.dart';

final class Post {
  const Post({
    required this.id,
    required this.title,
    required this.body,
    this.publishedAt,
    required this.viewCount,
    required this.createdAt,
    this.updatedAt,
  });

  final PostsId id;
  final String title;
  final String body;
  final DateTime? publishedAt;  // nullable — not published yet
  final int viewCount;
  final DateTime createdAt;
  final DateTime? updatedAt;
}

final class PostTable extends Table<Post> {
  PostTable(super.$)
    : id = $.id('id', (s) => s.id, fromString: PostsId.new, generate: PostsId.generate),
      title = $.text('title', (s) => s.title),
      body = $.text('body', (s) => s.body),
      publishedAt = $.dateTime('published_at', (s) => s.publishedAt),
      viewCount = $.integer('view_count', (s) => s.viewCount),
      createdAt = $.createdAt('created_at', (s) => s.createdAt),
      updatedAt = $.updatedAt('updated_at', (s) => s.updatedAt);

  @override
  Post fromRow(RowReader read) => Post(
    id: read(id),
    title: read(title),
    body: read(body),
    publishedAt: read(publishedAt),
    viewCount: read(viewCount),
    createdAt: read(createdAt),
    updatedAt: read(updatedAt),
  );

  final IdColumn<PostsId> id;
  final TextColumn title;
  final TextColumn body;
  final ColumnType<DateTime?> publishedAt;
  final IntColumn viewCount;
  final DateTimeColumn createdAt;
  final ColumnType<DateTime?> updatedAt;
}

final posts = table('posts', PostTable.new, (t) {
  index('posts_title_index').on(t.title);
});