Skip to content

Database Schemas

Phexium supports multiple databases with equivalent schemas optimized for each engine.

Schema Location

database/
├── schema.sqlite.sql
├── schema.mysql.sql
└── schema.postgresql.sql

Tables Overview

Table Purpose
user User accounts with authentication
book Library book catalog
loan Book borrowing records

Key Differences by Database

Feature SQLite MySQL PostgreSQL
ID type TEXT VARCHAR(36) VARCHAR(36)
Enums CHECK constraint ENUM type Custom TYPE
Datetime TEXT DATETIME TIMESTAMP
Reserved words - - "user" quoted

SQLite Example

CREATE TABLE book (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    status TEXT NOT NULL CHECK(status IN ('available', 'borrowed'))
);

PostgreSQL Example

CREATE TYPE book_status AS ENUM ('available', 'borrowed');

CREATE TABLE book (
    id VARCHAR(36) PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    status book_status NOT NULL DEFAULT 'available'
);

Initialization

task dev:database:init    # SQLite development database

For MySQL and PostgreSQL, databases are initialized via Docker when running acceptance tests.

Schema Principles

  • UUID primary keys (36-character strings)
  • Explicit NOT NULL and UNIQUE constraints
  • Foreign keys for referential integrity
  • Enum validation via appropriate mechanism per database

See Also