Places App with MariaDB Connector/R2DBC

Overview

Places is a sample web application to manage location information. The application is compatible with MariaDB Connector/R2DBC and Single Node Transactions and Replicated Transactions topologies on MariaDB SkySQL.

Places demonstrates the power and versatility of MariaDB Connectors and Transactions services by managing arbitrary location data in JSON through a Google Maps interface.

Places Application

Places has three components:

  • React.js and Google Maps front-end web application

  • Java-based back-end application service

  • Single Node Transactions back-end database service, running on MariaDB SkySQL

Prerequisites

Clone the Repository

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

To download the Places code, clone the repository from GitHub:

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

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

Provision Database Services

Places requires a back-end database service using the InnoDB storage engine. The back-end app is compatible with Single Node Transactions and Replicated Transactions topologies. For the purposes of demonstration, this guide uses the Single Node Transactions topology on MariaDB SkySQL.

For additional information, see "Launch".

Create the Database Schema

Places uses the Google Maps API to provide map information and a Single Node Transactions service to store data on locations of interest to the user. You need to create a database and table to store this data for the application.

To create the schema for the Places application:

  1. Create a places database:

    CREATE DATABASE places;
    
  2. Create a Locations table:

    CREATE TABLE places.locations (
       id INT PRIMARY KEY AUTO_INCREMENT,
       name VARCHAR(100) NOT NULL DEFAULT '',
       description VARCHAR(500) DEFAULT '',
       type CHAR(1) NOT NULL DEFAULT '',
       latitude DECIMAL(9,6) NOT NULL,
       longitude DECIMAL(9,6) NOT NULL,
       attr LONGTEXT
          CHARACTER SET utf8mb4
          COLLATE utf8mb4_general_ci
          DEFAULT NULL
          CHECK(JSON_VALID(attr)))
    ENGINE = InnoDB
    AUTO_INCREMENT = 19
    DEFAULT CHARSET = utf8mb4;
    

Set Google Maps API Key

Places requires an active Google Maps API key. For information on obtaining this key, see Google Cloud.

Install Back-end Application Service

Places 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, allowing you to test and experiment with different languages and different MariaDB Connectors. These instructions are for Java using MariaDB Connector/R2DBC.

Navigate to the correct api directory:

$ cd /path/to/dev-example-places/api/r2dbc/

Configuring the Back-end Application Service

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

  1. Create properties file at src/main/resources/application.properties and populate it with your MariaDB SkySQL credentials:

    spring.r2dbc.url=r2dbc:mariadb://example.skysql.net:5001/places?sslMode=ENABLE_TRUST&clientSslCert=classpath:static/skysql_chain.pem
    spring.r2dbc.username=db_user
    spring.r2dbc.password=db_password
    spring.r2dbc.pool.initial-size=5
    spring.r2dbc.pool.max-size=10
    spring.r2dbc.pool.max-idle-time=30m
    
  2. Create a directory for static files:

    $ mkdir src/main/resources/static
    
  3. Copy your SkySQL CA file into the static directory:

    $ cp /path/to/skysql_chain.pem src/main/resources/static
    

Build the Back-end Application Service

To build the Java back-end application service:

$ mvn package

Start the Back-end Application Service

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

$ mvn spring-boot:run

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

Install Front-end

Places uses a React.js front-end web application. The front-end connects to the back-end applications 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-places/client/
    
  2. Edit the .env file to add your Google API Key:

    REACT_APP_GOOGLE_API_KEY=your_api_key
    
  3. Install the client application:

    $ npm install
    
  4. Start the client:

    $ npm start
    

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

Live Demonstration

Places is a sample web application for MariaDB SkySQL that demonstrates a Single Node Transactions service working with JSON data to record locations in Google Maps.

Places Application

The web interface opens onto a Google Map. It provides a Map view and a Satellite view. You can zoom in, zoom out, and place the figure to get a Street view. Clicking the "Add Location" button opens a form to add a location to the database.

Places allows you to create an Attraction, Restaurant, or Sports Venue. You can then specify a name, description, position in latitude and longitude, and category. When you click "Save" the application writes the location to the database:

+----+---------------------+-------------+------+------------+--------------+-------------------------+
| id | name                | description | type | latitude   | longitude    | attr                    |
+----+---------------------+-------------+------+------------+--------------+-------------------------+
| 19 | Washington Monument | NULL        | A    | 38.8895563 |  -77.0352546 | {"category":"Landmark"} |
+----+---------------------+-------------+------+------------+--------------+-------------------------+

Locations can contain arbitrary information in addition to the general fields in the table. These are stored on the attr column using JSON data.

  • Attraction:

    {
       "category":"Landmark",
    }
    
  • Restaurant:

    {
       "details":{
          "foodType":"Pizza",
          "menu":"www.giodanos.com/menu"
       },
       "favorites":[
          {
             "description":"Classic Chicago",
             "price":24.99
          },
          {
             "description":"Salad",
             "price":9.99
          }
       ]
    }
    
  • Sports Venue:

    {
       "details":{
          "yearOpened":1994,
          "capacity":23500
       },
       "events":[
          {
             "date":"10/18/2019",
             "description":"Bulls vs Celtics"
          },
          {
             "date":"10/21/2019",
             "description":"Bulls vs Lakers"
          },
          {
             "date":"11/5/2019",
             "description":"Bulls vs Bucks"
          },
          {
             "date":"11/5/2019",
             "description":"Blackhawks vs Blues"
          }
       ]
    }