Relational Data

How it works

You store the ID of a related record as a field in your data, and can optionally DECLARE that field as a reference so the database enforces it. For example, a posts record might have an author_id field containing the ID of a record in the users collection.

When querying, pass a populate list to resolve those references server-side. The API fetches the related records in a single batched query and injects them inline — no extra round trips from your app.

The populated field is injected with the _id suffix removed. So author_id becomes author, and category_id becomes category.

Declared references

An undeclared reference is just a field holding an id: nothing stops it pointing at a record that was deleted, or one in the wrong collection. Declaring it turns it into a real constraint, enforced by the database rather than by your app.

POST /v1/projects/{project_id}/collections/disbursements/references

{
  "field": "grant_id",
  "target_collection": "grants",
  "on_delete": "restrict"
}

From then on, every write is checked: grant_id must hold the id of a live record in grants, or the write is refused with reference_invalid. A field left out or set to null is always allowed — a reference constrains what a value MAY be, not whether it must be there.

The check runs when the transaction commits, not when each row is written. So a batch may insert a disbursement before the grant it points at, or delete a grant and its disbursements in either order — what matters is that the data is consistent by the end.

What happens when the target is deleted

on_delete decides, and the three choices are the ones SQL foreign keys offer:

• restrict (default) — refuse the delete while live records still reference it. The safe answer for anything that must not lose its history.
• cascade — delete the referencing records too. A grant and its disbursements go together.
• set_null — keep the referencing records and clear the field. The rest of each record is untouched.

A field that holds several

An enrolment in several programmes, a post with several tags: declare the reference with "cardinality": "many" and the field holds a JSON array of ids. Every element is checked the same way — each must be a live record of the target — and the delete policy applies per element: restrict refuses while any list still holds the id, cascade deletes the records whose list holds it, and set_null removes the id from the list and keeps the rest. A list may hold up to 500 ids; a record that references thousands of others is a data model to reconsider, and the write is refused rather than quietly allowed.

Declaring one over existing data

The declaration checks what is already there. If records point at ids that do not exist, it is refused with dangling_references and the offending records are listed in details.dangling — repair them and declare again. Koolbase never repairs them for you.

Declaring a reference also tells the database how many distinct values the field holds. Without that, grouping a million records by a field with sixteen values is planned as if there were a million — it sorts every row and spills to disk. With it, the same query runs in a fraction of the time.

A declared reference also protects the collection it points at: deleting grants while disbursements still references it is refused with collection_referenced.

Storing references

Store the related record's ID as a field in your data. Use the _id suffix by convention — it makes populate work automatically.

// Insert a post with a reference to the author
await Koolbase.db.collection('posts').insert({
  'title': 'Getting started with Koolbase',
  'body': 'Koolbase makes Flutter backends easy...',
  'author_id': 'user-uuid-here',       // reference to a users record
  'category_id': 'category-uuid-here', // reference to a categories record
});

Querying with populate

Use .populate() on the query builder. Pass a list of strings in the format "field_name:collection_name".

final result = await Koolbase.db
  .collection('posts')
  .populate(['author_id:users', 'category_id:categories'])
  .get();

for (final post in result.records) {
  final title = post.data['title'] as String;

  // Populated author record
  final author = post.data['author'] as Map<String, dynamic>?;
  final authorName = author?['name'] as String?;

  // Populated category record
  final category = post.data['category'] as Map<String, dynamic>?;
  final categoryName = category?['name'] as String?;

  print('$title by $authorName in $categoryName');
}

Response shape

The original reference field is preserved and the populated record is injected alongside it:

{
  "$id": "post-uuid",
  "$collection": "posts",
  "$createdAt": "2026-03-25T00:00:00Z",
  "$updatedAt": "2026-03-25T00:00:00Z",
  "title": "Getting started with Koolbase",
  "author_id": "user-uuid",
  "author": {
    "$id": "user-uuid",
    "$collection": "users",
    "$createdAt": "2026-03-25T00:00:00Z",
    "$updatedAt": "2026-03-25T00:00:00Z",
    "name": "Kennedy Owusu",
    "email": "[email protected]"
  },
  "category_id": "category-uuid",
  "category": {
    "$id": "category-uuid",
    "$collection": "categories",
    "$createdAt": "2026-03-25T00:00:00Z",
    "$updatedAt": "2026-03-25T00:00:00Z",
    "name": "Engineering"
  }
}

Combining with filters and ordering

.populate() works alongside all other query methods:

final result = await Koolbase.db
  .collection('posts')
  .where('published', isEqualTo: true)
  .orderBy('created_at', descending: true)
  .limit(10)
  .populate(['author_id:users'])
  .get();

Limits