October 2021 Summaries

A serverless SQL database is a type of distributed relational database that embodies the core principles of a serverless application. These include no server management, automatic elastic scale, built-in resilience and fault tolerance, consumption-based billing, and instant access with always available service. The most complex to deliver is a serverless SQL database due to its additional requirements such as distributed architecture, geographic scale, and a simple SQL API in the cloud. A true serverless SQL database allows developers to achieve native scale and global coverage without the complexity of working with a distributed database while maintaining the familiarity of SQL.

Cockroach Labs introduced its first rotational product management program for early-career professionals. The goal of the program is to train future product leaders and break down barriers in the field. Ivory Ibuaka, a participant in the program, shares her experience working as a Rotational Product Manager at Cockroach Labs. She highlights her work on developing the CockroachDB Cloud Status Page and emphasizes the importance of leveraging customer feedback and data to improve products. The rotational product management program provides valuable opportunities for early-career professionals to learn from experienced teams and contribute to the growth of the organization.

This article guides developers on how to create a full-stack Node.js app using CockroachDB Serverless, a cloud-based SQL database designed for scalability and resilience. The tutorial begins with setting up the development environment by installing Node.js and creating a new project folder. It then moves on to creating a simple Express.js server and connecting it to a CockroachDB Serverless cluster using Sequelize, an Object-Relational Mapping (ORM) library for Node.js. The app is designed to store and display contact information. The data is stored in a PostgreSQL database managed by CockroachDB Serverless. To handle user input, the tutorial uses Pug templates, which are server-side templating engines that allow developers to create dynamic HTML pages with embedded JavaScript code. The app also includes error handling and deployment on Heroku, a cloud platform for building, running, and managing applications. The final section of the article provides an overview of the complete code used in the tutorial and suggests ways to extend the application further.

In this tutorial, we will create a simple web application using Node.js, Express, Sequelize, and CockroachDB Serverless. The app will display a list of Marvel characters with their images, names, and whether they were affected by the Blip (also known as the Decimation or the Snap) in Avengers: Infinity War and Endgame movies. To follow along, you'll need to have Node.js installed on your machine. You can download it from https://nodejs.org/. Additionally, install Sequelize by running `npm install --save sequelize`. First, let's create a new directory for our project and initialize it with npm: ```bash mkdir the-blip && cd the-blip npm init -y ``` Next, we need to install Express and CockroachDB driver: ```bash npm install --save express @cockroachdb/ cockroach-sdk ``` Now, let's create a new file called `app.js` in the root of our project folder. This will be our main application file. In this tutorial, we will use Pug as our template engine. If you don't have it installed yet, run: ```bash npm install --save pug ``` Now, let's set up the basic structure of our app in `app.js`. We will start by importing required modules and setting up some environment variables: ```javascript const express = require('express'); const { Sequelize } = require('sequelize'); const CockroachDB = require('@cockroachdb/ cockroach-sdk'); require('dotenv').config(); const app = express(); app.set('view engine', 'pug'); // Set up environment variables from .env file const port = process.env.PORT || 3000; const marvelApiKey = process.env.MARVEL_PUBLIC_KEY; const marvelPrivateKey = process.env.MARVEL_PRIVATE_KEY; ``` Next, let's create a new Sequelize instance and connect it to our CockroachDB Serverless cluster: ```javascript // Set up Sequelize connection const sequelize = new Sequelize( process.env.DB_NAME, process.env.DB_USER, process.env.DB_PASSWORD, { host: process.env.DB_HOST, dialect: 'postgres', port: process.env.DB_PORT, pool: { max: 5, min: 0, acquire: 30000, idle: 10000 } } ); ``` Now, let's define our `Character` model using Sequelize. We will also create a new table in the database if it doesn't exist yet: ```javascript // Define Character model const Character = sequelize.define('character', { marvelId: { type: Sequelize.INTEGER, primaryKey: true }, name: Sequelize.STRING, thumbnail: Sequelize.STRING, blip: Sequelize.BOOLEAN }); // Sync the database with our models sequelize.sync().then(() => { console.log('Database & tables created!'); }).catch((error) => { console.error('Error creating database:', error); }); ``` Next, let's set up some routes for our app. We will create three main routes: `/`, `/sync`, and `/:id`. The first one will render the list of characters, the second one will synchronize character data from Marvel API, and the last one will update a specific character's blip status. ```javascript // Route for getting all characters app.get('/', async (req, res) => { // -- Get All Characters -- const characters = await Character.findAll(); res.render('index', { title: 'The Blip (All Characters)', characters: characters }); }); // Route for syncing character data from Marvel API app.get('/sync', async (req, res) => { // -- Retrieve and Insert Character Data -- let result = await getCharacters(0); // Retrieve once to get the total const total = result.data.total; // Clear the table await Character.destroy({ truncate: true }); let batch = []; for (let offset = 0; offset < total; offset += 100) { // Get Character Data result = await getCharacters(offset); const characters = result.data.results; // Bulk Create for (let i = 0; i < characters.length; i++) { const isBlipped = blipped.some(c => characters[i].name.includes(c)); const isSafe = notBlipped.some(c => characters[i].name.includes(c)); batch.push({ marvelId: characters[i].id, name: characters[i].name, thumbnail: `${characters[i].thumbnail['path']}.${characters[i].thumbnail['extension']}`, blip: isBlipped ? true : (isSafe ? false : null) }); } } const c = await Character.bulkCreate(batch); res.json({ success: true }); }); // Route for updating character's blip status by ID app.get('/blip/:id', async (req, res) => { // -- Blip Character by ID -- const character = await Character.update({ blip: true }, { where: { marvelId: req.params['id'] } }); res.json(character); }); // Route for updating character's unblip status by ID app.get('/unblip/:id', async (req, res) => { // -- Unblip Character by ID -- const character = await Character.update({ blip: false }, { where: { marvelId: req.params['id'] } }); res.json(character); }); ``` Now, let's define the `getCharacters()` function that retrieves character data from Marvel API using their public and private keys stored in environment variables. We will also use CockroachDB to store the retrieved data: ```javascript // Function for getting character data from Marvel API async function getCharacters(offset = 0) { const baseUrl = "https://gateway.marvel.com"; const ts = new Date().getTime(); // Generate MD5 hash const hash = crypto.createHash('md5').update(`${ts}${process.env.MARVEL_PRIVATE_KEY}${process.env.MARVEL_PUBLIC_KEY}`).digest('hex'); let result = await fetch(`${baseUrl}/v1/public/characters?ts=${ts}&hash=${hash}&apikey=${process.env.MARVEL_PUBLIC_KEY}&limit=100&offset=${offset}`).then(r => r.json()); return result; } ``` Finally, let's start our server and listen for incoming requests: ```javascript // Start the server app.listen(port, () => { console.log(`App listening at http://localhost:${port}`); }); ``` Now, we need to create a new view file called `index.pug` in the `views` folder. This will be our main template for displaying character data. In this tutorial, we will use Bootstrap as our CSS framework. If you don't have it installed yet, run: ```bash npm install --save bootstrap ``` Now, let's set up the basic structure of our view in `index.pug`. We will include Bootstrap styles and scripts, display a title for the page, and render character data using a loop: ```jade doctype html html(lang="en") head meta(charset="utf-8") meta(name="viewport" content="width=device-width, initial-scale=1") link(href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet") script(src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js") title= title body div.container h1 <a href="https://en.wikipedia.org/wiki/The_Blip">The Blip</a> Marvel Character List small a(href="https://marvel.com") Data provided by Marvel. © 2021 MARVEL br button.btn.btn-primary(id="sync-btn" onclick="syncAll()") Sync Character Data from Marvel table tr th Image th Name th Blipped each c in characters tr td img(src=c.thumbnail height="32px") td= c.name if c.blip === true td button.btn.btn-danger(id=\`btn-${c.marvelId}\` onclick=\`callApi("unblip", ${c.marvelId})\`) Blipped (Click to Unblip) else if c.blip === false td button.btn.btn-success(id=\`btn-${c.marvelId}\` onclick=\`callApi("blip", ${c.marvelId})\`) Safe (Click to Blip) else td button.btn.btn-secondary(id=\`btn-${c.marvelId}\` onclick=\`callApi("blip", ${c.marvelId})\`) Unknown (Click to Blip) ``` Now, let's add some JavaScript code to our view for handling button clicks and making AJAX requests to update character data: ```javascript async function syncAll() { document.getElementById("sync-btn").innerText = "Syncing..."; let result = await fetch("/sync").then(r => r.json()); // Reload the page to grab the new data location.reload(); } async function callApi(api, id) { let result = await fetch(`/${api}/${id}`).then(r => r.json()); let elem = document.getElementById(`btn-${id}`); if (api === "blip") { elem.innerText = "Unblip"; elem.classList.remove("btn-success", "btn-secondary"); elem.classList.add("btn-danger"); elem.onclick = () => callApi("unblip", id); } else { elem.innerText = "Blip"; elem.classList.remove("btn-danger", "btn-secondary"); elem.classList.add("btn-success"); elem.onclick = () => callApi("blip", id); } } ``` Now, we need to create a new `.env` file in the root of our project folder and add the following environment variables: ``` DB_NAME=the-blip DB_USER=your-username DB_PASSWORD=your-password DB_HOST=free-tier1.aws-us-east-1.cockroachlabs.cloud DB_PORT=26257 MARVEL_PUBLIC_KEY=your-public-key MARVEL_PRIVATE_KEY=your-private-key ``` Replace `your-username`, `your-password`, and `your-keys` with your actual values. You can get them from CockroachDB Serverless dashboard or Marvel Developer Portal. Now, let's start our server by running: ```bash node app.js ``` You should see the following output in your terminal: ``` App listening at http://localhost:3000 Database & tables created! ``` Open your browser and navigate to `http://localhost:3000`. You should see a list of Marvel characters with their images, names, and whether they were affected by the Blip. Try clicking on some buttons to update character data and reload the page to see the changes. That's it! We have successfully created and deployed a simple web application using Node.js, Express, Sequelize, CockroachDB Serverless, and Pug template engine. You can now build upon this project by adding more features or creating an entirely different app.

Cockroach Labs has expanded its strategic alliance with Google Cloud, highlighting the benefits companies are witnessing by deploying CockroachDB on Google Cloud Platform (GCP). The partnership allows for more secure infrastructure and reduced operational overhead. With CockroachDB becoming an integration partner of Google Cloud Run, developers can build serverless applications that scale up and down with usage. This collaboration aims to empower organizations in their business transformation journey by providing a fundamentally consistent, resilient, and scalable database on cloud infrastructure.

Serverless is a crucial evolution of cloud computing that enables more efficient use of resources such as time, money, and developer brainpower. It abstracts and automates many hands-on tasks so developers can focus on building features and services. The four fundamental principles of serverless are: hands-off (server) service, automated elastic scale, consumption-based billing, and built-in resilience and fault tolerance. Serverless is ideal for organizations of any size as it democratizes high-caliber tech that was once only available to tech giants like Google, Netflix, or Amazon.

Change Data Capture (CDC) is a process that captures changes made at the data source and applies them throughout the entire system. It minimizes resources required for ETL processes by dealing only with data changes, providing efficient, distributed, row-level change feeds into a configurable sink for downstream processing such as reporting, caching, or full-text indexing. CockroachDB's mission is to Make Data Easy and plays well with others using CDC. A CockroachDB `CHANGEFEED` is a realtime stream of changes happening in a table or tables, emitting messages to an external system called a "sink". The biggest challenge in building CDC changefeeds for CockroachDB was maintaining strong transaction semantics while scaling out horizontally. CockroachDB's unique distributed architecture led to the development of internal mechanisms like RangeFeed and resolved timestamp messages, ensuring data durability and consistency.