Download the PHP package jonbaldie/type-db without Composer

This library provides a type-safe database connector for PHP.

Inspired by the HDBC package in Haskell, this library provides a series of classes and three helper functions to help make your database interaction more deterministic and testable.

Strings, floats, integers, and nulls are automatically converted to and from SqlValue objects, giving you more type-safe precision in your queries.

• Strings are mapped to SqlString<string>
• Floats are mapped to SqlFloat<float>
• Integers are mapped to SqlInteger<int>
• Finally, nulls are mapped to SqlNull

All of these implement an SqlValue interface.

Once you see how these types can be type-hinted throughout your codebase, you bolster your codebase's testability and precision.

The connector works with any database driver compatible with the PDO abstraction layer. That means you can use it with your SQLite3, MySQL (and MariaDB), and PostgreSQL databases with ease.

Installation

The library requires PHP 8.1 as a minimum along with whichever PDO driver you need for your database. Once you have your PDO object instantiated, you're good to go!

Usage

The test files inside tests/ provide a range of example usages for you to peruse, but here is some documentation on how to use the library.

\TypeDb\Connection

This class wraps the PDO class but adds the type-safe features that makes this library useful.

Here is a simple way you can query your database:

This returns a nested list of objects of type \TypeDb\SqlValue\SqlValue. If that's confusing, the PHPDoc type is \TypeDb\SqlValue\SqlValue[][].

If it helps, $result in the above example looks like this:

It works like this even for super simple queries with no prepared parameters.

The structure of $result here will look like this:

\TypeDb\to_sql

This helper takes a value of type string|float|int|null as its sole argument and returns an object of type \TypeDb\SqlValue\SqlValue depending on which type of value you supply.

There is a class implementing \TypeDb\SqlValue\SqlValue for each of the four argument types that you can pass into the helper.

You can grab the underlying value from each class by accessing the public readonly property named $value.

If you know the types in advance and don't need to use the helper, that's fine too. Simply instantiate each of the classes as follows:

\TypeDb\from_sql

This helper takes an object of type \TypeDb\SqlValue\SqlValue and returns its underlying value. You can think of it as the inverse mapping of \TypeDb\to_sql.

\TypeDb\quick_query

This helper wraps the \TypeDb\Connection class (itself a wrapper of PDO) to perform one-off queries.

It takes three arguments: an object of type \TypeDb\Connection, your SQL query as a string, and a list of objects of type \TypeDb\SqlValue\SqlValue.

The helper returns a nested list of objects of type \TypeDb\SqlValue\SqlValue. If that's confusing, the PHPDoc type is \TypeDb\SqlValue\SqlValue[][].

This happens automatically within the helper. The return types are automatically converted into \TypeDb\SqlValue\SqlValue objects for you!

quick_query() preserves whatever column names PDO would report for the caller's select list. For explicit aliases like select 1 as id, that means string keys such as 'id'. For an unaliased SQLite scalar select like select 1, PDO reports the column name as 1, so the row shape is [[1 => new \TypeDb\SqlValue\SqlInteger(1)]]. Internal SQLite casts used to preserve float parameter types do not change those logical result names.

If the underlying PDO connection is in PDO::ERRMODE_SILENT, \TypeDb\quick_query() still fails deterministically when prepare() or execute() fails. In those cases it throws a \RuntimeException containing the PDO SQLSTATE, driver error code/message, and the SQL string, instead of returning an empty result.

SqlInteger parameters use PDO's integer binding. SqlFloat parameters are bound as their shortest round-trip decimal representation (found independently of the precision and serialize_precision ini settings), and SQLite casts that parameter expression to REAL. This preserves numeric storage classes in affinity-neutral columns and lets floats written through quick_query() read back bit-identically from numeric-affinity columns instead of being truncated to 14 significant digits by PHP's default double-to-string conversion.

Positive and negative infinity are encoded as SQLite overflow literals and round-trip as SqlFloat. NAN cannot be represented by SQLite, so binding a SqlFloat(NAN) throws an InvalidArgumentException instead of storing text.

On SQLite, named parameters must use the : prefix (e.g. :name). Although SQLite's SQL syntax also permits @- and $-prefixed parameter identifiers, PDO's SQLite driver resolves named parameter bindings exclusively via the : form and fails with column index errors on @ or $ parameters. Passing named values for @- or $-prefixed parameters throws an InvalidArgumentException before query preparation to provide an immediate, actionable error.

But why does type safety matter?

If you're not precise about the types of the objects and variables passed around in your application, then you're not clear about what your application actually does.

After many years of experience as a developer and fixing way too many errors, I learned that type precision and static analysis were the biggest game-changers for the stability of any codebase.

Thanks

I'm grateful for the handy Docker images provided by thecodingmachine. Setting up the thecodingmachine/php:8.1-v4-cli image in the CI pipeline for this library was a breeze. To enable the pdo_sqlite extension for the test suite, I only had to add one environment variable to the CircleCI config file.

Roadmap

phase 1:

• [x] connection
• [x] SqlValue interface
• [x] toSql function
• [x] unit tests
• [x] phpstan level 9
• [x] phpa

phase 2:

• [x] set up GH workflow
• [x] test write queries
• [x] documentation

phase 3:

• [x] composer package
• [x] version
• [ ] transactions
• [ ] additional class-based usage
• [ ] date types
• [ ] binary type