Tutorial

Build a game voting system using CockroachDB and API Connect

Create an instant GraphQL API for CockroachDB using API Connect, and enrich this data with the Steam REST API

By

Roy Derks

Most of the time when you're building a back end, it takes more than a database. Often, you need to make combinations with third-party services or APIs to enrich the data that you want to display in your application. In this tutorial, learn how to create a voting system for games. The voting system allows you to vote for games that you like that are enriched with third-party content, like news articles on the gaming platform Steam, and the votes are stored in a CockroachDB database.

The process uses GraphQL to combine the data from this REST API with CockroachDB. This API query language is excellent for combining data from different data sources. I'll explain how to create an instant GraphQL API for CockroachDB using API Connect Essentials (formerly StepZen), which is the GraphQL API Development product of API Connect. The data in the database will be enriched with data from the Steam REST API. Both CockroachDB and API Connect Essentials are running serverless, meaning that you don't have to worry about performance issues when you take your project to production.

Prerequisites

Before you begin, you should create an account for CockroachDB and API Connect Essentials to use them for this project. Both services have free tiers, which are more than sufficient for this project:

  1. Sign up for CockroachDB.
  2. Sign up for API Connect Essentials Lite.

You should also install the CLI tools that you need to work on this project. You need the API Connect Essentials CLI tool, and a tool that you can use to connect to the CockroachDB. You could use psql for this and the CockroachDB Client, but you should choose your favorite.

In the next section, you learn how to set up and connect to CockroachDB.

Estimated time

It should take you approximately 20 minutes to complete this tutorial.

Steps

Step 1. Set up a CockroachDB cluster

Before building the GraphQL API, you must set up a CockroachDB cluster and upload a database schema. You can use CockroachDB locally by running a database cluster on your own machine or in the cloud. This tutorial uses the cloud version, for which you must sign up for a free CockroachDB account.

After signing up for a free account, you can create a new CockroachDB cluster from the Cloud Console. You must select the cloud provider that you want your cluster deployed on and in which region.

Set up CockroachDB

When you click Create, a new cluster with a database is created in the selected region.

After the cluster is created, you can connect to it and upload the database schema. In the CockroachDB Cloud Console, you can get the connection details for your new cluster by clicking Connect. Here, you find multiple ways to connect with CockroachDB. You can get the commands to connect with CockroachDB over the command line, the connection string (dsn), or the connection credentials themselves.

To connect to your database cluster using the CockroachDB client, you must select CockroachDB client and copy-paste the commands to install the client and connect to your terminal. For other CLI tools like psql, you need to select general connection string and copy-paste these commands in your terminal.

When using the CockroachDB Client, you can connect to the cluster using code like the following code example.

cockroach sql --url "postgresql://<COCKROACHDB_USERNAME>:<COCKROACHDB_PASSWORD>@<COCKROACHDB_HOST>:26257/defaultdb?sslmode=verify-full&options=--cluster%3D<DATABASE_NAME>"

Or, you can connect to the cluster with psql.

psql "postgresql://<COCKROACHDB_USERNAME>:<COCKROACHDB_PASSWORD>@<COCKROACHDB_HOST>:26257/defaultdb?sslmode=verify-full&options=--cluster%3D<DATABASE_NAME>"

When you can connect with the database, you populate it with a schema, shown in the next section.

Step 2. Populate the database

The schema for the database contains the tables and columns that you want to extend later using the data from the Steam REST API. For this voting system, you want to store two kinds of entities in the database:

  • Game: references to games
  • Vote: votes that are referenced to a game

All of this can be expressed in a PostgreSQL database schema, such as the following:

-- Every article is stored in a schema named `project`
CREATE SCHEMA project;

-- There are two kinds of entities games and votes.
-- And there is a one-to-many relationship between them.
CREATE TABLE project.game (
   id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
   appid VARCHAR(127) NOT NULL,
   title VARCHAR(127) NOT NULL,
   UNIQUE(appid) -- all games are unique
);

-- These are all the votes.
CREATE TABLE project.vote (
   id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
   appid VARCHAR(127) NOT NULL
);

-- This is the initial data
INSERT INTO
   project.game (appid, title)
VALUES
   ('730', 'Counter-Strike: Global Offensive'),
   ('570', 'Dota 2'),
   ('1172470', 'Apex Legends'),
   ('578080', 'PUBG: BATTLEGROUNDS'),
   ('1599340', 'Lost Ark'),
   ('271590', 'Grand Theft Auto V'),
   ('1203220', 'NARAKA: BLADEPOINT'),
   ('440', 'Team Fortress 2');

This initialization file creates the PostgreSQL schema project, the tables game and vote, and prepopulates these tables with the most popular Steam games.

Save this in a new file called init.sql so that it can be uploaded to the CockroachDB database. For example, with psql:

psql "postgresql://<COCKROACHDB_USERNAME>:<COCKROACHDB_PASSWORD>@<COCKROACHDB_HOST>:26257/defaultdb?sslmode=verify-full&options=--cluster%3D<DATABASE_NAME>" < init.sql

You can verify whether the tables have been created in the database in the Cloud Console or from the command line using the CockroachDB client or psql.

The next step is to introspect the CockroachDB database to generate a GraphQL schema for the database.

Step 3. Generate an instant GraphQL API

With API Connect Essentials, you can generate an instant GraphQL API for any data source, including CockroachDB. API Connect Essentials has an introspection service that looks at your data source and generates a GraphQL schema based on the structure and contents of that data source. Currently, it works for most relational databases, NoSQL databases, REST and SOAP APIs, and with other GraphQL APIs. As CockroachDB is PostgreSQL compatible, you can use the API Connect Essentials CLI to generate a GraphQL schema by running the following command.

stepzen import postgresql

Notice: StepZen was acquired by IBM in 2023 and renamed to API Connect Essentials. Some artifacts still use the StepZen name.

The CLI prompts you with several questions that you can answer with the following:

  • What would you like your endpoint to be called: api/with-cockroachdb
  • What is your host: <COCKROACHDB_HOST>:26257
  • What is your database name: <COCKROACHDB_CLUSTERNAME>.<DATABASE_NAME>
  • What is the username: <COCKROACHDB_USERNAME>
  • What is the password: <COCKROACHDB_PASSWORD>
  • The value for <COCKROACHDB_CLUSTERNAME> is the name of the cluster, which you can derive from the options value. If this is, for example, --cluster=demo-123, the cluster name would be demo-123.

  • Automatically link types with @materializer whenever there is database support: N

  • What is your database schema (leave blank to use defaults): project

When you click Enter after answering all of the prompts, the GraphQL schema for this CockroachDB database is generated. The CLI creates several files, but only two files are important to highlight. The first is config.yaml, which contains the connection string to CockroachDB. The second one is postgresql/index.graphql, which has the generated GraphQL schema.

The GraphQL schema in postgresql/index.graphql uses a custom directive to link a GraphQL query to a table in the database. The API Connect Essentials CLI has generated a CRUD-like GraphQL schema that lets you list, insert, and delete games into the database using GraphQL.

For example, the GraphQL query to get the complete list of games from the database looks like the following code.

getGameList: [Game]
  @dbquery(
    type: "postgresql"
    schema: "project"
    table: "game"
    configuration: "postgresql_config"
  )

This query lets you get all of the articles from the database table "game". But you can also write custom SQL queries to get, for example, a paginated list with the articles.

getPaginatedGameList(first: Int!, after: Int!): [Game]
  @dbquery(
    type: "postgresql"
    query: """
    select * from "project"."game" limit 1offset1 offset 1offset2
    """
    configuration: "postgresql_config"
  )

To deploy this GraphQL API, you only need to run the command stepzen start. This command tells the CLI to upload the GraphQL schema and deploy it to a GraphQL endpoint. This endpoint is then available to query using HTTP or the API Connect Essentials dashboard.

Running stepzen start deploys the GraphQL API to the cloud. In the terminal, both the production-ready endpoint and a link to the Explorer in the API Connect Essentials dashboard are printed. From the dashboard, or the production-ready endpoint, you can run the query from the following image to get a list of titles for all the games in the database.

Query a GraphQL API with data from CockroachDB and API Connect Essentials

With just two commands in your terminal, you've created this complete GraphQL API for CockroachDB. However, before diving deeper into the GraphQL API, you're going to add the connection to the Steam REST API.

Step 4. Adding the Steam REST API

The CockroachDB database is already set up to accept information for the game voting system that you're building. The GraphQL API should also contain more detailed information about the Steam games, such as detailed specifications and the latest news. To get this information, you'll be using the Steam REST API. Steam is one of the biggest video game distribution platforms in the world. With Steam, you can buy and play games from almost every video game creator.

The REST API endpoint you use for this project is the GetNewsForApp API. There are different endpoints available that contain more information and are easier to use, but those require a Steam API key. This endpoint is free and provides the information that you want to use.

The API is designed to be a feed that is linked to a required REST query parameter named appid. For example, the appid 440 corresponds to the game Team Fortress 2, which you'll use as your running example.

After looking at the documentation, the call looks like the following example.

curl https://api.steampowered.com/ISteamNews/GetNewsForApp/v2/\?appid\=440\&count\=1

If you have curl installed, you should be able to see the response. You can also view the response in your web browser by browsing the URL or using any other tool for HTTP requests that you're familiar with.

API Connect Essentials provides an easy way to convert the CURL call into a GraphQL schema. This is the CLI stepzen import curl command.

In the same directory that you imported the schema from CockroachDB, you can run this command together with the URL from the Steam REST API:

stepzen import curl https://api.steampowered.com/ISteamNews/GetNewsForApp/v2/\?appid\=440\&count\=1 --name steam --query-name getNewsForApp --query-type AppNews

In a few seconds, the CLI finishes introspecting the response of the Steam REST API call, and it creates a GraphQL schema to get news from a Steam game for you. A couple of flags are used to customize the output of the generated schema that is created in a new file called steam/index.graphql. The flag --name is used for the directory name, --query-name is used to name the new query getNewsForApp, and --query-type is used to name the return type as AppNews.

For now, you do not examine this output. You will come back to it later. However, do verify that there is a subfolder within your project directory called steam that contains a file called index.graphql with the contents as described previously.

Step 5. Combine data from CockroachDB and Steam

Because you do not have to mess with the autogenerated file, you'll create a new file to extend the auto-generated files. This way, if you make any changes to the data model in your CockroachDB database, you don't have to add any custom types when regenerating the GraphQL schema. Let's call this file extends.graphql and place it next to the index.graphql file in the directory where the schema for CockroachDB was generated (/postgresql). In this file, you can place the following code.

Also, you must add this new file to the index.graphl file in the project directory to be indexed by API Connect Essentials.

schema
 @sdl(
   files: [
     "postgresql/index.graphql"
     "postgresql/extends.graphql"
     "steam/index.graphql"
   ]
 ) {
 query: Query
}

This file to extend the schema contains all of the changes that you want to make to types that exist in the autogenerated GraphQL schema.

With API Connect Essentials you can combine data from different data sources, like data coming from a database and data coming from an API, into a single GraphQL API. The GraphQL schema generated for the Steam REST API has the query getNewsForApp that gets the news for a game based on the value for appid. Your GraphQL schema has a query to retrieve a game from the database, including the appid. These two queries can be combined on type-level, meaning that to add the news about a game to the query to get information about the game, the response type of the query to get a game (called Game) needs to be altered.

In postgresql/extends.graphql, you should add the following code.

extend type Game {
 news: AppNews
   @materializer(
     query: "getNewsForApp"
     arguments: [{ name: "appid", field: "appid" }]
   )
}

This code block extends the existing type Game, and adds a new field called news. The custom directive @materializer is then used to get the data for this field, which comes from the query getNewsForApp that needs the argument appid.

After saving the file, you're able to retrieve the news for a game on every query that has the response type Game, as shown in the following example.

query GetGames {
  getGameList {
    appid
    title
    news {
      appnews {
        appid
        count
      }
    }
  }
}

However, the news about the game is nested inside a field appnews, due to the structure of the JSON response from the Steam REST API. You can flatten the response by adding a resultroot option to the query getNewsForApp in steam/index.graphql.

type Query {
 getNewsForApp(appid: String, count: String): AppNews
   @rest(
     endpoint: "https://api.steampowered.com/ISteamNews/GetNewsForApp/v2/"
     resultroot: "appnews"
   )
}

You also need to change the type AppNews to have the following fields:

type AppNews {
 appid: Int
 count: Int
 newsitems: [NewsitemsEntry]
}

And you can delete the field Appnews (with the lowercase n) that was previously generated in the GraphQL schema.

After this change, you can query the GraphQL API and get the news for a game without the data being nested. Finally, to complete the game voting system, you must insert votes into the CockroachDB database.

Step 6. Inserting data into CockroachDB from GraphQL

The API Connect Essentials CLI has already created several queries and mutations for you, but there is no way to add a vote for a game. In GraphQL, when you want to mutate data (for example, inserting a vote in the database), you do this with a mutation. To add such a mutation, you must modify the GraphQL schema or extend it to include the mutation.

The mutation to insert a vote into the database should be added to file postgresql/extends.graphql that contains all the extensions for the autogenerated schema.

type Mutation {
 insertVote(appid: String!): Vote
   @dbquery(
     type: "postgresql"
     schema: "project"
     table: "vote"
     dml: INSERT
     configuration: "postgresql_config"
   )
}

Make sure to run stepzen deploy after making any changes so these will be deployed to your GraphQL endpoint.

In the Explorer or via the HTTPS enpoint, you should now be able to insert a vote into the database by using this GraphQL operation:

mutation InsertVote {
    insertVote(appid: "440") {
        id
        appid
    }
}

The mutation insertVote only needs the appid of the game that you want to vote for, and uses this to store the vote. From the response of the GraphQL mutation, you can see that the vote is inserted into the CockroachDB database with a unique identifier (id).

To get the number of votes for a game, you should be querying the CockroachDB database with the following query.

select count(id) as count from "project"."vote" where "appid" = "440"

This query gets the number of votes for the game with appid 440 and returns it as the field count. By creating a new query in postgresql/extends.graphql, you can translate this SQL query to GraphQL.

type VoteCount {
 count: Int
}x

type Query {
 getVoteCountByAppid(appid: String!): VoteCount
   @dbquery(
     type: "postgresql"
     query: """
     select count(id) as count from "project"."vote" where "appid" = $1
     """
     configuration: "postgresql_config"
   )
}

The new query has the response type VoteCount and takes a dynamic value for appid. When you query getVoteCountByAppid with an existing value for appid you can see the number of votes of a game.

As a final step, you add the number of votes to the type Game so that it can be queried using GraphQL. For this, you use the custom directive @materializer again to extend the type Game in postgresql/extends.graphql.

extend type Game {
 news: AppNews
  @materializer(
    query: "getNewsForApp"
    arguments: [{ name: "appid", field: "appid" }]
  )
 votes: Int
   @materializer(
     query: "getVoteCountByAppid"
     arguments: [{ name: "appid", field: "appid" }]
   )
}

From the Explorer or the HTTPS endpoint, you can now query the list of games and get the total number of votes for each game and the latest news from Steam using the below operation:

query GetGames {
  getGameList {
    title
        votes {
      count
    }
  }
}

Try adding more votes to any of the games using the insertVote mutation, and you'll see the value for count changing.

Conclusion

In this tutorial, you built a GraphQL API for a CockroachDB database using IBM API Connect Essentials. Both technologies can be run serverless and in the cloud and have a CLI that allows you to configure them. This makes the technologies very complementary to each other. With API Connect Essentials, you can auto-generate a GraphQL API based on the schema of your CockroachDB database, meaning most of the data modeling happens in your database. You also learned how to extend the autogenerated GraphQL schema by adding a connection to the Steam REST API.

You can find the complete source code for this blog post in GitHub, or join our Discord community to stay updated about our latest developments, ask any question, and let us know if there's a data source you have that you want easier access to. To get more information, take a look at the documentation or join our Discord to stay updated with our community.