cockroachcloud-connect-to-your-cluster.md

title	summary	toc	build_for
Connect to Your CockroachCloud Cluster	Learn how to connect and start interacting with your cluster.	true	cockroachcloud

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.

Use the Console to authorize networks:

1. Navigate to your cluster's Networking page.

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

   

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.

   

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.

     {{site.data.alerts.callout_info}} IPv6 addresses are currently not supported. {{site.data.alerts.end}}

     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 Admin UI, where you can observe your cluster's health and performance. For more information, see Admin UI Overview.

7. Click Save.

Step 2. Create a SQL user

{% include {{ page.version.version }}/cockroachcloud-ask-admin.md %}

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

   

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

   The Add User modal displays.

3. Enter a Username and Password. {{site.data.alerts.callout_info}} Password must be at least 12 characters long. {{site.data.alerts.end}}

4. Click Create.

   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.

   

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

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

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

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

5. Click Continue.

   The Connect tab is displayed.

   

6. 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.

   {{site.data.alerts.callout_info}} 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. {{site.data.alerts.end}}

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

8. 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 the cluster and referenced in 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: {% include copy-clipboard.html %}

   $ curl https://binaries.cockroachdb.com/cockroach-{{ page.release_info.version }}.darwin-10.9-amd64.tgz \
   | tar -xJ

   For Linux: {% include copy-clipboard.html %}

   $ wget -qO- https://binaries.cockroachdb.com/cockroach-{{ page.release_info.version }}.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: {% include copy-clipboard.html %}

   $ cp -i cockroach-{{ page.release_info.version }}.darwin-10.9-amd64/cockroach /usr/local/bin/

   For Linux: {% include copy-clipboard.html %}

   $ sudo cp -i cockroach-{{ page.release_info.version }}.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:

   {% include copy-clipboard.html %}

   $ 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:

   {% include copy-clipboard.html %}

   $ 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.

{{site.data.alerts.callout_success}} For more details about the built-in SQL client, and many examples of how to use it, see the cockroach sql documentation. {{site.data.alerts.end}}

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.

Python Go Java Node.js

Start by choosing the Python psycopg2 driver or SQLAlchemy ORM:

• psycopg2 driver
• SQLAlchemy ORM

psycopg2 driver

{% include copy-clipboard.html %}

# 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

{{site.data.alerts.callout_info}} 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. {{site.data.alerts.end}}

{% include copy-clipboard.html %}

# 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
• GORM

Go pq driver

{% include copy-clipboard.html %}

  //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

{% include copy-clipboard.html %}

  // 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
• Sequelize ORM

Node.js pg driver

{% include copy-clipboard.html %}

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

{% include copy-clipboard.html %}

// 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
• Hibernate ORM

Java JBDC driver

{% include copy-clipboard.html %}

// 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

{% include copy-clipboard.html %}

    //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

• Deploy a Python To-Do App with Flask, Kubernetes, and CockroachCloud