latest

Connect to CrateDB

Table of contents

Data source names

PDO makes use of Data Source Name (DSN) strings.

A basic version of the DSN string for CrateDB might be:

crate:<HOST_ADDR>:<PORT>

Here, replace <HOST_ADDR> with the host address of a CrateDB node, and replace <PORT> with the port number of the HTTP endpoint.

Example DSN strings:

  • crate:localhost:4200

  • crate:crate-1.vm.example.com:4200

  • crate:198.51.100.1:4200

You can specify a second CrateDB node, like so:

crate:<HOST_ADDR_1>:<PORT>,<HOST_ADDR_2>:<PORT>

Here, <HOST_ADDR_1> and <HOST_ADDR_2> are the host strings for the first and second CrateDB nodes, respectively.

In fact, you can specify as many nodes as you like. Each corresponding host string must be separated from the previous one using a , character.

Tip

The list of nodes is shuffled when the connection is first created.

For every query, the client will attempt to connect to each node in sequence until a successful connection is made. Nodes are moved to the end of the list each time they are tried.

Over multiple query executions, this behaviour functions as client-side round-robin load balancing. (This is analogous to round-robin DNS.)

You can also specify a schema, like so:

crate:<HOST_ADDR>:<PORT>/<SCHEMA>

Here, replace <SCHEMA> with the name of the schema you want to select.

Tip

The default CrateDB schema is doc, and if you do not specify a schema, this is what will be used.

However, you can query any schema you like by specifying it in the query.

To wrap up, here’s a more complex list of example DSN strings:

  • crate:localhost:4200/my_schema

  • crate:crate-1.vm.example.com:4200,crate-2.vm.example.com:4200

  • crate:198.51.100.1:4200,198.51.100.2:4200/another_schema

Get a connection

You can get a PDO connection like this:

use Crate\PDO\PDOCrateDB;

$dsn = '<DATA_SOURCE_NAME>';
$user = 'crate';
$password = null;
$options = null;
$connection = new PDOCrateDB($dsn, $user, $password, $options);

Note

Authentication was introduced in CrateDB versions 2.1.x.

If you are using CrateDB 2.1.x or later, you must supply a username. If you are using earlier versions of CrateDB, this argument is not supported.

See the compatibility notes for more information.

If you have not configured a custom database user, you probably want to authenticate as the CrateDB superuser, which is crate. The superuser does not have a password, so you should omit the password argument.

CrateDB Cloud

If you are connecting to CrateDB Cloud you will need to enable the SSL mode by setting the connection’s CRATE_ATTR_SSL_MODE attribute:

require __DIR__ . '/vendor/autoload.php';
use Crate\PDO\PDOCrateDB;
$dsn = 'crate:yourcluster.yourdomain.com:4200';
$user = 'user1';
$password = '<SECRET>';
$options = null;
$connection = new PDOCrateDB($dsn, $user, $password, $options);
$connection->setAttribute(PDOCrateDB::CRATE_ATTR_SSL_MODE, PDOCrateDB::CRATE_ATTR_SSL_MODE_REQUIRED);
$stmt = $connection->prepare('SELECT mountain FROM sys.summits LIMIT 1;');
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
             print_r($row);
     }

If this is not configured you will see the following error message: cURL error 52: Empty reply from server

Advanced settings

Once you have a connection, you can configure settings with the setAttribute method, like this:

$connection->setAttribute(<ATTRIBUTE>, <VALUE>);

Here, replace <ATTRIBUTE> with the a reference to a PDO attribute class constant, and replace <VALUE> with the value you want to set it to.

PDO attribute class constants look like this:

PDO::ATTR_TIMEOUT

See also

Consult the PDO setAttribute documentation for a full list of PDO attribute class constants.

Timeout

PDO::ATTR_TIMEOUT (int) seconds

The connection timeout.

Setting this attribute will raise a timeout exception and cancel the HTTP connection after the specified duration has elapsed.

Cancelling the HTTP connection, however, does not cancel the execution of the statement on the server.

Default: 0 (indefinitely)

Driver specific constants

The CrateDB driver provides number of PDO attribute class constants.

PDOCrateDB::CRATE_ATTR_DEFAULT_SCHEMA (string)

The default schema for the PDO connection.

Tip

The default CrateDB schema is doc, and if you do not specify a schema, this is what will be used.

However, you can query any schema you like by specifying it in the query.

PDOCrateDB::CRATE_ATTR_SSL_MODE (int) named attribute

The connection SSL mode.

Accepted values:

CRATE_ATTR_SSL_MODE_DISABLED (default)

Disable SSL mode.

CRATE_ATTR_SSL_MODE_ENABLED_BUT_WITHOUT_HOST_VERIFICATION

Enable SSL mode, but do not perform host verification.

CRATE_ATTR_SSL_MODE_REQUIRED

Enable SSL mode, and perform host verification.

PDOCrateDB::CRATE_ATTR_SSL_KEY_PATH (string)

The path to an SSL client key file.

PDOCrateDB::CRATE_ATTR_SSL_KEY_PASSWORD (string)

The SSL client key file password.

PDOCrateDB::CRATE_ATTR_SSL_CERT_PATH (string)

The path to an SSL client certificate file.

PDOCrateDB::CRATE_ATTR_SSL_CERT_PASSWORD (string)

The SSL client certificate file password.

PDOCrateDB::CRATE_ATTR_SSL_CA_PATH (string)

The path to an SSL Certificate Authority (CA) certificate file.

See also

Consult the CrateDB reference for more help with setting up SSL.

Fetch modes

CrateDB PDO supports a subset of the PDO fetch modes, depending on the fetch method used.

Fetch Method

Supported Modes

PDOStatement::fetchAll

PDO::FETCH_NUM

PDO::FETCH_NAMED

PDO::FETCH_ASSOC

PDO::FETCH_BOTH

PDO::FETCH_FUNC

PDO::FETCH_COLUMN

PDO::FETCH_OBJ

PDOStatement::fetch

PDO::FETCH_NAMED

PDO::FETCH_ASSOC

PDO::FETCH_BOTH

PDO::FETCH_BOUND

PDO::FETCH_NUM

PDO::FETCH_OBJ

Bulk operations

With CrateDB Bulk operations, suitable for INSERT, UPDATE, and DELETE statements, you can submit multiple records, aka. batches, to CrateDB within a single operation. By using this way of communication, both the client and the server will not waste resources on building and decoding huge SQL statements, and data will also propagate more efficiently between CrateDB cluster nodes.

To use this mode, the PDOStatement offers a corresponding bulkMode option. When creating a statement instance with it, the $parameters data will be obtained as a list of records, like demonstrated in the example below.

Please note that you must use PDO::FETCH_NUM on the fetch operation, because the response object type BulkResponse is different than the regular response type Collection.

// Run insert operation.
$parameters = [[5, 'foo', 1], [6, 'bar', 2], [7, 'foo', 3], [8, 'bar', 4]];
$statement = $connection->prepare(
    'INSERT INTO test_table (id, name, int_type) VALUES (?, ?, ?)',
    array("bulkMode" => true));
$statement->execute($parameters);

// Evaluate response.
// MUST use `PDO::FETCH_NUM` for returning bulk operation responses.
print("Total count: {$statement->rowCount()}\n");
$response = $statement->fetchAll(PDO::FETCH_NUM);
print_r($response);

Next steps

Use the standard the PDO documentation documentation for the rest of your setup process.

See also

Check out the sample application (and the corresponding sample application documentation) for a practical demonstration of this driver in use.