PHP download

PHP code example of thomas-0816 / laravel-duckdb

1. Go to this page and download the library: Download thomas-0816/laravel-duckdb library. Choose the download type require.

2. Extract the ZIP file and open the index.php.

3. Add this code to the index.php.

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

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

thomas-0816 / laravel-duckdb example snippets


'connections' => [
    'duckdb' => [
        'driver'   => 'duckdb',
        'database' => env('DB_DATABASE', database_path('analytics.duckdb')),
        'options' => [
            PDO::DUCKDB_ATTR_CONFIG => [
                'TimeZone' => 'Europe/Berlin',
                // 'threads' => 4, # max. number of threads
                // 'memory_limit' => '4GB', # max. memory usage
                // 'access_mode' => 'read_only', # open database file read-only
            ],
        ],
    ],
],


'connections' => [
    'duckdb' => [
        'driver'   => 'duckdb',
        'database' => ':memory:',
        'options' => [
            PDO::DUCKDB_ATTR_CONFIG => [
                'TimeZone' => 'Europe/Berlin',
                // 'threads' => 4, # max. number of threads
                // 'memory_limit' => '4GB', # max. memory usage
            ],
        ],
    ],
],


$connection = (new ConnectionFactory(app()))
    ->make(['driver' => 'duckdb', 'database' => ':memory:', 'duckdb');


use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

// up
Schema::connection('duckdb')->create('events', function (Blueprint $table) {
    $table->id(); // creates sequence "seq_events_id" as auto-increment
    $table->string('category');
    $table->decimal('amount', 12, 2);
    $table->json('tags')->nullable();
    $table->timestamps();
});

// php artisan migrate --pretend
// php artisan migrate

// down
// Schema::connection('duckdb')->createSequence('seq_events_id', 1, 1);
Schema::connection('duckdb')->dropSequence('seq_events_id');
Schema::connection('duckdb')->dropIfExists('events');


use Illuminate\Support\Facades\DB;

dump(DB::connection('duckdb')->table('events')->insertGetId([[
    'category' => 'conference',
    'amount' => 42.21,
    'tags' => ['Hello', 'DuckDB'],
    'created_at' => now(),
    'updated_at' => now(),
]]));

# 1


use Illuminate\Support\Facades\DB;

$result = DB::connection('duckdb')->query()
    ->selectExpression("date_trunc('week', created_at)", 'week')
    ->selectExpression('sum(amount)', 'revenue')
    ->selectExpression('histogram(tags)', 'tags')
    ->from('events')
    ->where('amount', '>', 100)
    ->groupBy('week')
    ->orderBy('week')
    ->get();

dump($result->toArray());


namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Event extends Model
{
    protected $connection = 'duckdb';
    protected $table = 'events';
}


use App\Models\Event;

$event = new Event();
$event->category = 'conference';
$event->amount = 42.21;
$event->tags = ['Hello', 'DuckDB'];
$event->save();
dump($event->toArray());

$events = Event::where('created_at', '>=', now()->subWeek())->get();
dump($events->toArray());


use Illuminate\Support\Facades\DB;

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

$result = DB::connection('duckdb')->query()
    ->select('aaa')
    ->from('/tmp/test.csv') // or multiple files using '/tmp/*.csv'
    ->get();
dump($result->toArray());

# Array
#     [0] => stdClass Object
#         [aaa] => 123
#     [1] => stdClass Object
#         [aaa] => aaa


namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class TestCsv extends Model
{
    protected $connection = 'duckdb';
    protected $table = '/tmp/test.csv'; // or multiple files using '/tmp/*.csv'
}


use App\Models\TestCsv;

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

$rows = TestCsv::select('aaa')->get();
dump($rows->toArray());

# Array
#     [0] => Array
#         [aaa] => 123
#     [1] => Array
#         [aaa] => ddd


use Illuminate\Support\Facades\DB;

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

$db = DB::connection('duckdb');
$db->statement("CREATE TABLE test_csv AS SELECT * FROM '/tmp/test.csv'"); // schema + data import
$db->statement("INSERT INTO test_csv SELECT * FROM '/tmp/test.csv'"); // only import data
dump($db->select('SHOW test_csv'));

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


use Illuminate\Support\Facades\DB;

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

$result = DB::connection('duckdb')->query()
    ->select('log')
    ->from('/tmp/logs.json') // or multiple files using '/tmp/*.json'
    ->get();
dump($result->toArray());

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

// Convert JSON file to Parquet file
DB::connection('duckdb')->statement("COPY (SELECT * FROM '/tmp/logs.json') TO '/tmp/logs.parquet'");

$result = DB::connection('duckdb')->query()
    ->select('log')
    ->from('/tmp/logs.parquet')
    ->get();
dump($result->toArray());

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


namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class LogsJson extends Model
{
    protected $connection = 'duckdb';
    protected $table = '/tmp/logs.json'; // or multiple files using '/tmp/*.json'
}

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

$rows = LogsJson::select('log')->get();
dump($rows->toArray());

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


namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class LogsParquet extends Model
{
    protected $connection = 'duckdb';
    protected $table = '/tmp/logs.parquet'; // or multiple files using '/tmp/*.parquet'
}

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

// Convert JSON file to Parquet file
DB::connection('duckdb')->statement("COPY (SELECT * FROM '/tmp/logs.json') TO '/tmp/logs.parquet'");

$rows = LogsParquet::select('log')->get();
dump($rows->toArray());

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


use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

Schema::connection('duckdb')->create('table1', function (Blueprint $table) {
    $table->id();
    $table->string('text');
    $table->json('data');
});
DB::connection('duckdb')->table('table1')->insert([[
    'text' => 'Hello DuckDB 🦆',
    'data' => ['foo' => 'bar', 'baz' => 42],
]]);
DB::connection('duckdb')->statement("COPY (SELECT * FROM table1) TO '/tmp/table1.parquet'");

$result = DB::connection('duckdb')->query()
    ->from('/tmp/table1.parquet')
    ->get();
dump($result->toArray());

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


use Illuminate\Support\Facades\DB;

DB::connection('duckdb')->unprepared('INSTALL excel; LOAD excel');

DB::connection('duckdb')->unprepared('CREATE TABLE table1 (text VARCHAR, amount DECIMAL(10, 2))');
DB::connection('duckdb')->table('table1')->insert([
    ['text' => 'Hello Excel 🦆', 'amount' => 42.21],
]);

DB::connection('duckdb')->unprepared("COPY (SELECT * FROM table1) TO '/tmp/table1.xlsx'");

DB::connection('duckdb')->query()
    ->from('/tmp/table1.xlsx')
    ->get()
dump($result->toArray());

# Array
#     [A1] => Hello DuckDB 🦆
#     [B1] => 42.21


use Illuminate\Support\Facades\DB;

$result = DB::connection('duckdb')->query()
    ->select(['id', 'name.en'])
    ->fromRaw("read_json('https://bulk.meteostat.net/v2/stations/lite.json.gz')")
    ->whereLike('name.en', '%Berlin%')
    ->limit(2);
echo json_encode($result->get()->toArray()), PHP_EOL;

# [{"id":"10381","en":"Berlin \/ Dahlem"},{"id":"10382","en":"Berlin \/ Tegel"}]

$result = DB::connection('duckdb')->query()
    ->select(['hour', 'temp'])
    ->fromRaw("read_csv('https://data.meteostat.net/hourly/2026/10381.csv.gz')")
    ->where('year', 2026)->where('month', 7)->where('day', 25)->where('hour', '>', 9)
    ->limit(3);
echo json_encode($result->get()->toArray()), PHP_EOL;

# [{"hour":10,"temp":24.1},{"hour":11,"temp":25.5},{"hour":12,"temp":26.4}]


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

$rows = DB::connection('duckdb')->select("
    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
");
dump(array_map('json_encode', $rows));

$rows = DB::connection('duckdb')->select("
    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'
");
dump(array_map('json_encode', $rows));

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


use Illuminate\Support\Facades\DB;

$url = 'https://httpbin.org/headers';
DB::connection('duckdb')->unprepared("CREATE SECRET http_auth (TYPE http, SCOPE '{$url}', BEARER_TOKEN 'some secret')");

$result = DB::connection('duckdb')->query()
    ->select('*')
    ->fromRaw("read_json('{$url}?foo=bar')")
    ->get();
var_dump($result[0]);

# stdClass Object
#     [headers] => Array
#         [Accept] => */*
#         [Authorization] => Bearer some secret


use Illuminate\Support\Facades\DB;

// set credentials
DB::connection('duckdb')->unprepared("
    CREATE SECRET rustfs (TYPE s3, ENDPOINT '127.0.0.1:9000', USE_SSL false,
    URL_STYLE 'path', KEY_ID 'access-key', SECRET 'secret-key')");
// write data to S3
DB::connection('duckdb')->unprepared("
    COPY ( select 'bar' as foo ) TO 's3://my-bucket/numbers.parquet' (FORMAT parquet)");
// read data from S3
$result = DB::connection('duckdb')->query()
    ->select('*')
    ->fromRaw("'s3://my-bucket/numbers.parquet'")
    ->get();
var_dump($result[0]);

# stdClass Object
#     [foo] => bar


use Illuminate\Support\Facades\DB;

DB::connection('duckdb')->unprepared("
    INSTALL mysql;
    ATTACH 'host=127.0.0.1 port=3306 user=root password=secret database=testdb' AS testdb (TYPE mysql);
    COPY (select * from testdb.orders) TO '/tmp/orders.parquet' (FORMAT parquet);
");
$rows = DB::connection('duckdb')->query()
    ->from('/tmp/orders.parquet')
    ->get();
dump($rows->toArray());

# Array
#     [0] => stdClass Object
#         [id] => 1
#         [customerId] => 42
#         [amount] => 123.42
#         [origin] => shop
#     [1] => stdClass Object
#         [id] => 2
#         [customerId] => 21
#         [amount] => 12.21
#         [origin] => offline


use Illuminate\Support\Facades\DB;

DB::connection('duckdb')->unprepared("
    INSTALL postgres;
    ATTACH 'host=127.0.0.1 port=5432 user=postgres password=secret' AS testdb (TYPE postgres);
    COPY (select * from testdb.orders) TO '/tmp/orders.parquet' (FORMAT parquet);
");
$rows = DB::connection('duckdb')->query()
    ->from('/tmp/orders.parquet')
    ->get();
dump($rows->toArray());

# Array
#     [0] => Array
#         [id] => 1
#         [customerId] => 42
#         [amount] => 123.42
#         [origin] => shop
#     [1] => Array
#         [id] => 2
#         [customerId] => 21
#         [amount] => 12.21
#         [origin] => offline


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

DB::connection('duckdb')->unprepared('CREATE TABLE t1 (i1 integer, v1 varchar)');
DB::connection('duckdb')->unprepared("INSERT INTO t1 SELECT value->>'i1', value->>'v1' FROM json_each('" . json_encode($data) . "')");

$result = DB::connection('duckdb')->query()
    ->selectExpression('count(*)', 'count')
    ->from('t1')
    ->first();
dump($result->count); # 1000000


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

DB::connection('duckdb')->unprepared('CREATE TABLE t1 (i1 integer, v1 varchar)');
DB::connection('duckdb')->unprepared("INSERT INTO t1 SELECT value->>0, value->>1 FROM json_each('" . json_encode($data) . "')");

$result = DB::connection('duckdb')->query()
    ->selectExpression('count(*)', 'count')
    ->from('t1')
    ->first();
dump($result->count); # 1000000


use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

Schema::connection('duckdb')->create('events', function (Blueprint $table) {
    $table->id();
    $table->rawColumn('numbers', 'integer[]');
    $table->rawColumn('categories', 'varchar[]');
    $table->rawColumn('person', 'STRUCT(v VARCHAR, va VARCHAR[], d DECIMAL)');
});

class Person {
    public string $v;
    public array $va;
    public float $d;
}

$person = new Person();
$person->v = 'foo';
$person->va = ['bar', 'baz'];
$person->d = 12.34;

DB::connection('duckdb')->table('events')->insert([[
    'numbers' => [21, 42],
    'categories' => ['cat1', 'cat2'],
    'person' => $person,
]]);
$events = DB::connection('duckdb')->query()
    ->from('events')
    ->get();
dump($events->toArray());

# Array
#     [0] => stdClass Object
#         [id] => 1
#         [numbers] => Array
#             [0] => 21
#             [1] => 42
#         [categories] => Array
#             [0] => cat1
#             [1] => cat2
#         [person] => Array
#             [v] => foo
#             [va] => Array
#                 [0] => bar
#                 [1] => baz
#             [d] => 12.34


use Illuminate\Database\Eloquent\Casts\Attribute;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

Schema::connection('duckdb')->create('events', function (Blueprint $table) {
    $table->id();
    $table->rawColumn('numbers', 'integer[]');
    $table->rawColumn('categories', 'varchar[]');
    $table->rawColumn('person', 'STRUCT(v VARCHAR, va VARCHAR[], d DECIMAL)');
    $table->rawColumn('persons', 'STRUCT(v VARCHAR, va VARCHAR[], d DECIMAL)[]');
    $table->timestamps();
});

class Person extends Model
{
    protected $guarded = [];
}
class Event extends Model
{
    protected $connection = 'duckdb';
    protected $table = 'events';
    protected function person(): Attribute
    {
        return Attribute::get(fn ($person) => new Person($person));
    }
    protected function persons(): Attribute
    {
        return Attribute::get(fn ($persons) => collect($persons)->map(fn ($values) => new Person($values)));
    }
}

$person = new Person();
$person->v = 'foo';
$person->va = ['bar', 'baz'];
$person->d = 12.34;

$event = new Event();
$event->numbers = [21, 42];
$event->categories = ['cat1', 'cat2'];
$event->person = $person;
$event->persons = [$person];
$event->save();

dump(Event::first()->toArray());

# Array
#     [id] => 1
#     [numbers] => Array
#         [0] => 42
#         [1] => 21
#     [categories] => Array
#         [0] => cat1
#         [1] => cat2
#     [person] => Person Object
#         [v] => foo
#         [va] => Array
#             [0] => bar
#             [1] => baz
#         [d] => 42.21
#     [persons] => Array
#         [0] => Array
#             [v] => foo
#             [va] => Array
#                 [0] => bar
#                 [1] => baz
#             [d] => 42.21


DB::connection('duckdb')->unprepared('INSTALL vss; LOAD vss');

Schema::connection('duckdb')->create('events', function (Blueprint $table) {
    $table->id();
    $table->vector('embeddings', 3);
    $table->vectorIndex('embeddings');
});
DB::connection('duckdb')->table('events')->insert([
    ['id' => 1, 'embeddings' => [1, 2, 3]],
    ['id' => 2, 'embeddings' => [4, 5, 6]],
]);

$results = DB::connection('duckdb')->table('events')
    ->select('id')
    ->selectVectorDistance('embeddings', [1.1, 2.1, 3.1], 'distance')
    ->where('distance', '<=', 0.01)
    ->orderBy('distance')
    ->get();
dump($results->toArray());

# Array
#     [0] => stdClass Object
#         [id] => 1
#         [distance] => 0.0001407862\n


use Illuminate\Support\Facades\Schema;

// create or drop views
Schema::connection('duckdb')->createView('view1', 'SELECT * FROM events');
Schema::connection('duckdb')->dropView('view1');


use Illuminate\Support\Facades\DB;

$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::connection('duckdb')->transaction(function ($db) {
    $db->statement("CREATE TABLE test_csv AS SELECT * FROM '/tmp/test.csv'");
    $db->statement("INSERT INTO test_csv SELECT * FROM '/tmp/test.csv'");
});


'connections' => [
    'duckdb' => [
        'driver' => 'duckdb',
        'database' => ':memory:',
        'options' => [
            PDO::DUCKDB_ATTR_INIT_COMMAND => "ATTACH 'quack:127.0.0.1:9494' AS remote (TOKEN 'secret'); USE remote;",
        ]


use Illuminate\Support\Facades\DB;

// open quack connection on demand
// DB::connection('duckdb')->unprepared("ATTACH 'quack:127.0.0.1:9494' AS remote (TOKEN 'secret'); USE remote;");
DB::connection('duckdb')->unprepared('CREATE TABLE IF NOT EXISTS table1 (v VARCHAR, v2 VARCHAR)');
DB::connection('duckdb')->unprepared("INSERT INTO table1 VALUES ('foo', 'bar')");

$result = DB::connection('duckdb')->select('SELECT * FROM table1');
dump($result);

# Array
#     [0] => stdClass Object
#         [v] => foo
#         [v2] => bar


# start llama.cpp at 127.0.0.1:8080
# ./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

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

DB::connection('duckdb')->unprepared("
    INSTALL open_prompt FROM community;
    LOAD open_prompt;
    SET VARIABLE openprompt_api_url = 'http://127.0.0.1:8080/v1/chat/completions';
");
Schema::connection('duckdb')->create('customers', function (Blueprint $table) {
    $table->id();
    $table->string('first_name');
    $table->string('last_name');
    $table->date('birth_date');
});
$result = DB::connection('duckdb')->query()
    ->selectExpression("open_prompt('write duckdb sql, no markdown, find customers older than 30, schema: ' || group_concat(sql))", 'llm')
    ->fromRaw('duckdb_tables()')
    ->first();

# SELECT * FROM customers WHERE birth_date < now() - INTERVAL '30 years';


$result = Octane::concurrently([
    fn () => DB::connection('duckdb')->selectOne("select sleep_ms(1000)"),
    fn () => DB::connection('duckdb')->selectOne("select sleep_ms(1000)"),
    fn () => DB::connection('duckdb')->selectOne("select sleep_ms(1000)"),
]); // takes 1s


class AppServiceProvider extends ServiceProvider {
    public function boot(): void {
        Octane::tick('my-ticker', function () {
            $result = DB::connection('duckdb')->selectOne("select sleep_ms(1000)");
            dump($result);
        })->seconds(10);


'connections' => [
    'duckdb' => [
        'driver' => 'duckdb',
        'database' => ':memory:',
# or
'connections' => [
    'duckdb' => [
        'driver'   => 'duckdb',
        'database' => env('DB_DATABASE', database_path('analytics.duckdb')),
        'options' => [PDO::DUCKDB_ATTR_CONFIG => ['access_mode' => 'read_only'],


'listeners' => [
    OperationTerminated::class => [
        DisconnectFromDatabases::class,

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

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

bash
php artisan schema:dump --database=duckdb # creates ./database/schema/duckdb-schema.sql