README.md

DuckDB API for PHP

This package provides a DuckDB Client API for PHP.

Focused on performance, it uses the official C API internally through FFI, achieving good benchmarks. This library is more than just a wrapper for the C API; it introduces custom, PHP-friendly methods to simplify working with DuckDB. It is compatible with Linux, Windows, and macOS, requiring PHP version 8.3 or higher.

Full documentation is available in https://duckdb-php.readthedocs.io/.

Automatic install (recommended for newcomers)

composer require satur.io/duckdb-auto

Quick Start

DuckDB::sql("SELECT 'quack' as my_column")->print();

-------------------
| my_column       |
-------------------
| quack           |
-------------------

It's that simple! :duck:

The function we used here, DuckDB::sql(), performs the query in a new in-memory database which is destroyed after retrieving the result.

This is not the most common use case, let's see how to get a persistent connection.

Connection

$duckDB = DuckDB::create('duck.db'); // or DuckDB::create() for in-memory database

$duckDB->query('CREATE TABLE test (i INTEGER, b BOOL, f FLOAT);');
$duckDB->query('INSERT INTO test VALUES (3, true, 1.1), (5, true, 1.2), (3, false, 1.1), (3, null, 1.2);');

$duckDB->query('SELECT * FROM test')->print();

As you probably guessed, DuckDB::create() creates a new connection to the specified database, or create a new one if it doesn't exist yet and then establishes the connection.

After that, we can use the function query to perform the requests.

In addition, the library also provides prepared statements for binding parameters to our query.

Prepared Statements

$duckDB = DuckDB::create();

$duckDB->query('CREATE TABLE test (i INTEGER, b BOOL, f FLOAT);');
$duckDB->query('INSERT INTO test VALUES (3, true, 1.1), (5, true, 1.2), (3, false, 1.1), (3, null, 1.2);');

$boolPreparedStatement = $duckDB->preparedStatement('SELECT * FROM test WHERE b = \$1');
$boolPreparedStatement->bindParam(1, true);
$result = $boolPreparedStatement->execute();
$result->print();

$intPreparedStatement = $duckDB->preparedStatement('SELECT * FROM test WHERE i = ?');
$intPreparedStatement->bindParam(1, 3);
$result = $intPreparedStatement->execute();
$result->print();

Appenders

Appenders are the preferred method to load data in DuckDB. See DuckDB docs for more information.

$duckDB = DuckDB::create();
$result = $duckDB->query('CREATE TABLE people (id INTEGER, name VARCHAR);');

$appender = $duckDB->appender('people');

for ($i = 0; $i < 100; ++$i) {
    $appender->appendRow(rand(1, 100000), 'string-'.rand(1, 100));
}

$appender->flush();

Upgrading from 1.x to 2.x

See upgrade from 1.x to 2.x docs.

DuckDB power

DuckDB provides some amazing features. For example, you can query remote files directly.

Let's use an aggregate function to calculate the average of a column for a parquet remote file:

DuckDB::sql(
    'SELECT "Reporting Year", avg("Gas Produced, MCF") as "AVG Gas Produced"
                FROM "https://github.com/plotly/datasets/raw/refs/heads/master/oil-and-gas.parquet"
                WHERE "Reporting Year" BETWEEN 1985 AND 1990
                GROUP BY "Reporting Year";'
)->print();

--------------------------------------
| Reporting Year   | AVG Gas Produce |
--------------------------------------
| 1985             | 2461.4047344111 |
| 1986             | 6060.8575605681 |
| 1987             | 5047.5813074014 |
| 1988             | 4763.4090541633 |
| 1989             | 4175.2989758837 |
| 1990             | 3706.9404742437 |
--------------------------------------

Or summarize a remote csv:

DuckDB::sql('SUMMARIZE TABLE "https://blobs.duckdb.org/data/Star_Trek-Season_1.csv";')->print();

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| column_name      | column_type      | min              | max              | approx_unique    | avg              | std              | q25              | q50              | q75              | count            | null_percentage |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| season_num       | BIGINT           | 1                | 1                | 1                | 1.0              | 0.0              | 1                | 1                | 1                | 30               | 0               |
| episode_num      | BIGINT           | 0                | 29               | 29               | 14.5             | 8.8034084308295  | 7                | 14               | 22               | 30               | 0               |
| aired_date       | DATE             | 1965-02-28       | 1967-04-13       | 35               |                  |                  | 1966-10-20       | 1966-12-22       | 1967-02-16       | 30               | 0               |
| cnt_kirk_hookup  | BIGINT           | 0                | 2                | 3                | 0.3333333333333  | 0.6064784348631  | 0                | 0                | 1                | 30               | 0               |

...

I would recommend taking a look at the DuckDB documentation to figure out all possibilities.

Requirements

• Linux, macOS, or Windows
• x64 platform
• PHP >= 8.3
• ext-ffi

Recommended

• ext-bcmath - Needed for big integers (> PHP_INT_MAX)
• ext-zend-opcache - For better performance

Type Support

From version 1.2.0 on the library supports all DuckDB file types.

DuckDB Type	SQL Type	PHP Type
DUCKDB_TYPE_BOOLEAN	BOOLEAN	bool
DUCKDB_TYPE_TINYINT	TINYINT	int
DUCKDB_TYPE_SMALLINT	SMALLINT	int
DUCKDB_TYPE_INTEGER	INTEGER	int
DUCKDB_TYPE_BIGINT	BIGINT	int
DUCKDB_TYPE_UTINYINT	UTINYINT	int
DUCKDB_TYPE_USMALLINT	USMALLINT	int
DUCKDB_TYPE_UINTEGER	UINTEGER	int
DUCKDB_TYPE_UBIGINT	UBIGINT	Saturio\DuckDB\Type\Math\LongInteger
DUCKDB_TYPE_FLOAT	FLOAT	float
DUCKDB_TYPE_DOUBLE	DOUBLE	float
DUCKDB_TYPE_TIMESTAMP	TIMESTAMP	Saturio\DuckDB\Type\Timestamp
DUCKDB_TYPE_DATE	DATE	Saturio\DuckDB\Type\Date
DUCKDB_TYPE_TIME	TIME	Saturio\DuckDB\Type\Time
DUCKDB_TYPE_INTERVAL	INTERVAL	Saturio\DuckDB\Type\Interval
DUCKDB_TYPE_HUGEINT	HUGEINT	Saturio\DuckDB\Type\Math\LongInteger
DUCKDB_TYPE_UHUGEINT	UHUGEINT	Saturio\DuckDB\Type\Math\LongInteger
DUCKDB_TYPE_VARCHAR	VARCHAR	string
DUCKDB_TYPE_BLOB	BLOB	Saturio\DuckDB\Type\Blob
DUCKDB_TYPE_TIMESTAMP_S	TIMESTAMP_S	Saturio\DuckDB\Type\Timestamp
DUCKDB_TYPE_TIMESTAMP_MS	TIMESTAMP_MS	Saturio\DuckDB\Type\Timestamp
DUCKDB_TYPE_TIMESTAMP_NS	TIMESTAMP_NS	Saturio\DuckDB\Type\Timestamp
DUCKDB_TYPE_UUID	UUID	Saturio\DuckDB\Type\UUID
DUCKDB_TYPE_TIME_TZ	TIMETZ	Saturio\DuckDB\Type\Time
DUCKDB_TYPE_TIMESTAMP_TZ	TIMESTAMPTZ	Saturio\DuckDB\Type\Timestamp
DUCKDB_TYPE_DECIMAL	DECIMAL	float
DUCKDB_TYPE_ENUM	ENUM	string
DUCKDB_TYPE_LIST	LIST	array
DUCKDB_TYPE_STRUCT	STRUCT	array
DUCKDB_TYPE_ARRAY	ARRAY	array
DUCKDB_TYPE_MAP	MAP	array
DUCKDB_TYPE_UNION	UNION	mixed
DUCKDB_TYPE_BIT	BIT	string
DUCKDB_TYPE_BIGNUM	BIGNUM	string
DUCKDB_TYPE_SQLNULL	NULL	null

Other PHP DuckDB Integrations

This project takes inspiration from thbley/php-duckdb-integration and kambo-1st/duckdb-php. Without these prior works, satur-io/duckdb-php might not exist.

However, there are some key differences:

• satur-io/duckdb-php leverages all modern C API methods, avoiding deprecated ones.
• It supports all major platforms (Linux, macOS, and Windows) and automatically selects the appropriate C library.
• Prioritizes performance.
• Simple to install and use.
• Bundles all necessary resources into a single Composer package.

Contributions Are Welcome

There are several open issues you can contribute to. Feel free to create new issues for feature requests or bug reports. Contributions of any kind are highly appreciated!

If you'd like to contribute, please follow these steps:

1. Fork the repository.
2. Create a new branch for your feature or bug fix.
3. Commit your changes with clear and concise messages.
4. Submit a pull request with a detailed description of your changes.

Thank you for helping improve this project!