> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/trailbaseio/trailbase/llms.txt
> Use this file to discover all available pages before exploring further.

# 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:

<Steps>
  <Step title="STRICT Typing">
    All tables must use SQLite's `STRICT` mode for type safety:

    ```sql theme={null}
    CREATE TABLE users (
      id INTEGER PRIMARY KEY,
      name TEXT NOT NULL
    ) STRICT;
    ```

    <Warning>
      Without `STRICT`, SQLite allows any value in any column, which can lead to type errors in your application.
    </Warning>
  </Step>

  <Step title="Primary Key Column">
    Tables must have an `INTEGER` or `BLOB` (for UUIDv7) primary key:

    <CodeGroup>
      ```sql Integer Primary Key theme={null}
      CREATE TABLE posts (
        id INTEGER PRIMARY KEY NOT NULL,
        title TEXT NOT NULL
      ) STRICT;
      ```

      ```sql UUIDv7 Primary Key theme={null}
      CREATE TABLE articles (
        id BLOB PRIMARY KEY NOT NULL CHECK(is_uuid_v7(id)) DEFAULT (uuid_v7()),
        title TEXT NOT NULL
      ) STRICT;
      ```
    </CodeGroup>

    <Info>
      UUIDv7 provides globally unique, time-sortable identifiers. TrailBase provides built-in `uuid_v7()` and `is_uuid_v7()` functions.
    </Info>
  </Step>

  <Step title="Quote Column Names">
    Use double quotes around column names to avoid SQL keyword conflicts:

    ```sql theme={null}
    CREATE TABLE items (
      "id"    INTEGER PRIMARY KEY,
      "order" INTEGER NOT NULL,  -- "order" is a SQL keyword
      "text"  TEXT NOT NULL
    ) STRICT;
    ```
  </Step>
</Steps>

## 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                      |

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

## Creating Tables

### Basic Table Structure

Here's a complete example from the blog example:

```sql migrations/main/U1725019362__create_articles.sql theme={null}
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;
```

<Tip>
  **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()`
</Tip>

### Profiles Table with Username Validation

```sql migrations/main/U1725019361__create_profiles.sql theme={null}
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);
```

<Info>
  TrailBase provides the `REGEXP` operator for pattern validation. Regular expressions are evaluated efficiently using Rust's regex engine.
</Info>

## Indexes

Indexes dramatically improve query performance for large datasets:

### Single-Column Indexes

```sql theme={null}
-- 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:

```sql theme={null}
-- 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);
```

<Warning>
  **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`.
</Warning>

### 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:

```sql theme={null}
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

<Note>
  TrailBase automatically enables foreign key enforcement (`PRAGMA foreign_keys = ON`).
</Note>

### Check Constraints

Validate data at the database level:

```sql theme={null}
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

```sql theme={null}
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:

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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;
```

<Warning>
  Triggers run for every row operation and can impact performance. Use them judiciously.
</Warning>

## Views

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

```sql theme={null}
-- 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

```sql theme={null}
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;
```

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

## File Upload Columns

Store file metadata with built-in validation:

```sql theme={null}
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](/guides/file-uploads) for details.

## Virtual Tables

TrailBase supports virtual tables for special use cases:

### FTS5 Full-Text Search

```sql theme={null}
-- 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

```sql theme={null}
-- 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

<Steps>
  <Step title="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
  </Step>

  <Step title="Index Foreign Keys">
    Always create indexes on foreign key columns:

    ```sql theme={null}
    CREATE INDEX _comments__post_id_index ON comments(post_id);
    CREATE INDEX _comments__user_id_index ON comments(user_id);
    ```
  </Step>

  <Step title="Normalize Data">
    Avoid duplicating data across tables. Use foreign keys and joins instead:

    **Bad:**

    ```sql theme={null}
    CREATE TABLE orders (
      id INTEGER PRIMARY KEY,
      user_email TEXT,  -- Duplicated from users table
      user_name TEXT    -- Duplicated from users table
    ) STRICT;
    ```

    **Good:**

    ```sql theme={null}
    CREATE TABLE orders (
      id INTEGER PRIMARY KEY,
      user_id INTEGER REFERENCES users(id)
    ) STRICT;
    ```
  </Step>

  <Step title="Use Views for Complex Queries">
    Create views for frequently-used joins to simplify client code and ensure consistency.
  </Step>
</Steps>

## 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      |

<Warning>
  Do not modify built-in tables directly. Use TrailBase's auth APIs and admin UI instead.
</Warning>

## Next Steps

<CardGroup cols={2}>
  <Card title="Migrations" icon="code-branch" href="/guides/migrations">
    Learn how to evolve your schema over time
  </Card>

  <Card title="Authentication" icon="lock" href="/guides/authentication">
    Integrate user tables with auth
  </Card>

  <Card title="File Uploads" icon="upload" href="/guides/file-uploads">
    Add file upload columns
  </Card>

  <Card title="First App" icon="rocket" href="/guides/first-app">
    Build a complete application
  </Card>
</CardGroup>