100 Days of Learning: Day 6 – Let’s build our own Lego database using MariaDB in AWS RDS and OpenFaaS

by Posted byAndré Jacobs

Photo by brickset.com

Here is my Log book

In Severless for Everyone Else Alex shows an example of using a node.js function that uses Postgre as a database.

I am going to see if I can setup MariaDB (MySQL) in my free tier of AWS.

For this little project I want to have a Lego set database. I will be using Python for the function coding (most likely I will be using Flask and SQLalchemy).

This learning / development will span over a few days depending on how much free time I get.

I want to be able to:

  • Post new entries and persist them to a MariaDB database hosted in AWS RDS.
  • Get all entries from the database.
  • The OpenFaaS functions will be using secrets and environment variables.
  • Fetch and persist the product image into the table.
  • Run a scheduled cron (I guess) job to do the above (fetch image).

Setting up a MariaDB database in AWS

First hurdle was trying to log in with my non-root admin user and was asked to change the password. The first thing you must do once you create an AWS account is to create a new admin user that is not the root user account. You should practically never use the root user on AWS. The problem for me was that I setup a strict password policy and it was trial and error (and logging in as root user) before I could reset the expired password. P.S. Symbol did not include the character > that was generated by 1Password.

I will be following the official AWS documentation and tutorial for RDS

  • Open up the AWS Console
  • I am using the eu-west2 region which is London
  • Search for RDS and select it
  • Scrolled down a bit until I found this. Doubled checked the region is correct.

  • Create database
  • Select MariaDB as the engine
  • Select the Template to be Free tier
  • DB instance identifier: openfaas
  • Generated the master password in 1Password
  • DB instance class: db.t2.micro — 1vCPU, 1 GIB RAM.
  • Disabled Storage autoscaling
  • Under Connectivity
  • Enabled Public Access
  • Select Create new VPC security group (this should allow my current IP to access the instance)
  • Entered a name for this new VPC
  • Additional configuration
  • Initial database name: openfaasdb
  • Disabled automatic backups since I won’t need it for this learning exercise
  • Create Database!

  • Now we just sit and wait till it has been created.

Installing MySQLWorkbench as GUI

Incase you did not know, MySQL was acquired by Oracle a good while ago. MariaDB sprung up as a fork from MySQL (we won’t get into the politics here).

The best GUI I have found to use on Mac with MySQL / MariaDB is MySQLWorkbench (owned by Oracle).

You can download the installer here. Select "No thanks, just start my download."

  • Add a new connection. + button next to MySQL Connections.
  • On the RDS console, select the newly created database.
  • Look for the Endpoint and Port values.
  • Copy the Endpoint value and paste it as the Hostname in MySQLWorkbench.
  • Do the same with the port if you changed the default to not be 3306.
  • Enter the username (default is admin).
  • Store the password in your keychain.
  • Set a name for this new connection: openfaasdb – amazon rds
  • Click the Test Connection button.

  • Continue Anyway. You get this warning whenever you connect to MariaDB servers.
  • If the connection succeeded then you will get the following message.

  • Click Ok
  • Double click the new connection. Get presented with the MySQL warning again. Select the "do not bother me about this BS again".
  • Select the Schemas tab.
  • Right click on the new database (mine from the example is openfaasdb) and Set as Default Schema. This way in queries I don’t have to specify the schema.

Create the database table

Open a new query in MySQLWorkbench and paste the following.

CREATE TABLE `openfaasdb`.`legosets` (
  `pkID` INT NOT NULL AUTO_INCREMENT,
  `LegoID` INT NOT NULL,
  `Description` VARCHAR(200) NULL,
  `ProductURL` VARCHAR(4096) NULL,
  `ImageURL` VARCHAR(4096) NULL,
  PRIMARY KEY (`pkID`),
  UNIQUE INDEX `LegoID_UNIQUE` (`LegoID` ASC));

Execute the query and with a bit of luck the new table has been created.

Let us populate some sample data into the table. Again execute the following queries.

INSERT INTO `openfaasdb`.`legosets` (`LegoID`, `Description`, `ProductURL`, `ImageURL`) VALUES ('21322', 'Pirates of Barracuda Bay', 'https://www.lego.com/en-gb/product/pirates-of-barracuda-bay-21322', 'https://live.staticflickr.com/65535/49698377257_be3f773feb_b.jpg');
INSERT INTO `openfaasdb`.`legosets` (`LegoID`, `Description`, `ProductURL`, `ImageURL`) VALUES ('21325', 'Medieval Blacksmith', 'https://www.lego.com/en-gb/product/medieval-blacksmith-21325', 'https://images.brickset.com/sets/AdditionalImages/21325-1/21325-1_95850.jpg');

Note: I will later be altering the table to include a BLOB to store a cached version of the product Image (download and store ImageURL)

That is a wrap for tonight. Tomorrow I need to setup my Mac for Python 3 development first and then explore the OpenFaaS Flask template and possibly see how to add SQLalchemy as a dependency.