Document use with Laravel

[amotl]

i am using laravel 11.42.1 (latest version) and i am able to connect to crate and get data like this:

$connection = app(CrateDB::class)->createConnection();
$stmt       = $connection->query('SELECT id FROM something LIMIT 3');
$rows       = $stmt->doFetchAll(PDO::FETCH_ASSOC);

and everything is working fine 🤷 😏

Originally posted by @michabbb in #160

[amotl]

Hi @michabbb. I think Laravel uses DBAL for connecting to databases. crate/crate-dbal#132 says it is currently unsupported, so we wonder how you are using it successfully.

NB: I will transfer this issue to the crate-dbal project.

[amotl]

I recently picked up that Laravel is now featuring the Eloquent ORM, already starting with version 4.x? Does it no longer use Doctrine DBAL under the hood, or even never did?

Pardon my ignorance, PHP database adapters are not my native habitat, still we are currently doing some refurbishments, so we appreciate any kinds of little hints from the community, if there is still interest about a better status of our driver and adapter layers.

/cc @seut

[amotl]

I think those pages make it clear that Laravel needs PDO only.

• https://laravel.com/docs/12.x/database
• https://laravel.com/docs/12.x/deployment

In previous eras, there has been a Doctrine 2 extension for Laravel (article from 2000), but we guess there is no such code path today?

• https://www.laraveldoctrine.org/docs/1.8/orm

=> Transferring back to crate-pdo.

[michabbb]

@amotl pardon didn´t see your post - is there anything you still need to know from my side ?

[amotl]

Hi @michabbb. I am just trying to figure out how Laravel is typically connecting to CrateDB, i.e. if it's using its own Eloquent ORM on top of the PDO driver, or if it's also a viable way to use it with Doctrine. In this case, the CrateDB DBAL adapter would come into play.

I've just discovered this set of packages:

• Umbrella: https://packagist.org/packages/laravel-doctrine/
• ORM: https://laravel-doctrine-orm-official.readthedocs.io/ (GitHub)

My goals are to create and demonstrate fully working examples for the most popular options in this area.

[michabbb]

@amotl perplexity says:

Laravel 12 internally uses the Illuminate/Database component for database communication, built on PDO as the core driver for connections to supported databases like MySQL or PostgreSQL. The Query Builder provides a fluent interface over PDO for raw and parameterized queries, while Eloquent ORM layers ActiveRecord patterns on top for models and relationships, translating to PDO statements. For CrateDB, native PDO/Eloquent support is absent; instead, integrate via Doctrine DBAL (wrapping PDO) using packages like laravel-doctrine to replace Eloquent with Doctrine ORM.[1][2][3][4][5][6]

Sources:
1
2
3
4
5
6

BUT: ☝️

i recently found a better way while using the latest version of laravel (12). because cratedb supports the "PostgreSQL wire protocol" - i can just use the "pgsql" driver in laravel and use it like every other database 😏 i don´t know the limitations here because i only do very basic stuff here, but using the pgsql driver makes it much easier, and it does not require any external library at all.

// config/database.php
return [
     'cratedb' => [
            'driver' => 'pgsql', <---------- !!!!
            'host' => env('DB_HOST_CRATEDB', '127.0.0.1'),
            'port' => env('DB_PORT_CRATEDB', '5432'),
            'database' => env('DB_DATABASE_CRATEDB', 'doc'),
            'username' => env('DB_USERNAME_CRATEDB', 'crate'),
            'password' => env('DB_PASSWORD_CRATEDB', ''),
            'charset' => 'utf8',
            'prefix' => '',
            'prefix_indexes' => true,
            'search_path' => env('DB_SCHEMA_CRATEDB', 'doc'),
            'sslmode' => 'prefer',
            'options' => [
                PDO::ATTR_EMULATE_PREPARES => false,
            ],
        ],
]

so here are things that work out of the box:

$results = DB::connection('cratedb')->select('SELECT CURRENT_TIMESTAMP LIMIT 1');

$model                    = new AnyModel();
$model->save(); // INSERT

$retrieved = AnyModel::query()
                ->where('set_id', $testRecord->set_id)
                ->where('lst_id', $testRecord->lst_id)
                ->where('testing', 1)
                ->first();

$testRecord->delete();

DB::connection('cratedb')->statement('.........any valid cratedb sql............');

AnyModel::upsert(
                $updateData,
                uniqueBy: ['set_id', 'lst_id', 'testing'],
                update: ['notes1', 'notes2', 'notes3', 'version']
            );

also bulk inserts work pretty well. i hope this was useful to you 😏

[amotl]

Excellent, thank you very much. How is AnyModel defined, and how would I invoke bulk inserts?

[michabbb]

@amotl AnyModel is a simple laravel model file, nothing special: https://laravel.com/docs/12.x/eloquent#generating-model-classes

inside the model you can define the database connection:

class AnyModel extends Model
{
    protected $connection = 'cratedb';
}

bulk inserts work as normal, also nothing special here: https://laravel.com/docs/12.x/eloquent#upserts

[amotl]

Introduction

I can just use the "pgsql" driver in laravel and use it like every other database 😏. I don't know the limitations here because I only do very basic stuff here, but using the [native] pgsql driver makes it much easier, and does not require any external library at all.

That's the spirit, @michabbb 💯. Thank you for letting us know this works well: It is a completely valid approach to establish connectivity without much ado, building upon CrateDB's PostgreSQL interface so you don't need to install any driver at all.

Let us connect the dots here by elaborating about the (current) limitations of this approach with respect to support for CrateDB's special data types like OBJECT and ARRAY, and how we can bring both things together in the future (PostgreSQL wire support + CrateDB dialect support).

Details

Let's also enumerate those resources in this context, that (also) use Eloquent ORM, ratkor/laravel-crate.io, and crate/crate-dbal to connect to CrateDB.

• https://cweiske.de/tagebuch/crate-consistency.htm
• https://github.com/RatkoR/laravel-crate.io

@cweiske and @RatkoR: I wonder if you could also use the PostgreSQL drivers for your purposes, but then the whole stack would lack the CrateDB SQL dialect peculiarities like the Map and Array type implementations in CrateDB DBAL. We've added a note about a possible way forward here, which would resemble to reuse the existing dialect implementations but put them on top of DBAL's new PgSQL adapter.

• Add compatibility with Doctrine DBAL 3.x and 4.x crate-dbal#106 (comment)