DBD::Crate - DBI driver for Crate db


    use DBI;
    my $dbh = DBI->connect('dbi:Crate:' );
    my $sth = $dbh->prepare( 'SELECT id, content FROM articles WHERE id > 2' );
    my $res = $sth->execute;

    $sth->bind_columns (\my ($id, $content));
    while ($sth->fetch) {
        print "id: $id, content: $content\n";

    print "Toatal ", $res, "\n";


DBD::Crate is a DBI driver for Crate DB, DBD::Crate is still in early development so any feedback is much appreciated.


If you haven't heard of Crate I suggest you to give it a try, it's a is a distributed data store With SQL query support, and fulltext seach based on Elasticsearch, please read this overview


    use DBI;

    my $dbh = DBI->connect(DBI:Crate:"); #<- [localhost:4200] defaults
    my $dbh = DBI->connect("DBI:Crate:localhost:5000"); #<-- localhost port 5000
    my $dbh = DBI->connect("DBI:Crate:", '', '', { utf8 => [0|1] });

IP:PORT address of Crate server to which you need to connect to, if not available, localhost and default port [4200] will be used.

Crate does't have builtin user permissions or ACL concept, but some times you may use it behind a proxy with user authintication

    my $dsn = DBI->connect("DBI:Crate:", 'user', 'pass');

This will issue a request with Basic-style user:pass authintication, in the example folder you can find a plack proxy example with basic authintication, you can also read Crate post on how to set a proxy behind ngix here

DBD::Crate has a simple fail over setup with multi servers

    my $dbh = DBI->connect("DBI:Crate:[localhost:4200, localhost:42001, ...]");

Since Crate DB will handle distributing the job to the best available server for you, we only implement a simple fail over process, it check if the first connection failed, we will try the next one and append the failed host to the end of the list.


Sometimes you need to get raw json data, maybe to send as jsonp response


The returned data will be of json format as the following

    {"cols" : ["id","content"], "duration" : 0, "rows" : [ [1, "content"], ... ], "rowcount" : 2}

BLOB Methods

Crate includes support to store binary large objects (BLOBS)

    $dbh->crate_blob_insert("blobtable", [sh1-digest], data);

crate_blob_insert accepts three arguments, blob table name, sha1 hex digest of data, and data tp store. sha1 hex digest argument is optional in which DBD::Crate will create the digest for you

    my $digest = $dbh->crate_blob_insert("blobtable", data);

crate_blob_insert returns the sha1 hex digest of the data stored on success or undef on failure and set $dbh->errstr & $dbh->err

    $dbh->crate_blob_get("blobtable", "sha1 digest");

returns stored data, in blobtable with sha1 digest previously used to store data, on error returns undef, and set $dbh->errstr & $dbh->err

    $dbh->crate_blob_delete("blobtable", "sha1 digest");

Delete blob, returns true on success, undef on failure and set $dbh->errstr & $dbh->err

Table Info Method

    my $tables = $dbh->crate_tables_list();
    my $table = $dbh->crate_table_info("tablename");
    my $columns = $dbh->crate_table_columns("tablename");

Accepts table schema argument [optional] if not provided will fetch tables from default doc schema, to get blobs tables list, use blob schema


return a list of tables under schema with information

            'number_of_replicas' => '0-all',
            'partitioned_by' => undef,
            'blobs_path' => undef,
            'schema_name' => 'doc',
            'table_name' => 'articles',
            'number_of_shards' => 5,
            'clustered_by' => 'id'

Same as crate_tables_list but returns single hash ref reult for the tablename

    my $columns = $dbh->crate_table_columns("tablename");

returns list of table columns

        'data_type' => 'string',
        'column_name' => 'content',
        'ordinal_position' => 1
        'data_type' => 'string',
        'column_name' => 'description',
        'ordinal_position' => 2
        'data_type' => 'integer',
        'column_name' => 'id',
        'ordinal_position' => 3


Pimary keys

Crate doesn't auto generate primary keys, you need to provide a unique key by your self and it must be **ehem** UNIQUE :)

last inserted id

Well, there is also no way to get the last inserted id, for the obvious reason mentioned above I guess, but you can query that if you want in a new statement


To install this module, run the following commands:

    perl Makefile.PL
    make test
    make install


    cpan install DBD-Crate

If you want to run the complete test suite, you need to have Crate DB installed and running, then set environment variable CRATE_HOST to crate "ip:port"

on windows

    $ set CRATE_HOST=

on linux

    $ export CRATE_HOST=

See Also
Crate Docs


Mamod A. Mehyar, <>


This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself