This page shows you how to connect to your CockroachCloud cluster.

Step 1. Authorize your network

CockroachCloud requires you to authorize the networks that can access the cluster to prevent denial-of-service and brute force password attacks:

  • In a development environment, you need to authorize your application server’s network and your local machine’s network. If you change your location, you need to authorize the new location’s network, or else the connection from that network will be rejected.
  • In a production environment, you need to authorize your application server’s network.
  • If you have a GCP cluster, you can set up and authorize a VPC peered network.

Add IP addresses to the allowlist

  1. Navigate to your cluster's Networking page.

    The Networking page displays a list of authorized networks (i.e., an IP network allowlist) that can access the cluster.

    Networking page

  2. Check if the current network has been authorized. If not, proceed with the following steps.

  3. Click the Add Network button in the top right corner.

    The Add Network modal displays.

    Add network

  4. (Optional) Enter a descriptive name for the network.

  5. From the Network dropdown, select:

    • New Network to authorize your local machine's network or application server's network. Enter the public IPv4 address of the machine in the Network field.
    • Current Network to auto-populate your local machine's IP address.
    • Public (Insecure) to allow all networks, use 0.0.0.0/0. Use this with caution as your cluster will be vulnerable to denial-of-service and brute force password attacks.

    Note:
    IPv6 addresses are currently not supported.

    To add a range of IP addresses, use the CIDR (Classless Inter-Domain Routing) notation. The CIDR notation is constructed from an IP address (e.g., 192.168.15.161), a slash (/), and a number (e.g., 32). The number is the count of leading 1-bits in the network identifier. In the example above, the IP address is 32-bits and the number is 32, so the full IP address is also the network identifier. For more information, see Digital Ocean's Understanding IP Addresses, Subnets, and CIDR Notation for Networking.

  6. Select whether the network can connect to the cluster's UI, SQL client, or both.

    The UI refers to the cluster's DB Console, where you can observe your cluster's health and performance. For more information, see DB Console Overview.

  7. Click Apply.

Establish a VPC Peering connection

Note:

Self-service VPC peering is a limited-availability feature for GCP clusters. For AWS clusters, contact us.

  1. Navigate to your cluster's Networking page and click Peering.
  2. Click Set up a VPC peering connection.
  3. On the Request a VPC peering connection window, enter your GCP Project ID and GCP VPC network name. In the Connection name field, enter a descriptive name for the VPC connection.
  4. Click Request Connection.
  5. Run the command displayed on the Accept VPC peering connection request window using Google Cloud Shell or using the gcloud command-line tool.
  6. On the Networking page, verify the connection status is Active.

Step 2. Create a SQL user

Note:

Only Console Admins can create SQL users. If you are a Developer, you need to ask your Console Admin for the credentials of a SQL user to access the database. To find out who's your Console Admin, log in and navigate to Cluster Overview > Access.

  1. Navigate to your cluster's SQL Users page.

    SQL users

  2. Click the Add User button in the top right corner.

    The Add User modal displays.

  3. Enter a Username and Password.

    Note:
    Password must be at least 12 characters long.

  4. Click Save.

    Currently, all new users are created with full privileges. For more information and to change the default settings, see Granting privileges and Using roles.

Step 3. Select a connection method

  1. In the top right corner of the Console, click the Connect button.

    The Connect modal displays.

    Connect to cluster

  2. IP Allowlist is selected by default as the Network Security option. Select VPC Peering if you have already:

  3. From the User dropdown, select the SQL user you created in Step 2. Create a SQL user.

  4. From the Region dropdown, select the region closest to where your client or application is running.

  5. From the Database dropdown, select the database you want to connect to.

    The default database is defaultdb. For more information, see Default databases.

  6. Click Continue.

    The Connect tab is displayed.

    Connect to cluster

  7. Select a connection method:

    You can connect to your cluster using the in-built SQL client or using a Postgres-compatible ORM or driver.

    To connect to your cluster using the in-built SQL client, use the command displayed on the CockroachDB Client tab.

    To connect to your cluster using a Postgres ORM or driver, use either the Connection String or the Parameters as required by your ORM or driver.

    Note:

    The connection string allows you to connect to the cluster's regional load balancer. No additional load balancing needs to be implemented on the application side.

  8. Click the name of the ca.crt file to download the CA certificate.

  9. Create a certs directory and move the ca.crt file to the certs directory. The ca.crt file must be available on every machine from which you want to connect to the cluster and must be referenced in the connection string.

    You will need to replace the <certs_dir> placeholders with the path to your certs directory in the CockroachDB client command or the connection string.

Step 4. Connect to your cluster

Use the CockroachDB SQL client

The CockroachDB binary comes with a built-in SQL client for executing SQL statements from an interactive shell or directly from the command line. The CockroachDB SQL client is the best tool for executing one-off queries and performing DDL and DML operations.

On the machine where you want to run the CockroachDB SQL client:

  1. Download the CockroachDB binary:

    For Mac:

    copy
    icon/buttons/copy

    $ curl https://binaries.cockroachdb.com/cockroach-v20.2.2.darwin-10.9-amd64.tgz \
    | tar -xJ
    

    For Linux:

    copy
    icon/buttons/copy

    $ wget -qO- https://binaries.cockroachdb.com/cockroach-v20.2.2.linux-amd64.tgz \
    | tar  xvz
    
  2. Copy the binary into the PATH so it's easy to run the SQL client from any location:

    For Mac:

    copy
    icon/buttons/copy

    $ cp -i cockroach-v20.2.2.darwin-10.9-amd64/cockroach /usr/local/bin/
    

    For Linux:

    copy
    icon/buttons/copy

    $ sudo cp -i cockroach-v20.2.2.linux-amd64/cockroach /usr/local/bin/
    
  3. Use the cockroach sql command to open an interactive SQL shell, replacing placeholders in the client connection method with the correct path to the ca.crt:

    copy
    icon/buttons/copy
    $ cockroach sql \
    --url='postgres://<username>:<password>@<global host>:26257/<database>?sslmode=verify-full&sslrootcert=<path to the CA certificate>'
    

    You can add the --execute flag to run specific SQL statements directly from the command-line:

    copy
    icon/buttons/copy
    $ cockroach sql \
    --url='postgres://<username>:<password>@<global host>:26257/<database>?sslmode=verify-full&sslrootcert=<path to the CA certificate>' \
    --execute="CREATE TABLE accounts (id INT PRIMARY KEY, balance DECIMAL);"
    
  4. Execute some CockroachDB SQL.

Tip:

For more details about the built-in SQL client, and many examples of how to use it, see the cockroach sql documentation.

Use a Postgres driver or ORM

You can use the connection string or parameters to connect to the cluster using a PostgreSQL-compatible driver or ORM. The following language-specific versions assume that you have installed the relevant client drivers.

For code samples in other languages, see Build an App with CockroachDB.

Start by choosing the Python psycopg2 driver or SQLAlchemy ORM:

psycopg2 driver

copy
icon/buttons/copy
# Import the driver.
import psycopg2

# Connect to the database.
conn = psycopg2.connect(
    user='<username>',
    password='<password>',
    host='<host>',
    port=26257,
    database='<database_name>',
    sslmode='verify-full',
    sslrootcert='<path to the CA certificate>'
)

SQLAlchemy ORM

Note:

You must replace the postgres:// prefix with cockroachdb:// in the connection string passed to sqlalchemy.create_engine to make sure the cockroachdb dialect is used. Using the postgres:// URL prefix to connect to your CockroachDB cluster will not work.

copy
icon/buttons/copy

# Create an engine to communicate with the database. The "cockroachdb://" prefix
# for the engine URL indicates that we are connecting to CockroachDB.
engine = create_engine('cockroachdb://<username>:<password>@<host>:26257/<database>?sslmode=verify-full&sslrootcert=<absolute path to CA certificate>')

Start by choosing the Go pq driver or GORM ORM:

Go pq driver

copy
icon/buttons/copy
  //Connect to the database.
    db, err := sql.Open(
        "postgres",
        "postgresql://<username>:<password>@<host>:26257/<database>?sslmode=verify-full&sslrootcert=<absolute path to CA certificate>",
    )
    if err != nil {
        log.Fatal("error connecting to the database: ", err)
    }

GORM

copy
icon/buttons/copy

  // Connect to the database.
    const addr = "postgresql://<username>:<password>@<host>:26257/<database>?sslmode=verify-full&sslrootcert=<absolute path to CA certificate>"
    db, err := gorm.Open("postgres", addr)
    if err != nil {
        log.Fatal(err)
    }

Start by choosing the Node.js pg driver or Sequelize ORM:

Node.js pg driver

copy
icon/buttons/copy
var fs = require('fs');
var pg = require('pg');

// Connect to the database.
var config = {
    user: '<username>',
    password: '<password>',
    host: '<host>',
    database: '<database_name>',
    port: 26257,
    ssl: {
        ca: fs.readFileSync('<path to the CA certificate>')
            .toString()
    }
};

var pool = new pg.Pool(config);

pool.connect(function (err, client, done) {
    // Your code goes here.
    // For more information, see the 'node-postgres' docs:
    // https://node-postgres.com
}

Sequelize ORM

copy
icon/buttons/copy
// Connect to CockroachDB through Sequelize.
var sequelize = new Sequelize('<database_name>', '<username>', '<password>', {
    host: '<host>',
    dialect: 'postgres',
    port: 26257,
    logging: false,
    dialectOptions: {
        ssl: {
            ca: fs.readFileSync('<path to the CA certificate>')
                .toString()
        }
    }
});

Start by choosing the Java JBDC driver or Hibernate ORM:

Java JBDC driver

copy
icon/buttons/copy

// Configure the database connection.
        PGSimpleDataSource ds = new PGSimpleDataSource();
        ds.setServerName("<host>");
        ds.setPortNumber(26257);
        ds.setDatabaseName("<database_name>");
        ds.setUser("<username>");
        ds.setPassword("<password>");
        ds.setSsl(true);
        ds.setSslMode("verify-full");
        ds.setSslCert("<path to the CA certificate>");

Hibernate ORM

copy
icon/buttons/copy

    //Database connection settings
        <property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
         <property name="hibernate.dialect">org.hibernate.dialect.PostgreSQL95Dialect</property>
        <property name="hibernate.connection.url"><![CDATA[jdbc:postgresql://<username>:<password>@<host>:26257/<database>?sslmode=verify-full&sslrootcert=<absolute path to CA certificate]]></property>
        <property name="hibernate.connection.username">username</property>

What's next



YesYes NoNo