This tutorial shows you how build a simple Node.js application with CockroachDB and the Sequelize ORM.
We have tested the Sequelize ORM enough to claim beta-level support. If you encounter problems, please open an issue with details to help us make progress toward full support.
For a more realistic use of Sequelize with CockroachDB, see our examples-orms
repository.
Step 1. Start CockroachDB
Choose whether to run a temporary local cluster or a free CockroachDB cluster on CockroachDB Serverless. The instructions below will adjust accordingly.
Create a free cluster
Organizations without billing information on file can only create one CockroachDB Serverless cluster.
- If you haven't already, sign up for a CockroachDB Cloud account.
- Log in to your CockroachDB Cloud account.
- On the Clusters page, click Create Cluster.
- On the Select a plan page, select Serverless.
- On the Cloud & Regions page, select a cloud provider (GCP or AWS) in the Cloud provider section.
- In the Regions section, select a region for the cluster. Refer to CockroachDB Cloud Regions for the regions where CockroachDB Serverless clusters can be deployed. To create a multi-region cluster, click Add region and select additional regions. A cluster can have at most six regions.
- Click Next: Capacity.
- On the Capacity page, select Start for free. Click Next: Finalize.
On the Finalize page, click Create cluster.
Your cluster will be created in a few seconds and the Create SQL user dialog will display.
Set up your cluster connection
Once your cluster is created, the Connect to cluster-name dialog displays. Use the information provided in the dialog to set up your cluster connection for the SQL user that was created by default:
In your terminal, run the second command from the dialog to create a new
certs
directory on your local machine and download the CA certificate to that directory:curl --create-dirs -o ~/.postgresql/root.crt -O https://cockroachlabs.cloud/clusters/<cluster-id>/cert
Your
cert
file will be downloaded to~/.postgresql/root.crt
.curl --create-dirs -o ~/.postgresql/root.crt -O https://cockroachlabs.cloud/clusters/<cluster-id>/cert
Your
cert
file will be downloaded to~/.postgresql/root.crt
.mkdir -p $env:appdata\.postgresql\; Invoke-WebRequest -Uri https://cockroachlabs.cloud/clusters/<cluster-id>/cert -OutFile $env:appdata\.postgresql\root.crt
Your
cert
file will be downloaded to%APPDATA%/.postgresql/root.crt
.Copy the connection string provided, which will be used in the next steps (and to connect to your cluster in the future).
Warning:This connection string contains your password, which will be provided only once. If you forget your password, you can reset it by going to the SQL Users page for the cluster, found at
https://cockroachlabs.cloud/cluster/<CLUSTER ID>/users
.cockroach sql --url 'postgresql://<username>:<password>@<serverless-host>:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt'
cockroach sql --url 'postgresql://<username>:<password>@<serverless-host>:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt'
cockroach sql --url "postgresql://<username>:<password>@<serverless-host>:26257/defaultdb?sslmode=verify-full&sslrootcert=$env:appdata/.postgresql/root.crt"
Where:
<username>
is the SQL user. By default, this is your CockroachDB Cloud account username.<password>
is the password for the SQL user. The password will be shown only once in the Connection info dialog after creating the cluster.<serverless-host>
is the hostname of the CockroachDB Serverless cluster.<cluster-id>
is a unique string used to identify your cluster when downloading the CA certificate. For example,12a3bcde-4fa5-6789-1234-56bc7890d123
.
You can find these settings in the Connection parameters tab of the Connection info dialog.
- If you haven't already, download the CockroachDB binary.
Run the
cockroach demo
command:$ cockroach demo \ --no-example-database
This starts a temporary, in-memory cluster and opens an interactive SQL shell to the cluster. Any changes to the database will not persist after the cluster is stopped.
Note:If
cockroach demo
fails due to SSL authentication, make sure you have cleared any previously downloaded CA certificates from the directory~/.postgresql
.Take note of the
(sql)
connection string in the SQL shell welcome text:# Connection parameters: # (webui) http://127.0.0.1:8080/demologin?password=demo76950&username=demo # (sql) postgres://demo:demo76950@127.0.0.1:26257?sslmode=require # (sql/unix) postgres://demo:demo76950@?host=%2Fvar%2Ffolders%2Fc8%2Fb_q93vjj0ybfz0fz0z8vy9zc0000gp%2FT%2Fdemo070856957&port=26257
Step 2. Create a database
In the SQL shell, create the
bank
database that your application will use:> CREATE DATABASE bank;
Create a SQL user for your app:
> CREATE USER <username> WITH PASSWORD <password>;
Take note of the username and password. You will use it in your application code later.
Give the user the necessary permissions:
> GRANT ALL ON DATABASE bank TO <username>;
- If you haven't already, download the CockroachDB binary.
Start the built-in SQL shell using the connection string you got from the CockroachDB Cloud Console earlier:
$ cockroach sql \ --url='postgres://<username>:<password>@<global host>:26257/<cluster_name>.defaultdb?sslmode=verify-full&sslrootcert=<certs_dir>/cc-ca.crt'
In the connection string copied from the CockroachDB Cloud Console, your username, password and cluster name are pre-populated. Replace the
<certs_dir>
placeholder with the path to thecerts
directory that you created earlier.In the SQL shell, create the
bank
database that your application will use:> CREATE DATABASE bank;
Exit the SQL shell:
> \q
Step 3. Install the Sequelize ORM
To install Sequelize, as well as a CockroachDB Node.js package that accounts for some minor differences between CockroachDB and PostgreSQL, run the following command:
$ npm install sequelize sequelize-cockroachdb
Step 4. Get the code
Download the sample code directly, or clone the code's GitHub repository.
Step 5. Update the connection parameters
Open app.js
, and edit the connection configuration parameters:
- Replace the value for
username
with the user you created earlier. - Replace the value for
password
with the password you created for your user. - Replace the value for
port
with the port to your cluster.
At the top of the file, uncomment the
const fs = require('fs');
line.This line imports the
fs
Node module, which enables you to read in the CA cert that you downloaded from the CockroachDB Cloud Console.Replace the value for
username
with the user you created earlier.Replace the value for
password
with the password you created for your user.Replace the value for
host
with the name of the CockroachDB Cloud Free host (e.g.,host: 'free-tier.gcp-us-central1.cockroachlabs.cloud'
).Replace the value for
port
with the port to your cluster.Replace the value for
database
with the database that you created earlier, suffixed with the name of the cluster (e.g.,database: '{cluster_name}.bank'
).Remove the
rejectUnauthorized
key-value pair.Uncomment the
ca
key-value pair, and edit thefs.readFileSync('certs/ca.crt').toString()
call to use the path to thecc-ca.crt
file that you downloaded from the CockroachDB Cloud Console.
Step 6. Run the code
The following code uses the Sequelize ORM to map Node.js-specific objects to SQL operations. Specifically, Account.sync({force: true})
creates an accounts
table based on the Account model (or drops and recreates the table if it already exists), Account.bulkCreate([...])
inserts rows into the table, and Account.findAll()
selects from the table so that balances can be printed.
const Sequelize = require("sequelize-cockroachdb");
// Connect to CockroachDB through Sequelize.
const connectionString = process.env.DATABASE_URL
const sequelize = new Sequelize(connectionString, {
dialectOptions: {
application_name: "docs_simplecrud_node-sequelize"
}
});
// Define the Account model for the "accounts" table.
const Account = sequelize.define("accounts", {
id: {
type: Sequelize.INTEGER,
primaryKey: true,
},
balance: {
type: Sequelize.INTEGER,
},
});
// Create the "accounts" table.
Account.sync({
force: true,
})
.then(function () {
// Insert two rows into the "accounts" table.
return Account.bulkCreate([
{
id: 1,
balance: 1000,
},
{
id: 2,
balance: 250,
},
]);
})
.then(function () {
// Retrieve accounts.
return Account.findAll();
})
.then(function (accounts) {
// Print out the balances.
accounts.forEach(function (account) {
console.log(account.id + " " + account.balance);
});
process.exit(0);
})
.catch(function (err) {
console.error("error: " + err.message);
process.exit(1);
});
To run the code:
$ node app.js
The output should be:
1 1000
2 250
What's next?
Read more about using the Sequelize ORM, or check out a more realistic implementation of Sequelize with CockroachDB in our examples-orms
repository.
You might also be interested in the following pages: