Working with MySQL Databases using Swift 3.0

swift-og

If you’ve read our other Swift articles you’ll know that we’re big proponents of Swift on the server. Today we’ll keep with that theme by looking at working with MySQL databases with Vapor’s MySQL wrapper.

Warning: This is not a tutorial on MySQL or SQL. If you aren’t familiar with either there are plenty of tutorials out there. We’re going to focus specifically on working with MySQL with Swift 3.0 on Linux.

Getting Started

We chose MySQL 5.7 on an Ubuntu 16.04 system for this tutorial. MySQL 5.7 introduces a number of new features, one of which is the ability to store JSON data in a more efficient manner, as well as provide capabilities to select within the JSON data. More on this later. Since MySQL 5.7 is the default MySQL on Ubuntu 16.04 we’ll go with it as our OS.

If you don’t have Swift installed you can use our apt-get repo. See this post for instructions on setting it up. As of late September 2016 Apple also began building snapshots for Ubuntu 16.04. See Swift.org for more details.

Set up our database

Our database will be called swift_test, and it should be manipulated with a MySQL user swift whose password is swiftpass. If you’ve worked with MySQL for any period of time you are probably already chanting GRANT ALL ON swift_test.* and so on. So let’s set that up:

# sudo mysql
...
mysql> create user swift;
Query OK, 0 rows affected (0.00 sec)

mysql> create database swift_test;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on swift_test.* to 'swift'@'localhost' identified by 'swiftpass';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

Create a Swift Package

On to the code! Let’s create a package.

# mkdir swift_mysql
# swift package init --type executable

Update your Package.swift to look like:

Second, we’re going to be using a few helper routines to fill in some random data in our database. Create a file in the Sources directory called utils.swift and add the following:

Vapor MySQL

On to the real code, our main.swift file which will use the Vapor MySQL module.

Connecting to the Database

Add the following to Sources/main.swift:

Here we are setting up our database handle mysql. The initializer Database(host:String, user:String, password:String, database:String) is straightforward. The statement try mysql.execute("SELECT @@version") is a test to ensure we connected properly and can communicate with the database.

If our do block succeeds with no errors we can proceed to interacting with our database!

Ints and Strings

All of the calls made to MySQL will be through the execute(_:String) method. Note that this is different from using an abstraction API that provides methods like .create(table:String, ...) or .insert(table:String, .... execute takes raw SQL and passes it down through the MySQL connector.

Querying for results also uses the execute(_:String) method, but now we treat the result of the call as an array of [String:Node]. The keys of the dictionary are the column names returned.

The Node type here is a Vapor data structure that is used for converting amongst different representations. You can read more about it here. When using it with MySQL we take advantage of the Node properties int, string, object to convert from its agnostic representation to a Swift type. Thus, let bar = result["bar"]?.int gives us an Int.

Moving On

Now let’s look at a more advanced example with creating a table that contains MySQL DATE, POINT, and JSON datatypes.

Our table is called samples.

To insert a date into the database using a SQL statement we will need to appropriately format it:

Now let’s create a POINT as a Swift tuple:

Finally, we want to utilize MySQL 5.7’s new JSON datatype, and moreover we’ll use the Jay package in Swift to quickly create a JSON-string from a Swift [String:Any] dictionary.

Hint: You do not have to explicitly call out Jay as a dependency in the Package.swift file because it is included transitively by the MySQL package.

We now want to convert this to a String suitable for giving to MySQL:

Now we have our date, point, and JSON string (sample), let’s insert the data into the samples table:

Note we did use a bit of a trick with the POINT in that \(point) will expand in our string to (37.20262, -112.98785), thus the full string will be POINT(37.20262, -112.98785) which is what MySQL will expect. The entire statement string looks like:

Retrieving Results

Warning! As of this writing (September 22, 2016), there is a bug in Vapor MySQL 1.0.0 that crashes on reading a POINT datatype, so we’ll have to make do and not use SELECT * below. We’ve filed this issue against Vapor MySQL and will update the post once the issue is fixed.

In the example below we’re going to take advantage of MySQL 5.7’s ability to include JSON data in the SELECT ... WHERE clauses. Here we are looking for those samples where the speed field of the JSON data in the sample is greater than 80.

A couple of notes here. The JSON_EXTRACT function is used to return data from a JSON document, selected from the parts of the document matched by the path arguments. In our case here we want to extract the value at the path $.speed from the sample column. To extract the longitude value from our JSON document we would use the path $.gps.longitude.

To iterate over our results we use the for result in results construct in Swift, and then the if let construct to validate our result data. First, use let sample = result["sample"]?.object to get a dictionary that is built from the MySQL JSON document. This is key here! The Vapor MySQL library does not return back a String object that needs to be handed to a JSON parser; that work is already done for you, so you can begin accessing the sample dictionary directly.

The remaining lets give us our speed, temperature, and created_at. Note that created_at is a MySQL DATETIME which will read in as a String. To convert to a Date in Swift you will need to use a DateFormatter with .date(from:String).

Get the Code!

If you want to get started with less typing, check out the code from Github. Build everything with swift build, but before running the executable remember that you have to have the database, user, and grants already configured.

Leave a Reply

Your email address will not be published. Required fields are marked *