Database Backends

Cloacina supports two database backends: PostgreSQL and SQLite. This guide explains the differences between them, when to use each, and important considerations for your deployment.

Overview

Cloacina uses runtime backend detection based on your connection URL. This approach provides:

• Flexibility to switch between backends without recompilation
• Same binary works with both PostgreSQL and SQLite
• Type safety across different backends
• Consistent API regardless of backend choice

Backend Selection

Backend selection happens automatically at runtime based on your connection URL:

PostgreSQL

Use a PostgreSQL connection URL:

let runner = DefaultRunner::new("postgres://user:pass@localhost:5432/mydb").await?;
// or
let runner = DefaultRunner::new("postgresql://user:pass@localhost:5432/mydb").await?;

SQLite

Use an SQLite connection URL:

// File-based database (WAL mode and a 30s busy timeout are applied automatically)
let runner = DefaultRunner::new("sqlite://myapp.db").await?;

// In-memory database (for testing)
let runner = DefaultRunner::new("sqlite://:memory:").await?;

Installation

Add Cloacina to your Cargo.toml:

[dependencies]
cloacina = "0.12"

Both backends are included by default. No feature flags needed.

Compile-Time Backend Selection

While Cloacina includes both backends by default for runtime flexibility, you can optionally compile with only the backend you need. This reduces binary size and dependencies.

Single Backend Builds

PostgreSQL only:

[dependencies]
cloacina = { version = "0.12", default-features = false, features = ["postgres", "macros"] }

SQLite only:

[dependencies]
cloacina = { version = "0.12", default-features = false, features = ["sqlite", "macros"] }

When to Use Single-Backend Builds

Consider compiling with a single backend when:

• Deploying to production where you know the target database
• Reducing binary size is important (embedded systems, serverless)
• Minimizing dependencies for security or compliance reasons
• Faster compile times during development with a known backend

Limitations

When using single-backend builds:

• Schema isolation (multi-tenancy via PostgreSQL schemas) is only available with the postgres feature
• Connection URLs must match the compiled backend or the application will panic
• You cannot switch backends at runtime without recompiling

Key Differences

Connection Strings

PostgreSQL:

let runner = DefaultRunner::new("postgres://user:pass@localhost:5432/mydb").await?;

SQLite:

// File-based database
let runner = DefaultRunner::new("sqlite://myapp.db").await?;

// In-memory database (for testing)
let runner = DefaultRunner::new("sqlite://:memory:").await?;

Concurrency Characteristics

Aspect	PostgreSQL	SQLite
Concurrent Readers	Unlimited	Unlimited (with WAL)
Concurrent Writers	Multiple	Single
Connection Pool Size	10 (default)	1 (recommended)
Lock Contention	Minimal	Can experience “database is locked”
Ideal For	Scalable systems, High concurrency	Local development, Embedded, Single-user

Feature Comparison

Feature	PostgreSQL	SQLite
Setup complexity	Moderate (requires a server)	Minimal (zero external dependencies)
ACID Compliance	Full	Full
Triggers	Yes	No (handled in application)
Default Timestamps	Yes	No (handled in application)
UUID Generation	Native	Application-generated
JSON Validation	Native	Via CHECK constraints
Performance	High for concurrent loads	High for single-user loads
Deployment	Requires server	Embedded, no server needed
Multi-tenancy (schema isolation)	Full support (with_schema)	Not supported
Per-tenant credentials / DatabaseAdmin	Available	Not available
Production readiness	Yes	Development / single-tenant / embedded only

Multi-tenancy and the per-tenant credential Admin API are the two decision axes that hard-gate the choice: any deployment that needs isolated tenants, or the DatabaseAdmin provisioning surface, must use PostgreSQL. SQLite remains a first-class choice for development, single-tenant, and embedded deployments.

SQLite Configuration

SQLite needs no tuning parameters in the connection string — Cloacina applies the concurrency-critical settings itself on every connection (crates/cloacina/src/database/connection/mod.rs):

let runner = DefaultRunner::new("sqlite://myapp.db").await?;

// Applied automatically on connect/migration:
// - PRAGMA journal_mode=WAL   — concurrent readers while writing
// - PRAGMA busy_timeout=30000 — wait 30s for locks instead of failing immediately

Query parameters in the URL (?mode=..., ?_journal_mode=...) are not interpreted — the path after sqlite:// is used literally as the filename.

Implementation Details

Timestamp Handling

PostgreSQL uses database-generated timestamps:

created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

SQLite requires application-generated timestamps:

let now = current_timestamp();
diesel::insert_into(table)
    .values((
        created_at.eq(&now),
        updated_at.eq(&now),
        // ... other fields
    ))

UUID Handling

PostgreSQL can generate UUIDs:

id UUID PRIMARY KEY DEFAULT uuid_generate_v4()

SQLite requires application-generated UUIDs:

let id = UniversalUuid::new_v4();

When to Use Each Backend

Use PostgreSQL When:

• Building scalable systems
• Requiring high concurrency
• Multiple applications share the database

Use SQLite When:

• Developing locally
• Building embedded applications
• Single-user or low-concurrency scenarios
• Simplicity is more important than scalability
• Want zero-dependency deployment

Note

Migration Between Backends

Cloacina does not officially support or provide tools for migrating between PostgreSQL and SQLite backends. While it may be technically possible to migrate data between backends, this process is not supported and may lead to data inconsistencies or loss.

If you need to switch backends, we recommend:

1. Starting fresh with the new backend
2. Re-implementing your data model
3. Migrating your data through your application layer

Performance Considerations

PostgreSQL Performance

• Excels at concurrent workloads
• Benefits from connection pooling
• Can utilize multiple CPU cores
• Scales horizontally

SQLite Performance

• Extremely fast for single-threaded access
• Minimal memory overhead
• No network latency
• Entire database can fit in memory

Conclusion

Both PostgreSQL and SQLite are first-class citizens in Cloacina. Choose based on your specific needs:

• PostgreSQL: Systems requiring scalability and high concurrency
• SQLite: Local development, testing, and embedded deployments

The runtime backend detection means you can use the same code and simply change your connection URL to switch between databases.