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 with optimizations
let runner = DefaultRunner::new("sqlite://myapp.db?mode=rwc").await?;

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

Installation

Add Cloacina to your Cargo.toml:

[dependencies]
cloacina = "0.1.0"

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.1.0", default-features = false, features = ["postgres", "macros"] }

SQLite only:

[dependencies]
cloacina = { version = "0.1.0", 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 with optimizations
let runner = DefaultRunner::new("sqlite://myapp.db?mode=rwc&_journal_mode=WAL&_synchronous=NORMAL&_busy_timeout=5000").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
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

SQLite Configuration

SQLite requires specific configuration for optimal performance. Here’s the recommended setup:

// Recommended connection string with optimizations
let runner = DefaultRunner::new("sqlite://myapp.db?mode=rwc&_journal_mode=WAL&_synchronous=NORMAL&_busy_timeout=5000").await?;

// Configuration details:
// - WAL mode: Enables concurrent readers while writing
// - Synchronous=NORMAL: Balances durability and performance
// - Busy timeout: 5s wait for locks instead of immediate failure
// - Connection pool: Use single connection (default)

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:

Starting fresh with the new backend

Re-implementing your data model

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.