Supported databases

Propulsion supports five database engines. They are not equally cheap to adopt: two are effectively drop-in, one needs you to understand a couple of real behavioural differences, and one needs you to compile a PDO extension yourself. This page is the honest scoping, so you know what you’re signing up for before you commit a project to a platform.

Engines and versions

Engine	Minimum version	PDO extension	Setup effort
PostgreSQL (default, recommended)	16	pdo_pgsql	None — prebuilt everywhere
MySQL	8.0	pdo_mysql	None — prebuilt everywhere
MariaDB	10.5	pdo_mysql	None — prebuilt everywhere
SQLite	3.35	pdo_sqlite	None — bundled with PHP on most distributions
MSSQL / SQL Server	2012 (2016 for temporal tables)	pdo_dblib	Install one package
Oracle	12c (21c for native JSON)	pdo_oci	Build the extension yourself

PostgreSQL is the default and the recommended target for new projects. It is what the code generator defaults to (propulsion.database is pgsql out of the box), what the test suite and CI run against, and the platform that gets the most feature-parity attention — several features are PostgreSQL-only by design, and they are marked as such throughout this documentation.

Set a different target per project with propulsion.database in your build.php, or pass --database to the console commands. See Configuration file.

About those minimum versions

Only PostgreSQL’s floor is enforced anywhere in code; the rest are the versions the emitted SQL and DDL actually assume:

• PostgreSQL 16 is the documented floor (referenced from generator/default.php and PgsqlPlatform). Nothing in Propulsion has version-conditional behaviour below it. PostgreSQL 16, 17, and 18 are all fine.
• MySQL 8.0 is what the generated SQL assumes: common table expressions, window functions, and FOR SHARE all need it, and the native JSON column type needs 5.7.8+. VECTOR columns additionally need MySQL 9.0+.
• MariaDB 10.5 is where RETURNING support starts. Propulsion detects MariaDB at connection time from PDO::ATTR_SERVER_VERSION (DBMySQL::isMariaDb()) and gates the RETURNING-based paths on it — there is no separate MariaDB platform or adapter class, MariaDB is otherwise served by the MySQL ones. VECTOR columns need MariaDB 11.7+.
• SQLite 3.35 is where RETURNING was added (2021). Propulsion assumes it unconditionally, with no runtime probe — every currently-supported PHP version bundles a much newer SQLite than that. Upsert needs 3.24+, generated columns 3.31+, and partial/expression indexes 3.8+/3.9+, all below the same floor.
• SQL Server 2012 is where native OFFSET ... FETCH pagination and CREATE SEQUENCE arrived; Azure SQL is always current, so it needs no thought. Temporal tables are the one exception with a higher floor: SQL Server 2016+.
• Oracle 12c is where native GENERATED ... AS IDENTITY columns and OFFSET ... FETCH arrived. The native JSON column type is the one exception with a higher floor: Oracle 21c+. Below 21c, map JSON columns to CLOB with an explicit sqlType.

MariaDB is exercised against mariadb:11 and MSSQL against azure-sql-edge, but neither those nor Oracle run in Propulsion’s CI, and both have had less scrutiny overall than PostgreSQL and MySQL.

Driver setup

PostgreSQL, MySQL/MariaDB, SQLite

Install the extension (pdo_pgsql, pdo_mysql, pdo_sqlite) if your PHP build doesn’t already have it. That’s the whole story — no further configuration.

MSSQL / SQL Server

Propulsion’s MSSQL adapter assumes FreeTDS’s pdo_dblib:

# Debian/Ubuntu
apt install php-sybase

No further environment setup is needed. pdo_sqlsrv is also usable via the separate DBSQLSRV adapter, but pdo_dblib is the driver the MSSQL integration tests run over, and therefore the better-exercised of the two.

Oracle

Oracle is materially more work than the other four, and worth being upfront about: pdo_oci is not distributed as a prebuilt package for any current PHP version. You have to build it yourself against a real Oracle Instant Client.

1. Download Instant Client Basic and SDK for your architecture from Oracle’s Instant Client downloads.

2. Build the extension:

   pecl download pdo_oci
   tar xzf PDO_OCI-*.tgz && cd PDO_OCI-*
   phpize
   ./configure --with-pdo-oci=instantclient,/path/to/instantclient,<version>
   make

3. Either install the resulting modules/pdo_oci.so system-wide, or load it per-invocation:

   php -d extension=/path/to/pdo_oci.so bin/propulsion model:build

4. The Instant Client directory must be on LD_LIBRARY_PATH at runtime, not just at build time.

Ubuntu 24.04+

The libaio1 package was renamed to libaio1t64 as part of the 64-bit time_t transition, and the Instant Client still looks for the old SONAME. Install libaio1t64 and create a compatibility symlink:

ln -s libaio.so.1t64 /usr/lib/x86_64-linux-gnu/libaio.so.1

Feature support matrix

Everything below is a real SQL-dialect difference, not an unimplemented Propulsion feature. Where a capability is missing on a platform, calling the corresponding API against a connection to that platform throws a PropulsionException with a clear message rather than silently emitting SQL that does something else.

Capability	PostgreSQL	MySQL	MariaDB	SQLite	MSSQL	Oracle
FOR UPDATE	✅	✅	✅	—	table hints	✅
FOR SHARE	✅	✅	✅	—	table hints	—
NOWAIT	✅	✅	✅	—	—	✅
SKIP LOCKED	✅	✅	✅	—	READPAST	✅
Upsert	ON CONFLICT	ON DUPLICATE KEY	ON DUPLICATE KEY	ON CONFLICT	MERGE	MERGE
Bulk load	COPY	LOAD DATA	LOAD DATA	—	—	—
ID folded into INSERT	✅	—	✅	✅	✅	✅
UPDATE/DELETE ... RETURNING	✅	—	✅	✅	✅	—
Common table expressions	✅	✅	✅	✅	✅	✅
RECURSIVE keyword required	✅	✅	✅	✅	rejected	not needed
Window functions	✅	✅	✅	✅	✅	✅
Set operations	✅	✅	✅	✅	✅	✅
explain()	✅	✅	✅	✅	—	—
Real savepoints	✅	✅	✅	✅	✅	✅
Named (advisory) locks	pg_advisory_lock	GET_LOCK	GET_LOCK	—	sp_getapplock	DBMS_LOCK
JSON path extraction	✅	✅	✅	✅	✅	✅
Vector distance	all four metrics	—	L2, cosine	—	—	—
Schema migrations (sql:diff)	✅	✅	✅	—	✅	✅

The RECURSIVE keyword row is Propulsion’s problem, not yours — it emits the keyword where it’s required and omits it where it isn’t. It’s listed because a recursive CTE’s SQL looks different across platforms if you’re reading generated SQL.

Vector distance is a property of what each engine ships: pgvector gives PostgreSQL all four metrics as infix operators, MariaDB 11.7+ has Euclidean and cosine functions and no others, and community MySQL 9 has the VECTOR type but no distance function at all — DISTANCE() is a HeatWave feature. Everywhere else the column is a text emulation.

explain() isn’t implemented for MSSQL or Oracle because neither can produce a plan by rewriting a single statement: MSSQL needs SET SHOWPLAN_ALL ON toggled around the query as separate statements, and Oracle needs EXPLAIN PLAN FOR <sql> followed by a second query against PLAN_TABLE. Bulk load isn’t implemented for MSSQL because BULK INSERT requires the file to be readable by the SQL Server process itself, which a client library can’t assume shares a filesystem with it.

Identifier quoting

This matters if you write raw SQL fragments — a literal string passed to Criteria::addAsColumn(), a ColumnExpression::raw(), a hand-written migration — or if you name a column something that collides with a SQL keyword. The three behaviours are genuinely different:

• MySQL/MariaDB quote every identifier, unconditionally, with backticks. A reserved word never breaks. The flip side: raw SQL fragments you write don’t get this treatment automatically, so quote them yourself.
• Oracle quotes only identifiers that are actual Oracle reserved words, matched case-insensitively against a fixed list (DBOracle::RESERVED_WORDS at runtime, OraclePlatform::RESERVED_WORDS for DDL — the two are kept in sync). Everything else is left unquoted and case-folded to uppercase. The list includes several names that look perfectly ordinary as column names: uid, size, level, date, comment, char, check, column, current, default, decimal.
• PostgreSQL, SQLite, and MSSQL do no automatic quoting at all. A column named after a reserved word breaks outright.

Tip

Avoid reserved words in schema.xml regardless of your target platform. Portability across engines is a stated goal of the Propulsion type system, and a name that works on MySQL because MySQL quotes everything will break the day you build the same schema against PostgreSQL.

Row locking differs structurally, not just syntactically

See Locking for the API. The platform differences:

• PostgreSQL, MySQL/MariaDB, Oracle emit a real trailing FOR UPDATE / FOR SHARE clause, with NOWAIT and SKIP LOCKED variants.
• Oracle has no FOR SHARE equivalent at all — only row-exclusive FOR UPDATE. This is by design in Oracle, not a Propulsion gap; setLockForShare() against an Oracle connection throws.
• MSSQL has no trailing lock clause at all. Locking is expressed as inline table hints spliced onto every table in the FROM/JOIN clauses: WITH (UPDLOCK, ROWLOCK) for a write lock, WITH (HOLDLOCK, ROWLOCK) for a read lock, plus READPAST for SKIP LOCKED. If your code branches on “did this query get a lock clause”, note that MSSQL’s approach is structurally different — the lock is in the FROM clause, not at the end of the statement. MSSQL has no per-query NOWAIT equivalent (SET LOCK_TIMEOUT is session-level), so $noWait throws there.
• SQLite has no row-level locking — it locks the whole database file at the connection/transaction level. setLockForUpdate()/setLockForShare() both throw rather than silently emitting nothing.

Nested transactions and savepoints

All five platforms use real savepoints: a nested rollBack() undoes only its own work, and the outer transaction can still commit normally afterwards. Only the syntax differs — PostgreSQL, MySQL/MariaDB, SQLite, and Oracle use standard SAVEPOINT / ROLLBACK TO SAVEPOINT, and MSSQL uses T-SQL’s SAVE TRANSACTION / ROLLBACK TRANSACTION.

Two dialect quirks, both handled for you:

• Oracle has no RELEASE SAVEPOINT statement, and doesn’t need one — reusing a savepoint name replaces the old one, and outstanding savepoints are released by the outer commit.
• MSSQL likewise has no explicit release statement for a SAVE TRANSACTION savepoint; it’s discarded when the outer transaction commits.

See Transactions for the API.

LOB (BLOB/CLOB) columns

The contract is the same on all five platforms: the setter accepts a plain string or a stream resource, and the getter returns a stream resource for BLOB and a string for CLOB.

One Oracle-specific note, relevant only if you inspect generated SQL or store large values: BLOB writes go through a hex-encode plus HEXTORAW(...) rewrite rather than a native LOB bind, working around a pdo_oci extension bug in which PDO::PARAM_LOB silently writes an empty LOB. This is transparent in normal use, but each byte becomes two hex characters bound as a string, so there is a practical ceiling on value size. If you’re storing large file attachments in an Oracle BLOB, test with realistic sizes rather than assuming it scales.

Writing your own migration SQL

Migration SQL is written by you, per datasource — it isn’t abstracted across platforms. The dialect differences you’ll hit first:

• MSSQL and Oracle: ALTER TABLE t ADD col ... — no COLUMN keyword. ADD COLUMN is a syntax error on both. PostgreSQL, MySQL, and SQLite accept ADD COLUMN.

• Oracle has no DROP TABLE IF EXISTS. For an idempotent drop, use the guard idiom:

  BEGIN EXECUTE IMMEDIATE 'DROP TABLE my_table'; EXCEPTION WHEN OTHERS THEN NULL; END;

• Oracle aliased deletes are DELETE FROM t alias WHERE alias.col = ... — no AS, and the alias cannot appear directly after the DELETE keyword.

• MySQL aliased deletes need the alias named twice: DELETE alias FROM t AS alias WHERE ..., unlike PostgreSQL’s DELETE FROM t AS alias.

• SQLite does not support sql:diff at all — SqlitePlatform::supportsMigrations() returns false, and the diff path skips SQLite datasources with a logged message. Rebuild the database from the schema instead.

One Oracle-specific interaction with the migration ledger table: if you name your own via --migration-table / PropulsionMigrationManager::setMigrationTable(), and the name is long, Oracle’s 30-character identifier limit means the sequence Propulsion creates for it is truncated and uniquely suffixed rather than being literally {table}_SEQ. Propulsion handles this itself, but don’t write your own cleanup scripts or catalog queries that assume the literal name — or just keep custom migration table names short under Oracle.

See Schema migrations.

Auto-increment primary keys with explicit values

If you insert or update an explicit primary-key value into an auto-increment column (via allowPkInsert), SQL Server needs the statement bracketed with SET IDENTITY_INSERT tbl ON / OFF. Propulsion does this for you — DBAdapter::supportsInsertNullPk() and getIdentityInsertOnSql() handle it in both doInsert() and doUpsert(), and nothing in your own code changes. It’s noted here only because a reader inspecting generated SQL will see those statements appear on MSSQL and nowhere else, and might reasonably wonder why.

One consequence worth knowing on MSSQL specifically: an explicit primary-key value permanently advances SQL Server’s internal identity counter, even after the row is deleted. If you insert rows with high literal ids (fixtures, tests, imports), later auto-increment inserts on that table will continue from there.

MSSQL: session options in generated DDL

Generated DDL for MSSQL opens with seven SET statements:

SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;

SQL Server requires all seven to be in exactly this state in the session that creates them for several object types, computed columns among them (also indexed views, filtered indexes, and indexes on computed columns) — it rejects the CREATE/ALTER outright otherwise. SSMS and pdo_sqlsrv default all seven correctly, but FreeTDS’s pdo_dblib does not, and bin/propulsion sql:exec opens a plain PDO connection with no MSSQL-specific session setup. Emitting them at the top of the file covers both cases, since each is session-scoped and persists for the rest of that connection’s statements.

You don’t need to do anything about this; it’s documented because a generated DDL file for MSSQL looks different from every other platform’s, and this is why. They’re only emitted when the database actually has tables to emit DDL for, so an all-skipSql schema still produces an empty file.

Picking a platform

Ranked by how much you need to know or do beyond setting one config key:

1. PostgreSQL — the default. Best feature coverage, most tested, and the target for every PostgreSQL-only feature in the schema reference.
2. MySQL/MariaDB and SQLite — close to drop-in. SQLite has no row-level locking and no sql:diff support; MySQL’s upsert reports affected rows as 2 for an updated row (a documented MySQL quirk) and can’t express “do nothing on conflict”.
3. MSSQL — understand the locking differences above: table hints rather than a trailing lock clause, and no per-query NOWAIT.
4. Oracle — the most setup work (a hand-built pdo_oci) and the most dialect differences to keep in mind: identifier quoting against a reserved-word list, HEXTORAW LOB handling, no FOR SHARE, no explain(), no UPDATE/DELETE ... RETURNING, and its own DDL syntax quirks.