README

Prerequisites

PHP 8.3+

Installation

composer require kirameki/database

Isolation level

Kirameki will set the isolation level to SERIALIZABLE for all transactions. Lower isolation level is risky and is not worth the small gain in performance for most apps. You can change the isolation level by passing IsolationLevel to a transaction(...).

SQL Database Nuances

Numeric (Decimal) Type Handling

Database	Description
SQLite	Value is converted to INTEGER or REAL. Only the first 15 significant decimal digits of the number are preserved. You may lose precision without knowing it. (Docs)
PostgreSQL	Up to 131072 digits before the decimal point; Up to 16383 digits after the decimal point. Can be specified by user. (Docs)
MySQL	Precision can have range 1 to 65. Scale can be set from 0 to 30. Can be specified by user. (Docs)
MariaDB	Same as MySQL

In this framework, MySQL and MariaDB will use DECIMAL(65, 30) by default, while PostgreSQL will use NUMERIC without precision or scale.

Session Level Timeout

Database	Supported	Description	Query
SQLite	╳	No option exists.
PostgreSQL	◯	Works. (Docs)	`SET statement_timeout={milliseconds}`
MySQL	△	Only works for SELECT. (Docs)	`SET SESSION max_execution_time={milliseconds}`
MariaDB	◯	Works. (Docs)	`SET max_statement_time={seconds}`

Isolation Level Changes Per Transaction

Database	Supported	Description	Query
SQLite	╳	Only supports read_uncommitted per connection via `PRAGMA`.
PostgreSQL	◯	Works. Must call within the open transaction. (Docs)	`SET TRANSACTION {mode}`
MySQL	◯	Works. Must call before BEGIN. (Docs)	`SET TRANSACTION ISOLATION LEVEL {mode}`
MariaDB	◯	Same as MySQL (Docs)	Same as MySQL

Upsert

Database	Supported	Description	Query
SQLite	◯	Works. (Docs)	`INSERT INTO … ON CONFLICT … DO UPDATE SET…`
PostgreSQL	◯	Works. (Docs)	`INSERT INTO … ON CONFLICT … DO UPDATE SET…`
MySQL	△	Does not work as expected on tables with multiple unique indexes. Use with caution. Read the docs carefully. (Docs)	`INSERT INTO … ON DUPLICATE KEY UPDATE …`
MariaDB	△	Same as MySQL (Docs)	Same as MySQL

Affected Row Count

SELECT statements usually return 0 when calling QueryResult::getAffectedRowCount(), but when you run a SELECT statement using RawStatement, the method will give different results depending on the database you use. This is stated in the PHP PDO documentation.

For example, running the following statement will return different results for different databases.

Query:

SELECT 1 as a;

Database	Result
SQLite	0
MySQL	1

CURRENT_TIMESTAMP

SQLite's CURRENT_TIMESTAMP returns the time in UTC, while MySQL and PostgreSQL return the time in the server's timezone. This library uses DATETIME('now', 'localtime') for SQLite to get the time in the system timezone instead. This is still not perfect because the system timezone is not always the same as the PHP's and date_default_timezone_set() does not affect the timezone for SQLite.

SUM() function

MySQL will return the sum as DECIMAL represented as string. This is because SUM can be larger than PHP's integer limit. In SQLite the sum is returned as an integer/float and will return an error if integer overflows.

Note

On a related note, SUM will return NULL if no rows are found. This is the same for all databases.

NULL Ordering

SQLite and MySQL will treat NULL as the lowest value when ordering, while PostgreSQL will treat NULL as the highest value.

License

This is an open-sourced software licensed under the MIT License.

kirameki / database

Maintainers

Details