Multi-Database Support

FraiseQL supports multiple database backends, allowing you to use your preferred database while maintaining the same GraphQL API.

Supported Databases

Database	Status	Best For
PostgreSQL	Primary	Production, full feature set
MySQL/MariaDB	Supported	MySQL-based infrastructure
SQLite	Supported	Development, embedded, testing
SQL Server	Supported	Enterprise Windows environments

Configuration

Database configuration is set in the [database] section of fraiseql.toml. Pool settings are direct fields on [database] — there is no [database.pool] sub-table.

PostgreSQL (Default)

[database]
type = "postgresql"
url = "postgresql://user:password@localhost:5432/mydb"
pool_min = 5
pool_max = 20
idle_timeout_ms = 300000
connect_timeout_ms = 5000
ssl_mode = "prefer"

MySQL / MariaDB

[database]
type = "mysql"
url = "mysql://user:password@localhost:3306/mydb"
pool_min = 5
pool_max = 20

SQLite

[database]
type = "sqlite"
url = "sqlite:///path/to/database.db"

# Or in-memory for testing
# url = "sqlite::memory:"

SQL Server

[database]
type = "sqlserver"
url = "mssql://user:password@localhost:1433/mydb"

Note

Database-specific connection parameters (character sets, journal mode, SSL trust, etc.) are passed via the connection URL query string, not as TOML sub-tables. The [database] section only accepts url, pool_min, pool_max, idle_timeout_ms, connect_timeout_ms, and ssl_mode.

Feature Matrix

Feature	PostgreSQL	MySQL	SQLite	SQL Server
JSONB columns	Native	JSON type	JSON1 ext	NVARCHAR
UUID type	Native	CHAR(36)	TEXT	UNIQUEIDENTIFIER
Arrays	Native	JSON	JSON	JSON
Full-text search	Native	FULLTEXT	FTS5	Full-text
Window functions	Full	Full	Limited	Full
CTEs	Recursive	Recursive	Recursive	Recursive
Subscriptions	LISTEN/NOTIFY	Polling	Polling	Polling
Connection pooling	Deadpool	Deadpool	Single	Deadpool

Database-Specific Considerations

PostgreSQL

Full feature support, recommended for production:

-- Trinity pattern: pk_ (internal), id (public UUID), identifier (human key)
CREATE TABLE tb_user (
    pk_user    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id         UUID   DEFAULT gen_random_uuid() UNIQUE NOT NULL,
    identifier TEXT   UNIQUE NOT NULL,
    data       JSONB  NOT NULL
);

-- GIN index for JSONB
CREATE INDEX idx_user_data ON tb_user USING GIN (data);

-- Native array support
CREATE TABLE tb_post (
    pk_post    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id         UUID   DEFAULT gen_random_uuid() UNIQUE NOT NULL,
    identifier TEXT   UNIQUE NOT NULL,
    tags       TEXT[] NOT NULL
);

Advantages:

• Native JSONB type with full indexing support
• GIN indexes for efficient JSON queries
• Native array types
• LISTEN/NOTIFY for subscriptions

MySQL

JSON stored as JSON type:

-- MySQL adaptation of the trinity pattern
-- Note: MySQL 8.0+ supports GENERATED ALWAYS AS IDENTITY syntax
CREATE TABLE tb_user (
    pk_user    BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    id         CHAR(36) NOT NULL UNIQUE DEFAULT (UUID()),
    identifier VARCHAR(255) NOT NULL UNIQUE,
    data       JSON NOT NULL
);

-- Generated column for indexing
ALTER TABLE tb_user
ADD COLUMN email VARCHAR(255) AS (JSON_UNQUOTE(data->>'$.email')) STORED,
ADD INDEX idx_user_email (email);

Notes:

• Use JSON_UNQUOTE() for string extraction
• Generated columns for indexed access to JSON fields
• UUID stored as CHAR(36)
• JSON functions: JSON_OBJECT(), JSON_ARRAYAGG()

SQLite

Lightweight, ideal for development:

-- SQLite adaptation of the trinity pattern
CREATE TABLE tb_user (
    pk_user    INTEGER PRIMARY KEY AUTOINCREMENT,
    id         TEXT NOT NULL UNIQUE DEFAULT (lower(hex(randomblob(16)))),
    identifier TEXT NOT NULL UNIQUE,
    data       TEXT NOT NULL CHECK (json_valid(data))
);

-- Expression index for JSON
CREATE INDEX idx_user_email ON tb_user (json_extract(data, '$.email'));

Notes:

• Enable JSON1 extension
• UUID generated as hex string
• Single writer, multiple readers (WAL mode)
• JSON functions: json_object(), json_group_array()

SQL Server

Enterprise Windows support:

-- SQL Server adaptation of the trinity pattern
CREATE TABLE dbo.tb_user (
    pk_user    BIGINT IDENTITY(1,1) PRIMARY KEY,
    id         UNIQUEIDENTIFIER NOT NULL UNIQUE DEFAULT NEWID(),
    identifier NVARCHAR(255) NOT NULL UNIQUE,
    data       NVARCHAR(MAX) NOT NULL
);

-- Computed column for indexing
ALTER TABLE dbo.tb_user
ADD email AS JSON_VALUE(data, '$.email');
CREATE INDEX idx_user_email ON dbo.tb_user (email);

Notes:

• JSON stored in NVARCHAR(MAX)
• Use JSON_VALUE() and JSON_QUERY()
• UNIQUEIDENTIFIER for UUIDs
• Use FOR JSON PATH for building JSON

Schema Adaptation

FraiseQL automatically adapts SQL generation per database:

WHERE Clause Generation

query {
    users(where: { email: { _eq: "user@example.com" } }) {
        id
        name
    }
}

PostgreSQL

SELECT * FROM v_user WHERE data->>'email' = 'user@example.com'

MySQL

SELECT * FROM v_user WHERE JSON_UNQUOTE(data->'$.email') = 'user@example.com'

SQLite

SELECT * FROM v_user WHERE json_extract(data, '$.email') = 'user@example.com'

SQL Server

SELECT * FROM v_user WHERE JSON_VALUE(data, '$.email') = 'user@example.com'

Multi-Database Architecture

FraiseQL does not support connecting a single service to multiple databases simultaneously. To expose multiple databases through a unified GraphQL API, run a separate FraiseQL service for each database and compose them with Apollo Federation:

Client
  └── Apollo Federation Gateway
        ├── fraiseql-users  (PostgreSQL)
        ├── fraiseql-orders (PostgreSQL — separate cluster)
        └── fraiseql-legacy (SQL Server)

Each service has its own fraiseql.toml with its own [database] section and schema. The Federation gateway merges the subgraph schemas into a single API surface.

Development Workflow

Local Development (SQLite)

dev.toml

[database]
type = "sqlite"
url = "sqlite:///dev.db"

# Quick iteration
fraiseql run --config dev.toml

Testing (SQLite In-Memory)

test.toml

[database]
type = "sqlite"
url = "sqlite::memory:"

Production (PostgreSQL)

prod.toml

[database]
type = "postgresql"
url = "${DATABASE_URL}"
pool_min = 10
pool_max = 50
idle_timeout_ms = 600000
connect_timeout_ms = 5000
ssl_mode = "require"

Connection Pooling

Pool Configuration

Pool settings are direct fields on the [database] section:

[database]
url = "postgresql://user:password@localhost:5432/mydb"
pool_min = 5
pool_max = 20
connect_timeout_ms = 30000
idle_timeout_ms = 600000

Key	Default	Description
pool_min	2	Minimum connections to keep open
pool_max	20	Maximum connections in the pool
connect_timeout_ms	5000	Milliseconds to wait for a connection
idle_timeout_ms	600000	Milliseconds before idle connections are closed

SQLite

SQLite uses a single connection with WAL mode. Configure WAL and busy timeout via the connection URL:

[database]
type = "sqlite"
url = "sqlite:///path/to/db.sqlite?mode=rwc&journal_mode=wal&busy_timeout=5000"

Troubleshooting

Connection Issues

Check connection by verifying your URL is reachable from the host running FraiseQL:

# PostgreSQL
psql "${DATABASE_URL}" -c "SELECT 1"

# MySQL
mysql -u user -p -h localhost mydb -e "SELECT 1"

Performance Problems

1. Check pool settings — pool_max too low causes queuing under load
2. Verify indexes on JSON fields
3. Review query plans with fraiseql-cli explain

Transport Support Across Databases

All four databases (PostgreSQL, MySQL, SQL Server, SQLite) support all three transports — GraphQL, REST, and gRPC. The compiler generates transport-aware views for each database dialect:

Database	JSON views (GraphQL + REST)	Row views (gRPC)
PostgreSQL	json_agg(row_to_json(t))	Standard SELECT
MySQL	JSON_ARRAYAGG(...)	Standard SELECT
SQL Server	FOR JSON PATH	Standard SELECT
SQLite	json_group_array(...)	Standard SELECT

Transport choice is independent of database choice.

Next Steps

• Schema Design - Database-agnostic patterns
• Performance - Database optimization
• Deployment - Production database setup
• Multi-Tenancy - Tenant isolation with PostgreSQL RLS