Database Setup

Creating tables, defining schemas, indexes, and constraints in TrailBase

TrailBase uses SQLite as its database engine, providing a powerful yet simple foundation for your application. This guide covers schema design, table creation, and optimization strategies.

Schema Requirements

TrailBase requires tables to meet specific criteria to expose them via Record APIs:

1. STRICT Typing

   All tables must use SQLite’s STRICT mode for type safety:

   CREATE TABLE users (
     id INTEGER PRIMARY KEY,
     name TEXT NOT NULL
   ) STRICT;
   

   Without STRICT, SQLite allows any value in any column, which can lead to type errors in your application.

2. Primary Key Column

   Tables must have an INTEGER or BLOB (for UUIDv7) primary key:

   Integer Primary Key

   CREATE TABLE posts (
     id INTEGER PRIMARY KEY NOT NULL,
     title TEXT NOT NULL
   ) STRICT;
   

   CREATE TABLE articles (
     id BLOB PRIMARY KEY NOT NULL CHECK(is_uuid_v7(id)) DEFAULT (uuid_v7()),
     title TEXT NOT NULL
   ) STRICT;
   

   UUIDv7 provides globally unique, time-sortable identifiers. TrailBase provides built-in uuid_v7() and is_uuid_v7() functions.

3. Quote Column Names

   Use double quotes around column names to avoid SQL keyword conflicts:

   CREATE TABLE items (
     "id"    INTEGER PRIMARY KEY,
     "order" INTEGER NOT NULL,  -- "order" is a SQL keyword
     "text"  TEXT NOT NULL
   ) STRICT;
   

Column Types

SQLite’s STRICT mode enforces these types:

Type	Description	Example Usage
INTEGER	Signed 64-bit integer	IDs, counts, booleans (0/1), timestamps
REAL	64-bit floating point	Prices, ratings, measurements
TEXT	UTF-8 text string	Names, descriptions, emails
BLOB	Binary data	UUIDs, files, encrypted data
ANY	Any type (not recommended)	Mixed-type columns

SQLite uses integers (0/1) for boolean values. TrailBase’s type generation will map these appropriately in client code.

Creating Tables

Basic Table Structure

Here’s a complete example from the blog example:

migrations/main/U1725019362__create_articles.sql

CREATE TABLE articles (
    id           BLOB PRIMARY KEY NOT NULL CHECK(is_uuid_v7(id)) DEFAULT (uuid_v7()),
    author       BLOB NOT NULL REFERENCES _user(id) ON DELETE CASCADE,

    title        TEXT NOT NULL,
    intro        TEXT NOT NULL,
    tag          TEXT NOT NULL,
    body         TEXT NOT NULL,

    image        TEXT CHECK(jsonschema('std.FileUpload', image, 'image/png, image/jpeg')),

    created      INTEGER DEFAULT (UNIXEPOCH()) NOT NULL
) STRICT;

Key Features:

• UUIDv7 primary key with validation
• Foreign key to _user table with cascade delete
• File upload column with JSON schema validation
• Automatic timestamp using UNIXEPOCH()

Profiles Table with Username Validation

migrations/main/U1725019361__create_profiles.sql

CREATE TABLE profiles (
    user         BLOB PRIMARY KEY NOT NULL REFERENCES _user(id) ON DELETE CASCADE,

    -- Require at least 3 alphanumeric characters
    username     TEXT NOT NULL CHECK(username REGEXP '^[\w]{3,}$'),

    created      INTEGER DEFAULT (UNIXEPOCH()) NOT NULL,
    updated      INTEGER DEFAULT (UNIXEPOCH()) NOT NULL
) STRICT;

-- Ensure usernames are unique
CREATE UNIQUE INDEX _profiles__username_index ON profiles (username);

TrailBase provides the REGEXP operator for pattern validation. Regular expressions are evaluated efficiently using Rust’s regex engine.

Indexes

Indexes dramatically improve query performance for large datasets:

Single-Column Indexes

-- Index for foreign key lookups
CREATE INDEX _articles__author_index ON articles(author);

-- Index for filtering
CREATE INDEX _posts__published_index ON posts(published);

-- Unique index for constraints
CREATE UNIQUE INDEX _profiles__username_index ON profiles(username);

Multi-Column Indexes

For queries that filter on multiple columns:

-- Composite index for queries like: WHERE user = ? AND created > ?
CREATE INDEX _todos__user_created_index ON todos(user, created);

-- Unique composite constraint
CREATE UNIQUE INDEX _likes__user_post_index ON likes(user, post_id);

Index Order Matters: SQLite can only use the leftmost columns of an index. An index on (user, created) helps queries filtering by user alone, but not queries filtering only by created.

Index Naming Convention

Follow TrailBase’s naming convention for consistency:

_<table_name>__<column_name(s)>_index

Examples:

• _articles__author_index
• _todos__user_created_index

Constraints

Foreign Keys

Enforce referential integrity with foreign keys:

CREATE TABLE comments (
  id      INTEGER PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  user_id BLOB NOT NULL REFERENCES _user(id) ON DELETE CASCADE,
  text    TEXT NOT NULL
) STRICT;

Cascade Actions:

• ON DELETE CASCADE - Delete child records when parent is deleted
• ON DELETE SET NULL - Set foreign key to NULL when parent is deleted
• ON DELETE RESTRICT - Prevent parent deletion if children exist

TrailBase automatically enables foreign key enforcement (PRAGMA foreign_keys = ON).

Check Constraints

Validate data at the database level:

CREATE TABLE products (
  id       INTEGER PRIMARY KEY,
  name     TEXT NOT NULL,
  price    REAL NOT NULL CHECK(price > 0),
  rating   REAL CHECK(rating >= 0 AND rating <= 5),
  email    TEXT CHECK(email LIKE '%_@_%._%'),
  status   TEXT CHECK(status IN ('draft', 'published', 'archived'))
) STRICT;

Unique Constraints

CREATE TABLE accounts (
  id       INTEGER PRIMARY KEY,
  email    TEXT NOT NULL UNIQUE,
  username TEXT NOT NULL,
  UNIQUE(username)  -- Alternative syntax
) STRICT;

Default Values

Provide sensible defaults for optional fields:

CREATE TABLE todos (
  id          INTEGER PRIMARY KEY,
  text        TEXT NOT NULL,
  completed   INTEGER NOT NULL DEFAULT 0,
  priority    INTEGER DEFAULT 1,
  created_at  INTEGER DEFAULT (UNIXEPOCH()) NOT NULL,
  due_date    INTEGER  -- NULL by default
) STRICT;

Triggers

Automate common tasks with triggers:

Update Timestamp Trigger

CREATE TRIGGER _profiles__updated_trigger AFTER UPDATE ON profiles FOR EACH ROW
  BEGIN
    UPDATE profiles SET updated = UNIXEPOCH() WHERE user = OLD.user;
  END;

Validation Trigger

CREATE TRIGGER _posts__validate_dates BEFORE INSERT ON posts FOR EACH ROW
  BEGIN
    SELECT RAISE(ABORT, 'published_at must be after created_at')
    WHERE NEW.published_at < NEW.created_at;
  END;

Triggers run for every row operation and can impact performance. Use them judiciously.

Views

Views provide read-only, computed data combining multiple tables:

-- Join articles with user profiles
CREATE VIEW articles_view AS 
  SELECT 
    a.*,
    p.username 
  FROM articles AS a 
  LEFT JOIN profiles AS p ON p.user = a.author;

Views with Computed Columns

CREATE VIEW profiles_view AS
  SELECT
    p.*,
    -- Type cast required for JSON schema generation
    CAST(CASE
      WHEN avatar.file IS NOT NULL THEN CONCAT('/api/auth/avatar/', uuid_text(p.user))
      ELSE NULL
    END AS TEXT) AS avatar_url,
    CAST(IIF(editors.user IS NULL, FALSE, TRUE) AS INTEGER) AS is_editor
  FROM profiles AS p
    LEFT JOIN _user_avatar AS avatar ON p.user = avatar.user
    LEFT JOIN editors ON p.user = editors.user;

Type Casts Required: TrailBase generates JSON schemas from view column types. Use explicit CAST() for computed columns so TrailBase can infer the correct type.

File Upload Columns

Store file metadata with built-in validation:

CREATE TABLE posts (
  id       INTEGER PRIMARY KEY,
  title    TEXT NOT NULL,
  -- Single file upload with type constraints
  image    TEXT CHECK(jsonschema('std.FileUpload', image, 'image/png, image/jpeg')),
  -- Multiple file uploads
  gallery  TEXT CHECK(jsonschema('std.FileUploads', gallery))
) STRICT;

The JSON schema validation ensures uploaded files meet your requirements. See the File Uploads guide for details.

Virtual Tables

TrailBase supports virtual tables for special use cases:

FTS5 Full-Text Search

-- Create FTS index on articles
CREATE VIRTUAL TABLE articles_fts USING fts5(
  title,
  body,
  content='articles',
  content_rowid='id'
);

-- Triggers to keep FTS index in sync
CREATE TRIGGER articles_fts_insert AFTER INSERT ON articles BEGIN
  INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

CREATE TRIGGER articles_fts_update AFTER UPDATE ON articles BEGIN
  UPDATE articles_fts SET title = new.title, body = new.body WHERE rowid = old.id;
END;

CREATE TRIGGER articles_fts_delete AFTER DELETE ON articles BEGIN
  DELETE FROM articles_fts WHERE rowid = old.id;
END;

Custom Query APIs

-- Create a custom query that checks group membership
CREATE VIRTUAL TABLE _is_editor USING define((
  SELECT EXISTS (SELECT * FROM editors WHERE user = $1) AS is_editor
));

Optimization Tips

1. Use Appropriate Types

   Choose the smallest type that fits your data:

   • Use INTEGER for IDs and counts (not TEXT)
   • Use INTEGER for timestamps (UNIX epoch)
   • Avoid ANY type in STRICT tables
2. Index Foreign Keys

   Always create indexes on foreign key columns:

   CREATE INDEX _comments__post_id_index ON comments(post_id);
   CREATE INDEX _comments__user_id_index ON comments(user_id);
   
3. Normalize Data

   Avoid duplicating data across tables. Use foreign keys and joins instead:

   Bad:

   CREATE TABLE orders (
     id INTEGER PRIMARY KEY,
     user_email TEXT,  -- Duplicated from users table
     user_name TEXT    -- Duplicated from users table
   ) STRICT;
   

   Good:

   CREATE TABLE orders (
     id INTEGER PRIMARY KEY,
     user_id INTEGER REFERENCES users(id)
   ) STRICT;
   
4. Use Views for Complex Queries

   Create views for frequently-used joins to simplify client code and ensure consistency.

Built-in Tables

TrailBase provides several built-in tables (prefixed with _):

Table	Purpose
_user	User accounts and authentication
_user_avatar	User avatar uploads
_schema_history	Migration tracking
_file_deletions	Pending file deletion queue

Do not modify built-in tables directly. Use TrailBase’s auth APIs and admin UI instead.

Next Steps

Migrations

Learn how to evolve your schema over time

Authentication

Integrate user tables with auth

File Uploads

Add file upload columns

First App

Build a complete application