harish81/laravel-duckdb

DuckDB CLI wrapper to interact with duckdb databases through laravel query builder.

v1.0.3 2023-10-20 03:09 UTC

Requires

• php: ^8.1
• guzzlehttp/guzzle: ^7.2
• illuminate/container: ^10.0
• illuminate/contracts: ^10.0
• illuminate/database: ^10.0
• illuminate/events: ^10.0
• illuminate/http: ^10.0
• illuminate/support: ^10.0
• spatie/laravel-package-tools: ^1.14.0

Requires (Dev)

• laravel/pint: ^1.0
• nunomaduro/collision: ^7.9
• orchestra/testbench: ^8.0
• pestphp/pest: ^2.0
• pestphp/pest-plugin-arch: ^2.0
• pestphp/pest-plugin-laravel: ^2.0
• phpunit/phpunit: ^10.0

MIT

• harish

• laravel
• harish81
• laravel-duckdb

README

https://github.com/duckdb/duckdb

• Download CLI (either)
  • https://duckdb.org/docs/installation/
  • https://github.com/duckdb/duckdb/releases/latest
  • run php artisan laravel-duckdb:download-cli (Experimental)
  • You can also pass argument --ver for specific version like php artisan laravel-duckdb:download-cli --ver=0.7.1

Support us

Installation

You can install the package via composer:

composer require harish81/laravel-duckdb

Usage

• Connect

'connections' => [
    'my_duckdb' => [
        'driver' => 'duckdb',
        'cli_path' => env('DUCKDB_CLI_PATH', base_path('vendor/bin/duckdb')),
        //'dbfile' => env('DUCKDB_DB_FILE', '/tmp/duck_main.db'),
    ],
...

• Examples

# Using DB facade
DB::connection('my_duckdb')
    ->table(base_path('genderdata.csv'))
    ->where('Gender', '=', 'M')
    ->limit(10)
    ->get();

# Using Raw queries
DB::connection('my_duckdb')
    ->select("select * from '".base_path('genderdata.csv')."' limit 5")

# Using Eloquent Model
class GenderDataModel extends \Harish\LaravelDuckdb\LaravelDuckdbModel
{
    protected $connection = 'my_duckdb';
    public function __construct()
    {
        $this->table = base_path('genderdata.csv');
    }
}
...
GenderDataModel::where('Gender','M')->first()

Advanced Usage

You can install duckdb extensions too.

Query data from s3 files directly.

• in database.php

'connections' => [
    'my_duckdb' => [
        'driver' => 'duckdb',
        'cli_path' => env('DUCKDB_CLI_PATH', base_path('vendor/bin/duckdb')),
        'cli_timeout' => 0, //0 to disable timeout, default to 1 Minute (60s)
        'dbfile' => env('DUCKDB_DB_FILE', storage_path('app/duckdb/duck_main.db')),
        'pre_queries' => [
            "SET s3_region='".env('AWS_DEFAULT_REGION')."'",
            "SET s3_access_key_id='".env('AWS_ACCESS_KEY_ID')."'",
            "SET s3_secret_access_key='".env('AWS_SECRET_ACCESS_KEY')."'",
        ],
        'extensions' => ['httpfs'],
    ],
    ...

• Query data

DB::connection('my_duckdb')
  ->select("SELECT * FROM read_csv_auto('s3://my-bucket/test-datasets/example1/us-gender-data-2022.csv') LIMIT 10")

Writing a migration

return new class extends Migration {
    protected $connection = 'my_duckdb';
    public function up(): void
    {
        DB::connection('my_duckdb')->statement('CREATE SEQUENCE people_sequence');
        Schema::create('people', function (Blueprint $table) {
            $table->id()->default(new \Illuminate\Database\Query\Expression("nextval('people_sequence')"));
            $table->string('name');
            $table->integer('age');
            $table->integer('rank');
            $table->timestamps();
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('people');
        DB::connection('my_duckdb')->statement('DROP SEQUENCE people_sequence');
    }
};

Readonly Connection - A solution to concurrent query.

• in database.php

    'connections' => [
        'my_duckdb' => [
            'driver' => 'duckdb',
            'cli_path' => env('DUCKDB_CLI_PATH', base_path('vendor/bin/duckdb')),
            'cli_timeout' => 0,
            'dbfile' => env('DUCKDB_DB_FILE', storage_path('app/duckdb/duck_main.db')),
            'schema' => 'main',
            'read_only' => true,
            'pre_queries' => [
                "SET s3_region='".env('AWS_DEFAULT_REGION')."'",
                "SET s3_access_key_id='".env('AWS_ACCESS_KEY_ID')."'",
                "SET s3_secret_access_key='".env('AWS_SECRET_ACCESS_KEY')."'",
            ],
            'extensions' => ['httpfs', 'postgres_scanner'],
        ],
        ...

Testing

• Generate test data

# Syntax: ./data-generator.sh <lines> <file-to-save.csv>
./data-generator.sh 100 _test-data/test.csv
./data-generator.sh 90000000 _test-data/test_big_file.csv

• Run Test case

composer test

Limitations & FAQ

• https://duckdb.org/faq

Changelog

Please see CHANGELOG for more information on what has changed recently.

Contributing

Please see CONTRIBUTING for details.

Security Vulnerabilities

Please review our security policy on how to report security vulnerabilities.

Credits

• harish
• All Contributors

License

The MIT License (MIT). Please see License File for more information.