Database Backends

maniflex ships two database adapters, both built on database/sql and sharing a single SQL core (db/sqlcore). They expose the same interface; switching between them is one import line.

Each adapter lives in its own Go module so a project only pulls in the driver it actually uses.

SQLite

The default choice for development, tests, and small deployments. The pure-Go driver means no CGo and no external service — go run . is enough to start a local server with a working database.

import "github.com/xaleel/maniflex/db/sqlite"

db, err := sqlite.Open("./app.db", server.Registry())
if err != nil {
    log.Fatal(err)
}
defer db.Close()
server.SetDB(db)

Common DSNs:

SQLite is single-writer by design. The framework serialises writes through one connection internally; reads run on a pool. This is plenty for most internal tools and many production APIs.

Write connections open their transactions with BEGIN IMMEDIATE (the _txlock=immediate DSN option, applied for you). That is what makes a read-then-write transaction — LockForUpdate, an If-Match check, mfx:"lock_scope" — behave the way it does on Postgres: a second transaction waits at its BEGIN rather than reading the same stale row and then failing, or overwriting, on the way out. Spell out your own _txlock= in the DSN and yours is kept.

PostgreSQL

The recommended adapter for any multi-process deployment. It supports genuine concurrent writers, real FOR UPDATE locks, and read replicas.

import "github.com/xaleel/maniflex/db/postgres"

// Open(writeDSN, readDSN, registry) — positional arguments.
db, err := postgres.Open(
    "postgres://user:pass@host/db?sslmode=require",      // write DSN
    "postgres://user:[email protected]/db?sslmode=require", // read DSN (optional)
    server.Registry(),
)
if err != nil {
    log.Fatal(err)
}
defer db.Close()
server.SetDB(db)

Pass an empty read DSN ("") to route reads to the primary. The adapter selects the appropriate pool per request based on the operation — OpList and OpRead go to the read pool, everything else to the write pool.

For connection-pool and session tuning use postgres.OpenWithConfig(writeDSN, readDSN, registry, writePool, readPool, session) — see PostgreSQL in Production for replica lag handling and SSL.

Switching between them

The adapter is the only thing that changes; nothing else in the application needs to know which database is in use:

- import "github.com/xaleel/maniflex/db/sqlite"
+ import "github.com/xaleel/maniflex/db/postgres"

- db, err := sqlite.Open("./app.db", server.Registry())
+ db, err := postgres.Open(os.Getenv("DB_URL"), "", server.Registry())

Models, middleware, and queries are portable across both backends because they go through database/sql + the shared db/sqlcore adapter. Migrations emitted by AutoMigrate use a portable subset of SQL.

AutoMigrate

Unless Config.DisableAutoMigrate is set (migration runs by default), the adapter:

1. Creates any table that does not yet exist for a registered model.
2. Adds any column that exists on the struct but not in the table.
3. Logs a warning for columns that exist in the table but not on the struct (the framework never drops columns automatically).
4. Logs a warning for a column whose type no longer matches its model field (the framework never rewrites columns automatically).
5. Creates indexes declared in ModelConfig.Indices or auto-generated for mfx:"scheduled" fields.

AutoMigrate adds; it does not rewrite. Change a field’s Go type — int to string, say — and the existing column keeps the type it was created with. You get a warning naming the table, the column, and both types on every startup, but the schema is left alone: rewriting a column can lose data and locks the table while it runs, so the conversion (and whatever backfill it needs) belongs in an explicit, versioned migration you run against the database, not in a startup routine that has to guess. Until that migration runs, reads and writes of the field can fail against the old column.

AutoMigrate is suitable for development and many small deployments. For larger systems, set DisableAutoMigrate: true and either manage the schema with a dedicated migration tool or migrate from a single process — see below.

Migrations in production

Server.ValidateProduction requires Config.DisableAutoMigrate, so a production server does not migrate as it boots. That flag is about the automatic migration — the one Start runs as a side effect. MigrateOnly is the explicit call and runs regardless, which is what lets one binary and one config serve both roles:

cfg.DisableAutoMigrate = true // every replica; ValidateProduction requires it

server := maniflex.New(cfg)
// … register models, actions, middleware …

if os.Getenv("MIGRATE_ONLY") == "1" {
    log.Fatal(server.MigrateOnly(ctx)) // the init container / pre-deploy job
}
log.Fatal(server.Start()) // the replicas, which migrate nothing

MigrateOnly validates and seals the whole configuration before it touches schema, so a misconfigured application fails the job rather than half-migrating. To validate and seal without any schema work, call Handler or StartServices instead.

What is safe under a rolling deploy

AutoMigrate only ever adds. That is the property that makes it rolling-safe: while old and new replicas run side by side, a new column is invisible to the old ones, and a new table is unreferenced by them.

Everything else is yours, and is not rolling-safe:

For a removal, the two-phase shape is the usual one: ship the code that stops writing the column, complete the rollout, then drop it. For a type change, add a new column, backfill, switch reads, and drop the old one — each phase its own deploy.

Concurrent replicas

Several processes may run migration at once without corrupting each other. Each table’s create-introspect-alter sequence is one transaction, columns are added with IF NOT EXISTS (or duplicate-tolerantly on SQLite), and a foreign key another process added first is accepted rather than treated as an error.

Prefer migrating from one process anyway. Concurrency-safe means the losing replica does not break; it does not mean the work is coordinated, and there is no migration lock or leader election here — the framework does not attempt to sequence a schema change against the replicas that are mid-rollout.

The DBAdapter interface

Both shipped adapters implement maniflex.DBAdapter. Custom backends — an HTTP data service, a remote API, a different SQL database — implement the same interface and inject the result through server.SetDB(myAdapter). The interface is in db.go.

Adapters are compared by identity

The framework uses == on DBAdapter values to decide whether two models share a database — that is what stops a transaction from silently spanning two of them. So a custom adapter must:

• use a pointer receiver — return *MyAdapter, not MyAdapter. Two separately constructed value-type adapters with equal fields compare equal, so the framework would treat two databases as one and let a transaction span them. That fails silently, not loudly.
• stay comparable — comparing interface values whose dynamic type is not comparable (a struct holding a map, slice, or func, used as a value) panics at run time. A pointer receiver gives you this for free.

Nothing in the type system enforces either, which is why it is written down here and on the DBAdapter godoc.

Rendering filters

A SQL-backed adapter does not have to write its own filter renderer, and should not. maniflex.BuildFilterSQL turns a []*FilterExpr into the body of a WHERE clause — grouping, operator rendering, LIKE escaping, IN expansion, field resolution and value coercion — and maniflex.Quote quotes identifiers the way the framework does:

p := sqlcore.NewPlaceholderBuilder(maniflex.SQLite) // or your own PlaceholderBinder
where := maniflex.BuildFilterSQL(model, qp.Filters, maniflex.SQLite, p)

sql := "SELECT * FROM " + maniflex.Quote(model.TableName)
if where != "" {
    sql += " WHERE " + where
}
rows, err := db.Query(sql, p.Args()...)

PlaceholderBinder is one method, Add(any) string, returning the placeholder text and recording the value. Implementations must append in call order: SQLite binds ? by where it appears in the statement text, so a binder that registered a placeholder out of order would misalign every argument after it without raising a syntax error.

This is shared rather than copied for a reason. The framework used to carry two filter renderers, and every capability one grew that the other did not shipped as a silently wrong answer — three separate P0 bugs before they were merged. A third copy inside an adapter is the same trap.

BuildFilterSQL renders a nested-relation filter as "relation"."column", which assumes your FROM clause joined that relation. An adapter or query path that does not join must reject nested filters rather than pass them through.

Per-model adapter routing

Config.DB sets the default adapter. Individual models can override it by passing ModelConfig.Adapter:

ordersDB, _    := postgres.Open(ordersDSN, "", server.Registry())
inventoryDB, _ := postgres.Open(inventoryDSN, "", server.Registry())

server.MustRegister(
    Order{},         maniflex.ModelConfig{Adapter: ordersDB},
    InventoryItem{}, maniflex.ModelConfig{Adapter: inventoryDB},
    User{},          // unrouted — falls back to Config.DB
)

“Distinct” here means distinct under ==. Two models that share a database must be given the same adapter value, not two opened against the same DSN — those are separate connection pools and separate transactions, and nothing tells the framework they point at one database.

The framework treats each distinct adapter as its own database:

• AutoMigrate runs once per adapter, with a filtered registry view so each adapter only sees the models routed to it. Tables for Order are never created on the inventory DB and vice-versa.
• CRUD requests (GET /orders, POST /orders) route through Order.Adapter. The DB step picks the per-model adapter automatically.
• ctx.BeginTx / ctx.RawQuery / ctx.RawExec use the request’s model adapter, so middleware and custom actions stay on the right DB.
• ctx.GetModel("OtherModel") uses the target model’s adapter — handy for cross-DB reads — but it cannot share a transaction across adapters: if ctx.Tx was opened on dbA and you call GetModel("X") where X lives on dbB, the accessor falls back to a non-transactional read against dbB.

Config.DB is optional when every registered model has its own Adapter. The server starts cleanly with DB: nil and routes everything through the per-model overrides. If any model is unrouted and Config.DB is also nil, startup fails with a clear error naming the unrouted models.

Constraint: transactions are adapter-scoped

A single database transaction cannot span two adapters. Two consequences:

1. maniflex.Batch rejects a b.Create("X", ...) call where X routes to a different adapter than the batch transaction was opened on. The error message points to pkg/saga as the cross-adapter pattern.
2. Manually-opened ctx.Tx only protects writes against the request’s own model adapter. Cross-adapter writes through ctx.GetModel(...) happen outside that transaction.

For coordinated writes across databases, use pkg/saga — compensating transactions are the supported pattern.

Choosing