DEV Community: Rob Hedgpeth

iOS Auto Code Signing: How Mobile DevOps Can Simplify Your Life

Rob Hedgpeth — Wed, 06 Apr 2022 18:44:33 +0000

Look, there’s no sugar coating it, code signing, or the process of proving an app has been created by a known source, for iOS can be painful. Like, really painful. There are several assets and moving parts that you need to remember, and, along the way, it’s easy to miss or flat out forget to do something. But surely there has to be a way to simplify things?

In this guide, you’ll learn how to use a Bitrise, an end-to-end mobile DevOps solution, to automatically code sign an iOS app.

What is automatic code signing?

Signing an application allows the system to identify who signed the application and to verify that the application has not been modified since it was signed. So, automatic code signing, in this context, means automatically managing the provisioning profiles that are available on your Apple Developer Portal account. This is done by setting up some form of authentication to your Apple account, and then using Bitrise to download and install the provisioning profile for your app during the build process.

So, from a high level, to use auto code signing with Bitrise, you will need to:

Upload the code signing certificates to Bitrise.
Connect your Apple Developer Portal account to Bitrise either via Apple ID or via the App Store Connect API.
Use one of the Bitrise steps that support automatically managing code signing assets.

How do you auto code sign with Bitrise?

Recently, as detailed in this article, Bitrise has simplified the code signing process. The gist is that there are now two ways to automate code signing on Bitrise: using Xcode Build/Archive Step (with iOS Auto Provision Step merged into it) and not using these steps, but the 'Manage iOS Code Signing' Step instead.

In this tutorial we’ll be specifically looking at the process of using the “Xcode Archive & Export for iOS” step to automatically sign assets for an iOS application.

Xcode Archive & Export for iOS

As I previously mentioned, the “Xcode Archive & Export for iOS” step automatically manages your code signing assets, archives and then exports an .ipa file. It accomplishes this by utilizing xcodebuild, a command-line tool that allows you to build, query, analyze, test, and archive operations on your Xcode projects and workspaces.

Alright, let’s dive in!

Creating code

First things first, in order to be able to sign an app you’re going to need to have some assets like certificates, app identifiers, and provisioning profiles already created and ready to use.

If you don’t already have the necessary code signing files ready, you can generate new files by following the instructions here.

It’s important to note that before you can proceed, all Bitrise iOS code signing methods require you to export and upload your iOS code signing certificates directly to Bitrise.

Provide access to App Store Connect

Next, Bitrise requires access to App Store Connect to be able to access your app’s signing assets.

So, to do this, log into the Apple Developer Portal and navigate to App Store Connect. Then, from there, click “Users and Access”.

Within “Users and Access” in App Store Connect you’ll have the ability to configure two different approaches that Bitrise will use (later on in this tutorial) to connect.

Using an API Key
Using an Apple ID

Configure Bitrise for App Store Connect

After configuring your desired approach for establishing an Apple Service connection within the Apple Developer Portal it’s time to jump into Bitrise to hook everything up.

Once you’ve logged into your Bitrise account navigate to your “Profile settings” within your user context menu.

Within your profile settings select the “Apple Service Connection” in the left navigation menu. There you’ll have the ability to configure your Apple Service connection using an API Key (recommended) or Apple ID.

Set up Apple Service connection within your app

Assuming you have an existing iOS project within Bitrise, head back to the Bitrise Dashboard and select your app.

Tip: If you haven’t added an iOS project yet be sure to check out this guide to walk you through the process!

Click the “Teams” and scroll down to the “Apple Service connection” where you can select the connection you’d like to use.

Set up the Bitrise workflow

Finally, it’s time to configure our project workflow. Start by navigating to the “Workflow” tab. If you’ve created a new iOS project your workflow will look the same or similar to the following by default.

To set up auto code signing for our iOS app, we’re going to be modifying the workflow by adding and configuring one additional step; Xcode Archive & Export for iOS.

Xcode Archive & Export for iOS

Start by adding the “Xcode Archive & Export for iOS” Step after the “Bitrise.io Cache:Push” Step. This Step will be used to code-sign the previously built assets and then export the .ipa (and other assets).

Add the “Xcode Archive & Export for iOS” Step by clicking the (+) button and searching for “Xcode Archive & Export for iOS”. Easy enough, right?

After targeting the “Xcode Archive & Export for iOS” step and loading the details in the panel, scroll to the Input variables section. There you’ll notice three key configuration settings:

Project path: the path to the Xcode Workspace within your project.
Scheme: defines a collection of targets to build, a configuration to use when building, and a collection of tests to execute.
Distribution method: defines how Xcode should export the archive. The distribution options are development, ad-hoc, app-store, and enterprise.

The great news here is that these values have already been added for you, as part of the initial iOS project setup. You can see what’s already been configured by clicking and displaying the Env Vars tab.

Next, you need to choose the automatic code signing method, which defines which Bitrise Apple Service connection (e.g. API Key or Apple ID) option will be used.

And, finally, if you scroll down just a bit within the Automatic code signing section, you’ll see two required fields, Code signing certificate URL and Code signing certificate passphrase, which have already been configured due to you adding the code signing certificates earlier in this walk-through.

I point this out so you know why you were required to do that step before.

Build & output artifacts

And that’s it, that’s all there is to it! Now that you’ve finished the workflow configuration, save the changes and navigate back to the project landing page to start a new build.

Once the build has completed (hopefully successfully), navigate into the build and view the output within the Artifacts tab.

Next steps

Thank you for reading this article! As you can see, the process of signing your iOS app is simplified with Bitrise and the Xcode Archive & Export for iOS Step. Now all that’s left to do is try it out for yourself!

If you have any questions or comments please reach out to me on Twitter or LinkedIn.

Happy building!

Introducing the world’s first Mobile DevOps assessment: MODAS

Rob Hedgpeth — Fri, 01 Apr 2022 16:33:52 +0000

In this article, we’ll take a look at the world’s first Mobile DevOps, Performance, Productivity, and Maturity Assessment, or MODAS for short, and what it means for teams wanting to gain more insight into optimizing their development, testing and deployment processes.

The age of mobile innovation is upon us. We live in an unprecedented time of both technological opportunity and expectation. We have the ability to create all kinds of amazing solutions, but, at the same time, we have an enormous amount of room for improvement. But in order to improve we need to know how we compare, relative to others.

And as you know, we can’t improve what we don’t measure, which I’m so excited to announce that Bitrise has unveiled the world’s first Mobile DevOps Performance, Productivity, and Maturity Assessment (MODAS), which is a survey designed to provide insight and actionable guidance for mobile product organizations of all sizes and industries.

TL;DR - The gist of MODAS is to provide the mobile community with a comprehensive comparative analysis of modern mobile development, testing, and delivery practices.

Assessing DevOps maturity

As I touched on above, the purpose of MODAS is to assess a mobile team’s Mobile Devops performance, productivity, and maturity. To do this, the survey has divided the mobile app delivery process into five stages.

Creation
Testing
Deployment
Monitoring
Collaboration

Each stage features a mandatory performance metric question, followed by optional clarifying questions and statements. The optional questions and statements will enable the survey to both gather more detailed data about how others in your industry are performing (and why) and to provide you with detailed recommendations about how to improve in each stage in the mobile delivery lifecycle.

What's in it for you?

While the assessment only takes a few minutes to complete, the impact for you and your team will be great and long lasting. Once we’ve collected enough responses, following the initial launch of MODAS, we’ll be able to use the data to benchmark your results against those of organizations similar to yours.

To wrap up the assessment, you’ll have the opportunity to submit your email along with your responses. This will allow Bitrise to follow up with you later, once the survey has hit a threshold of responses, with a benchmark of your results.

Note: Know that your email will not be used for any marketing purposes. It will strictly be used to communicate your benchmarked results.

The benchmarks you receive will help shed some light on the areas your organization has relative Mobile DevOps maturity, as well as highlighting areas that could use improvement.

Along with providing you information on how you stack up, in terms of Mobile DevOps maturity, Bitrise will also be launching a suite of resources to provide more information on how you can improve in the areas that have been targeted for improvement.

So, if you’d like to gain more insight into how your mobile development, testing, monitoring, delivery, and collaborations stack up... [you can jump into the MODAS survey now]((https://survey.alchemer.eu/s3/90442780/Mobile-DevOps-Performance-Productivity-and-Maturity-Assessment)!

Happy building!

Developer Quickstart: PHP Data Object (PDO) and MariaDB

Rob Hedgpeth — Tue, 15 Feb 2022 21:47:13 +0000

In a previous article I introduced you to the process of connecting to and communicating with MariaDB databases using the MySQL improved extension, MySQLi, for PHP. Ultimately, when you’re writing PHP code to connect to and interact with MariaDB you’re likely going to use one of two popular options, MySQLi or PHP data objects (PDO).

The gist of the PDO extension is that it defines a lightweight, consistent interface for accessing databases in PHP, just like MySQLi. In fact, both PDO and MySQLi both offer an object-oriented API, but MySQLi also offers a procedural API - which can make it easier for PHP newbies to understand.

Now, if you’re familiar with the native PHP MySQL driver, you might find the migration to the procedural MySQLi interface to be easier. On the other hand, once you’ve mastered PDO, you can use it with any database you desire, which can be incredibly useful when switching from another database to, say, MariaDB.

In this article, I’m going to dive into PDO and how you can use it to communicate with MariaDB. So let’s get to it and jump into an application to get an idea of how to connect to and query a MariaDB database using PDO.

In this article I’m going to highlight some of the fundamental details of using PDO to connect to and communicate with a MariaDB database. Everything I’ll be exploring is based on the code for the Rolodex application, and if you’d like to dive into the code you can check it out here.

Prepare the database

Before jumping into the code for the application it’s important to note that it uses a single database called rolodex.

CREATE DATABASE `rolodex`;

The rolodex database contains a single table, contacts, that is used to store basic information.

CREATE TABLE `rolodex`.`contacts` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `age` INT(3) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  PRIMARY KEY  (`id`)
);

The SQL necessary to run the Rolodex application can be found in the schema.sql file.

Configuring the application

To facilitate the use of a MariaDB database within the Rolodex PHP application I’ve created a new file called config.php that contains the configuration settings and database connection object that can be reused across PHP pages. Connecting to and communicating with an underlying MariaDB database is facilitated by the PDO extension.

config.php

<?php

$dsn = "mysql:host=<insert_host_address_here>;dbname=rolodex;charset=utf8mb4";

$options = [
  PDO::ATTR_EMULATE_PREPARES   => false, // Disable emulation mode for "real" prepared statements
  PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // Disable errors in the form of exceptions
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // Make the default fetch be an associative array
];

try {
  $pdo = new PDO($dsn, "<insert_user_here>", "<insert_password_here>", $options);
} 
catch (Exception $e) {
  error_log($e->getMessage());
  exit('Something bad happened'); 
}

?>

Within the config.php file I’ve started by defining variables that hold the host address, username, password and default database that are used to create a new PDO connection object, which contains a variety of configuration options that you can use to tailor to your environment.

Executing SQL

Using, and reusing, the PDO connection within config.php is as easy as including it within a PHP code block on another PHP page.

<?php
// Include the database connection file
include_once("config.php");
...
?>

Then, with an established connection, you have the ability to use a plethora of capabilities from the PDO extension, including executing queries using PDO::query. Note that I’ve also demonstrated how you can map the results directly into a class called Contact.

Selecting data

<?php
// Include the database connection file
include_once("config.php");

// PHP class 
class Contact {
    public $id;
    public $name;
    public $age;
    public $email;
}

// Fetch contacts (in descending order)
$contacts = $pdo->query( "SELECT * FROM contacts ORDER BY id DESC")->fetchAll(PDO::FETCH_CLASS, 'Contact');
?>

Selecting contacts using PDO:query

Or, in the case that you need to handle dynamically inserted parameter values, you can use PDO::prepare.

Inserting data

$stmt = $pdo->prepare("INSERT INTO contacts (name,age,email) VALUES(?, ?, ?)");
$stmt->execute([$name, $age, $email]);

Inserting contacts using PDO::prepare

Updating data

$stmt = $pdo->prepare("UPDATE contacts SET name = ?, age = ?, email = ? WHERE id = ?");
$stmt->execute([$name, $age, $email, $id]);

Updating contacts using PDO::prepare

Deleting data

$stmt = $pdo->prepare("DELETE FROM contacts WHERE id = ?");
$stmt->execute([$id]);

Deleting contacts using PDO::prepare

As you can see, getting started with PDO and MariaDB is easy, but we’ve only scratched the surface of what’s possible. If you’d like to see for yourself what else is possible with PHP and MariaDB, start by checking out the full source code for the Rolodex application in the new PHP Data Object Quickstart GitHub repository.

Learn more

And if you’d like to learn even more about what’s possible with MariaDB, be sure to check out the Developer Hub and our new Developer Code Central GitHub organization. There you can find much more content just like this spanning a variety of other technologies, use cases and even programming languages.

You can also dive even deeper into MariaDB capabilities in the official documentation.

And, as always, we’d be nothing without our awesome community! If you’d like to help contribute you can check out the open source projects on the MariaDB GitHub, send feedback directly to us at developers@mariadb.com, or join the conversation in the new MariaDB Community Slack!

Developer Quickstart: PHP and MariaDB

Rob Hedgpeth — Fri, 14 Jan 2022 11:03:00 +0000

The PHP programming language has long been considered a fairly light and simple solution for developers to use to create robust and creative web applications. In fact, that’s as true now as it has ever been as past few versions of PHP have made the language more appealing than ever.

Like many other languages, PHP has the ability to take advantage of the power of MariaDB, and it’s actually a pretty simple and straightforward process.

But talk, text in this case, is cheap. So to demonstrate this I’ve created a simple PHP web application called Rolodex to manage contacts.

In this article I’m going to highlight, from a high level, some of the fundamental details of using PHP to connect to and communicate with a MariaDB database. Everything I’ll be exploring is based on the code for the Rolodex application, and if you’d like to dive into the code you can check it out here.

Preparing the database

Before jumping into the PHP code for the application it’s important to note that it uses a single database called rolodex.

CREATE DATABASE `rolodex`;

The rolodex database contains a single table, contacts, that is used to store basic information.

CREATE TABLE `rolodex`.`contacts` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `age` INT(3) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  PRIMARY KEY  (`id`)
);

The SQL necessary to run the Rolodex application can be found in the schema.sql file.

Tip: If you don't have an instance of MariaDB up and running yet you can find more information on how to get started in this MariaDB Quickstart Guide.

Configuring the application

<?php
// Basic connection settings
$databaseHost = '<host_address>';
$databaseUsername = '<user_name>';
$databasePassword = '******';
$databaseName = 'rolodex';

// Connect to the database
$mysqli = mysqli_connect($databaseHost, $databaseUsername, $databasePassword, $databaseName);

Within the config.php file I’ve started by defining variables that hold the host address, username, password and default database that are used to create a new mysqli connection object, which contains a variety of configuration options that you can use to tailor to your environment.

Executing SQL

Using, and reusing, the mysqli connection within config.php is as easy as including it within a PHP code block on another PHP page.

<?php
// Include the database connection file
include_once("config.php");
...
?>

Then, with an established connection, you have the ability to use a plethora of capabilities from the mysqli extension, including executing queries using mysqli_query.

Selecting data

<?php
// Include the database connection file
include_once("config.php");

// Fetch contacts (in descending order)
$result = mysqli_query($mysqli, "SELECT * FROM contacts ORDER BY id DESC"); 
?>

Selecting contacts with mysqli_query

Or, in the case that you need to handle dynamically inserted parameter values, you can use mysqli_prepare.

Inserting data

$stmt = $mysqli->prepare("INSERT INTO contacts (name,age,email) VALUES(?, ?, ?)");

Inserting contacts using mysqli_prepare

Updating data

$stmt = $mysqli->prepare("UPDATE contacts SET name=?, age=?, email=? WHERE id=?");
$stmt->bind_param("sisi", $name, $age, $email, $id);
$stmt->execute();

Updating contacts using mysqli_prepare

Deleting data

$stmt = $mysqli->prepare("DELETE FROM contacts WHERE id=?");
$stmt->bind_param("i", $id);
$stmt->execute();

Deleting contacts using mysqli_prepare

As you can see, getting started with PHP and MariaDB is easy, but we’ve only scratched the surface of what’s possible. If you’d like to see for yourself what else is possible with PHP and MariaDB, start by checking out the full source code for the Rolodex application in the new PHP Quickstart GitHub repository.

Learn more

And if you’d like to learn even more about what’s possible with MariaDB be sure to check out the Developer Hub and the new MariaDB Developer Code Central GitHub organization. There you can find much more completely open-source content just like this spanning a variety of other technologies, use cases and even programming languages.

You can also dive even deeper into MariaDB capabilities in the official MariaDB documentation.

And, as always, MariaDB is deeply rooted in open-source and would be nothing without their awesome community! If you’d like to help contribute directly to MariaDB you can find them on GitHub, send feedback directly to their Developer Relations team at developers@mariadb.com, or join the conversation in the new MariaDB Community Slack!

Happy coding!

Get Started with MariaDB in 3 Steps

Rob Hedgpeth — Wed, 27 Oct 2021 18:28:15 +0000

OK, we all know that there are a ton of database options out there these days. Relational (SQL), NoSQL, Distributed SQL (NewSQL). And that’s really just the tip of the iceberg. Choosing a new database is likely one of the most important architectural decisions that go into creating or integrating into a new solution or application.

The reasons for choosing a database can be everything from extremely simple to pretty damn complex, but, ultimately, it all boils down to the type of data workload you’re looking to handle. But that’s not something I’m going to get into, because that’s the kind of research you can certainly go off and do on your own.

Instead, in this article, I want to focus on the quickest way you can go from nothing to having a local instance of a MariaDB database up, running and ready to use so you can start checking things out, using a Docker container, for yourself.

OK, let’s get into it.

Step 1 - Install Docker

Now, unless you’ve been living under a rock for the past several years I'm willing to bet you’ve at least heard of Docker, or, at the very least, the concept of containerization. And, if not, no biggie check this out if you’re curious.

Either way, the good news is that using Docker on your local machine is incredibly easy. Just download and install Docker Desktop.

Step 2 - Start a MariaDB Docker Container

Once you’ve successfully installed Docker on your machine you’re ready to pull the MariaDB image and spin up a container (which will contain a MariaDB database instance).

For this you’re going to use the Official MariaDB Docker Image hosted at https://hub.docker.com.

Open up a new terminal window and execute the following command.

$ docker run -p 127.0.0.1:3306:3306  --name mdb -e MARIADB_ROOT_PASSWORD=Password123! -d mariadb:latest

The previous statement will pull down the latest version of the Official MariaDB image and spin up a new container on localhost (127.0.0.1), exposing port 3306 and allow you to connect using the root user with password Password123!.

Hint: I guess this is something like Step 2-b, but you can confirm that the docker run command has successfully pulled the MariaDB Image and spun up a container by executing docker ps, which will show you all of the containers currently running.

Step 3 - Connect to MariaDB

Lastly, you don’t need to install anything else to start using MariaDB. While you certainly can use other types of clients or tools to connect to and communicate with a MariaDB database, for this brief walkthrough you don't have to worry about any of that. Everything you need you already have. Just use the mariadb command-line client that’s included within the MariaDB Docker container.

Execute the following to connect to MariaDB using the command-line client.

$ docker exec -it mdb mariadb --user root -pPassword123!

Which should result in something like the following:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.6.3-MariaDB-1:10.6.3+maria~focal mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

And that’s it! That’s all you need to connect to and start using (querying) MariaDB.

Happy coding, friends!

JSON + MariaDB: How Hybrid Data Models Allow You to Have Your Cake and Eat It Too

Rob Hedgpeth — Tue, 03 Aug 2021 19:27:01 +0000

I’m willing to bet that if you’re reading this article you’ve at least heard of MariaDB. Heck, maybe you’ve even used it at some point. But, if not, I highly recommend that you give it a quick glance as, at the highest level, it’s a pretty solid, free (yea, free) open-source database solution that you can use for anything from some lightweight tinkering to supporting fully-fledged production grade applications.

Tip: To get started with MariaDB, just in case you want to follow along with the samples in this article, you can download it directly from the website, start a service using their database-as-a-service offering SkySQL or spin up a container from the official MariaDB Docker image.

In the beginning, and at its core, MariaDB is a relational database management system, or RDBMS for short, but diving deeper into its capabilities you’ll quickly discover that it's more than that.

Much, much more.

One of those capabilities is its ability to handle JavaScript Object Notation (JSON) formatted data, completely free and out-of-the-box. OK, but why is that important? Well, in the context of databases, JSON has often been thought of as something you’d use with NoSQL solutions. And that makes sense as one of the problems the “NoSQL Revolution” set out to solve was flexibility, or having the ability to create, update and remove data and the structures they're housed in without having to modify things like those pesky relational database schemas.

Structured vs. Semi-structured data

Keying off of the success that NoSQL solutions have been able to achieve by using semi-structured data in that time, over the past few years JSON integrations have made their way into the relational world. And for good reason. The ability to store JSON documents within a relational database allows you to create hybrid data models, containing both structured and semi-structured data, and enjoy all of the benefits of JSON without having to sacrifice the advantages of relational databases (e.g. SQL and all things data integrity).

"Keying off of the success that NoSQL solutions have been able to achieve by using semi-structured data in that time, over the past few years JSON integrations have made their way into the relational world."

OK, enough of this “setting the stage” b’niss. Let’s check out some of the JSON functionality that’s available in MariaDB and how you can use it. I say some because MariaDB contains a large amount of JSON functionality. In fact, too much to cover in just a single blog post. However, we can certainly hit the high points, which should give you the foundation you need to dive even deeper.

Structured Data + Semi-Structured Data

There are a multitude of use cases where it may make sense to combine structured and semi-structured data. That’s just the world of software development for ya. However, I’ve always found it easiest to consume new technologies by focusing on a simple, (hopefully) relatable use case that you can then use to get your own creative juices flowing.

To help walk-through the JSON capabilities that are available within MariaDB I’m going to be using a hypothetical application. This application will only contain one table, called locations, that will store, yep, you guessed it, locations. Simple enough, right?

We won’t be using any kind of front-end management, but imagine that the locations could be represented on some kind of map like the following.

Using location data to place points of interest on a map

From the simplest standpoint, geographic locations, no matter the type, contain foundational information such as the name, type, longitude and latitude. But, depending on the type, each location could have different details.

Structured and semi-structured data in the same table

Table Creation

Using JSON within MariaDB is as easy as enabling the ability to store JSON data within a table. In fact, the SQL used to create a new location table should look very familiar.

CREATE TABLE locations (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,  
    type CHAR(1) NOT NULL,
    latitude DECIMAL(9,6) NOT NULL,
    longitude DECIMAL(9,6) NOT NULL,
    attr JSON, 
    PRIMARY KEY (id)
);

Note that the attr column included within the locations table is defined with a JSON data type. More specifically the column is using a JSON alias data type. That means there’s no actual JSON data type, but, instead, the JSON specified data type is converted into an existing data type within MariaDB.

Taking a closer look, we can use the SHOW CREATE query to inspect the details of what’s actually been created.

SHOW CREATE TABLE locations;

Executing the previous statement will yield the following result.

CREATE TABLE locations (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,  
    type CHAR(1) NOT NULL,
    latitude DECIMAL(9,6) NOT NULL,
    longitude DECIMAL(9,6) NOT NULL,
    attr LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL 
    CHECK (JSON_VALID(`attr`)
    PRIMARY KEY (id)
);

Notice that the data type for the attr column is LONGTEXT. Beyond that you’ll see that there are a few constraints added to the field for the character set and collation. Most importantly, however, is the CHECK constraint, which indicates a function that will be executed when the data in attr has been modified, either through insertion or updating.

The JSON_VALID() function is a predefined function that serves to receive JSON data (in the form of a string) and validate whether or not it’s valid. Valid meaning properly formed JSON data.

Using the JSON_VALID to validate JSON data before storing it

Inserting Data

The JSON data that you insert is contained within quotes, just like any other string-based information you’d insert. The only difference is that the string must be valid JSON.

INSERT INTO locations (type, name, latitude, longitude, attr) VALUES 
     ('R', 'Lou Malnatis', 42.0021628, -87.7255662,
  '{"details": {"foodType": "Pizza", "menu": "https://www.loumalnatis.com/our-menu"}, 
        "favorites": [{"description": "Pepperoni deep dish", "price": 18.75}, 
{"description": "The Lou", "price": 24.75}]}');

Note that you can specify different JSON data, with completely different structures, within the insert to the same table. Which makes sense, of course, because that’s the entire point!

INSERT INTO locations (type, name, latitude, longitude, attr) VALUES 
     ('A', 'Cloud Gate', 41.8826572, -87.6233039, 
'{"category": "Landmark", "lastVisitDate": "11/10/2019"}');

Querying JSON Data

You’ve probably picked up on it by now but managing JSON data within MariaDB really boils down to using predefined functions. For the rest of this article we’ll be taking a look at several of the functions that are available to you.

Reading Scalar Data

The JSON_VALUE() function returns a JSON scalar value from the specified path in the specified data. In the following example I’ve used the attr column as the “specified data”, but note that the JSON supplied to the function could just as well be a raw string of JSON data.

SELECT name, latitude, longitude, 
    JSON_VALUE(attr, '$.details.foodType') AS food_type
FROM locations
WHERE type = 'R';

Which, depending on the data previously inserted into the locations table, could yield a result similar to the following.

And if you’re wondering “what about handling null/non-existent values?”, because, due to the nature and, really, the purpose of semi-structured, that’s kind of the point. Yep, the JSON_VALUE() function handles that.

Note: This whole query then displaying the tabular results type flow is something I’ll keep going with throughout this article. Just a heads up!

You’re also not limited to using the JSON functions strictly as part of the SELECT clause. You can just as easily use them within the filtering portions.

SELECT id, name, latitude, longitude
FROM locations
WHERE type = 'S' AND
    JSON_VALUE(attr, '$.details.yearOpened') = 1924;

Reading Object Data

The JSON_QUERY() function accepts JSON data and a JSON path and returns JSON data. The difference between JSON_VALUE() and JSON_QUERY() is that JSON_QUERY() returns entire JSON object data.

SELECT name, description, 
    JSON_QUERY(attr, '$.details') AS details
FROM locations
WHERE type = 'R'

The JSON_QUERY() function can also return arrays.

SELECT name, description, 
    JSON_QUERY(attr, '$.teams') AS home_teams
FROM locations
WHERE type = 'S';

Creating Indexes

At this point you may be wondering with all this querying going on, what about being able to create (performance enhancing) indexes? Is that even possible? You’re damn right it is!

It all starts with the creation of a virtual column.

ALTER TABLE locations ADD COLUMN 
    food_type VARCHAR(25) AS (JSON_VALUE(attr, '$.details.foodType')) VIRTUAL;

Then you can use the virtual column, in combination with other virtual or persistent columns, to create a new index.

CREATE INDEX foodtypes ON locations(food_type);

Modifying JSON Data

As you know, reading data is really only half the battle. To really get the value out of being able to store JSON data within a relational database you also need to be able to modify, or write, it. Luckily, MariaDB provides a bunch of functionality for this as well.

Inserting Fields

The JSON_INSERT() function returns JSON data created by inserting one or more path/value pairs to JSON data.

UPDATE locations
SET attr = JSON_INSERT(attr,'$.nickname','The Bean')
WHERE id = 8;

Inserting Arrays

You can also create new arrays using the JSON_ARRAY() function. Then, within the JSON_INSERT() function, the new array can be inserted into the specified JSON data (in this case the attr field).

UPDATE locations
    SET attr = JSON_INSERT(attr, 
 '$.foodTypes', 
        JSON_ARRAY('Asian', 'Mexican'))
WHERE id = 1;

Adding Array Elements

Using the JSON_ARRAY_APPEND() function you can modify an existing array by adding one or more elements.

UPDATE locations
    SET attr = JSON_ARRAY_APPEND(attr, 
                '$.foodTypes', 'German’)
WHERE id = 1;

Removing Array Elements

The JSON_REMOVE() can be used to remove an array element, specified by index.

UPDATE locations
    SET attr = JSON_REMOVE(attr,       
                           '$.foodTypes[2]')
WHERE id = 1;

Tip: The JSON_REMOVE() function is so powerful that it can be used to return a resulting JSON document after removing any JSON data (i.e. array element, object, etc.) at the specified path(s) from JSON data.

Hybrid Data Querying

It may be the case that you want to create JSON data from structured data. For that you can use the JSON_OBJECT() function.

SELECT 
JSON_OBJECT('name', name, 'latitude', latitude, 'longitude', longitude) AS data
FROM locations
WHERE type = 'S';

Merging Data

You can merge the data returned from the JSON_OBJECT() function and merge it with existing JSON data by using the JSON_MERGE() function. Notice below that you can create an entirely new JSON object, using the JSON_OBJECT() function, and then use the JSON_MERGE() function to combine, or merge, it with the value of the attr field.

SELECT 
  JSON_MERGE(
    JSON_OBJECT(
        'name', name, 
        'latitude', latitude,
        'Longitude', longitude), 
    attr) AS data
FROM locations
WHERE type = 'R';

JSON to Tabular Data

In MariaDB 10.6, currently the latest version, the JSON_TABLE() function was added. This new function enables you to transform JSON data directly into tabular format, which can even be used directly within a FROM clause to join to other tables (or tabular data).

SELECT l.name, d.food_type, d.menu
FROM
    locations AS l,
JSON_TABLE(l.attr,
         ‘$’ COLUMNS(
                food_type VARCHAR(25) PATH ‘$.foodType’,
                menu VARCHAR(200) PATH ‘$.menu’)
           ) AS d
WHERE id = 2;

Tip: You can find a more detailed article on the new JSON_TABLE() function here!

Enforcing Data Integrity

Lastly, I’d like to touch on the ability you have to be able to enforce data integrity within JSON data that exists within MariaDB. In more plain English that means that you have the ability to create constraints, or requirements, for the types of JSON that is allowed to exist within your tables.

Below is an example of how you can create a new CONSTRAINT, in this case named check_attr, that specifies that for each location of type ‘S’ the JSON data within it has to fit particular criteria. Namely you can control things like the data types for properties or values, whether a property must exist, and even the length of the values within a specified property. This is all accomplished using the JSON functions within MariaDB. As you can see it’s extremely flexible and powerful.

ALTER TABLE locations ADD CONSTRAINT check_attr
    CHECK(
        type != 'S' OR (type = 'S' AND 
            JSON_TYPE(JSON_QUERY(attr, '$.details')) = 'OBJECT' AND
            JSON_TYPE(JSON_QUERY(attr, '$.details.events')) = 'ARRAY' AND
            JSON_TYPE(JSON_VALUE(attr, '$.details.yearOpened')) = 'INTEGER' AND
       JSON_TYPE(JSON_VALUE(attr, '$.details.capacity')) = 'INTEGER' AND
       JSON_EXISTS(attr, '$.details.yearOpened') = 1 AND
            JSON_EXISTS(attr, '$.details.capacity') = 1 AND
            JSON_LENGTH(JSON_QUERY(attr, '$.details.events')) > 0));

Next Steps

Thanks so much for reading this article on how you can combine the power of MariaDB with the flexibility of JSON. We were really only able to scratch the surface of what’s available within MariaDB.

The full list of JSON functions currently available within MariaDB.

Ultimately, we all learn in different ways. If you’d like to learn even more about the JSON functionality that’s available, and how you can start creating hybrid data models using MariaDB, please check out the following resources I’ve also put together.

And if you have any questions, comments or suggestions please feel free to reach out to me at robh@mariadb.com or @probablyrealrob on Twitter.

Reminder: To get started with MariaDB (completely for free) you can download it directly, start a service using their database-as-a-service offering SkySQL or spin up a container from their Docker image.

Happy coding!

Getting Started with MariaDB using Docker, Java Spring and JDBC

Rob Hedgpeth — Wed, 29 Jul 2020 19:26:22 +0000

Since being forked from MySQL back in 2009, MariaDB has become one of the most popular databases of choice for developers over the past decade. While many technologists have likely gravitated to it as a solution due to its open source roots and that it's rooted in the relational database world, that really only begins to scratch the surface of what MariaDB has to offer.

Over the years MariaDB has diverged from MySQL by adding many features and functionality, much of which we won't be able to dive into within the context of this article. However, one thing, above all else, has remained the same; providing a modern, open-source, high quality database solution that developers can use to power their innovation.

But before you can dive into MariaDB and check out all it has to offer you'll need to answer the most fundamental of questions; how do you get started using MariaDB?

MariaDB and Java Database Connectivity (JDBC)

It likely comes as no surprise that Java, and ultimately the Java Virtual Machine (JVM) environment as a whole, has been an extremely popular option used by developers to create applications.

With that in mind, I've written this short walkthrough to take you through the steps for getting started with MariaDB (Community Server) using a Docker image, Java and the MariaDB JDBC client.

In this walkthrough you'll be utilizing MariaDB and JDBC to create a simple (Maven based) "To do" application that, using Java Spring, will expose a variety of endpoints to use to perform basic CRUD (create-read-update-delete) operations on a MariaDB database instance.

Let's get started!

Requirements

Before jumping into code, you're going to need to make sure you have a few things on your machine:

MariaDB client
Docker
Java (v. 8+)
Curl (for testing the API endpoints)

Creating a new MariaDB instance using Docker

One of the simplest ways to get started with MariaDB, regardless of which operating system you're using, is to pull the MariaDB Server Docker image, from Docker Hub, and use it create a new container.

To do this simply open a terminal window and run the following:

$ docker run -p 3306:3306 -d --name mariadb -eMARIADB_ROOT_PASSWORD=Password123! mariadb/server:10.4

That's it. You should now have a running instance of MariaDB. Pretty painless, right?

You can confirm the instance within a Docker container by running the following:

$ docker ps

And you should see your container within the output.

Connecting to MariaDB

Now that you've got a running MariaDB Server instance within a new Docker container the next step will be to connect to and communicate with the database instance using the MariaDB client.

There are many SQL clients available out in the wild. For simplicity's sake, I've chosen to demonstrate how to use the official MariaDB Client, but certainly feel free to use whatever client you prefer.

Connect to your MariaDB instance by executing the following:

$ mariadb --host 127.0.0.1 -P 3306 --user root -pPassword123!

You should see something like the following, which means you've successfully connected to the MariaDB instance!

Once you're connected you can create a new database.

CREATE DATABASE todo;

Then create a new table.

CREATE TABLE todo.tasks (
  id INT(11) unsigned NOT NULL AUTO_INCREMENT,
  description VARCHAR(500) NOT NULL,
  completed BOOLEAN NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

Getting Started with Java Spring, JDBC and MariaDB

With a database instance spun up and schema created you're ready to create a new Java application.

Create a Maven project

Start by navigating to https://start.spring.io, which will enable you to create a new Spring-based Maven project. For this project you can enter the following criteria.

Next, add the following dependencies:

Lombok: A library that will eliminate having to create boiler plate code (e.g. getters, setters) within the domain objects.
Spring Web: A library to create and expose RESTful API endpoints.
Spring Data JPA: A library that provides abstractions to help eliminate boiler plate code for connecting to and communicating with databases.

Finally, click the "GENERATE" button to create and download the project (contained within a .zip file) to a desired location on your machine.

Add the MariaDB R2DBC connector

Navigate to the location where you downloaded the new Maven project (.zip file) to, and unzip. Then use a code editor to open the project, and open pom.xml.

Add a new dependency for MariaDB's JDBC connector to the collection of dependencies.

<dependency>             
   <groupId>org.mariadb.jdbc</groupId>           
   <artifactId>mariadb-java-client</artifactId>
   <version>2.6.0</version>
</dependency>

Preparing the data integration

Now that you've created a project that contains all of the dependencies you'll need, it's to jump into the Java code. Typically, I like to start by creating the entity classes.

Navigate to /src/main/java/com/mariadb/todo , create a new folder called "domain", and create a new file within it named "Task.java".

Open "Task.java" and add the following code.

package com.mariadb.todo.domain;

import javax.persistence.*;

import lombok.Data;

@Data
@Entity
@Table(name = "tasks")
public class Task {
    @Id 
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String description;
    private Boolean completed = false;
}

Next, create a new folder called "repositories" in /src/main/java/com/mariadb/todo, and create a new filed within it named "TaskRepository.java".

Open "TaskRepository.java" and add the following code.

package com.mariadb.todo.repositories;

import com.mariadb.todo.domain.Task;

import org.springframework.data.repository.CrudRepository;

public interface TaskRepository extends CrudRepository<Task, Integer> {
}

Like the name suggests the CrudRepository interface provides basic CRUD operations on a repository for a generic type (your Task class in this case) and the primary key type for that generic type.

Create a data service

Services can be used to manage the business logic of your application. The only service, TaskService, in this application is used for validating a Task object and integrating with the TaskRepository.

Create a new folder called "services" in /src/main/java/com/mariadb/todo, and create a new filed within it named "TaskService.java".

Open "TaskService.java" and add the following code.

package com.mariadb.todo.services;

import java.util.Optional;

import com.mariadb.todo.domain.Task;
import com.mariadb.todo.repositories.TaskRepository;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

// Registered as a Spring Service (Component)
@Service
public class TaskService {

    // Automatically instantiate (via Spring IoC) 
    @Autowired
    private TaskRepository repository;

    // 
    public Boolean isValid(final Task task) {
        if (task != null && !task.getDescription().isEmpty()) {
            return true;
        }
        return false;
    }

    // Get all records from the tasks table
    public Iterable<Task> getAllTasks() {
        return this.repository.findAll();
    }

    // Save a new task record
    public Task createTask(final Task task) {
        return this.repository.save(task);
    }

    // Update an existing task record
    @Transactional
    public Task updateTask(final Task task) {
        Optional<Task> ot = this.repository.findById(task.getId());
        Task t = ot.get();
        t.setDescription(task.getDescription());
        t.setCompleted(task.getCompleted());
        return this.repository.save(t);
    }

    // Delete the task record by specified id
    @Transactional
    public void deleteTask(final int id){
        Optional<Task> ot = this.repository.findById(id);
        Task t = ot.get();
        this.repository.delete(t);
    }
}

Expose API endpoints

Finally, you'll need to create a controller to expose four endpoints that can be used to perform the basic CRUD operations on your Tasks.

Create a new folder called "controllers" in /src/main/java/com/mariadb/todo, and create a new filed within it named "TaskController.java".

Open "TaskController.java" and add the following code.

package com.mariadb.todo.controllers;

import com.mariadb.todo.domain.Task;
import com.mariadb.todo.services.TaskService;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/api/tasks")
public class TaskController {

    @Autowired
    private TaskService service;

    // Get all tasks
    @GetMapping()
    public ResponseEntity<Iterable<Task>> get() {
        return ResponseEntity.ok(this.service.getAllTasks());
    }

    // Create a new task
    @PostMapping()
    public ResponseEntity<Task> post(@RequestBody Task task) {
        if (service.isValid(task)) {
            return ResponseEntity.ok(this.service.createTask(task));
        }
        return ResponseEntity.status(HttpStatus.I_AM_A_TEAPOT).build();
    }

    // Update a task
    @PutMapping()
    public ResponseEntity<Task> put(@RequestBody Task task) {
        if (service.isValid(task)) {
            return ResponseEntity.ok(this.service.updateTask(task));
        }
        return ResponseEntity.status(HttpStatus.I_AM_A_TEAPOT).build();
    }

    // Delete a task
    @DeleteMapping()
    public ResponseEntity<Void> delete(@RequestParam int id) {
        if (id > 0) {
            this.service.deleteTask(id);
            return ResponseEntity.ok().build();
        }
        return ResponseEntity.status(HttpStatus.I_AM_A_TEAPOT).build();
    }
}

Testing it out

Now that everything has been constructed, it's time to test it out!

First, build the application.

$ mvn package

And then run it.

$ mvn spring-boot:run

First, start by adding a new task to your to do list.

$ curl --header "Content-Type: application/json" \
  --request POST \
  --data '{"description":"A New Task"}' \
  http://localhost:8080/api/tasks

While you can certainly query the database directly to confirm that a new task record has been added, where's the fun in that? Back to the API!

$ curl https://localhost:8080/api/tasks

If all goes well you should receive the following JSON response:

{ "id": 1, "description": "A New Task", "completed": false }

Voilà, a fully reactive Java Spring application using JDBC and MariaDB! To view this code in its entirety check out the source here. And if you're wondering "it'd sure be nice to see an implementation with a user interface", you're in luck! You can find a fully fleshed out implementation of a TODO application using React.js and your choice of multiple API projects
(R2DBC, JDBC, Node.js and Python) that integrate directly with MariaDB here!

Just getting started

Now that you've successfully created a new Maven project using Spring Data JPA, JDBC and MariaDB, you have all the tools you need to get started creating fully reactive applications, utilizing the power of MariaDB, of your own!If you have any questions, suggestions or concerns with this blog post please let me know here or reach out to me directly on Twitter at @probablyrealrob!Thanks for taking the time to read this and happy coding!

Reactive Programming with Java Spring, R2DBC and MariaDB

Rob Hedgpeth — Mon, 22 Jun 2020 19:33:05 +0000

Whether or not you've been actively developing within one of the JVM languages or not, you've likely at least heard of the concept of reactive programming by now. If not, I certainly encourage you to do a little research into the whole idea of declarative programming using data streams.

In this article we'll take a look at the (very) brief background of not only what Reactive Relational Database Connectivity (R2DBC) is but why it exists, and then dive into the code to see how the new MariaDB R2DBC connector can be used to create fully reactive, Java Spring (boot and data) applications!

We're going to walking through the process of creating a simple todo application, but if you'd prefer to jump directly into the code you can find it here.

It all starts with Reactive Streams

While I'm not going to give you the entire backstory and technical details behind reactive programming, it's important that we go over a few of the high level points before diving to all the R2DBC hoopla.

For starters, it's important to know (or recall, depending on your current circumstance) that reactive programming boils down to the idea of using asynchronous data streams.

A data stream is basically what it sounds like, a stream of data. But what does that really mean exactly?

A stream is a sequence of ongoing events ordered in time. There are three types of events; value, error, completed signal.

While we could certainly dive even deeper into the plethora of use cases for data streams, that's a little out of scope for this walk-through (I promise all of this is culminating to a walk-through). Instead we're going to focus on that the structure of a data stream is what brings the concept of reactive streams to life.

Ok, ok, I know what you're thinking; "Damn, this dude is throwing around the word reactive more than a Midwesterner roaming through a crowd using the word 'ope'". Bear with me, there's a point to all this madness. I promise!

Reactive Streams is simply a standard for asynchronous stream processing using the idea of non-blocking back pressure.

"Seriously, Rob! Back pressure?! Are you just making things up now? I think I'm getting back pressure just reading this nonsense."

No, I'm not just messing with you.
You can think of back pressure as a way of the receiving side (of a data stream) telling the sending side when they are ready for more information and how much to send.

Back pressure is an important concept because it's the foundation of which the reactive streams initiative is built on.

Keeping out of the gritty details, the key takeaway here is that Reactive Streams is a set of specifications, or rules, used to define the way asynchronous streams are managed.

TL;DR - reactive streams creates a standard approach.

The following (very high level) diagram shows the interactions (to manage, yep, you guessed it, data streams!) between publishers and subscribers.

For more information on reactive streams, why specification came about, the interfaces involved and analysis on use cases, I encourage you to take a look at the this write-up by John Thompson.

For our purposes, we're interested in the specification itself, specifically how it's utilized with Reactive Relational Database Connectivity (R2DBC).

R2DBC to the rescue

R2DBC started as an experiment and proof of concept to enable integration of SQL databases into systems that use reactive programming models. It's necessary because of limitations of the Java Database Connectivity (JDBC) API.

According to the http://r2dbc.io:

"R2DBC specifies a service-provider interface (SPI) that is intended to be implemented by driver vendors and used by client libraries. By using the R2DBC SPI, applications written in a JVM programming language can run SQL statements and retrieve results by using an underlying data source."

And if you haven't guess by now, the R2DBC SPI is based on Reactive Streams and uses the concepts of publishers and subscribers to allow non-blocking back-pressure-aware data access. Ah, yes, we've come full circle!

TL;DR - R2DBC is a new connectivity specification that supports reactive interactions all the way to the database level.

MariaDB Corporation recently released their R2DBC connector implementation. It's completely open-source and follows the R2DBC 0.8.1 specifications.

This is where you ceremoniously crack your knuckles. It's time to code!

Requirements

Before jumping into code, you're going to need to make sure you have a few things on your machine:

MariaDB Client
Docker
Java (v. 8+)
Curl (for testing the API endpoints)

Getting Started with MariaDB

Let's assume you've either never used MariaDB or don't currently have an instance running on your machine. No problem! It only takes a couple of minutes, using a Docker container, to get MariaDB up and running. If you've already got an instance running feel free to skip down to the part where we create a new schema.

To pull the MariaDB Server image and spin up a container simply open a terminal window and run the following:

$ docker run -p 3306:3306 -d --name mariadb -eMARIADB_ROOT_PASSWORD=Password123! mariadb/server:10.4

The previous command will spin up a MariaDB Server container that you can connect to and communicate with using the MariaDB client.

There are many SQL clients available out in the wild. For simplicity's sake, I've chosen to demonstrate how to use the official MariaDB Client, but certainly feel free to use whatever client you prefer.

Connect to your MariaDB instance by executing the following command in a terminal window.

You should see something like the following, which means you've successfully connected to the MariaDB instance!

Next, create a new database for our TODO application.

CREATE DATABASE todo;

Then create a new table to store our tasks.

CREATE TABLE todo.tasks (id int, description varchar(200), completed boolean);

Getting reactive with R2DBC

With a database instance spun up and schema created you're ready to create a fully-reactive Java application.

Create a Maven project

Start by navigating to https://start.spring.io, which will enable you to create a new Spring-based Maven project.

For this project you can enter the following criteria.

Next, add the following dependencies:

Finally, click the "GENERATE" button to create and download the project (contained within a .zip file) to a desired location on your machine.

Add the MariaDB R2DBC connector

Navigate to the location where you downloaded the new Maven project (.zip file) to, and unzip. Then use a code editor to open the project, and open pom.xml.

Add a new dependency for MariaDB's R2DBC connector to the collection of dependencies.

<dependency>
    <groupId>org.mariadb</groupId>
    <artifactId>r2dbc-mariadb</artifactId>
    <version>0.8.2-alpha2</version>
</dependency>

The MariaDB R2DBC connector is not available in the Spring dependencies listing because it's currently still in the alpha phase of development.

Preparing the data integration

Now that you've created a project that contains all of the dependencies you'll need, it's time to start really coding. Typically, I like to start by creating the entity (or model) classes.

Navigate to /src/main/java/com/example/todo , create a new folder called
"models", and create a new file within it named "Task.java".

Open "Task.java" and add the following code.

package com.mariadb.todo.models;

import org.springframework.data.relational.core.mapping.Table;

import lombok.Data;

// Lombok annotation that eliminates getter/setter boilerplate code
@Data 
// Annotation that will point to table "tasks" (pluralized in the database)
@Table("tasks")
public class Task {
    @Id private Integer id;
    private String description;
    private Boolean completed;
}

Next, create a new folder called "repositories" in /src/main/java/com/mariadb/todo, and create a new filed within it named "TasksRepository.java"

Open "TasksRepository.java" and add the following code.

package com.mariadb.todo.repositories;

import com.mariadb.todo.models.Task;
import org.springframework.data.repository.reactive.ReactiveCrudRepository;

// Registered as a Spring Repository (Component)
// Repository = a mechanism for encapsulating storage, retrieval, and search behavior which emulates a collection of objects
public interface TasksRepository extends ReactiveCrudRepository<Task, Integer> {
}

The ReactiveCrudRepository interface provides CRUD operations on a repository for a specific type, and follows reactive paradigms and uses Project Reactor types which are built on top of Reactive Streams.

Setting up the connection configuration

As previously mentioned, in order to create a connection to a database using R2DBC you must first create a ConnectionFactory. The MariaDB implementation of the ConnectionFactory interface is called MariadbConnectionFactory and it requires a MariadbConnectionConfiguration instance, which contains a variety of
configuration details used to connect to a MariaDB database instance.

Create a new folder called "config" in /src/main/java/com/mariadb/todo, and create a new filed within it named "R2DBCConfig.java"

Open "R2DBCConfig.java" and add the following code.

package com.mariadb.todo.config;

import org.mariadb.r2dbc.MariadbConnectionConfiguration;
import org.mariadb.r2dbc.MariadbConnectionFactory;
import org.mariadb.r2dbc.SslMode;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.r2dbc.config.AbstractR2dbcConfiguration;
import org.springframework.data.r2dbc.repository.config.EnableR2dbcRepositories;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

@Configuration
@EnableR2dbcRepositories
public class R2DBCConfig extends AbstractR2dbcConfiguration {
    @Override
    @Bean
    public MariadbConnectionFactory connectionFactory() {
        ClassLoader loader = Thread.currentThread().getContextClassLoader();
        Properties props = new Properties();
        try (InputStream f = loader.getResourceAsStream("db.properties")) {
            props.load(f); 
            return new MariadbConnectionFactory(MariadbConnectionConfiguration.builder()
                .host(props.getProperty("host"))
                .port(Integer.parseInt(props.getProperty("port")))
                .username(props.getProperty("username"))
                .password(props.getProperty("password"))
                .database(props.getProperty("database"))
                .build());
        }
        catch (IOException e) {
            System.out.println(e.getMessage());
            return null;
        }
    }
}

Notice that R2DBCConfig retrieves the database connection details from a file called "db.properties". To add the connection configuration navigate to /src/main/java/com/mariadb/resources, create a new file called "db.properties" and add the following code.

host=127.0.0.1
port=3306
username=USERNAME_HERE
password=PASSWORD_HERE
database=todo

Although not recommended you can add connection details directly to R2DBCConfig.java.

Create a data service

Services can be used to manage the business logic of your application. The only service, TasksService, in this application is used for validating a Task object and integrating with the TasksRepository.

Create a new folder called "services" in /src/main/java/com/mariadb/todo, and create a new filed within it named "TasksService.java"

Open "TasksService.java" and add the following code.

package com.mariadb.todo.services;

import com.mariadb.todo.models.Task;
import com.mariadb.todo.repositories.TasksRepository;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

// Registered as a Spring Service (Component)
@Service
public class TaskService {

    // Automatically instantiate (via Spring IoC) 
    @Autowired
    private TasksRepository repository;

    // 
    public Boolean isValid(final Task task) {
        if (task != null && !task.getDescription().isEmpty()) {
            return true;
        }
        return false;
    }

    // Get all records from the tasks table
    public Flux<Task> getAllTasks() {
        return this.repository.findAll();
    }

    // Save a new task record
    public Mono<Task> createTask(final Task task) {
        return this.repository.save(task);
    }

    // Update an existing task record
    @Transactional
    public Mono<Task> updateTask(final Task task) {
        return this.repository.findById(task.getId())
                .flatMap(t -> {
                    t.setDescription(task.getDescription());
                    t.setCompleted(task.getCompleted());
                    return this.repository.save(t);
                });
    }

    // Delete the task record by specified id
    @Transactional
    public Mono<Void> deleteTask(final int id){
        return this.repository.findById(id)
                .flatMap(this.repository::delete);
    }
}

Expose API endpoints

Finally, you'll need to create a controller to expose four endpoints that can be used to perform the basic CRUD operations on your Tasks.

Create a new folder called "controllers" in /src/main/java/com/mariadb/todo, and create a new filed within it named "TasksController.java"

Open "TasksController.java" and add the following code.

package com.mariadb.todo.controllers;

import com.mariadb.todo.models.Task;
import com.mariadb.todo.services.TaskService;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

@RestController
@RequestMapping("/api/tasks")
public class TasksController {

    @Autowired
    private TaskService service;

    @GetMapping()
    public ResponseEntity<Flux<Task>> get() {
        return ResponseEntity.ok(this.service.getAllTasks());
    }

    @PostMapping()
    public ResponseEntity<Mono<Task>> post(@RequestBody Task task) {
        if (service.isValid(task)) {
            return ResponseEntity.ok(this.service.createTask(task));
        }
        return ResponseEntity.status(HttpStatus.I_AM_A_TEAPOT).build();
    }

    @PutMapping()
    public ResponseEntity<Mono<Task>> put(@RequestBody Task task) {
        if (service.isValid(task)) {
            return ResponseEntity.ok(this.service.updateTask(task));
        }
        return ResponseEntity.status(HttpStatus.I_AM_A_TEAPOT).build();
    }

    @DeleteMapping()
    public ResponseEntity<Mono<Void>> delete(@RequestParam int id) {
        if (id > 0) {
            return ResponseEntity.ok(this.service.deleteTask(id));
        }
        return ResponseEntity.status(HttpStatus.I_AM_A_TEAPOT).build();
    }
}

Testing it out

Now that everything has been constructed, it's time to test it out!

First, build the application.

$ mvn package

And then run it.

$ mvn spring-boot:run

First, start by adding a new task to your to do list.

$ curl --header "Content-Type: application/json" \
  --request POST \
  --data '{"description":"A New Task"}' \
  http://localhost:8080/api/tasks

While you can certainly query the database directly to confirm that a new task record has been added, where's the fun in that? Back to the API!

$ curl https://localhost:8080/api/tasks

If all goes well you should receive the following JSON response:

{ "id": 1, "description": "A New Task", "completed": false }

Voilà, a fully reactive Java Spring application using R2DBC and MariaDB!

To view this code in its entirety check out the source here. And if you're wondering "it'd sure be nice to see an implementation with a user interface", you're in luck! You can find a fully fleshed out implementation of a TODO application using React.js and your choice of multiple API projects
(R2DBC, JDBC, Node.js and Python) that integrate directly with MariaDB here!

Just getting started

Now that you've successfully created a new Maven project using Spring Data, R2DBC and MariaDB, you have all the tools you need to get started creating fully reactive applications, utilizing the power of MariaDB, of your own!

If you have any questions, suggestions or concerns with this blog post please let me know here or reach out to me directly on Twitter at @probablyrealrob!

Thanks for taking the time to read this and happy coding!

There and Back Again: A Browser's Tale, Part 1

Rob Hedgpeth — Sat, 16 May 2020 20:55:48 +0000

Using a browser to consume content is something most of us do everyday. Heck, you're doing it right now. Recently though, I was humbled by the realization that for something I do so often, I only had a vague idea of what was happening "under the hood". But rather than stew in my own shame and self-loathing, I decided to dive in to gain a better grasp on the complete end-to-end process, or life cycle, of a browser. While I won't be tackling the complete complexity and hundreds of components that make up a browser, hopefully this quick guide will give you a better understanding of what's happening the next time you navigate to a website.

Request Preparation

Most often a browser's journey begins with a Uniform Resource Locator, or, as it's more commonly referred to as, a URL. Put simply a URL is an address. Put a little less simply a URL is a specific type of Uniform Resource Identifier (URI) which is a sequence of character that identifies a resource, and, for our purposes, a website. Diving a bit deeper you'll find that the URL structure is surprisingly robust as it contains:

Protocol: the (network) method of access to the resource.
Domain: the address where the resources can be accessed, these are often used in place of an IP address because they're easier to remember.
Path: a more precise location for accessing specific resources.
Query string: where parameters and values are stored.
Fragment: an internal page reference, commonly referred to as an anchor.

Once you've entered a URL into the address bar and struck the enter key the browser gets to work!

The Power of DNS

Domain names are useful because they help us more easily remember the address for content, like web sites/pages, we want to consume. Computers, however, don't use domain names to communicate, they use Internet Protocol (IP) addresses. So, steps must be taken to use the domain name to find the associated IP address. This process is known as the Domain Name System (DNS) lookup.

The lookups are initially performed through use of a root server. Root servers contain mapping information between domains and their corresponding IP addresses. Though the number of root servers is in flux there are several hundred located around the world.

Pretty straight forward, right? Efficient? Not really. As you can imagine it takes time to communicate with root servers, and for data, like domain/IP mapping, that doesn't change often so it makes sense to cache this kind of information. That's not to say that a DNS lookup takes a long time, but it's faster to check cache.

DNS information can be cached at four levels:

Browser Cache
Operating System (OS) Cache
Router Cache
Internet Service Provider (ISP) Cache

The browser will start by searching its own cache. If that doesn't exist then the cache stored by the OS will be checked. In fact, this process of checking will continue using the router then the ISP.

If the information cannot be found at any of the cache levels then a search will be conducted using the root servers previously mentioned.

Request and Response

After successfully locating the IP address the browser is ready to make a request for data. It's important to note that from this point forward we'll be looking at a Hyper Text Transfer Protocol (HTTP) request workflow.

Note: While HTTP isn't the only type of request browsers support, it is a very common activity that will provide a good foundation of knowledge to expand in subsequent blog posts.

The client sends a request to open a connection via Transmission Control Protocol (TCP) synchronize (SYN) packet.
The server accepts the connection and responds to the client with a TCP SYN acknowledge (ACK) packet.
Having successfully established a connection with the server, the client prepares and sends an HTTP request header that includes information like the HTTP method, path, cookies, and a variety of other details.

Sample request header
The server receives the HTTP request and begins preparing a packet containing the HTTP response header along with a part of the requested content. There are a variety of content types that can be returned. You can find more information on them here.

Sample (HTML) response header


Sample request header


Sample (HTML) response header

Steps 5 and 6 show how the process can be repeated on an established connection. It's important to point out that establishing a new connection can be a time-consuming process and crucial to re-use already opened connections as much as possible.

Just the beginning

When a response from the server has been received the content is then handed off to the layout/rendering engine of the browser. There are a variety of rendering engines but they are all tasked with the same mission; to display information on the screen. While there is certainly more to unpack (see what I did there?) when it comes to the browser rendering from a workflow perspective, that's it! The complete, high level, journey of requesting information from a web server. Keep an eye out for future write-ups where I'll be diving deeper into the details of other types of requests (e.g. HTTPS), how browser layout engines work, and more!

Getting Started with MariaDB using Docker, Python and Flask

Rob Hedgpeth — Sat, 25 Apr 2020 19:38:58 +0000

It's no secret that, over the years, MariaDB has added many features and functionality that have set it apart from other database solutions, but what developers may not know is that there are two groups actively contributing to the ever advancing code base; MariaDB Foundation and MariaDB Corporation.

MariaDB Foundation is the custodian of the MariaDB community code and guardian of the MariaDB community and the open source principles that have propelled it to being one of the most popular databases in the world.
MariaDB Corporation contributes to the community codebase as well, but also provides superior quality, enterprise grade products that thrusts MariaDB into the forefront of database vendors. MariaDB Corporation even offers columnar and HTAP based solutions, but I digress.

With that in mind, I've written this short walkthrough to provide a launchpad for you to get started using MariaDB with Docker and Python (with the help of Flask), within a matter of minutes, so you can check things out for yourself.

Requirements

Before jumping into code, you're going to need to make sure you have a few things on your machine.

Using a MariaDB Docker Container

To pull the MariaDB Server image and spin up a container simply open a terminal window and run the following.

$ docker run -p 3306:3306 -d --name mariadb -eMARIADB_ROOT_PASSWORD=Password123! mariadb/server:10.4

The previous command will spin up a MariaDB Server container that you can connect to and communicate with using the MariaDB client.

While you can certainly use a variety of other SQL clients, for the sake of keeping things simple and uniform, I've only included samples using the official MariaDB client.

Connect to your MariaDB instance by executing the following command in a terminal window.

$ mariadb --host 127.0.0.1 -P 3306 --user root -pPassword123!

You should see something like the following, which means you've successfully connected to the MariaDB instance!

Next, create a new database.

CREATE DATABASE demo;

Then create a new table.

CREATE TABLE demo.people (name VARCHAR(50));

Finally, insert a couple records.

INSERT INTO demo.people VALUES ('rob'), ('tracy'), ('sam'), ('duke');

Getting Started with MariaDB Connector/Python

Now that you've downloaded, installed, and stood up a MariaDB database (using Docker), you're ready to put it to use within a new Python app.

Recently, MariaDB announced the availability of MariaDB Connector/Python beta! Connector/Python enables Python programs to access MariaDB databases using an API that is compliant with the Python DB API 2.0 (PEP-249).

For more information on the announcement of MariaDB Connector/Python check out my blog post here!

MariaDB developers chose to optimize performance of the new connector by writing it in C which allowed them the ability to leverage the MariaDB Connector/C client library for client-server communication.

To use the new MariaDB Python connector you'll also need to install MariaDB Connector/C (v3.1.5+).

Set up and activate a virtual environment

A virtual environment is a directory tree which contains Python executable files and other files which indicate that it is a virtual environment. Basically, it's the backbone for running your Python Flask app.

Creation of virtual environments is done by executing the following command:

$ pyvenv venv

Tip: pyvenv is only available in Python 3.4 or later. For older versions please use the virtualenv tool.

Before you can start installing or using packages in your virtual environment you’ll need to activate it. Activating a virtual environment will put the virtual environment-specific python and pip executables into your shell’s PATH.

Activate the virtual environment using the following command:

$ . venv/bin/activate activate

Create a Flask application

Flask is a micro web framework written in Python. It is classified as a microframework because it does not require particular tools or libraries.

TL;DR (if I am even allowed to do that for two sentences), it's a great, lightweight framework to use for an API.

First, install Flask.

$ pip3 install flask

Then create a new file called api.py that will be used to create a new Flask app, and add the following code:

# import necessary packages
import flask

# create the flask app
app = flask.Flask(__name__)
app.config["DEBUG"] = True

@app.route('/', methods=['GET'])
def index():
   return 'Success!'

# run the app
app.run()

What you have now is the foundation for an extremely simple API. You can test it out by running the app and testing the endpoint.

To create an instance, it has to be give a name. Using (name) ensures that it can be started as an application or imported as a module. The use of the route() decorator lets our flask app know which URL should trigger the corresponding method.

You're ready to run the app.

$ python3 api.py

By default, the app will start running on port 5000. You can test the results by requesting data from http://localhost:5000.

Integrate with MariaDB

Once you've created a simple Flask application, you're ready to add code to integrate with MariaDB.

Start by installing the MariaDB Python connector.

$ pip3 install mariadb

Open api.py, and add code to import the MariaDB and JSON packages directly under the Flask package import.

import json
import mariadb

Next, add a configuration object with the connection information for your running MariaDB instance.

config = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'password': 'Password123!',
    'database': 'demo'
}

The config object will be used within a new route method block to connect to MariaDB.

# route to return all people
@app.route('/api/people', methods=['GET'])
def index():
   # connection for MariaDB
   conn = mariadb.connect(**config)
   # create a connection cursor
   cur = conn.cursor()
   # execute a SQL statement
   cur.execute("select * from people")

   # serialize results into JSON
   row_headers=[x[0] for x in cur.description]
   rv = cur.fetchall()
   json_data=[]
   for result in rv:
        json_data.append(dict(zip(row_headers,result)))

   # return the results!
   return json.dumps(json_data)

Bringing it all together, your api.py content should look like the following:

# import the necessary packages
import flask
import json
import mariadb

app = flask.Flask(__name__)
app.config["DEBUG"] = True

# configuration used to connect to MariaDB
config = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'password': 'Password123!',
    'database': 'demo'
}

# route to return all people
@app.route('/api/people', methods=['GET'])
def index():
   # connection for MariaDB
   conn = mariadb.connect(**config)
   # create a connection cursor
   cur = conn.cursor()
   # execute a SQL statement
   cur.execute("select * from people")

   # serialize results into JSON
   row_headers=[x[0] for x in cur.description]
   rv = cur.fetchall()
   json_data=[]
   for result in rv:
        json_data.append(dict(zip(row_headers,result)))

   # return the results!
   return json.dumps(json_data)

app.run()

Testing it out

Start the app.

$ python3 api.py

Then test the peoples endpoint. This can be done through a variety of techniques (e.g. directly within a browser, Postman, etc.).

For instance, considering using a curl command:

$ curl http://localhost:5000/api/people

Which yields the following JSON response payload:

[{"name":"rob"},{"name":"tracy"},{"name":"duke"},{"name":"sam"}]

Just the beginning

Hopefully this short walkthrough has helped you get started using MariaDB with Docker, Python and Flask. And, yea, this was a very simple example, but it only gets more exciting from here!

I highly recommend that you check out all of what MariaDB has to offer and how you can use a truly innovative database to create modern applications.

Fight COVID-19 with a Free, Fully-Managed Cloud Database

Rob Hedgpeth — Mon, 20 Apr 2020 15:46:23 +0000

A few weeks ago MariaDB launched their new database-as-a-service (DBaaS), SkySQL, amid the Coronavirus Pandemic. While they also offered a $500 credit to get started, as of last week they announced a program to offer their fully-managed analytics (columnar based storage) service for free to help fight COVID-19.

If you or your organization is in the healthcare, medical, academic or other nonprofit space have an idea (e.g. application) that requires analysis of large amounts of data MariaDB is offering the opportunity to to use MariaDB SkySQL for Analytics for free. All you need to do is complete the form here.

MariaDB Platform for Analytics

For those that are unfamiliar, traditional RDBMS platforms deliver great transactional processing, which typically depends on indexes to achieve query performance. Modern applications demand the ability to query large data on the fly, with complicated filters – it’s not possible to add an index for every query. The solution is columnar data storage.

MariaDB Platform for analytics leverages the columnar capabilities of MariaDB ColumnStore with data placed in object storage in the cloud. In this configuration, you not only get the high-performance analytical benefits of columnar storage, but you eliminate the need to pre-allocate storage for growth. MariaDB analytics allows you to perform real-time ad hoc queries on big data, on object storage.

The Gist

MariaDB is offering their managed analytics service completely for free to help give developers the ability to innovate without having to worry about how to manage their data.

So, if you or your organization are looking to fight COVID-19 and are looking for a data storage solution, why not get one for free? Especially when it only takes a couple of minutes to submit your ideas here.

Getting Started with MariaDB using Docker and Node.js

Rob Hedgpeth — Mon, 13 Apr 2020 15:06:39 +0000

It's no secret that MariaDB has become a popular database solution for developers over the past decade. Why? Well, one could argue that it's largely because it's open source and relational. So, for developers, that basically means it's free, and we get the gist of it. But that really only begins to scratch the surface.

What you may not know is that there are two groups actively contributing to MariaDB; Foundation and Corporation.

MariaDB Foundation is the custodian of the MariaDB community code and guardian of the MariaDB community.
MariaDB Corporation contributes to the community codebase, but also provides superior quality, enterprise grade products that thrusts MariaDB into the forefront of database vendors. MariaDB Corporation even offers columnar and HTAP based solutions, but I digress.

With that in mind, I've written this short walkthrough to provide a launchpad for you to get started using MariaDB with Docker and Node.js, within a matter of minutes, so you can check things out for yourself.

Requirements

Before jumping into code, you're going to need to make sure you have a few things on your machine.

MariaDB client
Node.js (and NPM - Node Package Manager)
Docker

Using a MariaDB Docker Container

To pull the MariaDB Server image and spin up a container, simply open a terminal window and run the following.

$ docker run -p 3306:3306 -d --name mariadb -eMARIADB_ROOT_PASSWORD=Password123! mariadb/server:10.4

The previous command will pull down the MariaDB Server image (if you don't already have it) from Docker Hub and create a container that you can connect to and communicate with using the MariaDB client.

Note: While you can certainly use a variety of other SQL clients, for the sake of keeping things simple and uniform, I've only included samples using the official MariaDB client.

Connect to your MariaDB instance by executing the following command in a terminal window.

$ mariadb --host 127.0.0.1 -P 3306 --user root -pPassword123!

You should see something like the following, which means you've successfully connected to the MariaDB instance!

Next, create a new database.

CREATE DATABASE demo;

Then create a new table.

CREATE TABLE demo.people (name VARCHAR(50));

Finally, insert a couple records.

INSERT INTO demo.people VALUES ('rob'), ('tracy'), ('sam'), ('duke');

Connecting to MariaDB with Node.js

Now that you've downloaded, installed, and stood up a MariaDB database, you're ready to put it to use within a new Node.js app.

To start, pick a new directory, and create a new Javascript file to be used as the main entry point for the Node server. For simplicity, I used "server.js".

Then, within a terminal that the directory location, execute the following.

$ npm init

Feel free to fill out all of the prompts, or you can just hit the enter key through all of the options. Either way, you'll end up with a package.json file being generated next to server.js.

Note: You now have a runnable Node app, albeit a pretty uninteresting one. So, let's continue to spice it up!

Install the Express package which will be used as a lightweight web framework by the Node app.

$ npm install express

Install the MariaDB Node.js connector, which will be used to connect to and communicate with your MariaDB instance.

$ npm install MariaDB

Now it's time to add code to connect to MariaDB. To do this first create a new (reusable) module file called db.js.

The db module will use the MariaDB Node.js connector that will enable your app to connect to and communicate with MariaDB.

Then you'll paste the following code into it and save.

// import mariadb
var mariadb = require('mariadb');

// create a new connection pool
const pool = mariadb.createPool({
  host: "127.0.0.1", 
  user: "root", 
  password: "Password123!",
  database: "demo"
});

// expose the ability to create new connections
module.exports={
    getConnection: function(){
      return new Promise(function(resolve,reject){
        pool.getConnection().then(function(connection){
          resolve(connection);
        }).catch(function(error){
          reject(error);
        });
      });
    }
  }

Tip: You probably won't want to just slap all the sensitive connection information directly into your connection module. This has been done for demo purposes only. Instead, you may consider using something like dotenv to handle environmental data.

The final development step is to create an Express endpoint that uses the MariaDB Node.js connector (via db.js).

Open server.js, paste the following code into it, and save.

const express = require('express')
const pool = require('./db')
const app = express()
const port = 8080

// expose an endpoint "people"
app.get('/people', async (req, res) => {
    let conn;
    try {
        // establish a connection to MariaDB
        conn = await pool.getConnection();

        // create a new query
        var query = "select * from people";

        // execute the query and set the result to a new variable
        var rows = await conn.query(query);

        // return the results
        res.send(rows);
    } catch (err) {
        throw err;
    } finally {
        if (conn) return conn.release();
    }
});

app.listen(port, () => console.log(`Listening on port ${port}`));

Finally, run the node application.

$ npm start

Testing it out

Once the Node project has been started, you can test it out by executing a request. This can be done through a variety of techniques. For instance, consider executing the following curl command:

$ curl http://localhost:8080/people

Which yields the following JSON response payload:

[{"name":"rob"},{"name":"tracy"},{"name":"duke"},{"name":"sam"}]

Also, if you'd like to review the Node.js project in its entirety, I've pushed the complete code to this repository.

Just the beginning

Hopefully this short walkthrough has helped you get started using MariaDB with Node.js. And, yea, this was a very simple example, but it only gets more exciting from here!

I highly recommend that you check out all of what MariaDB has to offer and how you can use a truly innovative database to create modern applications.