Skip to main content
Cloacina Documentation
Toggle Dark/Light/Auto mode Toggle Dark/Light/Auto mode Toggle Dark/Light/Auto mode Back to homepage

Database Backends

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 a compile-time feature flag system to select between PostgreSQL and SQLite backends. This approach ensures:

  • Zero runtime overhead for backend selection
  • Optimal performance for each database type
  • Type safety across different backends
  • Consistent API regardless of backend choice

Backend Selection

PostgreSQL (Default)

PostgreSQL is the default backend, designed for scalable deployments:

[dependencies]
cloacina = "0.0.0-alpha.2"
# or explicitly:
cloacina = { version = "0.0.0-alpha.2", features = ["postgres"] }

SQLite

SQLite backend is ideal for development and embedded deployments:

[dependencies]
cloacina = { version = "0.0.0-alpha.2", default-features = false, features = ["sqlite", "macros"] }

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("myapp.db?mode=rwc&_journal_mode=WAL&_synchronous=NORMAL&_busy_timeout=5000").await?;

// In-memory database (for testing)
let runner = DefaultRunner::new(":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("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:

  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 compile-time backend selection ensures you get optimal performance and behavior for your chosen database, while maintaining a consistent API across both backends.