Skip to main content

​
Overview

Applad’s database layer is adapter-agnostic. You define tables in YAML files under database/tables/, and Applad manages migrations, connections, and permissions consistently across PostgreSQL, MySQL, SQLite, MongoDB, and more.

​
Database Configuration

First, configure your database connections in database/database.yaml:
database/database.yaml
connections:
  - id: "primary"
    default: true
    adapter: "postgres"
    url: ${DATABASE_URL}
    pool:
      min: 2
      max: 20
      idle_timeout: 300
      queue_timeout: 10
    migrations:
      auto: false
      dir: "./migrations/primary"
    ssl:
      required: true
      verify_cert: true

  - id: "cache"
    adapter: "redis"
    url: ${REDIS_URL}
    ssl:
      required: true

  - id: "analytics"
    adapter: "postgres"
    url: ${ANALYTICS_DATABASE_URL}
    migrations:
      auto: false
      dir: "./migrations/analytics"
    ssl:
      required: true

​
Environment Overrides

Use different adapters for different environments:
database/database.yaml
environment_overrides:
  development:
    primary:
      adapter: "sqlite"
      path: "./data/dev.db"
    analytics:
      adapter: "sqlite"
      path: "./data/analytics-dev.db"

  staging:
    primary:
      adapter: "postgres"
      url: ${STAGING_DATABASE_URL}
      pool:
        min: 1
        max: 5

​
Defining Tables

Each table lives in its own YAML file under database/tables/.

​
Basic Table Structure

database/tables/users.yaml
name: "users"
database: "primary"  # Optional — defaults to connection with default: true
timestamps: true     # Adds created_at and updated_at
soft_delete: true    # Adds deleted_at for soft deletes

fields:
  - name: "email"
    type: "string"
    unique: true
    required: true
    indexed: true

  - name: "name"
    type: "string"
    required: true

  - name: "avatar_url"
    type: "string"
    required: false

  - name: "role"
    type: "enum"
    values: ["user", "admin"]
    default: "user"

  - name: "mfa_enabled"
    type: "boolean"
    default: false

​
Field Types

Applad supports these field types across all adapters:
TypeDescriptionExample
stringText field"email"
textLong-form text"description"
numberInteger42
floatDecimal3.14
booleanTrue/falsetrue
dateDate only"2026-03-03"
datetimeDate and time"2026-03-03T10:30:00Z"
enumFixed set of values["draft", "published"]
jsonJSON data{"key": "value"}
arrayArray of items["tag1", "tag2"]
relationForeign keyReferences another table

​
Relations

Define relationships between tables:
database/tables/users.yaml
fields:
  - name: "org_id"
    type: "relation"
    table: "organizations"
    required: true
Cross-database relations are flagged at validation time. If users targets the primary connection but references a table in the analytics connection, Applad warns you that the relation must be resolved at the application layer.

​
Indexes

Define indexes for performance:
database/tables/users.yaml
indexes:
  - fields: ["email"]
    unique: true
  - fields: ["org_id", "role"]
  - fields: ["created_at"]
For full-text search:
database/tables/posts.yaml
indexes:
  - fields: ["title", "body"]
    type: "fulltext"

​
Permissions

Permission rules live alongside the schema in the same file, reviewed in the same PR:
database/tables/users.yaml
permissions:
  - role: "owner"
    actions: ["*"]

  - role: "admin"
    actions: ["read", "write"]

  - role: "user"
    actions: ["read"]
    filter: "id == $user.id"

  - role: "*"
    actions: ["read"]
    filter: "id == $user.id AND deleted_at == null"

​
Permission Actions

  • read — Can query records
  • write — Can create and update records
  • delete — Can delete records
  • * — All actions

​
Permission Filters

Filters use a simple expression syntax: 
# Only own records
filter: "id == $user.id"

# Only published posts
filter: "status == 'published'"

# Author or admin
filter: "author_id == $user.id OR $user.role == 'admin'"

# Org-level isolation
filter: "org_id == $user.org_id"

​
Migrations

1

Generate a migration

applad db generate "add_avatar_to_users"
Creates:
  • migrations/primary/003_add_avatar_to_users.sql
  • migrations/primary/003_add_avatar_to_users.down.sql (for rollback)
2

Write the SQL

migrations/primary/003_add_avatar_to_users.sql
ALTER TABLE users ADD COLUMN avatar_url TEXT;
migrations/primary/003_add_avatar_to_users.down.sql
ALTER TABLE users DROP COLUMN avatar_url;
3

Review pending migrations

applad db migrate --dry-run
Shows which migrations would be applied without actually applying them.
4

Apply migrations

applad db migrate
Migrations are applied in filename order (001*, 002*, etc.), each wrapped in a transaction.

​
Multi-Database Projects

You can route different tables to different connections:
database/tables/events.yaml
name: "events"
database: "analytics"  # Routes to the analytics connection
timestamps: true
soft_delete: false

fields:
  - name: "type"
    type: "string"
    required: true
    indexed: true

  - name: "user_id"
    type: "string"  # Plain string, not a relation — cross-database
    required: false
    indexed: true

  - name: "payload"
    type: "json"
    required: false
Applad validates that tables referencing different connections don’t have direct relations. Cross-database joins must be resolved at the application layer.

​
Working with Tables

​
List all tables

applad tables list

​
Generate a new table

applad tables generate posts
Creates database/tables/posts.yaml with placeholder fields.

​
Validate table definitions

applad tables validate
Checks field types, relation references, permission syntax, and index definitions.

​
View table schema

applad tables show users

​
Check for drift

applad tables diff users
Shows differences between the YAML definition and what exists in the database.

​
Database Shell

Open an interactive SQL shell: 
# Connect to primary connection
applad db shell

# Connect to specific connection
applad db shell --connection analytics

​
Example: Complete Table Definition

database/tables/posts.yaml
name: "posts"
database: "primary"
timestamps: true
soft_delete: false

fields:
  - name: "title"
    type: "string"
    required: true

  - name: "body"
    type: "text"
    required: true

  - name: "author_id"
    type: "relation"
    table: "users"
    required: true

  - name: "status"
    type: "enum"
    values: ["draft", "published", "archived"]
    default: "draft"

  - name: "tags"
    type: "array"
    items: "string"

indexes:
  - fields: ["author_id"]
  - fields: ["status", "created_at"]
  - fields: ["title", "body"]
    type: "fulltext"

permissions:
  - role: "*"
    actions: ["read"]
    filter: "status == 'published'"

  - role: "user"
    actions: ["write"]
    filter: "author_id == $user.id"

  - role: "admin"
    actions: ["*"]

​
Next Steps

Authentication

Configure auth providers and identity management

Storage

Add file storage with buckets and permissions

Build docs developers (and LLMs) love

Get started for freeTalk to us