PHPackages                             lucinda/sql-data-access - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. lucinda/sql-data-access

ActiveLibrary[Database & ORM](/categories/database)

lucinda/sql-data-access
=======================

API abstracting communication with SQL providers (eg: MySQL) on top of PDO inspired by Java JDBC

v4.1.6(1y ago)023.9k23MITPHPPHP ^8.1

Since Nov 7Pushed 1y ago1 watchersCompare

[ Source](https://github.com/aherne/php-sql-data-access-api)[ Packagist](https://packagist.org/packages/lucinda/sql-data-access)[ Docs](https://github.com/aherne/php-sql-data-access-api)[ RSS](/packages/lucinda-sql-data-access/feed)WikiDiscussions master Synced 1mo ago

READMEChangelogDependencies (1)Versions (43)Used By (3)

SQL Data Access API
===================

[](#sql-data-access-api)

Table of contents:

- [About](#about)
- [Configuration](#configuration)
- [Execution](#execution)
- [Installation](#installation)
- [Unit Tests](#unit-tests)
- [Examples](#examples)
- [Reference Guide](#reference-guide)

About
-----

[](#about)

This API is a ultra light weight [Data Access Layer](https://en.wikipedia.org/wiki/Data_access_layer) built on top of [PDO](https://www.php.net/manual/en/book.pdo.php) and inspired by [JDBC](https://en.wikipedia.org/wiki/Java_Database_Connectivity) in terms of architecture. As a data access layer, its purpose is to to shield complexity of working with different SQL vendors and provide a simple and elegant interface for connecting, querying and parsing query results that overcomes PDO design flaws (such as chaotic architecture and functionality).

[![diagram](https://camo.githubusercontent.com/5e73c1eacbf5f145e81deba8665a4a6d8297687f12ae1a8c26e5ec9f5076efc0/68747470733a2f2f7777772e6c7563696e64612d6672616d65776f726b2e636f6d2f73716c2d646174612d6163636573732d6170692e737667)](https://camo.githubusercontent.com/5e73c1eacbf5f145e81deba8665a4a6d8297687f12ae1a8c26e5ec9f5076efc0/68747470733a2f2f7777772e6c7563696e64612d6672616d65776f726b2e636f6d2f73716c2d646174612d6163636573732d6170692e737667)

The whole idea of working with SQL databases (vendors) is reduced to following steps:

- **[configuration](#configuration)**: setting up an XML file where SQL vendors used by your site are configured per development environment
- **[execution](#execution)**: using [Lucinda\\SQL\\Wrapper](https://github.com/aherne/php-sql-data-access-api/blob/master/src/Wrapper.php) to read above XML based on development environment, compile [Lucinda\\SQL\\DataSource](https://github.com/aherne/php-sql-data-access-api/blob/master/src/DataSource.php) object(s) storing connection information and inject them statically into [Lucinda\\SQL\\ConnectionFactory](#class-connectionfactory) classes to use in querying

API is fully PSR-4 compliant, only requiring PHP8.1+ interpreter, SimpleXML and PDO extensions. To quickly see how it works, check:

- **[installation](#installation)**: describes how to install API on your computer, in light of steps above
- **[unit tests](#unit-tests)**: API has 100% Unit Test coverage, using [UnitTest API](https://github.com/aherne/unit-testing) instead of PHPUnit for greater flexibility
- **[examples](#examples)**: shows a number of examples in how to implement CRUD queries using this API

Configuration
-------------

[](#configuration)

To configure this API you must have a XML with a **sql** tag inside:

```

		...

	...

```

Where:

- **sql**: holds global connection information for SQL servers used
    - {ENVIRONMENT}: name of development environment (to be replaced with "local", "dev", "live", etc)
        - **server**: stores connection information about a single server via attributes:
            - *name*: (optional) unique identifier. Required if multiple sql servers are used for same environment!
            - *driver*: (mandatory) PDO driver name (pdo drivers)
            - *host*: (mandatory) server host name.
            - *port*: (optional) server port. If not set, default server port is used.
            - *username*: (mandatory) user name to use in connection.
            - *password*: (mandatory) password to use in connection.
            - *schema*: (optional) default schema to use after connecting.
            - *charset*: (optional) default charset to use in queries after connecting.
            - *autocommit*: (not recommended) whether or not INSERT/UPDATE operations should be auto-committed (value can be: 0 or 1). Not supported by all vendors!
            - *persistent*: (not recommended) whether or not connections should be persisted across sections (value can be: 0 or 1). Not supported by all vendors!
            - *timeout*: (not recommended) time in seconds by which idle connection is automatically closed. Not supported by all vendors!

Example:

```

```

Execution
---------

[](#execution)

Once you have completed step above, you need to run this in order to be able to connect and query database(s) later on:

```
new Lucinda\SQL\Wrapper(simplexml_load_file(XML_FILE_NAME), DEVELOPMENT_ENVIRONMENT);
```

This will wrap each **server** tag found for current development environment into [Lucinda\\SQL\\DataSource](https://github.com/aherne/php-sql-data-access-api/blob/master/src/DataSource.php) objects and inject them statically into [Lucinda\\SQL\\ConnectionFactory](#class-connectionfactory) class.

Class above insures a single [Lucinda\\SQL\\Connection](#class-connection) is reused per server throughout session (input-output request flow) duration. To use that connection in querying, following methods are available:

- **statement**: returns a [Lucinda\\SQL\\Statement](#class-statement) object to use in creation and execution of a sql statement
- **preparedStatement**: returns a [Lucinda\\SQL\\PreparedStatement](#class-preparedstatement) object to use in creation and execution of a sql prepared statement
- **transaction**: returns a [Lucinda\\SQL\\Transaction](#class-transaction) object to use in wrapping operations with above two in transactions

Once an SQL statement was executed via *execute* methods above, users are able to process results based on [Lucinda\\SQL\\StatementResults](#class-statementresults) object returned.

Installation
------------

[](#installation)

First choose a folder where API will be installed then write this command there using console:

```
composer require lucinda/sql-data-access
```

Then create a *configuration.xml* file holding configuration settings (see [configuration](#configuration) above) and a *index.php* file (see [initialization](#initialization) above) in project root with following code:

```
require(__DIR__."/vendor/autoload.php");
new Lucinda\SQL\Wrapper(simplexml_load_file("configuration.xml"), "local");
```

Then you are able to query server, as in below example:

```
$connection = Lucinda\SQL\ConnectionFactory::getInstance("");
$users = $connection->statement("SELECT id, name FROM users")->toMap("id", "name");
```

Unit Tests
----------

[](#unit-tests)

For tests and examples, check following files/folders in API sources:

- [unit-tests.sql](https://github.com/aherne/php-sql-data-access-api/blob/master/unit-tests.xml): SQL commands you need to run ONCE on server (assuming MySQL) before unit tests execution
- [test.php](https://github.com/aherne/php-sql-data-access-api/blob/master/test.php): runs unit tests in console
- [unit-tests.xml](https://github.com/aherne/php-sql-data-access-api/blob/master/unit-tests.xml): sets up unit tests and mocks "sql" tag
- [tests](https://github.com/aherne/php-sql-data-access-api/tree/v3.0.0/tests): unit tests for classes from [src](https://github.com/aherne/php-sql-data-access-api/tree/v3.0.0/src) folder

If you desire to run [test.php](https://github.com/aherne/php-sql-data-access-api/blob/master/test.php) yourselves, import [unit-tests.sql](https://github.com/aherne/php-sql-data-access-api/blob/master/unit-tests.xml) file first!

Examples
--------

[](#examples)

### INSERT

[](#insert)

Example of processing results of an INSERT query:

```
$connection = Lucinda\SQL\ConnectionFactory::getInstance("");
$resultSet = $connection->statement("INSERT INTO users (first_name, last_name) VALUES ('John', 'Doe')");
$lastInsertID = $resultSet->getInsertId();
```

### UPDATE/DELETE

[](#updatedelete)

Example of processing results of an UPDATE/DELETE query:

```
$connection = Lucinda\SQL\ConnectionFactory::getInstance("");
$resultSet = $connection->statement("UPDATE users SET first_name='Jane' WHERE id=1");
if($resultSet->getAffectedRows()>0) {
    // update occurred
}
```

### SELECT

[](#select)

Example of getting a single value from SELECT resultset:

```
$connection = Lucinda\SQL\ConnectionFactory::getInstance("");
$firstName = $connection->statement("SELECT first_name FROM users WHERE id=1")->toValue();
```

Example of parsing SELECT resultset row by row:

```
$connection = Lucinda\SQL\ConnectionFactory::getInstance("");
$resultSet = $connection->statement("SELECT * FROM users");
while ($row = $resultSet->toRow()) {
    // process row
}
```

Example of getting all values of first column from SELECT resultset:

```
$connection = Lucinda\SQL\ConnectionFactory::getInstance("");
$ids = $connection->statement("SELECT id FROM users")->toColumn();
```

Example of getting all rows from SELECT resultset as array where value of first becomes key and value of second becomes value:

```
$connection = Lucinda\SQL\ConnectionFactory::getInstance("");
$users = $connection->statement("SELECT id, name FROM users")->toMap("id", "name");
// above is an array where id of user becomes key and name becomes value
```

Example of getting all values from SELECT resultset:

```
$connection = Lucinda\SQL\ConnectionFactory::getInstance("");
$users = $connection->statement("SELECT * FROM users")->toList();
// above is an array containing all rows, each as column-value associative array
```

Reference Guide
---------------

[](#reference-guide)

### Class Connection

[](#class-connection)

[Lucinda\\SQL\\Connection](https://github.com/aherne/php-sql-data-access-api/blob/master/src/Connection.php) can be used to execute operations on a connection.

Following methods are relevant to connection management (HANDLED BY API AUTOMATICALLY, so **to be used only in niche situations**):

MethodArgumentsReturnsDescriptionconnect[Lucinda\\SQL\\DataSource](https://github.com/aherne/php-sql-data-access-api/blob/master/src/DataSource.php)voidConnects to database server based on data source. Throws [Lucinda\\SQL\\ConnectionException](https://github.com/aherne/php-sql-data-access-api/blob/master/src/ConnectionException.php) if connection fails!disconnectvoidvoidCloses connection to database server.reconnectvoidvoidCloses then opens connection to database server based on stored data source. Throws [Lucinda\\SQL\\ConnectionException](https://github.com/aherne/php-sql-data-access-api/blob/master/src/ConnectionException.php) if connection fails!keepAlivevoidvoidRestores connection to database server in case it got closed unexpectedly. Throws [Lucinda\\SQL\\ConnectionException](https://github.com/aherne/php-sql-data-access-api/blob/master/src/ConnectionException.php) if connection fails!Following methods are relevant for querying:

MethodArgumentsReturnsDescriptionstatementvoid[Lucinda\\SQL\\Statement](https://github.com/aherne/php-sql-data-access-api/blob/master/src/Statement.php)Creates a statement to use in querying.preparedStatementvoid[Lucinda\\SQL\\PreparedStatement](https://github.com/aherne/php-sql-data-access-api/blob/master/src/PreparedStatement.php)Creates a prepared statement to use in querying.transactionvoid[Lucinda\\SQL\\Transaction](https://github.com/aherne/php-sql-data-access-api/blob/master/src/Transaction.php)Creates a transaction wrap above operations with.### Class ConnectionFactory

[](#class-connectionfactory)

[Lucinda\\SQL\\ConnectionFactory](https://github.com/aherne/php-sql-data-access-api/blob/master/src/ConnectionFactory.php) class insures single [Lucinda\\SQL\\Connection](#class-connection) per session and server name. Has following static methods:

MethodArgumentsReturnsDescriptionstatic setDataSourcestring $serverName, [Lucinda\\SQL\\DataSource](https://github.com/aherne/php-sql-data-access-api/blob/master/src/DataSource.php)voidSets data source detected beforehand per value of *name* attribute @ **server** tag. Done automatically by API!static getInstancestring $serverName[Lucinda\\SQL\\Connection](https://github.com/aherne/php-sql-data-access-api/blob/master/src/Connection.php)Connects to server based on above data source ONCE and returns connection for later querying. Throws [Lucinda\\SQL\\ConnectionException](https://github.com/aherne/php-sql-data-access-api/blob/master/src/ConnectionException.php) if connection fails!^ if your application uses a single database server per environment and *name* attribute @ server XML tag isn't set, empty string must be used as server name!

Usage example:

```
$connection = Lucinda\SQL\ConnectionFactory::getInstance("myServer");
$conection->statement()->execute("UPDATE users SET name='John' WHERE name='Jane'");
```

Please note this class closes all open connections automatically on destruction!

### Class Statement

[](#class-statement)

[Lucinda\\SQL\\Statement](https://github.com/aherne/php-sql-data-access-api/blob/master/src/Statement.php) implements normal SQL unprepared statement operations and comes with following public methods:

MethodArgumentsReturnsDescriptionquotemixed $valuevoidEscapes and quotes value against SQL injection.executestring $query[Lucinda\\SQL\\StatementResults](https://github.com/aherne/php-sql-data-access-api/blob/master/src/StatementResults.php)Executes query and returns results. Throws [Lucinda\\SQL\\StatementException](https://github.com/aherne/php-sql-data-access-api/blob/master/src/StatementException.php) if execution fails!Usage example:

```
$connection = Lucinda\SQL\ConnectionFactory::getInstance("");
$statement = $connection->statement();
$resultSet = $statement->execute("SELECT id FROM users WHERE name='".$statement->quote($name)."'");
```

Please note this class closes all open connections automatically on destruction!

### Class PreparedStatement

[](#class-preparedstatement)

[Lucinda\\SQL\\PreparedStatement](https://github.com/aherne/php-sql-data-access-api/blob/master/src/PreparedStatement.php) implements SQL prepared statement operations and comes with following public methods:

MethodArgumentsReturnsDescriptionpreparestring $queryvoidPrepares query for execution.bindstring $parameter, mixed $value, int $dataType=\\PDO::PARAM\_STRvoidBinds parameter to prepared query.executearray $boundParameters = array()[Lucinda\\SQL\\StatementResults](https://github.com/aherne/php-sql-data-access-api/blob/master/src/StatementResults.php)Executes query and returns results. Throws [Lucinda\\SQL\\StatementException](https://github.com/aherne/php-sql-data-access-api/blob/master/src/StatementException.php) if execution fails!Usage example:

```
$connection = Lucinda\SQL\ConnectionFactory::getInstance("");
$preparedStatement = $connection->preparedStatement();
$preparedStatement->prepare("SELECT id FROM users WHERE name=:name");
$preparedStatement->bind(":name", $name);
$resultSet = $preparedStatement->execute();
```

### Class Transaction

[](#class-transaction)

[Lucinda\\SQL\\Transaction](https://github.com/aherne/php-sql-data-access-api/blob/master/src/Transaction.php) can wrap *execute* methods of two classes above in transactions, in order to maintain data integrity, and thus comes with following public methods:

MethodArgumentsReturnsDescriptionbeginvoidvoidStarts a transaction.commitvoidvoidCommits transaction.rollbackvoidvoidRolls back transaction.Usage example:

```
$connection = Lucinda\SQL\ConnectionFactory::getInstance("");
$transaction = $connection->transaction();
$transaction->begin();
$connection->statement()->execute("UPDATE users SET name='John Doe' WHERE id=1");
$transaction->commit();
```

### Class StatementResults

[](#class-statementresults)

[Lucinda\\SQL\\StatementResults](https://github.com/aherne/php-sql-data-access-api/blob/master/src/StatementResults.php) encapsulates patterns of processing results of sql statement execution and comes with following public methods:

MethodArgumentsReturnsDescriptiongetInsertIdvoidintGets last insert id following INSERT statement execution.getAffectedRowsvoidintGets affected rows following UPDATE/DELETE statement execution.toValuevoidstringGets value of first column & row in resultset following SELECT statement execution.toRowvoidarrayfalsetoColumnvoidarrayGets first column in resulting rows following SELECT statement execution.toMapstring $columnKeyName, string $columnValueNamearrayGets two columns from resulting rows, where value of one becomes key and another as value, following SELECT statement execution.toListvoidarrayGets all resulting rows, each as column-value associative array, following SELECT statement execution.Usage examples of above methods can be seen below or in [unit tests](https://github.com/aherne/php-sql-data-access-api/blob/master/tests/StatementResultsTest.php)!

###  Health Score

46

—

FairBetter than 93% of packages

Maintenance46

Moderate activity, may be stable

Popularity25

Limited adoption so far

Community13

Small or concentrated contributor base

Maturity84

Battle-tested with a long release history

 Bus Factor1

Top contributor holds 100% of commits — single point of failure

How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window.

**Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores.

**Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement.

**Maturity (30%)** — Project age, version count, PHP version support, and release stability.

###  Release Activity

Cadence

Every ~65 days

Recently: every ~205 days

Total

42

Last Release

420d ago

Major Versions

v2.1.0.1 → v3.0.12020-01-14

v2.0.x-dev → v3.0.4.52020-04-17

v3.0.6 → v4.0.02021-12-28

v3.0.8 → v4.0.x-dev2022-01-09

v3.0.9 → v4.1.32022-12-25

PHP version history (3 changes)v3.0.0PHP ^7.1

v4.0.0PHP ^8.1

v3.0.8PHP ^7.1|^8.0

### Community

Maintainers

![](https://avatars.githubusercontent.com/u/3382770?v=4)[Lucian Gabriel Popescu](/maintainers/aherne)[@aherne](https://github.com/aherne)

---

Top Contributors

[![aherne](https://avatars.githubusercontent.com/u/3382770?v=4)](https://github.com/aherne "aherne (52 commits)")

---

Tags

processingsqlqueryingdata access

### Embed Badge

![Health badge](/badges/lucinda-sql-data-access/health.svg)

```
[![Health](https://phpackages.com/badges/lucinda-sql-data-access/health.svg)](https://phpackages.com/packages/lucinda-sql-data-access)
```

###  Alternatives

[doctrine/dbal

Powerful PHP database abstraction layer (DBAL) with many features for database schema introspection and management.

9.7k578.4M5.6k](/packages/doctrine-dbal)[jdorn/sql-formatter

a PHP SQL highlighting library

3.9k115.1M102](/packages/jdorn-sql-formatter)[doctrine/sql-formatter

a PHP SQL highlighting library

1.9k166.0M85](/packages/doctrine-sql-formatter)[illuminate/database

The Illuminate Database package.

2.8k52.4M9.4k](/packages/illuminate-database)[catfan/medoo

The lightweight PHP database framework to accelerate development

4.9k1.5M194](/packages/catfan-medoo)[phpmyadmin/sql-parser

A validating SQL lexer and parser with a focus on MySQL dialect.

47950.4M55](/packages/phpmyadmin-sql-parser)

PHPackages © 2026

[Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)