orm

Theme

Runtime connection ​

The clients take connection values as arguments and read no environment variable or secret store of their own. The client does not read a configuration file.

The DSN URI is the only database selector:

text
mysql://user:password@host:3306/orm_example
mysql://user@localhost/orm_example?socket=/tmp/mysql.sock
postgres://user:password@host:5432/orm_example?sslmode=disable
postgres:///orm_example?host=/tmp
sqlite:///var/lib/orm_example.sqlite?_pragma=busy_timeout(5000)
markdown://127.0.0.1:4100/tracker?token=0123456789abcdef0123456789abcdef
  • CFG-1 The scheme selects MySQL, PostgreSQL, or SQLite. The client creates the matching native driver and opens its pool; schema loading and hash checks are internal. The scheme markdown selects the markdown IR executor of a flowmark server instead of a database; its DSN, its server connections and the settings it refuses are in the protocol and the capability table. Every client implements it; the Python client keeps one server connection for it.
  • CFG-2 Every connection reads and writes datetime values in UTC, whatever the server time zone (dialects): MySQL connections set time_zone = '+00:00', PostgreSQL connections set TimeZone to UTC, and SQLite values and ORM value functions use UTC on the client. A value with an offset is written as its UTC wall clock and read back in UTC. The optional timezone parameter accepts only UTC or +00:00; any other value returns CONFIG.
  • CFG-3 Every MySQL connection sets the session sql_mode to STRICT_ALL_TABLES,ONLY_FULL_GROUP_BY,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION when it opens, after the driver's own setup, so a coercion, truncation or grouping result does not depend on the server's sql_mode, and modes that a driver adds (PIPES_AS_CONCAT from sqlx, IGNORE_SPACE from a client flag) are replaced; the connection reads @@SESSION.sql_mode as ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION (mysql.session.sql_mode_set, tests/dsn/mysql-session.json).
  • CFG-4 The connection configuration takes poolSize, the maximum open connections: Go orm.Config{PoolSize: n}, PHP new Config(poolSize: n), TypeScript { poolSize: n }, Rust Db::connect(dsn, n, config), and Python connect(dsn, {"poolSize": n}). Zero or unset opens at most 10 connections in a client that keeps a pool, and a negative value returns CONFIG. A pool never opens more connections than its maximum; a statement or transaction that needs a connection while all of them are in use waits for one. The TypeScript SQLite driver holds one connection. PHP opens a connection per request: each Db opens one connection that closes with it, and no connection outlives the request. Pooling for PHP is done by PgBouncer or ProxySQL (Connection poolers). The PHP client keeps no pool of its own: a php-fpm worker serves one request at a time and shares no state with the next, a connection kept across requests carries the session state of one request into the next and multiplies the server connections by the workers, and a pooler shares a few server connections among all workers instead. new Config(poolSize: n) with n other than zero returns CONFIG, because a setting the client ignored would let a caller believe the connections are pooled when they are not. stats() reports maxOpenConnections 1, the one connection of the Db.
  • CFG-5 The connection configuration takes poolIdleSize, the maximum idle connections of the pool, and poolLifetimeMs, the lifetime of a pool connection in milliseconds: Go orm.Config{PoolIdleSize: n, PoolLifetimeMs: ms}, TypeScript { poolIdleSize: n, poolLifetimeMs: ms }, Rust Config { pool_idle_size: n, pool_lifetime_ms: ms, ..Default::default() }, and Python connect(dsn, {"poolIdleSize": n, "poolLifetimeMs": ms}); PHP keeps no pool and refuses them, as the end of this item says. A connection released while the pool already keeps poolIdleSize idle connections is closed. A connection whose lifetime has passed is closed while it is idle or when it is released. Zero or unset poolIdleSize keeps up to the pool size of idle connections. Zero or unset poolLifetimeMs keeps connections without a lifetime in every client with a pool except Rust, which keeps the 30-minute lifetime of its pool. A negative value or an idle size above the pool size returns CONFIG; the Rust fields are unsigned. The TypeScript SQLite driver holds one connection and does not use these values. PHP returns CONFIG for any poolIdleSize or poolLifetimeMs other than zero, because it has no pool.
  • CFG-6 The connection configuration takes statementTimeoutMs, the bound of every statement of the connection: Go orm.Config{StatementTimeoutMs: n}, PHP new Config(statementTimeoutMs: n), TypeScript { statementTimeoutMs: n }, Rust Config { statement_timeout_ms: n, ..Default::default() }, and Python connect(dsn, {"statementTimeoutMs": n}). Zero or unset keeps the server default, and a negative value returns CONFIG. PostgreSQL bounds every statement with statement_timeout, which every client sends as a startup parameter of each server connection. MySQL bounds SELECT statements with max_execution_time; a write is bounded by the server lock wait timeout (innodb_lock_wait_timeout), not by this value. SQLite has no session timeout, so its bound is the DSN busy_timeout. A statement the bound stops returns CANCELED. A MySQL lock wait timeout (error 1205) is the expiry of that write bound, so it returns CANCELED too, in every client.
  • CFG-7 The PHP client and the Python client set the session settings when a connection opens: PostgreSQL receives the time zone and statement_timeout as startup parameters, and MySQL runs SET time_zone = '+00:00', sql_mode = '<declared modes>', SESSION max_execution_time = <ms> as one init command. A PHP request that ends inside a transaction, by exit or a fatal error, ends its connection with it, and the server rolls the transaction back and releases the named locks and session values of the connection.
  • CFG-8 The connection configuration takes auditSource, a function that returns the audit values of the current request, a map from column name to value of the audit record table, such as the account and the request id: Go orm.Config{AuditSource: func(ctx context.Context) (map[string]any, error)} (called with the transaction's context), PHP new Config(auditSource: fn (): array => [...]), TypeScript { auditSource: () => ({...}) }, Rust Config { audit_source: Some(Arc::new(|| Ok(vec![...]))), .. }, and Python connect(dsn, {"auditSource": lambda: {...}}). A transaction with audit values calls it once and inserts the audit record with these values and its own (usage); the ORM writes no audit value of its own, and an error of the source fails the transaction.
  • CFG-9 A flow cancels its statements with the cancellation primitive of its own language, on a connection handle. Go db.WithContext(ctx) and TypeScript db.withSignal(signal) return a handle on the same connection: models connect to the handle as they connect to the connection, transactions started on it carry the same cancellation, and cancelling the context or the signal ends the statement in flight with CANCELED while the connection stays usable. Python db.with_future(future) returns a handle whose active statement on a MySQL, PostgreSQL, or SQLite connection is cancelled when the supplied asyncio.Future is cancelled. Python SQL remains synchronous: run it with asyncio.to_thread so cancellation can be observed by the event loop; transaction cleanup is not cancelled, and the root connection remains reusable after rollback. The Python markdown executor does not support statement cancellation. In Rust, dropping the future of a statement cancels it; the checked-out connection is closed before it can return to the pool. PostgreSQL cancels the running statement, MySQL ends the running query, and the TypeScript SQLite driver runs a statement without yielding, so a signal there ends the next statement rather than the one in flight. PHP $db->withCancellation($source) returns a handle on the same logical connection. $source->cancel() ends every active statement started through a handle attached to that source with CANCELED. On SQLite a progress handler interrupts the virtual machine; on MySQL a control connection ends each active server connection attached to the source, the transaction is rolled back, and each root handle reconnects before its next statement. On PostgreSQL PHP requires both pdo_pgsql and ext-pgsql; it sends the protocol cancel request on the same physical connection, rolls back the interrupted transaction, and reuses the root connection. Transaction cleanup is not cancelled. The PHP markdown executor does not support statement cancellation. The chain has no cancellation operation; the connection handle carries it. A handle is a separate value, so Go db.Root(), Python db.root(), and TypeScript db.root() return the connection it was derived from, and two handles of one connection compare equal.
  • CFG-10 A MySQL socket parameter connects through a Unix socket. The client enables the found-rows count that optimistic updates rely on.
  • CFG-11 A SQLite path must be absolute. The client percent-decodes the path as UTF-8 before it opens the file: sqlite:///var/lib/my%20data.sqlite opens /var/lib/my data.sqlite, and %25, %23 and %3F give a literal %, # and ?. A % without two hexadecimal digits after it, a decoded NUL byte, and a decoded path that is not UTF-8 return CONFIG. tests/dsn/sqlite-paths.json holds the shared cases, which every client runs in its DSN test. The client enables foreign keys. A write transaction begins with BEGIN IMMEDIATE and holds the write lock of the database from its start, so two write transactions run one after the other and neither fails when it writes after a read. A transaction with the readOnly option begins with BEGIN and takes no write lock. The client selects the begin statement, and a DSN with _txlock returns CONFIG.
  • CFG-12 A SQLite connection waits up to 5000 milliseconds for a lock that another connection holds. The six configured implementations—Go, PHP, PHP extension, TypeScript, Python, and Rust—use the DSN parameter _pragma=busy_timeout(ms) to set another duration. For example, sqlite:///var/lib/orm_example.sqlite?_pragma=busy_timeout(10000); busy_timeout(0) does not wait. A lock that another connection still holds when the wait ends returns CANCELED. The transaction does not run again, because only DEADLOCK is retried. Every _pragma=name(value) parameter runs PRAGMA name = value on the connection.
  • CFG-13 A SQLite read runs while another connection has a write transaction open and returns the rows committed before it. The clients keep the journal mode of the database file and do not require WAL. In the default rollback journal, a read that starts during the commit of a write waits for the commit within busy_timeout. _pragma=journal_mode(WAL) changes the file to WAL, in which a read does not wait for a commit; the mode stays in the file.
  • CFG-14 The @polyspec/orm SQLite driver, in TypeScript, waits for a lock synchronously and stops the event loop while it waits. A TypeScript process therefore opens one Db for a SQLite file; the transactions of that Db run one after the other, and several processes share the file through the lock wait.

Database credentials and AES keys must not be committed, written to runtime files, or included in logs. Pass them through the client connection options after resolving them from the deployment secret source.

Lost connections ​

The server ends a connection when an administrator ends its session (pg_terminate_backend, KILL), when it restarts, and when the connection stays idle longer than its limit (PostgreSQL idle_session_timeout, MySQL wait_timeout); a network failure breaks it. The next statement on that connection fails, and every client reports that failure with the code CONNECTION_LOST, whatever the driver and its message: MySQL errors 2006, 2013 and 4031, PostgreSQL SQLSTATE class 08 and 57P01, 57P02 and 57P05, and the error of the driver for a connection that is no longer open (PHP pdo_pgsql reports it without a server SQLSTATE on a connection whose backend process id is 0, go-sql-driver ErrInvalidConn, driver.ErrBadConn, a broken socket in Rust, a fatal mysql2 error, a node-postgres connection that reported a connection error, a PyMySQL InterfaceError of a closed connection, and a psycopg error without a server SQLSTATE on a connection that psycopg marks closed). SQLite has no server connection.

  • CFG-15 Every client returns CONNECTION_LOST for the statement that failed so and does not send it again: the transaction or session state of the connection is gone.

DSN parameters and MySQL TLS ​

A MySQL DSN connects with TLS through one mode, which every client implements:

text
mysql://user:password@db.example:3306/orm_example?ssl-mode=VERIFY_IDENTITY&ssl-ca=/etc/orm/mysql-ca.pem
  • CFG-16 ssl-mode=VERIFY_IDENTITY with ssl-ca, the absolute path of a PEM file of the certificate authority, connects with TLS that checks the server certificate against that authority and the host of the DSN against the certificate. A certificate that another authority signed or that names another host fails the connection. The password then travels only inside TLS; the client requests no RSA public key of the server.
  • CFG-17 The host is a host name that the server certificate names, not an IP address, because the TypeScript driver checks an address host against the name localhost; every client returns CONFIG for an address with ssl-mode. The mode connects over TCP and does not accept socket.
  • CFG-18 Another value of ssl-mode, such as VERIFY_CA or REQUIRED, ssl-mode without ssl-ca, ssl-ca without ssl-mode, a relative ssl-ca path and an unreadable CA file return CONFIG in every client but Rust, whose parameters are below.
  • CFG-19 Go and Rust accept other parameters; every other client accepts only these parameters and returns CONFIG for another one: timezone, socket, ssl-mode and ssl-ca for MySQL; timezone, host and sslmode for PostgreSQL; timezone, _pragma and _txlock, which returns CONFIG itself, for SQLite.
  • CFG-20 The clients differ in the other parameters that they accept. The Go client passes another MySQL parameter to go-sql-driver/mysql, which sets a session variable or a driver option of that name, and another PostgreSQL parameter to pgx. The Rust client passes every parameter except timezone to sqlx, which also accepts other ssl-mode values such as verify_ca, in any case. A DSN that names only the parameters of this page behaves the same in every client.

Primary and replicas ​

The ORM does not route statements between servers. Reads from replicas use one connection per server, one to the primary and one to each replica, and connect gives the model or row the connection that a statement uses:

LanguageConnectionsRead through a replicaWrite through the primary
PHP$master = \Polyspec\Orm\Tests\Model\connect($primaryDsn, $config); $slave1 = \Polyspec\Orm\Tests\Model\connect($replicaDsn, $config);(new User)($slave1)->name($name)->get()$row->connect($master)->setName($new)->update()
Gomaster, err := model.Connect(primaryDSN, cfg) slave1, err := model.Connect(replicaDSN, cfg)model.User().Connect(slave1).Name(name).Get()row.Connect(master).SetName(next).Update()
Rustlet master = model::connect(&primary_dsn, n, cfg.clone()).await?; let slave1 = model::connect(&replica_dsn, n, cfg).await?;User::new().connect(&slave1).name(name).get().await?row.connect(&master).set_name(next).update(false).await?
TypeScriptconst master = await connect(primaryDsn, options); const slave1 = await connect(replicaDsn, options);new User().connect(slave1).name(name).get()row.connect(master).setName(next).update()
Pythonmaster = model.connect(primary_dsn, **options) slave1 = model.connect(replica_dsn, **options)model.User().connect(slave1).name(name).get()row.connect(master).set_name(new).update()
  • CFG-21 A model or a loaded row uses the connection it is connected to and no other. Inside a transaction of master, a model connected to slave1 runs on slave1 outside the transaction, and a model without connect runs in the transaction.
  • CFG-22 Each connection has its own pool, prepared statements, and session settings.
  • CFG-23 A replica applies the changes of the primary after the primary commits them. A read that must see a write of the same request uses the primary connection; orm does not choose the connection itself.
  • CFG-24 A replica rejects a write: PostgreSQL returns SQLSTATE 25006 and a MySQL replica with super_read_only returns error 1290. The clients return READ_ONLY with the driver message, as they do for a write in a read-only transaction and for a write to a SQLite database opened read-only.
  • CFG-25 The schema tools and utils().schema().install run on the primary, and the replicas apply the schema through replication.
  • CFG-26 SQLite is single-node only. A SQLite database is a local file named by an absolute path, and the ORM supports no SQLite replica.

make orm-test-servers starts a replica of the MySQL primary and a standby of the PostgreSQL primary. These rules apply to all six configured implementations—Go, PHP, PHP extension, TypeScript, Python, and Rust; the PHP extension's full database verification remains open. The PostgreSQL standby streams through the physical replication slot orm_replica, so the primary keeps the WAL the standby has not received, also while it is stopped.

Connection poolers ​

A connection pooler stands between the client and one database server and shares a small number of server connections among many client connections. The shared pooler rules apply to all six configured implementations—Go, PHP, PHP extension, TypeScript, Python, and Rust. Clients work through PgBouncer in transaction mode for PostgreSQL and through ProxySQL for MySQL, used as poolers only: a client connects to the pooler with that server's DSN, and the pooler routes no statement to another server. make client-pooler-check exercises the configured database cases; it does not establish that PHP extension database coverage is complete.

PgBouncer settings:

  • CFG-27 pool_mode: transaction. A server connection serves one transaction of a client, or one statement outside a transaction, at a time.

  • CFG-28 max_prepared_statements: above 0 (PgBouncer default 200). The Go client, the Rust client and the TypeScript client run statements as protocol-level named prepared statements; the PHP client sends each PostgreSQL statement with its binds as an unnamed statement; the Python client (psycopg) sends a statement unnamed and prepares it as a named statement after it has run five times on the connection. With 0, a statement prepared on one server connection is missing on the next and PgBouncer returns prepared statement … already exists or does not exist.

  • CFG-29 track_extra_parameters: statement_timeout. Needed when a connection sets statementTimeoutMs. The clients send statement_timeout as a startup parameter; PgBouncer rejects a startup parameter it does not track, and sets a tracked one on every server connection it assigns to that client connection and on no other.

  • CFG-30 Outside a transaction the clients set no PostgreSQL session state other than the time zone, which every client sends as the startup parameter TimeZone=UTC; PgBouncer tracks TimeZone by default. Transaction options (SET TRANSACTION, SET LOCAL statement_timeout), utils().setLocal (set_config(…, true)), and utils().lock (pg_advisory_xact_lock) end with their transaction.

  • CFG-31 Cancellation of a statement works through PgBouncer: PgBouncer forwards the protocol cancel request of a client connection to the server connection that runs its statement. TypeScript sends that request with the process id and secret key of its connection; PHP uses ext-pgsql to send the protocol cancel request through its PostgreSQL connection.

  • CFG-32 The polyspec-orm crate, the Rust client, does not send the startup parameter extra_float_digits; PostgreSQL 12 and later print float8 values exactly with the server default.

  • CFG-33 A violated foreign key, check or unique key is reported as FOREIGN_KEY, CONSTRAINT or DUPLICATE_KEY, with the native codes that errors.yaml lists for each database, and the statement changes no row. This is the mapping beside the 1205 rule of CFG-6.

ProxySQL runs with its default multiplexing and needs no ORM-specific setting. ProxySQL keeps the time_zone, sql_mode and max_execution_time of each client connection and applies them on every server connection it uses for it. A statement that contains a user variable, GET_LOCK, or CREATE TEMPORARY TABLE makes ProxySQL keep that client connection on one server connection until it disconnects. On MySQL, utils().setLocal uses the user variable @`orm.<key>` and utils().lock uses GET_LOCK, so a connection that uses them is no longer multiplexed; results do not change.

Applying migration plans connects to the primary directly, not through a pooler: the apply holds the session-scoped MySQL GET_LOCK or PostgreSQL advisory lock (plans).

MIT License · Go / PHP / Rust / TypeScript

polyspec · orm