README

Writing database abstraction layers is hard. Why not use one of the existing libraries?

• illuminate/database The schema-builder only supports incremental updates to a known schema. I need to update an existing schema to match a definition.
• doctrine/dbal This provides support for every feature of every database. I need to abstract away the differences between them.
• I also need something that will update the data to fit the new structure were possible, such as deleting rows that would violate a foreign key.

Philosophy

• Code is designed to be clear and straightforward.
• Only support features that are common to all databases.
• No external dependencies.
• Follow semantic versioning.
• Use ANSI standard SQL where possible. Use vendor-specific SQL only when necessary.
• Modern tooling:
  • phpcs for PSR-12
  • phpstan at max level
  • phpunit at 100% coverage
  • infection at 95%+ MSI

Supported databases and versions

• MySQL - 8.0+
• MariaDB - 10.6+ (10.5.4 should work, but is untested)
• PostgreSQL - 14+ (10 should work, but is untested)
• SQLite 3.35.0+
• SQL-Server - 2019+
• Firebird - 5.0

Identifiers

• Maximum length of 31 characters, minus the length of the optional table prefix. This restriction comes from Firebird.
• Only ASCII characters are allowed. This restriction comes from Firebird.
• Identifiers that differ only by case are not allowed. Tables Users and users may not co-exist. This restriction comes from SQLite.
• Although we always quote identifers because they may contain reserved words, there is no reason for a table called foo'"\ bar. So we only allow [-_+$# A-Za-z0-9].

Note that these restrictions only apply to schema definitions created by this library. When introspecting existing databases, we will accept any identifiers that the database allows. If you already have a table called, foo'"\ bar, then you will be able to introspect it.

Supported column types

• String (VARCHAR) up to 2000 characters, with a case/accent-insensitive, four-byte, UTF8 collation.
• Text (TEXT), with a case/accent-insensitive, four-byte, UTF8 collation.
• Four-byte signed integer (INTEGER)
• Floating point (DOUBLE PRECISION)
• Timestamp (TIMESTAMP / DATETIME); maximum available precision, no time-zone, no default value.

Supported foreign key actions

• Default is NO ACTION for SQL-Server and RESTRICT for other databases
• CASCADE
• SET NULL

Other restrictions

CASCADE and SET NULL are not available for self-relations, as SQL-Server does not support them.

Since Firebird converts all table/column names to upper-case internally, we cannot allow two tables/columns that differ only by case. If you have tables called users and Users, you will need to rename one of them.

How to use

All operations are performed through the class DB.

DB::attach($pdo, $table_prefix = '');

Prepared statement caching is enabled by default, except for Firebird. Firebird retains table metadata while prepared statements remain cached, preventing schema changes such as dropping a table. Enable/disable caching as required.

$connection = new Connection($pdo);
$cache      = new ArrayStatementCache();
$connection->setStatementCache($cache); // Enable caching
$connection->setStatementCache(null);   // Disable caching
$cache = $connection->statementCache(); // Get the current cache

Query logging is off by default. Enable/disable it as required. No logger is provided. You must implement StatementLoggerInterface to suit your application. The logger receives the SQL, the bindings and the timing of each statement.

$connection = new Connection($pdo);
$logger     = new MyStatementLogger();
$connection->setStatementLogger($logger); // Enable logging
$connection->setStatementLogger(null);    // Disable logging
$logger = $connection->statementLogger(); // Get the current logger

Prepared-statement caching benchmark

Run BENCH_DB_PASSWORD=test php bin/benchmark-prepared-statements.php against a local MySQL-compatible database named test with user test. Override the connection using BENCH_DSN and BENCH_DB_USER; pass optional iteration and round counts as arguments, for example BENCH_DB_PASSWORD=test php bin/benchmark-prepared-statements.php 2000 5.

The script creates two randomly named benchmark tables, populates 1,000 parent rows and five detail rows per parent, and drops both tables when finished. It times point lookups, five-row joins, and indexed updates via Connection, with native PDO prepares and alternating cached/uncached runs. It reports per-round prepare/cache-hit counts and median timings. Results depend on the server, connection latency, SQL workload, and result size.

Schema builder

Create an in-memory schema definition using static factory methods and fluent modifiers.

Column types

DB::integer('id')->autoIncrement()->notNull()
DB::integer('age')->default(0)->notNull()
DB::integer('score')->null()

DB::float('price')->default(0.0)->notNull()
DB::float('rating')->null()

DB::varchar('name', 100)->default('')->notNull()
DB::varchar('status', 20)->null()

DB::text('body')->notNull()
DB::text('notes')->null()

DB::datetime('created_at')->notNull()
DB::datetime('deleted_at')->null()

Keys and indexes

DB::primaryKey(['id'])

DB::unique('ux_users_email', ['email'])

DB::index('ix_users_status', ['status'])
DB::index('ix_users_name_email', ['name', 'email'])  // Composite

Foreign keys

DB::foreignKey('fk_orders_user', ['user_id'], 'users', ['id'])
DB::foreignKey('fk_orders_user', ['user_id'], 'users', ['id'])->onDeleteCascade()
DB::foreignKey('fk_orders_user', ['user_id'], 'users', ['id'])->onDeleteSetNull()
DB::foreignKey('fk_orders_user', ['user_id'], 'users', ['id'])->onUpdateCascade()
DB::foreignKey('fk_orders_user', ['user_id'], 'users', ['id'])->onUpdateSetNull()

Tables and schemas

$users = DB::table(
    name: 'users',
    columns: [
        DB::integer('id')->autoIncrement()->notNull(),
        DB::varchar('name', 100)->notNull(),
        DB::varchar('email', 200)->notNull(),
        DB::integer('age')->null(),
        DB::datetime('created_at')->notNull(),
    ],
    primary_key: DB::primaryKey(['id']),
    indexes: [
        DB::unique('ux_users_email', ['email']),
        DB::index('ix_users_name', ['name']),
    ],
    foreign_keys: [],
);

$orders = DB::table(
    name: 'orders',
    columns: [
        DB::integer('id')->autoIncrement()->notNull(),
        DB::integer('user_id')->notNull(),
        DB::float('amount')->notNull(),
    ],
    primary_key: DB::primaryKey(['id']),
    indexes: [
        DB::index('ix_orders_user', ['user_id']),
    ],
    foreign_keys: [
        DB::foreignKey('fk_orders_user', ['user_id'], 'users', ['id'])->onDeleteCascade(),
    ],
);

$schema = DB::schema([$users, $orders]);

Schema prefixing

Add a prefix to all tables/indexes in your schema definition so that you can compare it to an introspected schema that also has a prefix.

$schema = $schema->withPrefix('pfx_');

Schema introspection

$schema = DB::introspect();       // Introspect existing database schema
DB::hasTable('users');            // Check if a table exists
DB::hasColumn('users', 'email');  // Check if a column exists

Migrations

One purpose of this library is to generate a migration SQL script that will update an existing database schema to match a new schema definition.

Specifically, it is designed for remote databases, where the migration needs to proceed without error, even if there is data that conflicts with the new schema.

For example, if you add a foreign-key constraint and there are already rows in the database that violate the constraint, the conflicting values will be deleted or set to null, depending on the specified foreign-key action.

Similarly, if you add a unique index and there are already duplicate values in the database, the index will be changed to a non-unique index.

use Fisharebest\Database\Schema\MigrationMode;

// Generate migration SQL.  The migration mode must be chosen explicitly.
// Lax (strict_not_null: false) is data-aware and safe for populated databases.
$sql = DB::migrate($schema, new MigrationMode(strict_not_null: false));

// Strict enforces NOT NULL constraints, failing if existing data conflicts.
// Control orphan handling (tables/columns in DB but not in schema).
$sql = DB::migrate($schema, new MigrationMode(
    strict_not_null: true,
    drop_orphan_columns: true,
    drop_orphan_tables: true,
));

Query builder

The query builder provides a fluent, driver-agnostic interface for building and executing SQL queries. Each database engine has its own grammar class that compiles queries to the correct SQL dialect.

NOTE that we only support features that are available in all databases. If your favourite SQL construct is not listed here, then it is likely that one or more databases do not support it.

Getting started

// Create a query-builder object
$query = DB::query();

// Select, insert, update or delete from a table
$query = DB::query('table');

// Alternative syntax allows you to write a query with no tables or use a
// more natural syntax DB::query()->select(...)->from(...)->where(...)
$query = DB::query()->from('table');

// Fetching results
$query->get(); // all rows as objects
$query->cursor(); // lazy generator for all rows
$query->first(); // The first row as an object - or null
$query->pluck('value_column'); // A list of values from a single column
$query->pluck('value_column', 'key_column'); // An associative array of values
$query->count(); // The number of rows in the query

// Queries are immutable and can be re-used.
$rows  = $query->get();
$count = $query->count();

// $query->get() returns an array of stdClass objects by default.
// Using the 'as' parameter, you can return rows as your own type.
$query->get(as: DataTransferObject::class);
// Using the 'in' parameter, you can wrap the array in a collection or similar.
$query->get(in: Collection::class);
$query->pluck('value_column', in: Collection::class);
$query->pluck('value_column', 'key_column', in: Collection::class);

// If no ->select() clause is specified, ->select('*') is assumed.
DB::query('users')->select('name', 'email')->get();   // Specific columns

// Add where clauses, joins, group-by, etc. using fluent modifiers.
$query = DB::query('users')->whereNull('banned');

DB::query('users')->distinct()->get();                // Distinct rows
DB::query('users')->first();                          // First row or null
DB::query('users')->value('name');                    // Single column value
DB::query('users')->value(DB::coalesce(DB::column('nickname'), DB::value('Anonymous'))); // Expression value
DB::query('users')->pluck('name');                    // Column as list
DB::query('users')->pluck('name', 'id');              // Column keyed by another
DB::query('users')->cursor();                         // Lazy generator
DB::query('users')->exists();                         // true if rows exist
DB::query('users')->doesntExist();                    // true if no rows exist

WHERE clauses

// Comparison (AND)
->whereEqual('status', 'active')
->whereNotEqual('role', 'guest')
->whereGreaterThan('age', 18)
->whereGreaterThanOrEqual('score', 50)
->whereLessThan('price', 100)
->whereLessThanOrEqual('quantity', 10)
->whereLike('name', 'A%')
->whereNotLike('email', '%example%')
->whereILike('name', 'a%')            // Case-insensitive LIKE
->whereRegex('name', '^A')           // PostgreSQL/MySQL/MariaDB only
->whereIRegex('name', '^a')          // Case-insensitive regex

// Comparison (OR)
->orWhereEqual('role', 'admin')
->orWhereNotEqual('status', 'banned')
->orWhereGreaterThan('age', 65)
->orWhereGreaterThanOrEqual('score', 90)
->orWhereLessThan('price', 5)
->orWhereLessThanOrEqual('quantity', 0)
->orWhereLike('name', 'B%')
->orWhereNotLike('email', '%test%')
->orWhereILike('name', 'b%')
->orWhereRegex('name', '^B')
->orWhereIRegex('name', '^b')

// IN / NOT IN
->whereIn('status', ['active', 'pending'])
->whereNotIn('id', [1, 2, 3])
->whereIn(DB::coalesce(DB::column('nickname'), DB::value('Anonymous')), ['Anonymous'])
->whereInSubquery('id', $subquery)   // Single-column subquery
->whereNotInSubquery('id', $subquery)
->whereInSubquery(DB::coalesce(DB::column('manager_id'), DB::value(0)), $subquery)

// NULL checks
->whereNull('deleted_at')
->whereNotNull('email')
->whereNull(DB::nullIf('nickname', 'Anonymous'))

// BETWEEN
->whereBetween('age', 18, 65)
->whereNotBetween('price', 100, 200)
->whereBetween(DB::abs('balance'), 10, 100)

// EXISTS / NOT EXISTS
->whereExists($subquery)
->whereNotExists($subquery)

// Raw SQL
->whereRaw('"age" > ?', [18])

// Nested groups
->whereNested(fn($q) => $q->whereEqual('a', 1)->orWhereEqual('b', 2))
->orWhereNested(fn($q) => $q->whereEqual('c', 3)->whereEqual('d', 4))

Joins

->join('orders', ['orders.user_id' => 'users.id'])
->leftJoin('orders', ['orders.user_id' => 'users.id'])
->crossJoin('categories')

// Join with a literal value
use Fisharebest\Database\Query\Value;
->leftJoin('orders', ['orders.user_id' => 'users.id', 'orders.status' => new Value('active')])

GROUP BY / HAVING

->groupBy('status')
->groupBy('country', 'city')           // Multiple columns

->havingEqual('total', 100)
->havingNotEqual('count', 0)
->havingGreaterThan('total', 50)
->havingGreaterThanOrEqual('avg', 3.5)
->havingLessThan('count', 10)
->havingLessThanOrEqual('max', 999)
->havingGreaterThan(DB::count(), DB::value(5)) // Expressions on either side
->havingLike('label', 'A%')
->havingNotLike('label', '%test%')
->havingRaw('COUNT(*) > ?', [5])

// OR variants (all of the above have an orHaving* form)
->orHavingEqual('total', 100)
->orHavingGreaterThan('total', 50)
->orHavingLike('label', 'A%')
->orHavingRaw('COUNT(*) > ?', [5])

// Nested groups (to control AND/OR precedence)
->havingNested(fn($q) => $q->havingGreaterThan('total', 1000)->orHavingLessThan('total', 10))
->orHavingNested(fn($q) => $q->havingEqual('a', 1)->havingEqual('b', 2))

ORDER BY / LIMIT / OFFSET

->orderBy('name')                                       // Ascending (default)
->orderBy('created_at', \SortDirection::Descending)     // Explicit direction
->orderByDesc('updated_at')                             // Shorthand descending
->orderByRaw('FIELD("status", ?, ?, ?)', ['a', 'b', 'c'])

->limit(10)
->offset(20)
->take(10)              // Alias for limit()
->skip(20)              // Alias for offset()
->limitOne()            // Shorthand for limit(1)

SortDirection is the global PHP 8.6 enum. Composer loads a compatible polyfill on earlier PHP versions.

An offset greater than zero requires an orderBy() clause. Without an offset, a limit can be used without ordering, but the selected rows are not predictable. SQL Server uses TOP for these queries; an unordered limited UNION is not supported on SQL Server.

Aggregates

DB::query('users')->count();                  // COUNT(*)
DB::query('users')->count('email');           // COUNT(email)
DB::query('users')->countDistinct('status');  // COUNT(DISTINCT status)

INSERT / UPDATE / DELETE

// Insert
DB::query('users')->insert(['name' => 'Alice', 'email' => 'alice@example.com']);

// Batch insert
DB::query('users')->insertBatch([['name' => 'Alice'], ['name' => 'Bob']]);

// Insert rows returned by a SELECT; columns must match the SELECT order
DB::query('archived_users')->insertFrom(
    ['name', 'email'],
    DB::query('users')->select('name', 'email')->whereEqual('active', 0),
);

// Insert and get the auto-increment ID
$id = DB::query('users')->insertGetId(['name' => 'Alice'], 'id');

// Update (returns affected row count)
DB::query('users')->whereEqual('id', 1)->update(['name' => 'Updated']);

// Update with a SQL expression and a bound parameter
DB::query('users')->whereEqual('id', 1)->update([
    'age' => DB::raw(DB::wrapColumn('age') . ' + ?', [1]),
]);

// Delete (returns affected row count)
DB::query('users')->whereEqual('id', 1)->delete();

// Delete at most 100 matching rows
DB::query('users')->whereEqual('active', 0)->limit(100)->delete();

// Upsert (insert or update on conflict)
DB::query('settings')->upsert(
    ['key' => 'theme', 'value' => 'dark'],  // Values to insert
    ['key'],                                  // Conflict columns
    ['value'],                                // Columns to update on conflict
);

The rows selected by a limited delete are unpredictable; orderBy() is not applied to DELETE. SQLite's limited delete uses rowid, so it requires a rowid table (not WITHOUT ROWID).

Expression helpers

Use DB::raw() for raw SQL fragments in select, orderBy, etc.

// Raw expressions
DB::query('users')->select('name', DB::raw('COUNT(*) AS total'))->groupBy('name')->get();

// Override the usual interpretation of a string as a column or a bound value
DB::query('users')->whereEqual('users.email', DB::column('users.backup_email'));
DB::query('users')->select(DB::coalesce(DB::column('nickname'), DB::value('Anonymous')))->get();

// Give a selected expression (or column) a quoted alias
DB::query('users')->select(DB::alias(DB::substring('name', 1, 5), 'short_name'))->get();
DB::query('users')->select(DB::alias('name', 'display_name'))->get();

SQL function expressions

Static methods on DB create cross-database function expressions for use in select(), orderBy(), groupBy(), whereEqual(), etc.

// Aggregate functions
DB::avg('price')                  // AVG("price")
DB::count()                       // COUNT(*)
DB::count('email')                // COUNT("email")
DB::countDistinct('status')       // COUNT(DISTINCT "status")
DB::max('price')                  // MAX("price")
DB::min('price')                  // MIN("price")
DB::sum('amount')                 // SUM("amount")
DB::groupConcat('name', ' | ')     // Engine-specific aggregate with a custom separator
DB::listagg('name')               // LISTAGG("name")

// Comparison functions
DB::coalesce(DB::column('nickname'), DB::value('Anonymous')) // COALESCE("nickname", ?)
DB::greatest(DB::column('a'), DB::column('b'))                // GREATEST("a", "b")
DB::least(DB::column('a'), DB::column('b'))                   // LEAST("a", "b")
DB::nullIf('status', 'unknown')   // NULLIF("status", ?)

// Numeric functions
DB::abs('balance')                // ABS("balance")
DB::round('price', 2)            // ROUND("price", ?)

// String functions
DB::concat(DB::column('first'), DB::value(' '), DB::column('last')) // CONCAT("first", ?, "last")
DB::length('name')                // LENGTH("name")
DB::lower('email')                // LOWER("email")
DB::upper('name')                 // UPPER("name")
DB::trim('value')                 // TRIM("value")
DB::replace('name', 'old', 'new')      // REPLACE("name", ?, ?)
DB::substring('name', 1, 3)            // SUBSTRING("name", ?, ?)

// Example: use in a query
DB::query('users')
    ->select('id', DB::upper('name'), DB::coalesce(DB::column('nickname'), DB::column('name')))
    ->whereGreaterThan(DB::length('name'), 3)
    ->orderBy(DB::lower('name'))
    ->get();

Quoting helpers

Quote identifiers for use in raw SQL expressions.

DB::wrapColumn('name');          // e.g. "name" or `name`
DB::wrapColumn('users.name');    // e.g. "users"."name"
DB::wrapTable('users');          // Applies table prefix and quotes

Debug / inspect

$query = DB::query('users')->whereEqual('active', 1)->orderBy('name');

$query->toSql();       // The compiled SQL string
$query->getBindings(); // The bound parameter values

CTEs, unions, subqueries

// Common Table Expression
$cte = DB::query()
    ->select('user_id', DB::raw('SUM(amount) AS total'))
    ->from('orders')
    ->groupBy('user_id');
DB::withCte('totals', $cte)
    ->select('*')
    ->from('totals')
    ->get();

// Recursive CTE
DB::query('tree')
    ->withRecursiveCte(
        'tree',
        DB::query('categories')->whereNull('parent_id'), // Initial (anchor)
        DB::query('categories')                          // Recursive
            ->join('tree', ['categories.parent_id' => 'tree.id']),
    )
    ->get();

// Union / Union All
$active = DB::query('users')->whereEqual('active', 1);
$admins = DB::query('users')->whereEqual('role', 'admin');
$active->union($admins)->get();
$active->unionAll($admins)->get();

// Subquery in FROM
$sub = DB::query('orders')->select('user_id', DB::raw('SUM(amount) AS total'))->groupBy('user_id');
DB::query('sub')->fromSub($sub, 'sub')->get();

// Subquery in SELECT
DB::query('users')->selectSub(
    fn($q) => DB::query('orders')->select(DB::raw('COUNT(*)'))->whereRaw('"orders"."user_id" = "users"."id"'),
    'order_count',
)->get();

Transactions

Use forUpdate() inside a transaction to lock selected rows until the transaction commits or rolls back. Like other fluent modifiers, it returns a new query.

DB::transaction(function () {
    $user = DB::query('users')->select('*')
        ->whereEqual('id', 1)
        ->orderBy('id')
        ->forUpdate()
        ->first();

    DB::query('users')->whereEqual('id', 1)->update(['name' => 'Updated']);
});

// Transaction handling - retries on concurrency errors, rolls back on others
DB::transaction(function () {
    DB::query('users')->insert(['name' => 'Alice', 'email' => 'alice@example.com']);
});

// Not all databases support DDL statements in transactions - use this safe wrapper
DB::transactionDDL(function () {
...
});

// Not all databases allow you to insert into auto-increment columns - use this safe wrapper
DB::identityInsert('users', function () {
    DB::query('users')->insert(['id' => 1, 'name' => 'Alice']);
});

Test matrix

Docker images are used to run tests against the following databases and versions:

Continuous integration scripts

# Run phpcs, phpunit, phpstan and infection for the latest version of each database
composer ci

# Run tests for a specific version of a database
composer test-mariadb-11.8
composer test-pgsql-18
composer test-sqlsrv-2025
composer test-firebird-5
composer test-percona-8

# Run database tests for all versions of a database
composer test-mysql-all

# Run database tests for all versions of all databases
composer test-all

Test environment

Docker is used to create database containers for testing.

For macOS, here are typical steps to set up docker and the ODBC driver for SQL-Server.

brew install --cask docker
# Open the docker GUI application, and accept the terms and conditions.

# Install the Microsoft ODBC driver for SQL-Server.
brew trust --formula microsoft/mssql-release/msodbcsql18
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
HOMEBREW_ACCEPT_EULA=Y brew install msodbcsql18 unixodbc

# On macOS, brew installs openssl@v4 as the default, but SQL-Server requires v3.
brew install openssl@3