Flights App with MariaDB Connector/Node.js

Clone the Repository

Flights 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 Flights application, clone the repository from GitHub:

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

$ cd dev-example-flights

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

Provision Database Service

Flights requires a database back-end service using the MariaDB Enterprise ColumnStore storage engine. It is compatible with MariaDB SkySQL Analytics topologies. For the purposes of demonstration, this guide uses the Single Node Analytics topology on MariaDB SkySQL.

For additional information, see "Launch".

Load Flights Data

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

• gen_flights_data.sh retrieves data from the US Department of Transportation. The data is saved in the data directory as a series of CSV files with the necessary flight information.

  The 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.

• create_and_load.sh creates a set of MariaDB ColumnStore tables on the database service, then imports each of the CSV files into the database.

  The script is configured to connect to MariaDB SkySQL and expects to find the skysql_chain.pem Certificate Authority (CA) file in the same directory.

To load data into the Single Node Analytics database service:

1. Download flight data:

   $ ./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-flights directory:

   $ cp ~/Downloads/skysql_chain.pem .
   

3. Use the create_and_load.sh with your credentials to initialize the database on your SkySQL database service:

   $ ./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 the Back-end Application Service

To connect the back-end app to your MariaDB SkySQL database service, you must first 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
   DB_NAME_1 = orders
   

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,
       database: process.env.DB_NAME_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

Once you have the back-end application service configured and installed, you can start the service:

$ npm start

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