Download the PHP package v-dem/queasy-db without Composer

QuEasy PHP Framework - Database

Package v-dem/queasy-db

QuEasy DB is a set of database access classes for CRUD operations. Some of the most usual queries can be built automatically (like SELECT by field value, UPDATE, INSERT and DELETE). Complex queries can be defined in database and/or tables config. The main goal is to separate SQL queries out of PHP code and provide an easy way for CRUD operations.

Features

• QuEasy DB extends PDO class, so any project which uses PDO can be seamlessly moved to use QuEasy DB.
• Simple CRUD database operations in just one PHP code row.
• Separating SQL queries from PHP code.

Requirements

• PHP version 5.3 or higher

Installation

composer require v-dem/queasy-db

It will also install v-dem/queasy-helper.

Usage

Notes

• You can use setLogger() method which accepts Psr\Log\LoggerInterface implementation to log all queries, by default Psr\Log\NullLogger is used.
• By default error mode (PDO::ATTR_ERRMODE) is set to PDO::ERRMODE_EXCEPTION (as in PHP8). If you need to use other error mode and are using Db::trans() method then be sure to manually check errorInfo() and throw an exception inside transaction.
• For PostgreSQL you may need to add option Db::ATTR_USE_RETURNING => true on initialization to make Db::id() work (it will add RETURNING "id" to each single INSERT statement).
• All table and column names in auto-generated SQL code are enclosed in double quotes (as per ANSI SQL standard) so check following notes:
• For MySQL need to set option PDO::MYSQL_ATTR_INIT_COMMAND to SET SQL_MODE = ANSI_QUOTES or run same query after initialization.
• For MS SQL Server need to run SET QUOTED_IDENTIFIER ON or SET ANSI_DEFAULTS ON query after initialization.

Initialization

Or PDO-way:

If DSN is not set then SQLite in-memory database will be used:

Retrieving records

Get all records from users table

Using foreach with users table

Get single record from users table by id key

It's possible to use select() method to pass PDO options; select() returns PDOStatement instance:

Get multiple records

Innserting records

Insert a record into users table using associative array

Insert a record into users table by fields order

Insert many records into users table using associative array (it will generate single INSERT statement)

Insert many records into users table by order

Also it's possible to use insert() method (in the same way as above) when need to pass PDO options; returns last insert id for single insert and number of inserted rows for multiple inserts:

• Second argument ($options) is optional, it will be passed to PDO::prepare()

Updating records

Update a record in users table by id key

• Third argument ($options) is optional, it will be passed to PDO::prepare()

Update multiple records

Deleting records

Delete a record in users table by id key

Delete multiple records

• Second argument ($options) is optional, it will be passed to PDO::prepare()

Other functions

Get last insert id (alias for lastInsertId() method)

Get count of all records in users table

Using transactions

• On exception transaction is rolled back and exception re-thrown to outer code.

Run custom queries (returns PDOStatement)

• Third argument ($options) is optional, it will be passed to PDO::prepare()

Run query predefined in configuration

This feature can help keep code cleaner and place SQL code outside PHP, somewhere in config files.

• Possible values for returns option are Db::RETURN_STATEMENT (default, returns PDOStatement instance), Db::RETURN_ONE, Db::RETURN_ALL (using PDOStatement::fetchAll() method), Db::RETURN_VALUE

Also it is possible to group predefined queries by tables:

Using v-dem/queasy-db together with v-dem/queasy-config and v-dem/queasy-log

config.php:

Initializing:

• All queries will be logged with Psr\Log\LogLevel::DEBUG level. Also it's possible to use any other logger class compatible with PSR-3.