Connection Failures

Symptom: connect_result returns "connection refused"

The driver could not reach the database host or the credentials were rejected. Check, in order:

Is the server running? pg_isready -h localhost, systemctl status mysql, mongo –eval "db.runCommand('ping')".
Is the host/port correct? Compare your connection string against the server's bind address.
Are the credentials valid? Try the same connection string with the native CLI (psql, mysql, mongosh).
Is the backend compiled in? set_mode() returns false if the backend was not built. Re-run CMake with -DUSE_POSTGRESQL=ON or the equivalent flag for your backend.
TLS/SSL mismatch? PostgreSQL refuses connections when the server requires SSL but the client did not request it. Add sslmode=require to the connection string.

auto r = mgr->connect_result(conn);
if (!r.is_ok())
{
    std::cerr << "code=" << r.error().code
              << " message=" << r.error().message << "\n";
}

Symptom: connection hangs forever

A firewall is silently dropping the SYN packet, or the host name resolves to an unreachable address. Set an explicit timeout in the connection string (connect_timeout=5 for libpqxx, connectTimeoutMS=5000 for mongo-cxx-driver) so failures surface quickly, and verify the route with nc -vz host port.

Transaction Deadlocks and Locking Errors

Symptom: "deadlock detected" or "Lock wait timeout exceeded"

Two transactions are waiting on each other's locks. The fix is one or more of:

Acquire locks in a deterministic order. If transaction A always updates users before orders, transaction B must do the same.
Shorten the transaction. Move read-only work outside BEGIN / COMMIT, and avoid user-input prompts inside a transaction.
Retry on conflict. Wrap the transaction in a loop that retries up to N times when result.error() reports a serialization or deadlock code.
Lower isolation when safe. Switch from SERIALIZABLE to READ COMMITTED if your invariants allow it.

for (int attempt = 0; attempt < 3; ++attempt)
{
    transaction_guard tx(mgr);
    auto r = do_work(mgr);
    if (r.is_ok()) { tx.commit(); break; }
    if (!is_retryable(r.error())) throw std::runtime_error(r.error().message);
}

SQLite-specific: SQLITE_BUSY / "database is locked"

SQLite serialises writers. The error means another connection holds the write lock when yours tried to upgrade. Mitigations:

Open the database with PRAGMA journal_mode=WAL to allow readers while a writer is active.
Use BEGIN IMMEDIATE instead of plain BEGIN to acquire the write lock at transaction start.
Avoid sharing a single SQLite connection across threads — give each thread its own database_manager.

ORM Entity Mapping Errors

Symptom: empty fields() or missing columns in CREATE TABLE

The static metadata_ instance was never populated. Causes and fixes:

**Forgot to call add_field**. Every ENTITY_FIELD has a matching <name>metadata member that must be added inside initialize_metadata().
Two entities sharing the same static metadata. Each subclass needs its own "static @c inline @c entity_metadata @c metadata_{...}" member with a unique table name.
initialize_metadata() never ran. Make sure you used the ENTITY_METADATA() macro inside the class — it wires up the static initializer that calls initialize_metadata().

class user_entity : public entity_base
{
public:
    ENTITY_FIELD(int64_t,     id,
                 field_constraint::primary_key | field_constraint::auto_increment)
    ENTITY_FIELD(std::string, email, field_constraint::not_null | field_constraint::unique)
    ENTITY_METADATA()
private:
    static inline entity_metadata metadata_{"users"};
};
 
void user_entity::initialize_metadata()
{
    metadata_.add_field(id_metadata_);
    metadata_.add_field(email_metadata_);   // <-- easy to forget
}

Symptom: "NOT NULL constraint failed" on insert

A field marked not_null was left unset, or its value defaulted to null because the entity was constructed without assignment. Always assign every required field before calling save/insert, and consider adding a static factory that takes mandatory fields as constructor arguments.

Query Performance Issues

Symptom: a SELECT that "should be fast" is slow

Run the backend's EXPLAIN tool to see the execution plan. Common fixes:

Missing index. Add field_constraint::index to the entity field used in the WHERE clause and re-run create_indexes_sql().
Implicit type conversion. Comparing an indexed BIGINT column against a string literal forces a sequential scan. Use the typed query builder values (int64_t{...}) so the dialect formats them correctly.
Returning too many columns. Replace SELECT * with an explicit projection. The query builder makes this trivial.
N+1 problem. If you call select_query_result inside a loop, rewrite the work as a single JOIN or IN query.

Symptom: throughput drops as concurrency rises

You are likely contending on the driver-level pool. Increase max_connections / maximumPoolSize on the driver, distribute the load across more database_manager instances, or move the workload behind the proxy mode (when available).

Use monitoring_system metrics from database/monitoring/performance_monitor.h to confirm where time is being spent.

Migration Conflicts

Symptom: "column already exists" or "relation does not exist"

Your migration script and the live schema are out of sync. To recover:

Inspect the schema ("\\d @c users" in psql, DESCRIBE users in MySQL, db.users.findOne() in mongosh).
Compare against the migration that should have produced it.
Either re-run the missing migration or write a corrective migration that brings the live schema back in line.
If you regenerate DDL with entity::get_metadata().create_table_sql(), prefer CREATE TABLE IF NOT EXISTS so the script remains idempotent.

Symptom: works on PostgreSQL, fails on MySQL or SQLite

The DDL is using a dialect-specific feature. Examples:

JSONB exists only in PostgreSQL.
SERIAL is PostgreSQL-only; use BIGSERIAL or per-backend auto-increment.
"CREATE @c INDEX @c IF @c NOT @c EXISTS" is supported by SQLite and PostgreSQL but not by older MySQL versions.

When you want a single script to run everywhere, drive the schema from the ORM (create_table_sql) so the dialect translation happens centrally, or keep separate per-backend migration files.

Still stuck?

Frequently Asked Questions — General-purpose answers
Quick Start Tutorial — Verified end-to-end example
File an issue at https://github.com/kcenon/database_system/issues with the failing call site, the result.error() output, and your CMake flags.

Table of Contents