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.
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
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 backend is ideal for development and embedded deployments:
[dependencies]
cloacina = { version = "0.0.0-alpha.2", default-features = false, features = ["sqlite", "macros"] }
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?;
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 | 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 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)
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
))
PostgreSQL can generate UUIDs:
id UUID PRIMARY KEY DEFAULT uuid_generate_v4()
SQLite requires application-generated UUIDs:
let id = UniversalUuid::new_v4();
- Building scalable systems
- Requiring high concurrency
- Multiple applications share the database
- Developing locally
- Building embedded applications
- Single-user or low-concurrency scenarios
- Simplicity is more important than scalability
- Want zero-dependency deployment
NoteMigration 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
- Excels at concurrent workloads
- Benefits from connection pooling
- Can utilize multiple CPU cores
- Scales horizontally
- Extremely fast for single-threaded access
- Minimal memory overhead
- No network latency
- Entire database can fit in memory
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.