SQLite Guide for FraiseQL

Introduction

SQLite is the best choice for FraiseQL development because it requires zero setup and perfectly mirrors production behavior:

• Instant Setup: Single file, zero configuration, works everywhere
• Perfect for Development: Identical schema and queries to PostgreSQL/MySQL
• In-Memory Testing: Create isolated test databases instantly
• Embedded Deployments: Ship database with your application
• ~15 WHERE Operators: Fewer operators, but sufficient for most GraphQL queries
• JSON Support: Native json_object(), json_array() functions (SQLite 3.9+)
• ACID Transactions: Despite simplicity, maintains consistency

Caution

SQLite allows only one concurrent writer. It is not recommended for high-concurrency production deployments. Use PostgreSQL or MySQL for production.

Core Architecture

Single JSON Data Column Pattern

Like PostgreSQL and MySQL, FraiseQL views expose entities as single JSON columns named data:

-- Every v_* view returns:
-- 1. Metadata columns (id, is_current, etc.)
-- 2. Single JSON column named 'data' containing complete entity

SELECT
  id,
  is_current,
  data
FROM v_user
WHERE id = ?;

Trinity Pattern: TEXT UUID + INTEGER PKs

FraiseQL requires three identity columns per base table: pk_{entity} (internal PK), id (public UUID), and identifier (human-readable domain key). SQLite adapts the canonical pattern as follows:

CREATE TABLE tb_user (
  pk_user    INTEGER PRIMARY KEY AUTOINCREMENT,  -- Internal, fast FKs
  id         TEXT    NOT NULL UNIQUE,             -- Public, exposed in GraphQL (UUID stored as TEXT)
  identifier TEXT    NOT NULL UNIQUE,             -- Human-readable key (e.g. email, username, slug)
  name       TEXT    NOT NULL,
  deleted_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tb_post (
  pk_post    INTEGER PRIMARY KEY AUTOINCREMENT,
  id         TEXT    NOT NULL UNIQUE,
  identifier TEXT    NOT NULL UNIQUE,             -- Human-readable key (e.g. slug, order number)
  fk_user    INTEGER NOT NULL REFERENCES tb_user(pk_user) ON DELETE CASCADE,
  title      TEXT    NOT NULL,
  deleted_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Foreign key enforcement (CRITICAL - default is OFF in SQLite)
PRAGMA foreign_keys = ON;

Note

SQLite uses INTEGER PRIMARY KEY AUTOINCREMENT in place of the canonical FraiseQL convention BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY (used on PostgreSQL and other databases). They serve the same purpose; the deviation is SQLite-specific.

SQLite also has no built-in UUID generation. Set id at the application layer using str(uuid.uuid4()) before each insert. The identifier column holds the domain-meaningful human key (such as email for users, or slug for posts) and must be supplied by the caller.

Caution

Foreign keys are disabled by default in SQLite. You must run PRAGMA foreign_keys = ON; as the first statement after every connection to enforce referential integrity.

Mutation Response Type

SQLite has no composite types or stored procedures. Instead of returning a mutation_response composite (as PostgreSQL, MySQL, and SQL Server do), SQLite mutations use MutationStrategy::DirectSql — the Rust runtime executes INSERT/UPDATE/DELETE with a RETURNING clause and constructs the response envelope automatically.

The response shape is identical to other databases:

{
  "status": "success",
  "message": "User created successfully",
  "entity_id": "550e8400-e29b-41d4-a716-446655440000",
  "entity_type": "User",
  "entity": { "id": "...", "name": "John Doe" }
}

You do not need to define a mutation_response type in SQLite. The @fraiseql.mutation decorator works — the runtime handles the dispatch difference transparently. For complex validation logic, use INSTEAD OF triggers (see Mutation Triggers below) or application-level mutations.

View Structure: v_* (Regular Views)

Views are the source truth for read operations:

-- File: 02411_v_user.sql
CREATE VIEW v_user AS
SELECT
  u.id,
  u.deleted_at IS NULL AS is_current,
  json_object(
    'id',         u.id,
    'identifier', u.identifier,
    'name',       u.name,
    'created_at', u.created_at,
    'updated_at', u.updated_at
  ) AS data
FROM tb_user u
WHERE u.deleted_at IS NULL;

Nested Views (One-to-Many Relationships)

-- File: 02412_v_user_with_posts.sql
CREATE VIEW v_user_with_posts AS
SELECT
  u.id,
  u.deleted_at IS NULL AS is_current,
  json_object(
    'id',         u.id,
    'identifier', u.identifier,
    'name',       u.name,
    'posts', COALESCE(
      (
        SELECT json_group_array(
          json_object('id', p.id, 'identifier', p.identifier, 'title', p.title)
        )
        FROM tb_post p
        WHERE p.fk_user = u.pk_user AND p.deleted_at IS NULL
      ),
      json_array()
    )
  ) AS data
FROM tb_user u
WHERE u.deleted_at IS NULL;

Note

SQLite uses subqueries for aggregation rather than LEFT JOIN + GROUP BY. Use COALESCE(..., json_array()) to provide a default empty array when no related records exist.

Materialized Views: tv_* (Optional for Heavy Reads)

SQLite is single-writer, so materialized views are mainly useful for caching query results:

-- File: 02414_tv_user.sql
CREATE TABLE IF NOT EXISTS tv_user (
  id         TEXT    PRIMARY KEY,
  is_current BOOLEAN DEFAULT 1,
  data       TEXT    NOT NULL,  -- JSON stored as TEXT (SQLite has no JSONB type)
  updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  last_synced_at TIMESTAMP NULL,
  sync_count INTEGER DEFAULT 0,
  is_stale   BOOLEAN DEFAULT 0
);

CREATE INDEX IF NOT EXISTS idx_tv_user_is_current ON tv_user(is_current);

Refresh Triggers (called after INSERT/UPDATE):

CREATE TRIGGER trg_user_after_insert
AFTER INSERT ON tb_user
FOR EACH ROW
BEGIN
  INSERT OR REPLACE INTO tv_user (id, is_current, data)
  SELECT id, is_current, data
  FROM v_user WHERE id = NEW.id;
END;

Mutation Triggers

Note

SQLite uses MutationStrategy::DirectSql for mutations. Since SQLite has no stored procedures, FraiseQL executes INSERT, UPDATE, and DELETE statements directly with a RETURNING clause. Mutations decorated with @fraiseql.mutation work — the runtime automatically uses DirectSql dispatch instead of function calls.

SQLite uses INSTEAD OF triggers for mutation logic (no stored procedures):

-- File: 03311_create_user.sql
CREATE TRIGGER trg_user_create
INSTEAD OF INSERT ON v_user
WHEN NEW.id IS NOT NULL AND NEW.identifier IS NOT NULL
BEGIN
  SELECT CASE
    WHEN EXISTS(SELECT 1 FROM tb_user WHERE identifier = NEW.identifier AND deleted_at IS NULL)
    THEN RAISE(ABORT, 'conflict:identifier')
  END;

  INSERT INTO tb_user (id, identifier, name)
  VALUES (NEW.id, NEW.identifier, NEW.name);
END;

Alternative: Application-Level Mutations

For more complex mutations, handle them in application code rather than triggers:

def create_user(db, payload: dict) -> dict:
    identifier = payload.get('identifier')
    if not identifier:
        return {'status': 'failed:validation', 'message': 'identifier is required', 'entity': None}

    existing = db.execute(
        'SELECT id FROM tb_user WHERE identifier = ? AND deleted_at IS NULL',
        (identifier,)
    ).fetchone()

    if existing:
        return {'status': 'conflict:duplicate_identifier', 'message': 'Identifier already in use', 'entity': None}

    new_id = str(uuid.uuid4())
    db.execute(
        'INSERT INTO tb_user (id, identifier, name) VALUES (?, ?, ?)',
        (new_id, identifier, payload.get('name'))
    )
    db.commit()

    result = db.execute('SELECT data FROM v_user WHERE id = ?', (new_id,)).fetchone()
    return {'status': 'success', 'message': 'User created', 'entity': json.loads(result[0])}

Configuration: Essential Pragmas

-- File: 00001_pragmas.sql
PRAGMA foreign_keys = ON;          -- CRITICAL: enable foreign key enforcement
PRAGMA journal_mode = WAL;         -- Better concurrent reads
PRAGMA cache_size = -64000;        -- 64MB cache
PRAGMA temp_store = MEMORY;        -- Speed up temp tables
PRAGMA synchronous = NORMAL;       -- Balance safety vs speed
PRAGMA optimize;                   -- Query optimization hints
ANALYZE;                           -- Enable query planner statistics

Testing with In-Memory Databases

SQLite’s in-memory mode is perfect for isolated test databases:

import sqlite3
import pytest

@pytest.fixture
def test_db():
    """Create fresh in-memory SQLite database for each test"""
    conn = sqlite3.connect(':memory:')
    conn.row_factory = sqlite3.Row
    conn.execute('PRAGMA foreign_keys = ON')

    with open('db/0_schema/init.sql', 'r') as f:
        conn.executescript(f.read())

    yield conn
    conn.close()

def test_create_user(test_db):
    test_db.execute(
        'INSERT INTO tb_user (id, identifier, name) VALUES (?, ?, ?)',
        ('550e8400-e29b-41d4-a716-446655440000', 'test@example.com', 'Test User')
    )
    test_db.commit()

    result = test_db.execute(
        'SELECT data FROM v_user WHERE identifier = ?', ('test@example.com',)
    ).fetchone()
    assert result is not None

Troubleshooting

”database is locked” Error

-- Increase busy timeout
PRAGMA busy_timeout = 5000;  -- Wait 5 seconds before failing

SQLite allows only one writer at a time. If you see this error frequently, consider migrating to PostgreSQL for production.

Foreign Key Constraint Fails

-- MUST be first statement after opening connection
PRAGMA foreign_keys = ON;

View Returns NULL Instead of Empty Array

-- Problem: json_group_array returns NULL when no rows
-- Solution: Use COALESCE
SELECT COALESCE(
  json_group_array(id),
  json_array()
) FROM tb_post WHERE fk_user = 999;

JSON Functions Not Available

-- Check SQLite version (must be 3.9.0 or higher)
SELECT sqlite_version();

Limitations and Workarounds

Limitation	Workaround
Single concurrent writer	Use application-level locking; reads are concurrent
No JSONB type	JSON works fine; slightly slower but same patterns
No composite types	Use JSON structures in documentation
No stored procedures	Use INSTEAD OF triggers or application logic
Max ~15 WHERE operators	Use simpler queries; application-level filtering for complex logic

Performance Benchmarks

Query Type	Latency	Notes
Single entity (v_*)	0.3-0.8ms	Index lookup on id
List query (100 rows)	2-5ms	With is_current filter
Nested JSON (3 levels)	5-20ms	Depends on aggregation
Materialized view (tv_*)	0.5-1ms	Pre-computed cache
Analytics view (va_*)	10-100ms	GROUP BY aggregation

Optimization Tips:

• Always enable WAL mode for concurrent read performance
• Use in-memory :memory: databases for testing
• Create indexes on frequently-filtered columns
• Use tv_* (materialized views) for frequently-accessed complex objects

Multi-Transport Support

FraiseQL generates transport-aware views for SQLite:

• JSON-shaped views (GraphQL + REST) use json_group_array(...)
• Row-shaped views (gRPC) use standard SELECT with typed columns

All three transports are supported on SQLite.

See Also

Database Overview

Compare all supported databases

PostgreSQL Guide

Migrate to PostgreSQL for production

SQLite Troubleshooting

SQLite-specific troubleshooting