Bookings App with MariaDB Connector/Node.js

Overview

Bookings is a web application for analyzing flight bookings. The application is compatible with MariaDB Connector/Node.js and MariaDB Platform for Smart Transactions on MariaDB SkySQL.

Bookings demonstrates the power of MariaDB Platform for Smart Transactions by reading hundreds of millions of rows using lightning fast queries without indexes.

Bookings Application

Bookings has three components:

  • React.js front-end web application

  • Node.js-based back-end API using MariaDB Connector/Node.js

  • MariaDB Platform for Smart Transactions back-end database service, running on MariaDB SkySQL

Prerequisites

Clone the Repository

Bookings is available from a MariaDB Corporation repository on GitHub. The repository contains the SQL code to initialize the database, the API code in Node.js and other languages, and the React.js front-end web application that connects to the API.

To download the Bookings application, clone the repository from GitHub:

$ git clone https://github.com/mariadb-corporation/dev-example-bookings.git

$ cd dev-example-bookings

This creates a dev-example-bookings directory on your file system.

Provision the Database Service

Bookings requires a database back-end to support hybrid transactional-analytical processing (HTAP) workloads. It is compatible with MariaDB Platform for Smart Transactions. For the purposes of demonstration, this guide uses an HTAP topology on MariaDB SkySQL.

For additional information, see "Launch".

Load Flight Data

The repository provides scripts for retrieving data and configuring the database service:

Script

Description

gen_flights_data.sh

Retrieves data from the US Department of Transportation . The script writes a series of CSV files with the necessary flight information.

create_and_load.sh

Creates a set of MariaDB Enterprise ColumnStore tables on the database service, then imports each of the CSV files into the database.

The gen_flights_data.sh script downloads three decades of flight data, which requires approximately 30G of disk space. It can take a long time to download onto your system and a long time to upload to MariaDB SkySQL, depending on your internet speeds.

To load data into MariaDB Platform for Analytics:

  1. From the api/nodejs directory, use the script to download flights:

    $ ./get_flight_data.sh
    

    The script downloads the flight data from the US Department of Transportation as a series of CSV files in the data directory.

  2. Copy the skysql_chain.pem file into the dev-example-bookings directory:

    $ cp ~/Downloads/skysql_chain.pem .
    
  3. Use the create_and_load.sh with your SkySQL credentials to initialize the database and load the flight data:

    $ ./create_and_load.sh example.skysql.net 5001 db_user 'db_passwd'
    

    Note that default passwords set for MariaDB SkySQL may require single quotes to prevent the shell from misreading special characters.

Configure Replication

Bookings uses the travel database to store InnoDB tables and the travel_history database to store MariaDB ColumnStore tables. To enable Smart Transactions, your database service must be configured to replicate writes from the travel.flights table to the travel_history.flights table.

To configure replication:

  1. Use the SET_HTAP_REPLICATION() user-defined function (UDF) to configure the tables for replication:

    SELECT SET_HTAP_REPLICATION(
       "travel.flights",
       "travel",
       "travel_history");
    
  2. Use the SHOW_HTAP_REPLICATION() user-defined function (UDF) to confirm the replication configuration was changed:

SELECT SHOW_HTAP_REPLICATION();
+-------------------------------------------+
| SHOW_HTAP_REPLICATION()                   |
+-------------------------------------------+
|                                           |
     === replication_filter ===
     table: travel.flights
     source database: travel
     target database: travel_history
|                                           |
+-------------------------------------------+
  1. Use the SHOW TABLES statement to confirm the flights table was correctly replicated to the travel_history database:

SHOW TABLES FROM travel_history LIKE 'flights';

Create ColumnStore Table

The replication initially creates empty InnoDB tables, which must be copied into ColumnStore tables and also be populated with the initial copy of the data:

CREATE OR REPLACE TABLE travel_history.flights
ENGINE = COLUMNSTORE
SELECT * FROM travel.flights;

Generate Application Data

The travel_history.flights table contains historic data from the US Department of Transportation, but the travel.flights table is empty.

Test the application by adding new data for flights, tickets, and trips:

  1. Manually create searchable flights by adding a new flight that can be booked and creating a ticket for that flight:

    INSERT INTO travel.flights (
       year, month, day, day_of_week, fl_date, carrier, tail_num,
       fl_num, origin, dest, crs_dep_time, dep_time, dep_delay,
       taxi_out, wheels_off, wheels_on, taxi_in, crs_arr_time,
       arr_time, arr_delay, cancelled, cancellation_code, diverted,
       crs_elapsed_time, actual_elapsed_time, air_time, distance,
       carrier_delay, weather_delay, nas_delay, security_delay,
       late_aircraft_delay)
    VALUES (
       2020, 5, 5, 5, '2020-05-05', 'DL', NULL, 1280, 'LAX', 'ORD', '0600',
       '0600', NULL, NULL, NULL, NULL, NULL, '0913', '0913', NULL, NULL, NULL,
       NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
    
    INSERT INTO travel.tickets (
       id, fl_date, fl_num, carrier, origin, dest, price)
    VALUES (1, '2020-05-05', 1280, 'DL', 'LAX', 'ORD', 240.00);
    
  2. Manually create an upcoming trip by adding a flight, ticket, and trip:

    INSERT INTO travel.flights (
       year, month, day, day_of_week, fl_date, carrier, tail_num,
       fl_num, origin, dest, crs_dep_time, dep_time, dep_delay,
       taxi_out, wheels_off, wheels_on, taxi_in, crs_arr_time, arr_time,
       arr_delay, cancelled, cancellation_code, diverted, crs_elapsed_time,
       actual_elapsed_time, air_time, distance, carrier_delay, weather_delay,
       nas_delay, security_delay, late_aircraft_delay)
    VALUES (2020, 7, 4, 2, '2020-07-04', 'DL', NULL, 1170, 'ORD', 'LAX',
       '1420', '1420', NULL, NULL, NULL, NULL, NULL, '1730', '1730', NULL,
       NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
       NULL);
    
    INSERT INTO travel.tickets (
       id, fl_date, fl_num, carrier, origin, dest, price)
    VALUES (2, '2020-07-04', 1170, 'DL', 'ORD', 'LAX', 276.00);
    
    INSERT INTO travel.trips (id, ticket_id) VALUES (1, 2);
    

Install the Back-end Application Service

Bookings front-end web application connects to the database service using a back-end application service. The repository contains different versions of the back-end app, enabling you to test and experiment with different languages and different MariaDB Connectors. These instructions are for Node.js using MariaDB Connector/Node.js.

Navigate to the correct api directory:

$ cd /path/to/dev-example-bookings/api/nodejs

Configure the Back-end Application Service

To connect the back-end app to your MariaDB SkySQL database service, configure it to use the appropriate credentials:

  1. Create a .env file in the api/nodejs directory, set the variables to the login credentials for your MariaDB SkySQL service:

    DB_HOST_1 = example.skysql.net
    DB_USER_1 = db_user
    DB_PASS_1 = db_user_passwd
    DB_PORT_1 = 5001
    
  2. Edit the db.js file, uncomment the configuration variables for TLS connections:

// SSL (e.g. SkySQL) connections
// * Remember to change the location of "skysql_chain.pem" to wherever you placed it!
// * To use just uncomment the two lines below and the 'ssl' property (and value) within the connection pool configuration

const fs = require("fs");
const serverCert = [fs.readFileSync("skysql_chain.pem", "utf8")];
  1. In setting the serverCert constant, adjust the path to point to the location of the skysql_chain.pem file on your file system:

    const serverCert = [fs.readFileSync("/path/to/skysql_chain.pem", "utf8")];
    
  2. In the mariadb.createPool() configuration, uncomment the ssl entry, so that MariaDB Connector/Node.js uses the CA certificate:

    var pools = [
      mariadb.createPool({
        host: process.env.DB_HOST_1,
        user: process.env.DB_USER_1,
        password: process.env.DB_PASS_1,
        port: process.env.DB_PORT_1,
        multipleStatements: true,
        connectionLimit: 5,
        ssl: {
          ca: serverCert
        }
      })
    ];
    

Build the Back-end Application Service

To build the Node.js back-end application service:

$ npm install

Start the Back-end Application Service

To start the Node.js back-end application service:

$ npm start

The back-end application service is now running. Install and start the React.js front-end web application to use Bookings.

Install Front-end Web Application

Bookings uses a React.js front-end web application. The front-end connects to the back-end application service and provides a web interface for the user.

To install and start the web app:

  1. Navigate to the client directory:

    $ cd /path/to/dev-example-bookings/client/
    
  2. Install the client application:

    $ npm install
    
  3. Start the client:

    $ npm start
    

React.js web application is now running. The web app is available through your web browser at http://localhost:3000.

Live Demonstration

When the React.js front-end and the back-end application service are running, Bookings simulates a web application to book flights and view flight status on upcoming trips. In addition to manually adding flights, Bookings also features data from the US Department of Transportation, listing 30 years of flights information.

Orders Application

The web interface provides two views:

  • Book a Trip: Allows you to search for available flights from the database, according to the origin and destination airports, and departure date. You can search one-way or round trip.

  • Upcoming Trips: Lists the flight information for any booked tickets.

InnoDB storage engine is used on a smaller tables and to handle write transactions to the very large database of flight information. MariaDB MaxScale replicates changes made to the travel.flights InnoDB table to the travel_history.flights ColumnStore table. This replication allowing you to use InnoDB transactions when writing to travel.flights and to take advantage of the columnar storage in Enterprise Columnstore when performing reads, enabling you to analyze over 180 million flight records in real time without indexes.