PHP code example of thomas-0816 / pdo-duckdb-php

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

thomas-0816 / pdo-duckdb-php example snippets

$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! 🐘 💓 🦆

$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']
]);

$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

$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

$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

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)");

$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

// 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

$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

$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));

$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();

$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"]

$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

$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,
# )

$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
#         [customerId] => 42
#         [amount] => 123.42
#         [origin] => shop
#     [1] => Array
#         [id] => 2
#         [customerId] => 21
#         [amount] => 12.21
#         [origin] => offline

$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
#         [customerId] => 42
#         [amount] => 123.42
#         [origin] => shop
#     [1] => Array
#         [id] => 2
#         [customerId] => 21
#         [amount] => 12.21
#         [origin] => offline

$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}]

# 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}

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

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

$db->exec("CREATE SECRET http_auth (TYPE http, SCOPE '{$url}', BEARER_TOKEN 'some secret')");

$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.5(linux_amd64) capi d8cdaa33fd
#             [X-Amzn-Trace-Id] => Root=1-6aab0271-2c027dfa07bc6def14ce4e72

# ./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, first_name varchar, last_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;

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

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']]);
            $pdo->exec("select sleep_ms(1000)");
            echo '.';
            $waitGroup->done();
        });
    }
    $waitGroup->wait(10);
});
echo microtime(true) - $start, PHP_EOL; // 1 second

bash
pie install thomas-0816/pdo-duckdb-php

bash
pie install --skip-enable-extension thomas-0816/pdo-duckdb-php
php -d extension=pdo_duckdb some_script.php

bash
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));'

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

bash
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