This tutorial shows you how build a simple Node.js application with CockroachDB using a PostgreSQL-compatible driver or ORM.
We have tested the Node.js pg driver and the Sequelize ORM enough to claim beta-level support, so those are featured here. 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-ormsrepository.
Before you begin
- Install CockroachDB.
- Start up a secure or insecure local cluster.
- Choose the instructions that correspond to whether your cluster is secure or insecure:
Step 1. 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 2. Create the maxroach user and bank database
Start the built-in SQL client:
$ cockroach sql --certs-dir=certs
In the SQL shell, issue the following statements to create the maxroach user and bank database:
> CREATE USER IF NOT EXISTS maxroach;
> CREATE DATABASE bank;
Give the maxroach user the necessary permissions:
> GRANT ALL ON DATABASE bank TO maxroach;
Exit the SQL shell:
> \q
Step 3. Generate a certificate for the maxroach user
Create a certificate and key for the maxroach user by running the following command. The code samples will run as this user.
$ cockroach cert create-client maxroach --certs-dir=certs --ca-key=my-safe-directory/ca.key
Step 4. Run the Node.js 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.
Copy the code or download it directly.
var Sequelize = require('sequelize-cockroachdb');
var fs = require('fs');
// Connect to CockroachDB through Sequelize.
var sequelize = new Sequelize('bank', 'maxroach', '', {
dialect: 'postgres',
port: 26257,
logging: false,
dialectOptions: {
ssl: {
ca: fs.readFileSync('certs/ca.crt')
.toString(),
key: fs.readFileSync('certs/client.maxroach.key')
.toString(),
cert: fs.readFileSync('certs/client.maxroach.crt')
.toString()
}
}
});
// Define the Account model for the "accounts" table.
var 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);
});
Then run the code:
$ node sequelize-basic-sample.js
The output should be:
1 1000
2 250
To verify that funds were transferred from one account to another, start the built-in SQL client:
$ cockroach sql --certs-dir=/tmp/certs -e 'SELECT id, balance FROM accounts' --database=bank
+----+---------+
| id | balance |
+----+---------+
| 1 | 1000 |
| 2 | 250 |
+----+---------+
(2 rows)
Step 2. Create the maxroach user and bank database
Start the built-in SQL client:
$ cockroach sql --insecure
In the SQL shell, issue the following statements to create the maxroach user and bank database:
> CREATE USER IF NOT EXISTS maxroach;
> CREATE DATABASE bank;
Give the maxroach user the necessary permissions:
> GRANT ALL ON DATABASE bank TO maxroach;
Exit the SQL shell:
> \q
Step 3. Run the Node.js 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.
Copy the code or download it directly.
var Sequelize = require('sequelize-cockroachdb');
// Connect to CockroachDB through Sequelize.
var sequelize = new Sequelize('bank', 'maxroach', '', {
dialect: 'postgres',
port: 26257,
logging: false
});
// Define the Account model for the "accounts" table.
var 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);
});
Then run the code:
$ node sequelize-basic-sample.js
The output should be:
1 1000
2 250
To verify that the table and rows were created successfully, you can again use the built-in SQL client:
$ cockroach sql --insecure -e 'SHOW TABLES' --database=bank
+------------+
| table_name |
+------------+
| accounts |
+------------+
(1 row)
$ cockroach sql --insecure -e 'SELECT id, balance FROM accounts' --database=bank
+----+---------+
| id | balance |
+----+---------+
| 1 | 1000 |
| 2 | 250 |
+----+---------+
(2 rows)
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:
