Build a real-time leaderboard with Amazon Aurora Serverless and Amazon ElastiCache

Introduction

Background and setup

Provision a database

Plan your data model

Launch a cluster

Design your data model

Add authentication to your application

Deploy your application

Test your application

Clean up and next steps

Plan your data model and prepare your initial data

When building a new application, it is important to plan out your data model before diving into implementation. This approach ensures that you have a solid foundation on which you can build your application. In this module, you learn about the main entities in your application, plan the data model, and prepare your database.

A common way to plan your data model is to create an entity-relationship diagram (ERD). An ERD shows the different entities in your application and their properties. It also shows how the entities relate to each other.

In your simple application, you only have a single entity: Games. A Game represents a single time that a user has played a particular level in the game. It stores the user’s username, the level played, the timestamp of the game, and the user’s score in the game.

The ERD for your application is simple, since there is only one entity and no relationships. It is represented below.

You can see that your ERD contains the single entity -- Game -- and the various properties for that entity.

In the following steps, you translate your ERD into database code. First, you create the table to match your ERD. Then, you load some sample data into your database. Finally, you run some queries on your database to handle some of your use cases.

Time to Complete Module: 20 Minutes

  • Step 1. Create your database tables

    Step 1. Create your database tables

    First, create your database table. In the scripts/ directory, there is a file named createTable.js. The contents of that file are as follows:

    const AWS = require('aws-sdk')

const rdsdataservice = new AWS.RDSDataService();

const params = {
  resourceArn: process.env.DATABASE_ARN,
  secretArn: process.env.SECRET_ARN,
  database: 'leaderboard',
  sql: `CREATE TABLE games (
game_id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
gamedate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
score INT NOT NULL,
level INT NOT NULL
);`
}

rdsdataservice.executeStatement(params, function(err, data) {
  if (err) {
    console.log(err, err.stack)
  } else {
    console.log('Table created successfully!')
  }
})

    This script is similar to the testDatabase.js script you ran in the previous module. In this script, your SQL contains the Data Definition Language (DDL) statements to create your Games table. The table has a game_id property which is an auto-incrementing integer that serves as the primary key for the table. It also has other properties, including username and score.

    Execute the script and create your tables with the following command:

    node scripts/createTable.js

    You should see the following output in your terminal:

    Table created successfully!

  • Step 2. Load sample data

    Step 2. Load sample data

    Now that you have created your table, it is time to load it with some sample data.

    In the scripts/ directory, there is a games.json file that contains some JSON data. This JSON data is randomized example data to insert into your table.

    Look at the file in scripts/insertGames.js. It contains the following code:

    const AWS = require('aws-sdk')

const rdsdataservice = new AWS.RDSDataService();
const fs = require('fs');
const path = require('path');

const raw = fs.readFileSync(path.resolve( __dirname, 'games.json'));
const games = JSON.parse(raw)
const values = games.map((game) => { return `('${game.username}', '${game.gamedate}', ${game.score}, ${game.level})`}).join(',\n')
const sql = `INSERT INTO games (username, gamedate, score, level) VALUES ${values}`

const params = {
  resourceArn: process.env.DATABASE_ARN,
  secretArn: process.env.SECRET_ARN,
  database: 'leaderboard',
  sql
}

rdsdataservice.executeStatement(params, function(err, data) {
  if (err) {
    console.log(err, err.stack)
  } else {
    console.log('Games inserted successfully!')
  }
})

    Like the createTable.js script, it uses the RDSDataService client to access the Data API. In this script, you are reading your fake games from the games.json file, then writing an INSERT statement in SQL to insert the game data into your table.

    You can run the script with the following command: 

    node scripts/insertGames.js

    You should see the following output in your terminal:

    Games inserted successfully!

    You have now loaded 300 different game scores into your table. In the next step, you learn how to handle one of your common access patterns using the Data API.

  • Step 3. Test your data access

    Step 3. Test your data access

    With your data loaded, you can use the Data API to do something more complex than the Select 1 query you did in the last module.

    A common access pattern is to fetch the highest scores for a user. You can try that here.

    Look at the code in scripts/fetchHighScoresForUser.js. This code contains the internal method your application calls to fetch the highest scores for a particular user. The code looks as follows:

    const AWS = require('aws-sdk')

const rdsdataservice = new AWS.RDSDataService();

const fetchHighScoresForUser= async (username, count) => {
  const params = {
    resourceArn: process.env.DATABASE_ARN,
    secretArn: process.env.SECRET_ARN,
    database: 'leaderboard',
    includeResultMetadata: true,
    sql: 'SELECT game_id, username, gamedate, score, level FROM games WHERE username = :username ORDER BY score DESC LIMIT :count',
    parameters: [
      {
        name: 'username',
        value: { stringValue: username }
      },
      {
        name: 'count',
        value: { longValue: count }
      }
    ]
  }
  const results = await rdsdataservice.executeStatement(params).promise()
  return results
}

fetchHighScoresForUser(ubecker, 1).then((results) => console.log(JSON.stringify(results, null, 2)))

    Your fetchHighScoresForUser function takes two arguments -- the username for whom you want to fetch high scores, and the number of records you want to fetch. It then makes a query using the Data API to fetch the top scores for that user.

    At the bottom of the file is an example of using the fetchHighScoresForUser function by calling it with the username of ubecker and requesting only a single record.

    Execute the script by running the following command in your terminal:

    node scripts/fetchHighScoresForUser.js

    You should see the following output in your terminal:

    {
  "columnMetadata": [
    {
      "arrayBaseColumnType": 0,
      "isAutoIncrement": true,
      "isCaseSensitive": false,
      "isCurrency": false,
      "isSigned": true,
      "label": "game_id",
      "name": "game_id",
      "nullable": 0,
      "precision": 11,
      "scale": 0,
      "schemaName": "",
      "tableName": "games",
      "type": 4,
      "typeName": "INT"
    },
    {
      "arrayBaseColumnType": 0,
      "isAutoIncrement": false,
      "isCaseSensitive": false,
      "isCurrency": false,
      "isSigned": false,
      "label": "username",
      "name": "username",
      "nullable": 0,
      "precision": 50,
      "scale": 0,
      "schemaName": "",
      "tableName": "games",
      "type": 12,
      "typeName": "VARCHAR"
    },
    {
      "arrayBaseColumnType": 0,
      "isAutoIncrement": false,
      "isCaseSensitive": false,
      "isCurrency": false,
      "isSigned": false,
      "label": "gamedate",
      "name": "gamedate",
      "nullable": 0,
      "precision": 19,
      "scale": 0,
      "schemaName": "",
      "tableName": "games",
      "type": 93,
      "typeName": "TIMESTAMP"
    },
    {
      "arrayBaseColumnType": 0,
      "isAutoIncrement": false,
      "isCaseSensitive": false,
      "isCurrency": false,
      "isSigned": true,
      "label": "score",
      "name": "score",
      "nullable": 0,
      "precision": 11,
      "scale": 0,
      "schemaName": "",
      "tableName": "games",
      "type": 4,
      "typeName": "INT"
    },
    {
      "arrayBaseColumnType": 0,
      "isAutoIncrement": false,
      "isCaseSensitive": false,
      "isCurrency": false,
      "isSigned": true,
      "label": "level",
      "name": "level",
      "nullable": 0,
      "precision": 11,
      "scale": 0,
      "schemaName": "",
      "tableName": "games",
      "type": 4,
      "typeName": "INT"
    }
  ],
  "numberOfRecordsUpdated": 0,
  "records": [
    [
      {
        "longValue": 101
      },
      {
        "stringValue": "ubecker"
      },
      {
        "stringValue": "2019-11-06 09:00:37"
      },
      {
        "longValue": 9090
      },
      {
        "longValue": 84
      }
    ]
  ]
}

    This output is pretty verbose. The Data API includes a lot of information about the result, including detailed column metadata for each column returned.

    This information can be hard to parse in each data access method. In the next step, you use a utility method to wrap the Data API.

  • Step 4. Parsing the Data API response

    Step 4. Parsing the Data API response

    In the previous step, you looked at an example method to fetch the high scores for a single user and saw the verbose response from the Data API. In this step, you learn how to parse that response.

    In the scripts/ directory, look at the fetchHighScoresForUser2.js file. The contents of this file are as follows:

    const AWS = require('aws-sdk')

const rdsdataservice = new AWS.RDSDataService();

const parseRecords = (records, columnMetadata) => {
  // Format the results into key-value pairs with the column name and value
  const parsed = records.map((result) => {
    const obj = {}
    result.forEach((elem, idx) => {
      const columnName = columnMetadata[idx].name
      const [ columnValue, ]= Object.values(elem)
      obj[columnName] = columnValue
    })
    return obj
  })
  return parsed

}

const executeReadSql = async (sql, parameters) => {
  const params = {
    resourceArn: process.env.DATABASE_ARN,
    secretArn: process.env.SECRET_ARN,
    database: 'leaderboard',
    includeResultMetadata: true,
    sql
  }
  if (parameters) {
    params.parameters = parameters
  }
  const rawResults = await rdsdataservice.executeStatement(params).promise()
  let results = []
  if (rawResults.records) {
    results = parseRecords(rawResults.records, rawResults.columnMetadata)
  }
  return results
}

const fetchHighScoresForUser = async (username, count) => {
  const parameters = [
    {
      name: 'username',
      value: { stringValue: username }
    },
    {
      name: 'count',
      value: { longValue: count }
    }
  ]
  const sql = 'SELECT game_id, username, gamedate, score, level FROM games WHERE username = :username ORDER BY score DESC LIMIT :count'
  const result = await executeReadSql(sql, parameters)
  return result
}

fetchHighScoresForUser(ubecker, 1).then((results) => console.log(JSON.stringify(results, null, 2)))

    In this script, there are two helper functions: executeReadSql and parseRecords. The executeReadSql function eases some of the boilerplate around calling the Data API. It handles the extraneous parameters, like the Database ARN and Secret ARN, so that you can focus on just the SQL and parameters in your data access functions.

    The parseRecords function helps to change the returned rows into JavaScript objects with key-value pairs. This result is easier to handle in your application.

    Try running the script to fetch a single user. Execute the following command in your terminal:

    node scripts/fetchHighScoresForUser2.js

    You should see the following output:

    [
  {
    "game_id": 101,
    "username": "ubecker",
    "gamedate": "2019-11-06 09:00:37",
    "score": 9090,
    "level": 84
  }
]

    This output is much easier to read and to use in your application than your initial result.

    You use these utility functions in your data access functions in your application.

    Conclusion

    In this module, you designed your data model using an entity-relationship diagram (ERD). Then, you used the Data API from your Amazon Aurora Serverless database to create your table. Next, you loaded some sample data into your new table. Finally, you ran through a few more examples of using the Data API. You saw how some utility functions can be helpful when using the Data API.

    In the next module, you create your ElastiCache instance and configure it so that you can access it from your Cloud9 instance. 

Next: Launch a cluster