Sample Apps Built on CockroachDB
Check out a library of sample applications built on CockroachDB
See Sample AppsAs cloud-native web developers, we want to build apps that scale easily. Databases are often a pain point, though. We want the structure and data integrity of relational databases like PostgreSQL, but we also want the easy scalability of NoSQL databases like MongoDB. Until recently, we’ve been forced to choose one or the other.
Fortunately, CockroachDB provides the best of both worlds. It’s a robust relational database with full SQL support and all of the features you’d expect in an enterprise-ready database. It supports the PostgreSQL wire protocol, so you can use it with most tools and libraries that support Postgres. But that’s not all: it offers excellent scalability, so you no longer have to choose between relational data integrity or NoSQL scalability. And, if you use CockroachDB Serverless, you don’t have to worry about hosting and scaling a database yourself — so you can focus your efforts on building great apps.
This tutorial will show you how to start from scratch and build a modern cloud-native web app using TypeScript, React, Prisma, Netlify serverless functions, and CockroachDB.
The CockroachDB & Typescript Application
We’ll be building a simulated gaming leaderboard. Here’s a peek at how the final app will look:
We’ll create our entire application — both back end and front end — in TypeScript.
We’ll build the user interface using React with a project generated using Create React App. The app will have two main screens: the leaderboard shown above, and an admin screen that lets us add new leaderboard entries.
We’ll start by storing all of our gamer and leaderboard data in a CockroachDB Serverless database. We’ll host the entire app on Netlify and use Netlify serverless functions to read from and write to our database. Finally, we’ll use the terrific Prisma ORM to provide type-safe access to our data from TypeScript.
Let’s dive in and get started! If you’d like to see the complete code for the app, you can find it here on GitHub. Instead of showing every single line of code as we work through this tutorial, we’ll explain the steps you’d need to follow to create the app and database, and then highlight the key parts of the code you’ll need to understand from the repository.
Application Development Prerequisites
Before going any further, make sure you have installed the latest LTS build of Node.js. You can find installers for Windows, macOS, and Linux on the official Node.js download page.
Throughout the tutorial, I’ll assume you’ve worked with React before and you’re comfortable with building your React components. I recommend the official React getting started tutorial if you’re new to React.
Finally, you should know and understand modern JavaScript. It also helps if you already know TypeScript, but it’s okay if you don’t. TypeScript is a superset of JavaScript, so you can follow along and pick it up as you go. This isn’t a TypeScript tutorial, though, so I recommend the official TypeScript language tutorial if you’d like an in-depth introduction.
Creating the Project
Netlify has built-in support for apps generated by Create React App, so that’s what we’ll use to generate our project structure. Open a terminal or command prompt, switch to the directory where you usually keep your projects, and run the following command:
npx create-react-app cockroachdb-typescript --template typescript
Create React App will take a few minutes to run. When it finishes, you’ll end up with the following directory structure:
cockroachdb-typescript
├── node_modules
├── public
└── src
cockroachdb-typescript will be our main project directory — so for the remainder of the tutorial, make sure you’re in the cockroachdb-typescript directory when you run terminal commands.
Create React App also generated several files in the public and src subdirectories. We’ll revisit those later when we build out our app’s React UI. But first, let’s create a database and serverless functions our UI can interact with.
Creating a CockroachDB Serverless Database
If we want to create a leaderboard, we’ll need a place to store our data. We’re going to use CockroachDB Serverless to keep things simple. CockroachDB Serverless spins up a CockroachDB cluster in the cloud so we won’t need to worry about installing, maintaining, or scaling our database. Best of all, setup is quick and easy:
- If you haven’t already, sign up for a CockroachDB cloud account.
- Log in to your CockroachDB account.
- On the Clusters page, select Create Cluster.
- On the Create your cluster page, select Serverless.
- Select Create your free cluster.
Once you complete your account registration and create a new cluster, CockroachDB presents you with a connection string like the one below:
Note that CockroachDB only provides your password once, so make note of it after selecting REVEAL_PASSWORD. Copy the password and save it in a secure location, because we’ll need it later.
Next, select the Connect button near the top right of the CockroachDB Serverless dashboard.
You’ll see three sets of instructions - one to install the CockroachDB client, one to install the CA certificate, and one to connect to your CockroachDB cluster. The exact instructions will vary depending on what operating system you’re running. On a Mac, you’ll see something like this:
Run the first two sets of commands to download the CockroachDB client and the CA certificate. Keep the third command accessible — we’ll need it in a moment, along with the password we revealed earlier.
Creating Database Tables and Setting Up Prisma
With the cluster created, it’s time to set up the tables we’ll need for our leaderboard and configure the Prisma ORM.
Prisma comes with a migration tool that can automatically create and update your database tables — but it isn’t compatible with CockroachDB just yet. That’s okay, though! We can create the tables we need ourselves with a SQL script.
You can find it in the completed github project in database/schema.sql. Here’s how it looks:
DROP DATABASE IF EXISTS leaderboard;
CREATE DATABASE leaderboard;
USE leaderboard;
CREATE TABLE players (
id SERIAL,
name TEXT NOT NULL,
email TEXT NOT NULL,
CONSTRAINT "players_pkey" PRIMARY KEY (id ASC)
);
CREATE TABLE player_scores (
id SERIAL,
player_id INTEGER NOT NULL,
score INTEGER NOT NULL,
CONSTRAINT "player_scores_pkey" PRIMARY KEY (id ASC),
CONSTRAINT fk_player FOREIGN KEY (player_id) REFERENCES players(id)
);
CREATE INDEX player_scores_player_id on player_scores(player_id);
CREATE INDEX player_scores_score on player_scores(score);
INSERT INTO players (name, email) VALUES ('Test Player 1', 'test_player_1@example.com');
INSERT INTO players (name, email) VALUES ('Test Player 2', 'test_player_2@example.com');
INSERT INTO players (name, email) VALUES ('Test Player 3', 'test_player_3@example.com');
INSERT INTO players (name, email) VALUES ('Test Player 4', 'test_player_4@example.com');
INSERT INTO players (name, email) VALUES ('Test Player 5', 'test_player_5@example.com');
The script begins by creating a new database, then creates tables to store players and play scores, then creates indexes to ensure the app we create can look up and sort player scores quickly.
Note that in addition to creating the tables, I’ve also added a few sample players. I’ve done this because creating players is beyond the scope of a typical leaderboard app. Most leaderboards focus on creating and displaying entries for existing players.
We’ll use the following command to run the Cockroach SQL client and execute our script. We’ve seen it before: It’s the third command we saw before when we viewed our connection information on the CockroachDB dashboard. All you need to do is add -f schema.sql to the end. It’ll look something like this:
cockroach sql --url 'postgresql://**<your-db-username>:<your-db-password>**@free-tier.gcp-us-central1.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt&options=--cluster%3D**<your-database-name>**' -f schema.sql
Note that you’ll need to insert your database’s actual username / password / name before you run the command.
The output should look like this:
CREATE TABLE
Time: 98ms
CREATE TABLE
Time: 402ms
CREATE INDEX
Time: 1.164s
CREATE INDEX
Time: 819ms
And that’s all it takes to create a gaming leaderboard database with CockroachDB.
Before we can use Prisma, we have to ask Prisma to generate its own schema file based on our database tables.
Prisma expects a file called schema.prisma in the prisma subdirectory of your project. You can find the complete file on GitHub here. If you’re starting from scratch, you don’t need the entire file. Instead, you can start with the first two sections:
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
Here, you can see that Prisma expects to find a connection string in the DATABASE_URL environment variable. Creating a .env file in our project’s root directory is the easiest way to provide Prisma with the connection string it expects. The .env file should have a single line:
DATABASE_URL= postgresql://<username>:<password>@<host>:<port>/<database>.leaderboard
You can find the username, host, port, and database values by selecting the Connect button in your CockroachDB Serverless dashboard and then opening the Connection parameters tab:
Note that the database name in the dashboard will end with .defaultdb. When entering it in your .env file (and on Netlify), change .defaultdb to .leaderboard to ensure that our app connects to the leaderboard database created when we ran schema.sql earlier.
Although it’s not generally a good idea to commit a .env file to source control, you can make the connection string available to Prisma after we’ve deployed to Netlify by setting an environment variable in the Netlify dashboard. We’ll cover that when we get to it.
Once you’ve added the connection string to the .env file, run the following commands from the project’s root directory:
npm install --save @prisma/client@3.6.0
npx prisma db pull
Prisma will then introspect the database and generate the rest of its schema:
MacBook-Air-2018
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "angry-porcupine-3367.defaultdb", schema "public" at "free-tier.gcp-us-central1.cockroachlabs.cloud:26257"
Introspecting based on datasource defined in prisma/schema.prisma …
✔ Introspected 2 models and wrote them into prisma/schema.prisma in 3.30s
Run prisma generate to generate Prisma Client.
If you look at schema.prisma now, you’ll see that Prisma has added schema information it learned during introspection:
model player_scores {
id BigInt @id(map: "primary") @default(autoincrement())
player_id BigInt
score BigInt
players players @relation(fields: [player_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "fk_player")
@@index([player_id], map: "player_scores_player_id")
@@index([score], map: "player_scores_score")
}
model players {
id BigInt @id(map: "primary") @default(autoincrement())
name String
email String
player_scores player_scores[]
}
Next, run npx prisma generate. Prisma will create and provide usage instructions for a custom TypeScript client tailored to your database schema.
With that, we’ve created a gaming leaderboard database and generated a fully type-safe client to access it.
Creating Serverless Functions and Deploying to Netlify
We’re down to our final steps: creating Netlify functions so our React front-end can communicate with our CockroachDB database, and then deploying the app to Netlify.
Let’s start by installing the Netlify functions helper library for TypeScript. Run the following command in the project’s root directory:
npm install --save @netlify/functions
Netlify will automatically recognize our functions if we place them in a netlify/functions subdirectory of our main project directory, so let’s do exactly that.
If you’re creating your functions from scratch instead of using the code from GitHub, create a netlify subdirectory in the project’s root directory, and then create a functions directory inside it. Create three TypeScript Netlify functions for the leaderboard app: getScores.ts, addScore.ts, and getPlayers.ts, as shown here:
cockroachdb-typescript
├── .netlify
├── database
├── netlify
│ └── functions
│ ├── addScore.ts
│ ├── getPlayer.ts
│ └── getScores.ts
├── node_modules
└── prisma
Let’s look at how they work, starting with getScores.ts:
import { Handler } from '@netlify/functions'
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient();
const handler: Handler = async (event, context) => {
// load all scores from the database and include player name
// from the players table.
const allScores = await prisma.player_scores.findMany({
include: {
players: {
select: {
name: true
}
}
},
orderBy: {
score: 'desc'
}
});
return {
statusCode: 200,
body: JSON.stringify(allScores.map(score => (
// flatten player name into score entry
{ id: score.id, name: score.players.name, score: score.score }
))
, (key, value) =>
// need to add a custom serializer because CockroachDB IDs map to
// JavaScript BigInts, which JSON.stringify has trouble serializing.
typeof value === 'bigint'
? value.toString()
: value
)
}
}
export { handler }
This is the most complex of our Netlify functions, so let’s focus on a couple of key areas. First, note the call to prisma.scores.findMany, where we’re using the type-safe client Prisma generated for us.
We’re also passing an object as a parameter with two properties: include and orderBy. orderBy works as expected: it ensures we’ll receive a list of top scores sorted in descending order by score. The include property instructs Prisma to join the player_scores table to the players table so we can include player names on the leaderboard.
Once getScores.ts returns the scores via JSON, we flatten the object. Otherwise, the player’s name would appear in a nested object, like so:
{
id: "70354",
score: 250
"players": {
"name": "Player 1"
}
}
After flattening the data, the object looks like this:
{
id: "70354",
score: 250,
name: "Player 1"
}
We could work with the nested object in our React front-end easily enough. But it’s good practice to reformat the data server-side because, in a production application, server-side and client-side code is often written by different teams. Our front-end code shouldn’t need to know about low-level implementation details like this. We also provide a custom JSON serializer function. This is important because many CockroachDB ID fields are large enough that JavaScript translates them into a BigInt, which breaks JSON.strinfigy’s default serialization behavior.
Next, let’s look at the addScore.ts function:
import { Handler } from '@netlify/functions'
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient();
interface ScoreEntry {
playerId: string,
score: string
}
const handler: Handler = async (event, context) => {
if(event.body) {
const newScore = JSON.parse(event.body) as ScoreEntry;
await prisma.player_scores.create({
data: {
player_id: BigInt(newScore.playerId),
score: parseInt(newScore.score)
},
});
return {
statusCode: 200,
body: JSON.stringify(newScore)
};
}
return {
statusCode: 500
};
}
export { handler }
We pull the player ID and score from the body of the POST request our React front end will send. Note that we’ve created a TypeScript interface named ScoreEntry to let the compiler know the shape of the data we’ll get when we parse the request body. We’re also converting the player ID back to a BigInt when we call prisma.player_scores.create.
Finally, we have getPlayers.ts. It reads all players in the database so our React front end will be able to generate a player list to let us select for which player we want to add a new leaderboard score on our admin page:
import { Handler } from '@netlify/functions'
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient();
const handler: Handler = async (_event, _context) => {
// load all players from the database
const allPlayers = await prisma.players.findMany();
return {
statusCode: 200,
body: JSON.stringify(allPlayers, (_key, value) =>
// need to add a custom serializer because CockroachDB IDs map to
// JavaScript BigInts, which JSON.stringify has trouble serializing.
typeof value === 'bigint'
? value.toString()
: value
)
}
}
export { handler }
Creating a Front End Using React and Typescript
Our leaderboard needs a user interface. Earlier, we used Create React App to generate our project directory, so many of the pieces we need are already in place.
We’re going to use React Router to navigate between pages in our app. Install it now by running the following in your project’s root folder to install both React Router and its TypeScript type definitions:
npm install --save react-router-dom@6.1.1
In the GitHub repository, I’ve added Bootstrap 5 to the project’s public/index.html file to make the UI more visually appealing by adding the following line to the <head> section of public/index.html:
<link
href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css"
rel="stylesheet"
integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3"
crossorigin="anonymous">
You can, of course, use Tailwind or any other CSS framework if you prefer.
Setting up React Router is beyond the scope of this article, so take a look at the finished application’s src/App.tsx file here on GitHub if you’re curious about how it works.
Open the src subfolder, where you’ll see the following structure:
├── src
├── App.css
├── App.test.tsx
├── App.tsx
├── index.css
├── index.tsx
├── logo.svg
├── react-app-env.d.ts
├── reportWebVitals.ts
└── setupTests.ts
The two key parts of our Leaderboard go in two new component files in the src directory: Leaderboard.tsx and Admin.tsx.
Let’s start by looking at Leaderboard.tsx:
import React, { useState, useEffect } from 'react';
import { Link } from "react-router-dom";
interface Leader {
id: number,
name: string,
score: number
}
function renderLeader(leader: Leader) {
return <tr key={leader.id}>
<td>{leader.name}</td>
<td>{leader.score}</td>
</tr>
}
export default function Leaderboard() {
const [leaders, setLeaders] = useState([] as Leader[]);
useEffect(() => {
fetch('/.netlify/functions/getScores')
.then(response => response.json() as Promise<Leader[]>)
.then(data => setLeaders(data));
}, []);
return <>
<h2>Leaderboard</h2>
{leaders.length === 0 ?
<div>No leader scores to display. Would you like to <Link to="admin">add one</Link>?</div>
:
<table className="table leader-table">
<thead>
<tr>
<th>Name</th>
<th>Score</th>
</tr>
</thead>
<tbody>
{leaders.map(l => renderLeader(l))}
</tbody>
</table>
}
</>
}
We start by creating a Leader interface to let TypeScript know what a leaderboard entry looks like. Then, the renderLeader helper function generates rows for the leaderboard table.
The rest of the Leaderboard is a standard React function component. Note the useEffect hook, as it’s the piece that makes the component work. In this case, the hook uses the browser’s built-in Fetch API to call the Netlify function we created earlier, load the leaderboard scores, and update the leaderboard’s state by calling setLeaders. This in turn causes React to re-render the component and display the leaderboard.
Next, let’s look at the Admin.tsx component, which makes it possible to add new leaderboard entries:
import React, {useEffect, useState} from 'react';
interface Player {
id: string,
name: string,
}
export default function Admin() {
const [players, setPlayers] = useState([] as Player[]);
const [score, setScore] = useState("");
const [message, setMessage] = useState("");
const [selectedPlayer, setSelectedPlayer] = useState<Player | undefined>(undefined);
useEffect(() => {
fetch('/.netlify/functions/getPlayers')
.then(response => response.json() as Promise<Player[]>)
.then(data => {
setPlayers(data);
if(data && data.length > 0) {
setSelectedPlayer(data[0]);
}
});
}, []);
const selectPlayer = (players: HTMLSelectElement) => {
setSelectedPlayer({
id: players.value,
name: players.options[players.selectedIndex].text
});
}
const addScore = () => {
const scoreEntry = {
playerId: selectedPlayer?.id,
score: score
}
fetch("/.netlify/functions/addScore",
{
headers: {
'Accept': 'application/json',
'Content-Type': 'application/json'
},
method: "POST",
body: JSON.stringify(scoreEntry)
})
.then(function(res){
setMessage(`Leaderboard score of ${score} added for player ${selectedPlayer?.name}`);
})
.catch(function(res){
setMessage(`Unable to add score for player ${selectedPlayer?.name}`);
})
}
return <>
<h2>Admin</h2>
<h4>Add a Leaderboard Entry</h4>
{players.length === 0 ?
<div>Loading Players...</div>
:
<>
<div className="mb-3 score-entry">
<label>Player</label>
<select
className="form-select"
aria-label="player selection"
value={selectedPlayer?.id}
onChange={e => selectPlayer(e.target)}>
{players.map(p => <option key={p.id} value={p.id}>
{p.name}
</option>)}
</select>
</div>
<div className="mb-3 score-entry">
<label>Score</label>
<input type="text"
className="form-control"
aria-label="score entry"
value={score}
onChange={e => setScore(e.target.value)}/>
</div>
<div>
<button className="btn btn-primary" onClick={addScore}>Add Score</button>
</div>
</>
}
<div className="admin-message">{message}</div>
</>
}
This one is a bit more complex, but it’s nothing we can’t handle. It starts out similarly to the leaderboard component: by calling a Netlify function inside an effect hook to load data and update the React component.
Once the player list has loaded, the component renders a select box and a text box, allowing users of the app to pick a player, enter a score, and then press a button to add the player’s leaderboard score entry to the database via the addScore function. This function takes the data entered on the page and uses the Fetch API to call the back-end Netlify function we examined earlier.
The only thing we’re missing is authentication. Here, we’ve omitted it to keep things simple. But in a production app, you’ll want to use something like Netlify Identity to prevent strangers from adding fake entries to your leaderboard.
Deploy the Application to Netlify
Now all that remains is to deploy to Netlify. While an end-to-end Netlify guide is beyond the scope of this tutorial, there are a few simple steps that will quickly get you up and running.
Start by taking all the code you’ve written — or if you’d prefer, just fork the completed code for this project — and push it to your own GitHub repository. Then, sign in to Netlify using GitHub login.
Once you are signed in you’ll see a New site from Git button on the Netlify dashboard:
Select this button to create a site directly from your GitHub repository. It’ll even run Create React App to build your front end, and then automatically deploy your Netlify functions.
Once your app is up and running, Netlify will show you the URL to use to access it:
There’s a few more things we’ll need to do — remember the DATABASE_URL environment variable I asked you to set earlier for Prisma? We’ll need a way to provide that variable to our functions deployed on Netlify.
Netlify makes this easy. From your app’s dashboard, select Site settings, then in the menu on the left, select Build & deploy, and then select Environment. This lets you set the DATABASE_URL environment variable:
Enter the same connection string you used earlier when setting up the Prisma schema.
And one final step. Netlify bundles the environment variables into each of the functions when it deploys our app. However, Netlify deployed our app before we set the DATABASE_URL environment variable, so if we try to run our app now we’ll get an ugly error message. Fortunately, all we need to do is trigger a new deployment.
From our Netlify app’s dashboard, click on Production Deploys. Then, when the next screen loads, click on the Trigger deploy drop-down and choose Deploy site.
Wait for the deploy to finish, then navigate to your app’s Netlify URL. You won’t see any player scores yet, so click on the Admin link in the navbar and add some.
Once you’ve deployed to Netlify, you can also use the Netlify CLI’s netlify dev command to run your entire app locally so you won’t need to deploy to test out changes to your serverless functions.
Conclusion
And that’s it! While there was quite a bit of boilerplate to get through, we’re now in a position where we can build great React apps backed up by CockroachDB — and the best part is that we’re using serverless functions and a serverless database, so we never have to worry about managing or scaling servers!
If you haven’t already, why not sign up for a free CockroachDB Serverless account and use it to start building your next great app?
