Your First Query in Minutes with CockroachDB

So you’re looking for a quick and easy way to get your data in the cloud and ready for your next hack? Well it’s time to squash any anxieties you may have around database set up and get your project scurrying past the competition with CockroachDB (pun clearly intended). If you’re already familiar with relational databases and SQL (Structured Query Language), then getting started should be even more of a cinch. For the uninitiated among us however, not to worry, I’ve got the information you need to take home the ‘Best Use of CockroachDB’ prize category at your next participating MLH hackathon.

Luckily for us, CockroachDB has laid out an intuitive user experience, accounting for any potential bugs in the process. To get you started, I’ll be walking you through signup, creating your first database and data tables, querying your CockroachDB database using Node.JS, and as a bonus, I’ll be setting up an Express server and exposing my endpoints for local access. So if you’re looking for an easy to use and reliable database service and a guide on how to get started, then you’ve come to right place. CockroachDB is an elastic, indestructible, Postgres compatible SQL database that can ensure fast and accurate writes & reads, and can be instantly deployed using Cockroach Cloud.

Registration

The first thing you need to do is head over to https://hackp.ac/CockroachDB and submit your email address to get started.

Just a quick note about signup. After you submit your information, a verification email will be sent to your inbox and from there, you’ll be forwarded over to the database set up page.

Once you’re at the set up page, you can select your cloud provider, region, the number of nodes you require, hardware type and the cluster name. Bear in mind that a ‘free 30 day trial’ email should now be sitting in your inbox as well. Save that for later.

Moving along with the process, once you’ve added your trial code and payment method, you’ll be able to hit the ‘Create cluster’ button and get started with administering your brand new database.

As you can see I’m using the CRDB30 trial code to claim my first month of free access.

Setting Up Your Cluster

While your cluster is spinning up, feel free to check out any of the links to some of Cockroach’s documentation. Once your dashboard is up and running, you can check out the overview and start customizing your cluster by adding an SQL user, authorizing & adding a network name and finally monitoring your cluster activity. One of the most unique features of CockroachDB is that it natively starts as a cluster, meaning you can create and manage multiple database nodes using a single dashboard, secure IP and SQL user. Additionally, as your data expands, you can check all node activity by monitoring your single cluster.

You can find most of the important UI features on the left hand side, starting with Overview.

Adding a user is as simple as creating a username and password. Similarly, adding and authorizing a network name only requires you to specify the authorized network IP address and give it a reference name.

A username is needed to gain database access, both through the Cockroach UI and our application.

Note: When you’re working through this process, your current network will be displayed in the text field.

To monitor your cluster activity, just head over to the ‘Monitoring’ tab and select the ‘Open Admin UI’ button.

You’ll find a robust suite of features and data breakdowns here. Something to bear in mind once you have your database set up and ready for your writes and reads. With that being said, let’s dive into database setup and how to get started with writing some SQL queries of your own.

Connecting to your Database

You may have noticed the ‘connect’ button at the top right of your cluster overview page.

This is how you will be connecting to your database using the CockroachDB Client. Before we get to that, let’s go ahead and set up our connection credentials by inputting the user name we made earlier along with the database we are looking to make changes and updates to. You’ll also notice that this modal prompts you to authorize your network IP if you haven’t already done so from our previous steps.

For security reasons, we need valid SQL user permissions in order to access our database. If you are familiar with PostgreSQL, you will recall that in order to access data objects within the management system, you will have needed to use the GRANT command syntax to give users specific permissions. Here, CockroachDB has simplified the process by allowing you to create SQL users through the UI and grant permissions to specific databases during the connection set up process as pictured below.

Depending on the number of nodes you have in this cluster, you can select the specific database you are trying to access through the drop-down menu.

The connection piece may be a little confusing if you’re not familiar with cURL, but Cockroach Labs has made the process simple by providing you with a step by step break-down. I’ll explain each step here for even more clarity.

Once we’ve got our connection setup complete, we can download the CA certificate to our download folder. If you have a project directory that you want to move the file into, feel free to do so, but ensure that you keep the file path handy for later in the process.

You can now open up your terminal and run the appropriate cURL command based on your operating system by selecting the copy option and pasting directly into your command line.

Once the CDRB client is downloaded, you can finally connect to the database URL using the file path to the CA certificate. As you can see, the terminal command has been customized to reflect the User, Region and Database information we provided in the first step of the setup process. You can now simply use the copy option in the 3rd connection step and replace <your_certs_directory> with the file path for your CA certificate.

You’ll be prompted for your username password (the one you specified during cluster set up) and once you’ve successfully submitted it, you can create new databases and new tables as you see fit.

Leveraging Your Database

Now that we have established a connection to CockroachDB, I want to walk you through querying that database from your own application. For this example, I’ll be using Node to establish a pool connection with CockroachDB and Express to broadcast my query results. (If you’ve never used SQL before, I cover the basics of creating a data table in the next section. Feel free to jump ahead and come back once you’re all caught up).

First thing you’ll need to do is go back to your CockroachDB overview and click on that good ol’ connect button. Here it is again in case you’ve forgotten what it looks like:

Select the ‘Your tool’ tab to retrieve all the pedigree information associated with your database. I’m simply going to take that information and plug it into my script.js file as pictured below:

I’ve gone ahead and created a function that stores my SQL query and have it exporting to my index.js file as getUsers.

Here on line 16, I am creating an endpoint that broadcasts the response from my getUsers function to localhost:3000/users.

When I execute my index.js file, I receive an affirmative response stating that my app is running on the desired port and when I visit localhost:3000/users, I get a readout of all my users table content.

Heck yes! If you want to take a look at how I populated that data into my database, I’ve got you covered in the next section.

A Brief Guide to SQL

If you’ve never used SQL before, have no fear, Cockroach Labs has some great documentation you can take a look at to get started with the command and query syntax. Below you’ll find a breakdown of how I went about creating a User table with an auto-generated ID for each row.

First thing’s first, you can actually move between databases within the CRDB client console. If you have more than one database active, you can use the following command to navigate between instances.

SET DATABASE = your_database_name;

Keep in mind that this command won’t work unless the alternate database actually exists. Now that you’re inside your desired database, you should see the database name noted before your blinking cursor. From here, we can create a new table.

CREATE TABLE users (

id UUID NOT NULL DEFAULT gen_random_uuid(),

first STRING NOT NULL,

last STRING NOT NULL,

CONSTRAINT “primary” PRIMARY KEY (id)

);

Now this may seem like a lot is going on, but rest assured it’s fairly simple when we break it down line by line. First off, if you haven’t already noticed, I am writing my SQL commands in ALL CAPS as a way of differentiating that language from my column names, this is optional but good practice. In this case, I am creating a table named users and my table has 3 columns: id, first & last.

The first line of this command is creating a table named users which will contain all columns indicated within the following parentheses. Going further, my first column is named id and is set to the datatype of UUID, which is a universally unique identifier. Additionally, the NOT NULL command also specifies that this column cannot be empty. Finally, I am using a built in formula, gen_random_uuid(), and prefacing it with DEFAULT in order to ensure that whenever a new row of data is inserted into my users table, it has a randomly generated uuid set as its default id value.

The first and last columns are fairly self explanatory, denoting STRING data type. Once again, NOT NULL means that these columns cannot be left empty.

The last line of our command is setting our table CONSTRAINT as a PRIMARY KEY, which is essentially setting the column that will be used to connect our users data table to any other data table inside of our database. A CONSTRAINT in SQL is simply a rule for data, similar to the NOT NULL commands we have appended to our columns. In this case, the id will serve as our PRIMARY KEY and become a reference for any user-related data we decide to populate our database with.

Speaking of which, let’s go ahead populate our data table with some new rows of data now. Here, we can actually insert multiple rows in the same SQL command and simply separate our entries with a comma.

INSERT INTO users (first, last)

VALUES (‘Rosendo’, ‘Pili’), (‘Michael’, ‘Jordan’), (‘Lebron’, ‘James’);

Now that we’ve gotten that out of the way, let’s take a look at how our database is shaping up. Run the SELECT * FROM users; command in order to display all the content from your users table.

Which one of us is the GOAT?

As you can see, we did not have to manually input our data fields and all of our rows still have randomly generated UUIDs as their id.

Awesome! We now have a users data table with 3 rows of data and a database fully deployed to the cloud!

In Summary

Along with your 30-day free cluster on CockroachDB, a fully deployed database and functioning data table, you should now have an application that publishes your users table data in JSON format. You should now be well on your way to securing that “Best Use of CockroachDB” prize at your participating MLH hackathon!

If you want even more examples of how developers are using CockroachDB in their applications, look no further than the Cockroach Labs forum and developer center. Get a better idea of the exciting ways engineers are taking advantage of the elastic and indestructible SQL database known as CockroachDB and discuss your findings with their friendly and knowledgeable community.

Until next time! Happy Hacking.