Which backend should I choose?

It depends on the deployment shape and the workload:

Scenario	Recommended backend
Embedded app, CLI tool, single-user desktop	SQLite — zero-configuration, file-based
Unit tests for code that talks to a database	SQLite (`:memory:`)
Web/server OLTP, JSON-heavy workloads, analytics	PostgreSQL — most feature-complete
Existing MySQL infrastructure	MySQL — drop-in via the MySQL backend
Document-shaped or schemaless data	MongoDB (experimental)
Cache-style key/value lookups	Redis (experimental)

PostgreSQL is the safest default for new server applications. SQLite is the safest default for tests and embedded scenarios.

How do I configure connection pooling?

Local in-process connection pooling was removed in Phase 4.3 to make room for the upcoming proxy mode. Today there are two practical options:

Driver-level pooling — All supported drivers (libpqxx, MySQL connector, mongo-cxx-driver) provide their own pool / session manager. Configure the pool size through the connection string or driver options, then let database_manager open one logical handle per request.
Wait for proxy mode — A future database_server process will centralise pooling and expose it through the same client API. The client code does not change.

For high-throughput services, treat the database_manager as a short-lived facade and rely on the driver pool underneath.

What about transaction isolation levels?

database_manager exposes begin_transaction(), commit_transaction(), and rollback_transaction() helpers. These open a transaction at the backend default isolation level:

PostgreSQL: READ COMMITTED by default; raise per-transaction with "BEGIN @c TRANSACTION @c ISOLATION @c LEVEL @c SERIALIZABLE".
MySQL (InnoDB): REPEATABLE READ by default; change with "SET @c TRANSACTION @c ISOLATION @c LEVEL @c READ @c COMMITTED".
SQLite: serialised single-writer; use "BEGIN @c IMMEDIATE" to acquire the write lock up front and avoid SQLITE_BUSY surprises.
MongoDB: per-document atomicity, with multi-document transactions available on replica sets.

To customise, issue the appropriate SET / BEGIN statement before your work, then call commit_transaction() as usual.

How do I handle schema migrations?

database_system does not ship a migration runner today. The recommended patterns are:

Idempotent DDL — Use "CREATE @c TABLE @c IF @c NOT @c EXISTS" and "ALTER @c TABLE ... @c IF @c NOT @c EXISTS" so the same script can run repeatedly.
Versioned scripts — Keep numbered SQL files alongside your code (e.g. migrations/0001_init.sql) and run them at startup with a simple loop that records the current version in a schema_versions table.
Generated DDL from entities — Call entity::get_metadata().create_table_sql() to bootstrap a schema directly from your ORM declarations. See ORM Entity Mapping Tutorial.

Query builder vs raw SQL — when should I use which?

Use the query builder when...	Use raw SQL when...
Column lists or filters are dynamic	The statement is fixed and well-understood
You target multiple backends	You need a backend-specific feature (e.g. `JSONB`, `LATERAL` `JOIN`)
You want compile-time-ish safety on identifier names	You are running a one-off DDL script
You need to compose conditions programmatically	The query is short and the SQL is clearer than the builder calls

The two are not exclusive — many apps build the SELECT clause with the query builder and pass complex CTEs as raw text.

Does the ORM hurt performance?

The ORM is metadata-driven, not reflective. ENTITY_FIELD generates a small field_metadata struct at compile time and a static initializer adds it to a per-class entity_metadata instance. There is no runtime parsing, no virtual call per field, and no extra allocation per row read.

Practical guidance:

Hot paths can read columns directly from the row map and bypass the ORM entirely.
The ORM is most valuable for schema generation, validation, and mapping returned rows to typed objects.
For very wide rows, prefer projection (SELECT only the columns you need) over SELECT *.

Does database_system support prepared statements?

Each backend supports prepared statements at the driver layer. The database_manager helpers currently accept finished SQL strings, so parameterisation happens via the query builder (which formats values through value_formatter) or by calling backend-specific prepare APIs.

For high-volume queries, prefer the query builder so values are escaped correctly per dialect and so the same code keeps working when you switch backends.

How do async operations work?

The database/async/ module exposes asynchronous wrappers built on Asio. They run blocking driver calls on a worker pool and return std::future-style handles, so you can integrate database calls with the rest of an Asio-based application without spawning ad-hoc threads.

If you already have a thread pool from thread_system, hand it to the async layer rather than letting it create its own.

How should I handle errors and recovery?

Every high-level call returns kcenon::common::Result<T>, so error handling is explicit:

Check result.is_ok() before reading result.value().
Inspect result.error().message and result.error().code for diagnostics.
For transient connection drops, wrap the operation in a retry loop that re-establishes the connection on a fresh database_manager before retrying.
Always wrap multi-statement work in a transaction so a partial failure rolls back cleanly.

See error_handling.cpp for the canonical patterns.

How do I implement multi-tenancy?

There are three common shapes; pick the one that matches your isolation requirements:

Schema per tenant — Each tenant gets its own schema (PostgreSQL) or database (MySQL/MongoDB). Switch by setting the schema/search_path on connection. Strongest isolation, biggest operational cost.
Database per tenant — Open a separate database_manager per tenant. Use the upcoming proxy mode to share connections.
Row-level tenancy — Add a tenant_id column to every table and include it in every WHERE clause. Cheapest, but requires discipline; consider enforcing it with row-level security on PostgreSQL.

Table of Contents