ODBC¶

Connect to CrateDB with ODBC.

Open Database Connectivity (ODBC) is a standard application programming interface (API) for accessing database management systems (DBMS), conceived to be independent of database systems and operating systems. The application uses ODBC functions through an ODBC driver manager and addresses the driver and database using a Data Source Name (DSN).

Installation¶

While Windows typically includes an ODBC driver manager, you can install the unixODBC driver manager on Linux and macOS systems. The PostgreSQL ODBC driver is called psqlODBC.

Windows

Please navigate to the psqlODBC download site to download and install the latest psqlODBC driver for Windows systems. Installing PostgreSQL ODBC drivers on Windows includes an illustrated walkthrough.

Linux

On Linux, install the unixODBC ODBC driver manager and the psqlODBC driver. Installing PostgreSQL ODBC drivers on Linux includes an illustrated walkthrough.

Arch Linux

pacman -Sy psqlodbc

Debian and derivatives

apt install --yes odbc-postgresql odbcinst unixodbc

Red Hat and derivatives

yum install -y postgresql-odbc

Verify installation.

odbcinst -q -d

[PostgreSQL ANSI]
[PostgreSQL Unicode]

macOS

On macOS, install the unixODBC ODBC driver manager and the psqlODBC driver, then register it.

# macOS
brew install psqlodbc unixodbc

odbcinst.ini

[PostgreSQL Unicode]
Description     = PostgreSQL ODBC driver (Unicode version)
Driver          = /usr/local/lib/psqlodbcw.so

odbcinst -i -d -f odbcinst.ini

Verify installation.

odbcinst -q -d

[PostgreSQL Unicode]

Configuration¶

For connecting to CrateDB, either address the database using a named connection (DSN), e.g. using Dsn=CrateDB, or address it directly using the driver, like Driver={PostgreSQL Unicode}.

DSN configuration

When using a DSN, a typical connection string for CrateDB is:

Dsn=CrateDB

Windows

On Windows, you will create a DSN using the ODBC driver manager UI. To set up a DSN (Data Source Name), click the “System DSN” tab. Click “Add”. Select “PostgreSQL Unicode” and click “Finish”. The illustrated walkthrough also covers that part.

Linux and macOS

With unixODBC, configure a DSN within an .odbc.ini or /etc/odbc.ini file.

~/.odbc.ini

[CrateDB]
Description=CrateDB
Driver=PostgreSQL Unicode
Server=localhost
Port=5432
Uid=crate
Pwd=crate
MaxVarcharSize=1073741824

DSN-less configuration

For directly connecting using a driver, without a registered DSN, a typical connection string for CrateDB is:

Driver={PostgreSQL Unicode};Server=localhost;Port=5432;Uid=crate;Pwd=crate;MaxVarcharSize=1073741824

Examples¶

A few examples that demonstrate CrateDB connectivity with ODBC.