CockroachDB

A distributed SQL database designed for speed, scale, and survival.

Overview

The CockroachDB source supports both Full Refresh and Incremental syncs. You can choose if the Zenskar's CockroachDB connector will copy only the new or updated data or all rows in the tables and columns you set up for replication, every time a sync is run.

Resulting schema

Data-type mapping

CockroachDb data types are mapped to the following data types when synchronizing data:

CockroachDb Type	Resulting Type	Notes
bigint	integer
bit	boolean
boolean	boolean
character	string
character varying	string
date	string
double precision	string
enum	number
inet	string
int	integer
json	string
jsonb	string
numeric	number
smallint	integer
text	string
time with timezone	string	may be written as a native date type depending on the destination
time without timezone	string	may be written as a native date type depending on the destination
timestamp with timezone	string	may be written as a native date type depending on the destination
timestamp without timezone	string	may be written as a native date type depending on the destination
uuid	string

🚧

Note

Arrays for all the above types as well as custom types are supported, although they may be de-nested depending on the destination.

Features

Feature	Supported	Notes
Full Refresh Sync	Yes
Incremental Sync	Yes
Change Data Capture	No
SSL Support	Yes

🐕‍🦺 Setup guide

🚧

Prerequisites

1. CockroachDb v1.15.x or above
2. Allow connections from Zenskar to your CockroachDb database
3. Create a dedicated read-only Zenksar user with access to all tables needed for replication

Allow Zenskar to access the database

This is dependent on your networking setup. The easiest way to verify if Airbyte is able to connect to your CockroachDb instance is via the check connection tool in the UI.

Create a dedicated read-only user with access to the relevant tables

This step is optional but highly recommended to allow for better permission control and auditing. Alternatively, you can use Zenskar with an existing user in your database.

To create a dedicated database user, run the following commands against your database:

SQL

CREATE USER zenskar PASSWORD 'your_password_here';

Then give it access to the relevant schema:

SQL

GRANT USAGE ON SCHEMA <schema_name> TO zenskar

📖

You can re-run the above command to grant access to all the relevant schemas. However, you must set up multiple data source connections to the same CockroachDB database to replicate data from multiple schemas.

Grant the user read-only access to the relevant tables:

SQL

GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO zenskar;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> 
GRANT SELECT ON TABLES TO zenskar;

⚙️ Set up CockroachDB as data source in Zenskar

⚙️ Set up data source and type

1. Log into your Zenskar account.
2. In the left navigation bar, navigate to Metering > Data Sources. In the top-right corner, click + ADD DATA SOURCE.
3. In the Set Up Source section of the Add New Data Source page, enter a name for the CockroachDB data source connection.
4. Select CockroachDB from the Source Type dropdown.

⚙️ Configure data source

In the Source Config section of the Add New Data Source page, do the following:

• Connect using SSL: Select Yes if you need an SSL connection.
• Host: The host name or address of a CockroachDB node or load balancer.
• Port: The port number of the SQL interface of the CockroachDB node or load balancer. The default port number for CockroachDB is 26257. Use this value when in doubt.
• DB Name: A database name to use as current database. Defaults to defaultdb when using cockroach client commands. Drivers and ORMs may have different defaults.
• Password: The user's password.
• User: The user's password. It is not recommended to pass the password in the URL directly.
• JDBC URL Parameters (Advanced): Refer this.