ArchDB: modular DB schemas as code

Modular Schema Engineering with ArchDB: Templates, Packages, and Declarative Modeling

ArchDB allows you to describe database structure declaratively in .adb files, integrating it into a Git repository. Templates eliminate duplication of audit and identification fields, while Packages decompose a monolith into independent modules. The model generates DDL, documentation, and diagrams. This approach speeds up change reviews and synchronization with ORMs.

// Core.adb - base templates
Package com.example.ecommerce.Core

Template IdentifiableUuid {
  id uuid [pk, default: `gen_random_uuid()`]
}

Template Auditable {
  created_at timestamp [not_null, default: `now()`]
  created_by uuid [ref: > User.id, nullable]
  updated_at timestamp [default: `now()`]
  updated_by uuid [ref: > User.id, nullable]
  indexes {
    idx_created_at (created_at)
  }
}

Template BusinessEntity extends IdentifiableUuid, Auditable {
  version_no integer [default: 1]
  status varchar(50) [default: 'active']
}

Templates for Reusability

Templates are composed via extends, inheriting fields and indexes. BusinessEntity combines identification, audit, and versioning. Applying it to a User table adds all fields automatically:

• UUID id with PK and a default generator
• created_at/updated_at fields with indexes
• version_no for optimistic locking

Checks and unique indexes are configured at the table level:

Table User extends BusinessEntity {
  email varchar(255) [unique, not_null]
  password_hash text [not_null]
  checks {
    `char_length(password_hash) >= 8`
  }
}

Logical Groups enhance visualization in documentation by assigning colors and descriptions.

Modular Decomposition via Package

Each .adb file is a separate Package with imports. Product.adb imports Core.adb to extend BusinessEntity:

// Product.adb
Package com.example.ecommerce.Product
import "com.example.ecommerce.Core.adb" version 3.4.0

export Product as "com.example.ecommerce.Product" version 3.6.0

Table Product extends Core.BusinessEntity {
  sku varchar(50) [unique, not_null]
  price decimal(12, 2) [not_null]
  stock_quantity integer [default: 0]
  available_quantity integer [generated: `stock_quantity - reserved_quantity`]
  checks {
    `price >= cost_price`
    `reserved_quantity <= stock_quantity`
  }
}

Generated fields are computed at the database level. Relation describes M:N relationships:

Relation ProductCategory {
  Product.id <-> Category.id
}

Export versioning (export ... version) ensures module compatibility.

| Advantage | Description |

|-----------|-------------|

| Independence | Parallel development of modules |

| Versioning | Git + semantic versioning for imports |

| Reusability | Templates distributed via libraries |

Advanced Entities: Orders and Enums

Order.adb imports Core and Product, adding domain types:

// Order.adb
import "com.example.ecommerce.Core.adb" version 3.4.0
import "com.example.ecommerce.Product.adb" version 3.6.0

Enum OrderStatus {
  pending [note: "Awaiting payment"]
  paid
  shipped
  cancelled
}

Table Order extends Core.BusinessEntity {
  user_id uuid [ref: > Core.User.id]
  status OrderStatus [default: 'pending']
  total_amount decimal(12, 2) [not_null]
}

Enum integrates as a field type. Cascade deletion (on_delete: cascade) on FK.

Key Takeaways

• Declarative: Describe the desired state; generators create DDL and migrations.
• Modularity: Package + import/export for independent development and CI/CD.
• Templates: extends composes audit fields, soft-delete, business logic without copy-paste.
• Validation: Checks at the schema level, generated fields for computations.
• Documentation: note, Group, Metadata generate diagrams and README.

Practical Implementation Tips

• Create a base Core.adb with Identifiable, Auditable, SoftDeletable templates.
• Split the monolith into Packages by domain: User, Product, Order.
• Set up Git hooks for schema validation before commits.
• Integrate with CI: DDL generation, schema tests, documentation updates.
• For ORMs: Scripts to generate entities from .adb.

Metadata and Project set the context: database: "postgresql", version, author.

— Editorial Team