Implementing a simple object relational mapper in C

Object relational mapping turns rows in a relational table into C structures without forcing every caller to hand-code SQL and field extraction. That convenience becomes misleading in C, where there are no built-in objects, reflection or garbage-collected lifetimes. A practical mapper needs an explicit contract: which struct represents a table, which columns populate which members, and who owns the resulting memory. The goal is to remove repetitive persistence code while keeping control close to the caller.

A small scope works well when each mapped type has a stable table and each field has a clear C representation. The mapper can cover inserts, single-row lookups, updates and deletes, leaving joins, inheritance and complex graphs to SQL or application code. This boundary matters because schemas often contain relationships that nested structures cannot express cleanly. Hiding those mismatches inside an automatic layer usually creates debugging sessions instead of saving them.

The design should separate database concerns from business logic. SQL generation, parameter binding, row conversion and transaction control belong in the persistence layer; validation, pricing rules and workflow decisions belong in domain code. Prepared statements should be the default, ownership documented and errors consistent. Such restraint makes the library practical inside command-line tools, desktop utilities and small services.

Many Australian teams work across Sydney, Melbourne, Brisbane and regional offices, so portability matters. A consultancy might use SQLite in a local diagnostic tool and PostgreSQL in a hosted client system while keeping one object model. A narrow database interface can isolate those differences. It can also reduce the temptation to place personal information in ad hoc log files, an issue that becomes significant under Australian privacy duties.

Defining the mapper’s boundary

Start by naming the operations the library will support instead of promising generic object mapping. A first version might expose functions such as repo_insert_customer, repo_find_customer_by_id and repo_update_customer. Each function receives a connection or statement handle, binds known values and returns a status with an optional object. Fixed operation names are easier to audit than a string-driven mini-language and give the compiler a stable interface.

Because C lacks runtime type discovery, the mapper must be told what it is handling. It should not infer a table name from a struct tag or assume that name always maps to customer_name. An explicit descriptor makes those assumptions visible and reviewable. It also permits two structs to share a column while assigning different meanings to separate fields. The metadata can live beside the model or be emitted by a small generator.

Keep the database API small enough to implement against SQLite, PostgreSQL or another engine without rewriting the object layer. The abstraction needs only statement preparation, parameter binding, row stepping, transaction boundaries, error reporting and a way to retrieve generated keys. It does not need to mirror every proprietary feature. Dialect-specific SQL should stay behind named hooks, such as quoting an identifier or choosing a placeholder style.

Describing tables with metadata

A row map can consist of a table name, a list of field descriptors and a struct type. Each descriptor records the SQL column, its byte offset in the struct, a logical type and flags for nullability or key status. Table names and columns are identifiers, so they must come from trusted metadata rather than user input. Parameter values follow a different path and are always bound.

typedef enum {
    FIELD_INT64,
    FIELD_TEXT,
    FIELD_DOUBLE,
    FIELD_BLOB
} field_type;

typedef struct {
    const char *column;
    size_t offset;
    field_type type;
    unsigned flags;
} field_map;

typedef struct {
    const char *table;
    const field_map *fields;
    size_t field_count;
} row_map;

Schema changes then become a mapping problem as well as a database migration. A generated header can be rebuilt after each migration, while handwritten descriptors require discipline that small teams often underestimate. Generate the code, compare it in version control and treat differences as reviewable artefacts. The generator should reject unknown SQL types or ambiguous nullability instead of guessing.

Foreign keys and joins deserve special treatment. A basic mapper can expose a relation as a foreign-key identifier rather than pretending to materialise an entire graph. The child struct stores that identifier, and a repository function loads the related row when needed. This keeps memory use predictable and prevents an accidental recursive load. It also gives the application a clear place for deciding what a user may access.

Generating and binding SQL safely

SQL generation should never interpolate untrusted values. For an insert, the mapper can emit a fixed statement shape; for a lookup by identifier, it can prepare and reuse a specific statement. Placeholder syntax belongs to the dialect layer: SQLite commonly uses ?, while PostgreSQL uses numbered markers such as $1. Column and table names remain static metadata and must never be treated as bound values.

int rc = sqlite3_bind_int64(stmt, 1, customer->id);
if (rc != SQLITE_OK) {
    return mapper_error(db, rc, "bind customer id");
}

rc = sqlite3_bind_text(stmt, 2, customer->email,
                       -1, SQLITE_TRANSIENT);
if (rc != SQLITE_OK) {
    return mapper_error(db, rc, "bind customer email");
}

Binding by position makes mistakes more obvious when the statement and field list grow apart. The bind function should compare its argument count with the descriptor count before touching the statement. A textual value is not necessarily NUL-terminated, so pass its exact length and request a transient copy when the source may change before execution. Binary data needs an explicit length too.

Null handling requires an explicit policy. A nullable field can use a validity flag beside its value, or the caller can accept a null pointer for pointer fields. Silently converting SQL NULL into zero or an empty string corrupts data and creates subtle reporting errors. The converter should also range-check integers, reject malformed UTF-8 where the schema promises Unicode and preserve blob lengths without relying on a terminating byte.

Converting rows into C objects

Reading a row starts with matching selected columns to field descriptors. For each descriptor, the driver supplies a typed value or NULL. The mapper checks the logical type, converts it and stores it at the recorded offset in the destination struct. Using offsetof with memcpy avoids assumptions about padding, although assigning scalar members directly can be clearer for simple types.

switch (field->type) {
case FIELD_INT64:
    store_i64(object, field->offset,
              sqlite3_column_int64(row, column));
    break;
case FIELD_TEXT:
    copy_text(object, field->offset, row, column);
    break;
default:
    return MAPPER_BAD_TYPE;
}

Text ownership is a frequent source of dangling pointers. Some drivers return a pointer into a buffer owned by the statement or row. That buffer may become invalid after the next step, so the mapper should copy text and blobs into caller-owned storage when the object must outlive the query. If the repository always returns borrowed memory, its contract must state that clearly and keep the statement alive.

Optional fields also affect object design. A char * can represent an absent value only when the mapper guarantees that NULL becomes NULL rather than "". Fixed-width numeric fields often benefit from a separate presence bit, especially when zero is valid. Descriptor flags can guide conversion, and conversion errors should identify the column and row rather than returning an unexplained zero value.

Controlling writes and transactions

An object mapper becomes dangerous when writes blur into one generic entry point. Keep insert, update and delete functions separate so their intended semantics remain visible. An update should normally target a primary key and report the number of affected rows. A delete should either require an explicit matching key or state plainly that it removes every matching row.

Transactions protect multi-step changes. If creating an order also writes an audit event, both statements should run inside one transaction and roll back together on failure. The mapper can expose begin, commit and rollback, but it should not commit behind the caller’s back. Nested transaction handling varies among engines, so a portable API should reject ambiguous nesting or delegate it to a documented dialect operation.

Concurrency needs a clear choice. Optimistic locking with a version column can detect edits made after a record was loaded. The update then includes the expected version and increments it atomically. Generated keys also need care: last_insert_rowid is connection-specific, and portable code should use a dialect hook that returns the generated key for the correct table.

Testing persistence across databases

Test the mapper against SQLite while developing, then run the same contract against PostgreSQL before release. SQLite provides a fast, dependency-free fixture for unit tests; PostgreSQL exposes differences in placeholders, type affinity, identifier casing and transaction behaviour. Keep schema migrations under test, apply them to an empty database and verify both mapped rows and untouched columns. Each test should assert values, nullability and error paths, not merely check that a call returned.

Domain details deserve fixtures that mirror actual Australian use. Dates should be stored in UTC, with zone conversion applied at the boundary; daylight-saving changes make local timestamps ambiguous. Money can use integer cents when every amount is in Australian dollars, while products requiring sub-cent precision need a decimal representation. Test names with Unicode, addresses formatted for Australian streets and phone numbers kept as text so leading zeroes survive.

Version control is equally important for schema and generated bindings. A team can learn from the move from CVS about the value of stable branching, reviewable changes and a clear history for generated artefacts. Keep the generator source, migrations and checked-in output in the same repository, then review regenerated differences alongside ordinary code. That practice prevents a deployment from carrying a mapper description that no longer matches its database.

Running the mapper in Australian organisations

Deployment in Australia adds obligations that a mapping library cannot solve by itself. Under the Privacy Act 1988, covered organisations handling personal information must follow the applicable federal privacy principles, including requirements for collection, use, disclosure, security and access. Coverage can depend on an organisation’s activities and exceptions, not only its size, so legal advice remains important for businesses handling health, employment or customer records.

The mapper should support privacy by design without pretending to be a security product. Bound parameters prevent SQL injection, but they do not prevent excessive collection or unsafe backups. Avoid writing complete personal records to debug logs, encrypt connections and stored data where risk warrants it, and restrict database accounts by role. A customer record may need different retention treatment from an invoice, and a generic created_at field should not become a reason to keep everything indefinitely.

Small consultancies and software vendors in Sydney and Melbourne often value maintainable open-source dependencies and locally supported deployments. A compact mapper with no background thread fits that market, provided its build process and supported database versions are documented. Portable SQL and few external dependencies also reduce the operational burden on teams that cannot maintain a large framework.

Offline work adds another Australian reality. A field technician in Adelaide may review customer records on a laptop between site visits, then synchronise changes when office connectivity returns. The persistence layer should record conflicts or operation order instead of silently overwriting newer data. If the application sends service reminders by email, its sender must also check consent, identification and unsubscribe requirements under the Spam Act 2003 before connecting that channel to the mapper.