README

DuckDB is an embedded SQL database designed for high-performance analytics (OLAP).

pdo_duckdb is a native DuckDB database driver for the PHP Data Objects (PDO) interface.
As a native PHP extension, it is implemented in C/C++ and does not require PHP FFI or preloading.
Compared to FFI, pdo_duckdb offers much more performance and lower latency by processing query results and type conversions in C/C++. It is also thread safe and fully tested with FrankenPHP (PHP-ZTS) and Swoole.
The release packages contain pre-compiled binaries for all supported platforms and DuckDB is directly included. DuckDB extensions work the same way as they do in DuckDB CLI.

This extension supports all DuckDB types: Text, Numeric, Date, Time, Interval, JSON, Array, Struct, Map, List, Tuple, Type, Enum, Variant, Geometry, Union, Bitstring, Blob, and Boolean.

Supported PHP versions (nts & zts): 8.2, 8.3, 8.4, 8.5, 8.6

Supported operating systems: Ubuntu 22+, Debian 12+, Fedora 42+, openSUSE 16+, AmazonLinux, Arch, Alma, Rocky, Alpine, Wolfi OS, Windows Server 2022/2025, macOS 15+

Supported CPU architectures: x86_64/amd64 for Linux and Windows, aarch64/arm64 for Linux and MacOS

Supported SAPIs: php-cli, php-fpm, mod_php, FrankenPHP, TrueAsync, Swoole

Support end: macOS 14 (August 2026), PHP 8.2 (January 2027), Ubuntu 22.04 (April 2027), Debian 12 (April 2027)

DuckDB 2.0

DuckDB 2.0 brings many new features:

JSON mutation functions, variant functions, nearest joins, group by UNNEST, triggers, $variable shorthand, async IO, explain (sql), deferred constraints and many more

For a complete list, see What's New in the Docs for 2.0.

More information about migrating to DuckDB 2.0.

2.x backward compatibility settings:

SET error_on_division_by_zero = false;
SET default_transaction_invalidation_policy = 'SYNTACTIC_ERRORS_DO_NOT_INVALIDATE';

pdo_duckdb sets async_threads by default to 0 to avoid resource shortage on many concurrent requests, see Security.

Do I need to update to DuckDB 2.0 immediately?

DuckDB 1.5.x will be supported until 2027-03-01. More about the release calendar.

Install and setup with 🥧 PIE

# DuckDB 1.x
pie install thomas-0816/pdo-duckdb-php:^1
# DuckDB 2.x
pie install thomas-0816/pdo-duckdb-php:^2-beta

Install and load on demand with 🥧 PIE

# DuckDB 1.x
pie install --skip-enable-extension thomas-0816/pdo-duckdb-php:^1
# DuckDB 2.x
pie install --skip-enable-extension thomas-0816/pdo-duckdb-php:^2-beta

php -d extension=pdo_duckdb some_script.php

Install and setup with 🧟 FrankenPHP (Debian/Ubuntu)

sudo curl -s https://pkg.henderkes.com/api/packages/85/debian/repository.key -o /etc/apt/keyrings/static-php85.asc
echo "deb [signed-by=/etc/apt/keyrings/static-php85.asc] https://pkg.henderkes.com/api/packages/85/debian php-zts main" | \
    sudo tee -a /etc/apt/sources.list.d/static-php85.list
sudo apt-get update
sudo apt-get install php-zts-cli php-zts-pdo frankenphp pie-zts
sudo pie-zts install thomas-0816/pdo-duckdb-php

# test
frankenphp php-cli -r 'print_r((new PDO("duckdb::memory:"))->query("SELECT 42 as n")->fetch(PDO::FETCH_ASSOC));'

Install and setup with Docker

FROM php:8.5-cli
RUN <<EOF
    apt-get -y update && apt-get -y --no-install-recommends install unzip
    curl -fsSL -o /tmp/pie https://github.com/php/pie/releases/latest/download/pie.phar
    php /tmp/pie install --no-build-tools-check -v thomas-0816/pdo-duckdb-php
    php -r 'print_r((new PDO("duckdb::memory:"))->query("SELECT 42 as n")->fetch(PDO::FETCH_ASSOC));'
EOF

Usage examples

$duckDb = new PDO('duckdb::memory:', null, null, [PDO::DUCKDB_ATTR_CONFIG => ['TimeZone' => 'Europe/Berlin']]);
$duckDb->exec('CREATE TABLE table1 (id INTEGER, amount DECIMAL(10, 2), description VARCHAR)');

$statement = $duckDb->prepare('INSERT INTO table1 VALUES (?, ?, ?)');
$statement->execute([1, 42.21, 'Hello DuckDB! 🐘 💓 🦆']);

$statement = $duckDb->query('SELECT * FROM table1');
print_r($statement->fetchAll(PDO::FETCH_ASSOC));

# Array
#     [0] => Array
#         [id] => 1
#         [amount] => 42.21
#         [description] => Hello DuckDB! 🐘 💓 🦆

Open databases from disk or in-memory

$db = new PDO('duckdb::memory:'); // open in-memory database

$db = new PDO('duckdb:/tmp/test.db'); // open database file from disk

// open database file as read-only
$db = new PDO('duckdb:/tmp/test.db', null, null, [
    PDO::DUCKDB_ATTR_CONFIG => ['access_mode' => 'read_only']
]);

Read and write Parquet files

$db = new PDO('duckdb::memory:');
$db->exec("CREATE TABLE table1 (id INTEGER, text VARCHAR USING COMPRESSION zstd, data JSON)");

$statement = $db->prepare('INSERT INTO table1 VALUES (?, ?, ?)');
$statement->execute([1, 'Hello DuckDB 🦆', ['foo' => 'bar', 'baz' => 42]]);

$db->exec("COPY (SELECT * FROM table1) TO '/tmp/table1.parquet' (COMPRESSION zstd)");

foreach ($db->query("SELECT * FROM '/tmp/table1.parquet'", PDO::FETCH_ASSOC) as $row) {
    print_r($row);
}

# Array
#     [id] => 1
#     [text] => Hello DuckDB 🦆
#     [data] => Array
#         [foo] => bar
#         [baz] => 42

Apache Parquet: very fast and efficient column based storage file format containing one table of data.
Each column is split into several column groups. Depending on the query, the file can be read partially by certain columns groups.
Different compression or dictionary algorithms can be applied to each column. Also supports encryption.

Read CSV files with SQL

$list = [
    ['aaa', 'bbb', 'ccc'],
    ['123', '456', '789'],
    ['aaa', 'bbb', 'ccc']
];
$fp = fopen('/tmp/test.csv', 'w');
foreach ($list as $fields) {
    fputcsv($fp, $fields, ',', '"', "");
}
fclose($fp);

$db = new PDO('duckdb::memory:');
$statement = $db->query("SELECT * FROM '/tmp/test.csv'");
print_r($statement->fetchAll(PDO::FETCH_ASSOC));

# Array
#     [0] => Array
#         [aaa] => 123
#         [bbb] => 456
#         [ccc] => 789
#     [1] => Array
#         [aaa] => aaa
#         [bbb] => bbb
#         [ccc] => ccc

CSV data import

$list = [
    ['aaa', 'bbb'],
    ['123', '456'],
    ['aaa', 'bbb']
];
$fp = fopen('/tmp/test.csv', 'w');
foreach ($list as $fields) {
    fputcsv($fp, $fields, ',', '"', "");
}
fclose($fp);

$db = new PDO('duckdb::memory:');
$db->exec("CREATE TABLE test_csv AS SELECT * FROM '/tmp/test.csv'"); // schema + data import
$db->exec("INSERT INTO test_csv SELECT * FROM '/tmp/test.csv'"); // only import data

print_r($db->query('SHOW test_csv')->fetchAll(PDO::FETCH_ASSOC));

# Array
#     [0] => Array
#         [column_name] => aaa
#         [column_type] => VARCHAR
#         [null] => YES
#     [1] => Array
#         [column_name] => bbb
#         [column_type] => VARCHAR
#         [null] => YES

Read JSON files with SQL

file_put_contents('/tmp/logs.json', json_encode(['log' => 'log text']) . PHP_EOL, FILE_APPEND);
file_put_contents('/tmp/logs.json', json_encode(['log' => 'log text 2']) . PHP_EOL, FILE_APPEND);

$db = new PDO('duckdb::memory:');
$statement = $db->query("SELECT * FROM '/tmp/logs.json'");
print_r($statement->fetchAll(PDO::FETCH_ASSOC));

# Array
#     [0] => Array
#         [log] => log text
#     [1] => Array
#         [log] => log text 2

$db->exec("COPY (SELECT * FROM '/tmp/logs.json') TO '/tmp/logs_json.parquet' (COMPRESSION zstd)");

Read and write Excel files

$db = new PDO('duckdb::memory:');
$db->exec('INSTALL excel; LOAD excel');
$db->exec('CREATE TABLE table1 (id INTEGER, text VARCHAR, amount DECIMAL(10, 2))');

$statement = $db->prepare('INSERT INTO table1 VALUES (?, ?, ?)');
$statement->execute([1, 'Hello Excel 🦆', 42.21]);

$db->exec("COPY (SELECT * FROM table1) TO '/tmp/table1.xlsx'");

$statement = $db->query("SELECT * FROM '/tmp/table1.xlsx'");
print_r($statement->fetch(PDO::FETCH_ASSOC));

# Array
#     [A1] => 1
#     [B1] => Hello Excel 🦆
#     [C1] => 42.21

Use structured columns with a fixed schema

// s is array{v: string, i: int, a: string[], d: float}

$db = new PDO('duckdb::memory:');
$db->exec('CREATE TABLE table1 (s STRUCT(v VARCHAR, i INTEGER, a VARCHAR[], d DECIMAL))');

$statement = $db->prepare('INSERT INTO table1 VALUES (?)');
$statement->execute([['v' => 'foo', 'i' => 21, 'a' => ['b', 'c'], 'd' => 42.21]]);

$statement = $db->query('SELECT * FROM table1');
print_r($statement->fetch(PDO::FETCH_ASSOC));

# Array
#     [s] => Array
#         [v] => foo
#         [i] => 21
#         [a] => Array
#             [0] => b
#             [1] => c
#         [d] => 42.21

Vector Similarity Search (HNSW)

$db = new PDO('duckdb::memory:');
$db->exec('INSTALL vss; LOAD vss');

$db->exec('CREATE TABLE table1 (id int primary key, embeddings float[3])');
$db->exec("CREATE INDEX table1_hnsw ON table1 USING HNSW (embeddings) WITH (metric = 'cosine')");

$db->exec('INSERT INTO table1 VALUES (1, [1, 2, 3])');
$db->exec('INSERT INTO table1 VALUES (2, [4, 5, 6])');

$statement = $db->query('
    SELECT id, array_cosine_distance(embeddings, [1.1, 2.1, 3.1]::FLOAT[3]) as distance
    FROM table1 WHERE distance <= 0.01 ORDER BY distance
');
print_r($statement->fetch(PDO::FETCH_ASSOC));

# Array
#     [id] => 1
#     [distance] => 0.0001407862

Views

$db = new PDO('duckdb::memory:');
$db->exec('CREATE TABLE table1 (id INTEGER, text VARCHAR, amount DECIMAL(10, 2))');
$db->exec("INSERT INTO table1 VALUES (1, 'foo', 42.21)");
$db->exec('CREATE VIEW view1 as SELECT * FROM table1');

$statement = $db->query('SELECT * FROM view1');
print_r($statement->fetchAll(PDO::FETCH_ASSOC));

Transactions

$list = [
    ['aaa', 'bbb', 'ccc'],
    ['123', '456', '789'],
    ['ddd', 'eee', 'fff'],
];
$fp = fopen('/tmp/test.csv', 'w');
foreach ($list as $fields) {
    fputcsv($fp, $fields, ',', '"', "");
}
fclose($fp);

$db = new PDO('duckdb::memory:');
$db->beginTransaction();
$db->exec("CREATE TABLE test_csv AS SELECT * FROM '/tmp/test.csv'");
$db->exec("INSERT INTO test_csv SELECT * FROM '/tmp/test.csv'");
$db->commit();

Cast array columns to JSON-string

$db = new PDO('duckdb::memory:');
$db->exec('CREATE TABLE table1 (v VARCHAR[])');
$db->exec("INSERT INTO table1 VALUES (['a', 'b'])");

$statement = $db->query('SELECT v FROM table1');
print_r($statement->fetch(PDO::FETCH_ASSOC));

# Array
#     [v] => Array
#         [0] => a
#         [1] => b

$statement = $db->query('SELECT v::json::varchar as v FROM table1');
print_r($statement->fetch(PDO::FETCH_ASSOC));

# Array
#     [v] => ["a","b"]

Auto increment columns

$db = new PDO('duckdb::memory:');
$db->exec('CREATE SEQUENCE table1_id');
$db->exec("CREATE TABLE table1 (id INTEGER PRIMARY KEY DEFAULT nextval('table1_id'))");
$statement = $db->query('INSERT INTO table1 VALUES (default) RETURNING *');
print_r($statement->fetch(PDO::FETCH_ASSOC));

# Array
#     [id] => 1

Differences to MySQL / MariaDB

$db = new PDO('duckdb::memory:');
$statement = $db->query("SELECT
    0/0, 1/0, -1/0,
    nullif(0/0, 'NAN'), nullif(1/0, 'INF'), nullif(-1/0, '-INF')");
var_export($statement->fetch(PDO::FETCH_NUM));

# array (
#     0 => NAN, // MySQL,MariaDB: NULL
#     1 => INF, // MySQL,MariaDB: NULL
#     2 => -INF, // MySQL,MariaDB: NULL
#     3 => NULL,
#     4 => NULL,
#     5 => NULL,
# )

Copy data from MySQL or MariaDB to Parquet

Start MariaDB container, create and fill "orders" table:

docker run --rm -p 3306:3306 -e MARIADB_ROOT_PASSWORD=secret -e MARIADB_DATABASE=testdb mariadb:12
mysql -h 127.0.0.1 -u root -psecret testdb -e "
    CREATE TABLE orders (id integer primary key, customer integer, origin varchar(255));
    INSERT INTO orders VALUES (1, 42, 'shop');
    INSERT INTO orders VALUES (2, 21, 'offline');
"

Use DuckDB MySQL extension to copy "orders" table from MariaDB to a parquet file:

$db = new PDO('duckdb::memory:');
$db->exec('INSTALL mysql');
$db->exec("ATTACH 'host=127.0.0.1 port=3306 user=root password=secret database=testdb' AS testdb (TYPE mysql)");
$db->exec("COPY (select * from testdb.orders) TO '/tmp/orders.parquet' (FORMAT parquet)");

$rows = $db->query("SELECT * from '/tmp/orders.parquet'")->fetchAll(PDO::FETCH_ASSOC);
print_r($rows);

# Array
#     [0] => Array
#         [id] => 1
#         [customer] => 42
#         [origin] => shop
#     [1] => Array
#         [id] => 2
#         [customer] => 21
#         [origin] => offline

Copy data from PostgreSQL to Parquet

Start PostgreSQL container, create and fill "orders" table:

docker run --rm -p 5432:5432 -e POSTGRES_PASSWORD=secret postgres:18
PGPASSWORD=secret psql -h 127.0.0.1 -U postgres -c "
    CREATE TABLE orders (id integer primary key, customer integer, origin varchar(255));
    INSERT INTO orders VALUES (1, 42, 'shop');
    INSERT INTO orders VALUES (2, 21, 'offline');
"

Use DuckDB PostgreSQL extension to copy "orders" table from PostgreSQL to a parquet file:

$db = new PDO('duckdb::memory:');
$db->exec('INSTALL postgres');
$db->exec("ATTACH 'host=127.0.0.1 port=5432 user=postgres password=secret' AS testdb (TYPE postgres)");
$db->exec("COPY (select * from testdb.orders) TO '/tmp/orders.parquet' (FORMAT parquet)");

$rows = $db->query("SELECT * from '/tmp/orders.parquet'")->fetchAll(PDO::FETCH_ASSOC);
print_r($rows);

# Array
#     [0] => Array
#         [id] => 1
#         [customer] => 42
#         [origin] => shop
#     [1] => Array
#         [id] => 2
#         [customer] => 21
#         [origin] => offline

Bulk data insertion

Inserting many rows one by one is slow, use a single query to perform bulk data insertion (1M rows in <1s).

$data = [];
for ($i = 0; $i < 1_000_000; $i++) {
    $data[] = [$i, 'foo' . $i];
}

$db = new PDO('duckdb::memory:');
$db->exec('CREATE TABLE t1 (i1 integer, v1 varchar)');
$db->prepare("INSERT INTO t1 SELECT value->>0, value->>1 FROM json_each(?)")->execute([$data]);
echo $db->query('SELECT count(*) from t1')->fetchColumn(); // 1000000

$data = [];
for ($i = 0; $i < 1_000_000; $i++) {
    $data[] = ['i1' => $i, 'v1' => 'foo' . $i];
}

$db = new PDO('duckdb::memory:');
$db->exec('CREATE TABLE t1 (i1 integer, v1 varchar)');
$db->prepare("INSERT INTO t1 SELECT value->>'i1', value->>'v1' FROM json_each(?)")->execute([$data]);
echo $db->query('SELECT count(*) from t1')->fetchColumn(); // 1000000

Read public data using HTTPs, JSON, CSV and Parquet

Query weather data:

$db = new PDO('duckdb::memory:');

$url = 'https://bulk.meteostat.net/v2/stations/lite.json.gz';
$rows = $db->query("select id, name.en from read_json('{$url}') WHERE name.en like '%Berlin%' limit 2");
echo json_encode($rows->fetchAll(PDO::FETCH_ASSOC)), PHP_EOL;

$url = 'https://data.meteostat.net/hourly/2026/10381.csv.gz';
$rows = $db->query("select hour, temp from read_csv('{$url}') where year = 2026 and month = 7 and day = 25 and hour > 9 limit 4");
echo json_encode($rows->fetchAll(PDO::FETCH_ASSOC)), PHP_EOL;

# [{"id":"10381","en":"Berlin \/ Dahlem"},{"id":"10382","en":"Berlin \/ Tegel"}]
# [{"hour":10,"temp":24.1},{"hour":11,"temp":25.5},{"hour":12,"temp":26.4},{"hour":13,"temp":27.4}]

Download and query historical data from Deutsche Bahn:

# wget https://huggingface.co/datasets/piebro/deutsche-bahn-data/resolve/main/monthly_processed_data/data-2026-07.parquet

$db = new PDO('duckdb::memory:');
$rows = $db->query("
    SELECT train_type, train_number, round(avg(delay_in_min)) as delay_avg, count(*) as count
    FROM 'data-2026-07.parquet' WHERE train_type = 'ICE'
    GROUP BY train_number, train_type
    ORDER BY delay_avg DESC
    LIMIT 10
");
print_r(array_map('json_encode', $rows->fetchAll(PDO::FETCH_ASSOC)));

$rows = $db->query("
    SELECT train_number, station_name, delay_in_min, hour(time) as hour, departure_is_canceled
    FROM 'data-2026-07.parquet'
    WHERE train_number = 647 AND time::date = '2026-07-11'
");
print_r(array_map('json_encode', $rows->fetchAll(PDO::FETCH_ASSOC)));

# Array
#     {"train_type":"ICE","train_number":"647","delay_avg":70,"count":35}
#     {"train_type":"ICE","train_number":"1541","delay_avg":66,"count":198}
#     {"train_type":"ICE","train_number":"79152","delay_avg":44,"count":2}
#     {"train_type":"ICE","train_number":"2587","delay_avg":44,"count":72}
#     {"train_type":"ICE","train_number":"2214","delay_avg":43,"count":159}
#     {"train_type":"ICE","train_number":"953","delay_avg":42,"count":79}
#     {"train_type":"ICE","train_number":"2311","delay_avg":42,"count":289}
#     {"train_type":"ICE","train_number":"859","delay_avg":41,"count":80}
#     {"train_type":"ICE","train_number":"526","delay_avg":41,"count":337}
#     {"train_type":"ICE","train_number":"2512","delay_avg":39,"count":28}
# Array
#     {"train_number":"647","station_name":"Dortmund Hbf","delay_in_min":82,"hour":0,"departure_is_canceled":false}
#     {"train_number":"647","station_name":"Hamm (Westf) Hbf","delay_in_min":120,"hour":1,"departure_is_canceled":false}
#     {"train_number":"647","station_name":"Bielefeld Hbf","delay_in_min":120,"hour":1,"departure_is_canceled":false}
#     {"train_number":"647","station_name":"Minden (Westf)","delay_in_min":123,"hour":2,"departure_is_canceled":false}
#     {"train_number":"647","station_name":"Hannover Hbf","delay_in_min":138,"hour":2,"departure_is_canceled":false}
#     {"train_number":"647","station_name":"Wolfsburg Hbf","delay_in_min":135,"hour":3,"departure_is_canceled":false}
#     {"train_number":"647","station_name":"Berlin Hauptbahnhof","delay_in_min":121,"hour":4,"departure_is_canceled":true}
#     {"train_number":"647","station_name":"Berlin S\u00fcdkreuz","delay_in_min":120,"hour":4,"departure_is_canceled":false}
#     {"train_number":"647","station_name":"Berlin-Spandau","delay_in_min":146,"hour":4,"departure_is_canceled":false}

Read private data using REST APIs

$url = 'https://httpbin.org/headers';

$db = new PDO('duckdb::memory:');

$db->exec("CREATE SECRET http_auth (TYPE http, SCOPE '{$url}', BEARER_TOKEN 'some secret')");
# $db->exec("CREATE SECRET http_auth IN connection (TYPE http, SCOPE '{$url}', BEARER_TOKEN 'some secret')"); // DuckDB 2.x

$rows = $db->query("SELECT * FROM read_json('{$url}')");
print_r($rows->fetchAll(PDO::FETCH_ASSOC));

# Array
#     Array
#         [headers] => Array
#             [Accept] => */*
#             [Authorization] => Bearer some secret
#             [Host] => httpbin.org
#             [User-Agent] => duckdb/v1.5.6(linux_amd64) capi
#             [X-Amzn-Trace-Id] => Root=1-6aab0271-2c027dfa07bc6def14ce4e72

See the documentation for managing secrets and read_json().

Read data from S3, write data to S3

Start RustFS container, create new "my-bucket" bucket:

docker run --rm -p 9000:9000 rustfs/rustfs --access-key "access-key" --secret-key "secret-key"
docker run --network host --entrypoint sh rustfs/rc -c "
  rc alias set local http://127.0.0.1:9000 'access-key' 'secret-key'
  rc bucket create local/my-bucket"

Use DuckDB httpfs extension to read and write data from/to S3:

$db = new PDO('duckdb::memory:');
// set credentials
$db->exec("CREATE SECRET rustfs (TYPE s3, ENDPOINT '127.0.0.1:9000', USE_SSL false,
    URL_STYLE 'path', KEY_ID 'access-key', SECRET 'secret-key')");
// $db->exec("CREATE SECRET rustfs IN connection (TYPE s3, ENDPOINT '127.0.0.1:9000', USE_SSL false,
//     URL_STYLE 'path', KEY_ID 'access-key', SECRET 'secret-key')"); // DuckDB 2.x
// write data to S3
$db->exec("COPY ( select 'bar' as foo ) TO 's3://my-bucket/numbers.parquet' (FORMAT parquet)");
// read data from S3
$row = $db->query("SELECT * FROM 's3://my-bucket/numbers.parquet'");
print_r($row->fetch(PDO::FETCH_ASSOC));

# Array
#     [foo] => bar

Client-server mode (Quack Remote Protocol)

Start the DuckDB server:

duckdb database.duckdb --cmd "CALL quack_serve('quack:127.0.0.1:9494', token='secret');"

Client connect:

$client = new PDO('duckdb::memory:', null, null, [
    PDO::DUCKDB_ATTR_INIT_COMMAND =>
        "ATTACH 'quack:127.0.0.1:9494' AS remote (TOKEN 'secret'); USE remote;"
]);
$client->exec('CREATE TABLE IF NOT EXISTS table1 (v VARCHAR, v2 VARCHAR)');
$client->exec("INSERT INTO table1 VALUES ('foo', 'bar')");

$statement = $client->query('SELECT * FROM table1');
print_r($statement->fetchAll(PDO::FETCH_ASSOC));

For more information about the Quack Remote Protocol, see the documentation.

Community extensions

open_prompt integrates LLMs into your SQL queries:

# ./llama-server -hf JetBrains/Mellum2-12B-A2.5B-Thinking-GGUF-Q4_K_M --parallel 1 --ctx-size 16384 --temp 0.6 --top-k 20 --reasoning off

$db = new PDO('duckdb::memory:');
$db->exec('INSTALL open_prompt FROM community');
$db->exec('LOAD open_prompt');
$db->exec("SET VARIABLE openprompt_api_url = 'http://127.0.0.1:8080/v1/chat/completions'");
$db->exec('create table customers (id integer primary key, name varchar, birth_date date)');
$result = $db->query("
    SELECT open_prompt('write duckdb sql, no markdown, find customers older than 30, schema: ' || group_concat(sql))
    FROM duckdb_tables()")->fetch(PDO::FETCH_COLUMN);
echo $result, PHP_EOL;

# SELECT * FROM customers WHERE age(birth_date) > 30;

More extensions: List of Core Extensions, List of Community Extensions

Note: Community extensions are third party projects, NOT maintained or reviewed by the DuckDB team.

Performance

DuckDB is extremely fast when it comes to analytic queries.
Here is an example with 10M rows, performing in 170ms on 4 threads with 128M ram:

.timer on
/* generate 10M rows with random data */
COPY (
    SELECT i,
        (random()*1_000)::decimal(11,2) as d1,
        (random()*1_000)::int as i1,
        to_hex((random()*100000)::int) as h1,
        to_timestamp((i+1_0000_000) * random() * 100)::timestamp as created
    FROM generate_series(10_000_000) s(i)
) TO '/tmp/test.parquet' (format parquet, compression zstd);
/* Run Time (s): real 4.158 user 4.002094 sys 0.154674 */

SET threads = 4;
SET memory_limit = '128M';
SELECT count(*), sum(i), avg(d1), stddev(i1), avg(length(h1)), avg(date_diff('day', current_date, created))
FROM '/tmp/test.parquet';
/* Run Time (s): real 0.170 user 0.616465 sys 0.051658 */

Security

Use SQL SET variable = value; or put the settings inside the PDO::DUCKDB_ATTR_CONFIG connection options array:

# Disable extension loading
SET autoload_known_extensions = false;
SET autoinstall_known_extensions = false;
SET allow_community_extensions = false;
SET allow_persistent_secrets = false;

# Disable external file access, directory white listing
SET allowed_directories = ['/tmp'];
SET enable_external_access = false;

# Resource limits
SET threads = 4;
SET memory_limit = '4GB';
SET max_temp_directory_size = '4GB';

# Lock configuration
SET lock_configuration = true;

# Disable compression
SET force_compression='uncompressed';

# Query timeout (DuckDB 2.x, e.g. 30s)
SET max_execution_time = 30000;

# Request a checkpoint on database detach (DuckDB 2.x)
SET checkpoint_on_detach = 'ENABLED';

# Async IO resource limits (DuckDB 2.x)
SET async_threads = 4;
SET read_ahead_depth = 4;
SET http_client_pool_capacity = 4;

# Disable fsync (DuckDB 2.x)
SET fsync_mode = 'none';

A complete list is available in the DuckDB documentation: Securing DuckDB.
Asynchronous I/O is described here.

You can also run SQL statements when the connection is estabilshed using PDO::DUCKDB_ATTR_INIT_COMMAND:

$db = new PDO('duckdb::memory:', null, null, [PDO::DUCKDB_ATTR_INIT_COMMAND =>
    "SET threads = 2; SET async_threads = 4; SET memory_limit = '4GB';"]);

or use the PDO::DUCKDB_ATTR_CONFIG connection options array:

$db = new PDO('duckdb::memory:', null, null, [PDO::DUCKDB_ATTR_CONFIG =>
    ['threads' => 2, 'async_threads' => 4, 'memory_limit' => '4GB']]);

The DuckDB security model is described here.

Compile NTS

git clone --depth=1 --branch=main https://github.com/thomas-0816/pdo-duckdb.git
cd pdo_duckdb

wget https://github.com/duckdb/duckdb/releases/download/v2.0.0/duckdb-static-libs-linux-amd64.tar.gz
tar xvzf duckdb-static-libs-linux-amd64.tar.gz
tar xvzf libduckdb-src.tar.gz

phpize
./configure --with-pdo-duckdb CC=gcc CXX=g++
# ./configure --with-pdo-duckdb CC=clang CXX=clang++
make
NO_INTERACTION=1 TEST_PHP_ARGS="--show-diff --show-clean -q" make test

sudo make install
sudo sh -c 'echo "extension=pdo_duckdb.so" > /etc/php/8.5/mods-available/pdo_duckdb.ini'
sudo phpenmod pdo_duckdb

php -m | grep duckdb
php test.php

Compile ZTS

git clone --depth=1 --branch=main https://github.com/thomas-0816/pdo-duckdb.git
cd pdo_duckdb

wget https://github.com/duckdb/duckdb/releases/download/v2.0.0/duckdb-static-libs-linux-amd64.tar.gz
tar xvzf duckdb-static-libs-linux-amd64.tar.gz
tar xvzf libduckdb-src.tar.gz

phpize-zts
./configure --with-pdo-duckdb --with-php-config=php-config-zts CC=gcc CXX=g++
# ./configure --with-pdo-duckdb --with-php-config=php-config-zts CC=clang CXX=clang++
make
NO_INTERACTION=1 TEST_PHP_ARGS="--show-diff --show-clean -q" make test

sudo make install
sudo sh -c 'echo "extension=pdo_duckdb.so" > /etc/php-zts/conf.d/pdo_duckdb.ini'

php-zts -m | grep duckdb
php-zts test.php

Install with Swoole

echo "deb https://packages.sury.org/php/ noble main" >/etc/apt/sources.list.d/ondrej-php.list
curl -s https://packages.sury.org/php/apt.gpg >/etc/apt/trusted.gpg.d/php.gpg
sudo apt-get -y update
sudo apt-get -y --no-install-recommends install php8.5-cli php8.5-swoole
curl -fsSL -o /tmp/pie https://github.com/php/pie/releases/latest/download/pie.phar
sudo php /tmp/pie install thomas-0816/pdo-duckdb-php
# test
php -r 'print_r((new PDO("duckdb::memory:"))->query("SELECT 42 as n")->fetch(PDO::FETCH_ASSOC));'
php test_swoole.php

Swoole example

ini_set('swoole.aio_thread_num', 8); // default: max. number of CPU cores
// server workers: $server->set(['worker_num' => 8]);
$start = microtime(true);
Swoole\Coroutine\run(function() {
    $waitGroup = new Swoole\Coroutine\WaitGroup();
    for ($i = 0; $i < 8; $i++) {
        Swoole\Coroutine::create(function () use ($waitGroup) {
            $waitGroup->add();
            $pdo = new PDO('duckdb::memory:');
            // $pdo = new PDO('duckdb:/tmp/test.db', null, null, [
            //     PDO::DUCKDB_ATTR_CONFIG => ['access_mode' => 'read_only']]); # SWOOLE_PROCESS
            // $pdo = new PDO('duckdb:/tmp/test.db'); # SWOOLE_THREAD
            $pdo->exec('select sleep_ms(1000)');
            echo '.';
            $waitGroup->done();
        });
    }
    $waitGroup->wait(10);
});
echo microtime(true) - $start, PHP_EOL; // 1 second

Client-server mode (Quack Remote Protocol):

# start the DuckDB server
duckdb database.duckdb --cmd "
    CALL quack_serve('quack:127.0.0.1:9494', token='secret');
    CREATE TABLE IF NOT EXISTS table1 (v VARCHAR, v2 VARCHAR);"

# run the clients
Swoole\Coroutine\run(function() {
  $sql = "ATTACH 'quack:127.0.0.1:9494' AS remote (TOKEN 'secret'); USE remote;";
  $waitGroup = new Swoole\Coroutine\WaitGroup();
  for ($i = 0; $i < 8; $i++) {
    Swoole\Coroutine::create(function () use ($waitGroup, $sql) {
      $waitGroup->add();
      $client = new PDO('duckdb::memory:', null, null, [PDO::DUCKDB_ATTR_INIT_COMMAND => $sql]);
      $client->exec("INSERT INTO table1 VALUES ('foo', 'bar')");
      $waitGroup->done();
    });
  }
  $waitGroup->wait(10);
});

Learn more about concurrency in DuckDB.

Compile with PHP TrueAsync

docker build --no-cache -f Dockerfile.trueasync2 -t pdo_duckdb_trueasync2 .
docker run --rm pdo_duckdb_trueasync2 php -m
docker run --rm -v $(pwd):/app pdo_duckdb_trueasync2 php /app/test_trueasync.php

Backup and restore

Backup as .duckdb file:

$db = new PDO('duckdb:/tmp/my_database.duckdb');
$db->exec("ATTACH '/tmp/backup.duckdb' AS backup");
$db->exec('COPY FROM DATABASE my_database TO backup');
$db->exec('DETACH backup');

Backup and restore as SQL + Parquet:

$db = new PDO('duckdb:/tmp/my_database.duckdb');
$db->exec('CREATE TABLE t1 AS FROM generate_series(1000)');
$db->exec("EXPORT DATABASE '/tmp/backup/' (FORMAT parquet, COMPRESSION zstd)");

$restore = new PDO('duckdb:/tmp/my_database_restore.duckdb');
$restore->exec("IMPORT DATABASE '/tmp/backup/'");
var_dump($restore->query('SELECT count(*) from t1')->fetchColumn());

Learn more about export and import statements.

Features not implemented

Custom UDF scalar functions: requires calling PHP functions in parallel which is not supported by NTS and would be very slow
Custom UDF table functions: using select * from json_each('<json_string>') gives better performance and is easier to use
Appender API: using insert into ... select value->>0, ... from json_each('<json_string>') is easier to use, gives almost the same performance and provides better error handling
open_basedir: only provides security on application level, use real sandboxes like nono or lightweight VMs like firecracker instead
persistent connections: opening a connection to DuckDB is already very fast, a reset function for the database session is currently not available in DuckDB
ERRMODE_SILENT, ERRMODE_WARNING: the standard for PHP and all frameworks is ERRMODE_EXCEPTION

Why DuckDB?

https://duckdb.org/why_duckdb

Like SQLite, DuckDB embeds directly into host applications as a library, eliminating the need for network serialization and separate server setups. It uses columnar storage and vectorized processing, running analytics 10–100x faster than traditional row-oriented databases. DuckDB spills data to disk if needed, allowing to process datasets much larger than available system RAM. It includes an advanced query optimizer that handles joins, subqueries, expressions and filters.
DuckDB can directly query flat files (JSON, CSV, and Parquet) directly via SQL without needing to import the data first. Flat files can be read directly from disk, network attached storage or S3 comatible cloud storage.
Data is processed in cache-friendly batches on a multi-core architecture, allowing modern hardware to operate on arrays of data simultaneously. For analytical queries that only require a few metrics, DuckDB reads only the relevant columns from disk/memory, saving I/O and CPU cycles. This brings data warehouse-level performance to any laptop or server.

FAQ

Do I need an extra server for DuckDB?

No. DuckDB runs completely embedded inside of PHP as an extension, just like SQLite.

How much RAM and CPU do I need for DuckDB?

DuckDB normally runs good with 1-4 GB RAM and 2-4 CPU cores.

How good is the compression with Parquet and zstd?

For logs you normally achieve compression rates of 50-100x.

Who is maintaining DuckDB?

The DuckDB project is owned and maintained by the DuckDB Foundation, a non-profit organization from Amsterdam.

Can I get support for DuckDB?

Yes. Support is available on GitHub, see the community support page for details.

Can I get support for pdo_duckdb?

Yes. Just open an Issue or a Pull request.

Is the PHP PDO Driver for DuckDB developed by the DuckDB project?

No. This is a third-party open-source community project.

Is DuckDB fully open-source?

Yes. DuckDB and all components are fully open-source under the MIT license.

Development

# sanity check to detect crashes
php -d extension=$(pwd)/modules/pdo_duckdb.so test.php

php run-tests.php --show-diff --show-clean -q
php-zts run-tests.php --show-diff --show-clean -q

clang-format-20 -i *.c *.cpp php_pdo_duckdb.h
intercept-build-20 make && run-clang-tidy-20 -p . -quiet

# code coverage
docker build --progress=plain -f Dockerfile.coverage -t pdo_duckdb_coverage .
docker run --rm -w /pdo-duckdb-php/ pdo_duckdb_coverage /bin/sh -c 'tar -cf - ./coverage/' | tar -xvf - --one-top-level=coverage/
chromium coverage/coverage/index.html

thomas-0816 / pdo-duckdb-php

Package info

Security

Statistics

README

DuckDB 2.0

Install and setup with 🥧 PIE

Install and load on demand with 🥧 PIE

Install and setup with 🧟 FrankenPHP (Debian/Ubuntu)

Install and setup with Docker

Usage examples

Open databases from disk or in-memory

Read and write Parquet files

Read CSV files with SQL

CSV data import

Read JSON files with SQL

Read and write Excel files

Use structured columns with a fixed schema

Vector Similarity Search (HNSW)

Views

Transactions

Cast array columns to JSON-string

Auto increment columns

Differences to MySQL / MariaDB

Copy data from MySQL or MariaDB to Parquet

Copy data from PostgreSQL to Parquet

Bulk data insertion

Read public data using HTTPs, JSON, CSV and Parquet

Read private data using REST APIs

Read data from S3, write data to S3

Client-server mode (Quack Remote Protocol)

Community extensions

Performance

Security

Compile NTS

Compile ZTS

Install with Swoole

Swoole example

Compile with PHP TrueAsync

Backup and restore

Features not implemented

Why DuckDB?

FAQ

Development

Laravel, Symfony

DuckDB Resources

AI Disclosure

License