README

PSR-12 PHPStan PHPUnit Test coverage Infection

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

illuminate/database provides only updates to a known/existing schema. I need something that will update an existing schema to match a definition.
doctrine/dbal provides support for every feature of every database, not for abstracting away the differences between them.
I need something that will update the data to fit the new structure were possible, such as deleting rows that would violate a foreign key.
I want consistent behaviour for functions, such as CONCAT(...) with NULL parameters.

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, 8.4, 9.7
MariaDB - 10.6, 10.11, 11.4, 11.8, 12.2
PostgreSQL - 14, 15, 16, 17, 18
SQLite
SQL-Server - 2019, 2022, 2025
Firebird - 5

Other databases/versions may be considered if docker images are available.

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.

use Fisharebest\Database\DB;

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

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.

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 (e.g. CTEs) 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);

// A select query needs a select() call, although it doesn't need to be at the beginning.
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')->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('id', $subquery)           // Subquery

// NULL checks
->whereNull('deleted_at')
->whereNotNull('email')

// BETWEEN
->whereBetween('age', 18, 65)
->whereNotBetween('price', 100, 200)

// 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)
->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)

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 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']);

// Delete (returns affected row count)
DB::query('users')->whereEqual('id', 1)->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
);

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();

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::listagg('name')               // LISTAGG("name")

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

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

// String functions
DB::concat('first', '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('nickname', '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('orders')->select(DB::raw('user_id'), DB::raw('SUM(amount) AS total'))->groupBy('user_id');
DB::query('totals')->withCte('totals', $cte)->get();

// Recursive CTE
DB::query('tree')
    ->withRecursiveCte(
        'tree',
        DB::query('categories')->whereNull('parent_id'),           // Initial (anchor)
        fn(string $name) => DB::query('categories')                // Recursive
            ->join($name, ['categories.parent_id' => $name . '.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

// 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:

Database	Versions
MySQL	8.0, 8.4, 9.7
MariaDB	10.6, 10.11, 11.4, 11.8, 12.2
Percona	8.0
PostgreSQL	14, 15, 16, 17, 18
SQLServer	2019, 2022, 2025
Firebird	5.0
SQLite	As provided to PHP by the server (does not use docker)

The following steps may be useful to install the ODBC driver for SQLServer on OSX:

brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
HOMEBREW_NO_ENV_FILTERING=1 ACCEPT_EULA=Y brew install mssql-tools
odbcinst -j

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. After installing the PHP SQL-Server extension, you probably need to install the ODBC driver from Microsoft.

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

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

fisharebest / database

Package info

Security

Statistics