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:
|
1 2 3 4 5 6 7 8 |
import PackageDescription let package = Package( name: "swift_mysql", dependencies:[ .Package(url:"https://github.com/vapor/mysql", majorVersion:1) ] ) |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
import Glibc class Random { static let initialize:Void = { srandom(UInt32(time(nil))) return () }() } func randomString(ofLength length:Int) -> String { Random.initialize let charactersString = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789" let charactersArray:[Character] = Array(charactersString.characters) var string = "" for _ in 0..<length { string.append(charactersArray[Int(random()) % charactersArray.count]) } return string } func randomInt() -> Int { Random.initialize return Int(random() % 10000) } |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
import Glibc import MySQL var mysql:Database do { mysql = try Database(host:"localhost", user:"swift", password:"swiftpass", database:"swift_test") try mysql.execute("SELECT @@version") } catch { print("Unable to connect to MySQL: \(error)") exit(-1) } |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
do { try mysql.execute("DROP TABLE IF EXISTS foo") try mysql.execute("CREATE TABLE foo (bar INT(4), baz VARCHAR(16))") for i in 1...10 { let int = randomInt() let string = randomString(ofLength:16) try mysql.execute("INSERT INTO foo VALUES (\(int), '\(string)')") } // Query let results = try mysql.execute("SELECT * FROM foo") for result in results { if let bar = result["bar"]?.int, let baz = result["baz"]?.string { print("\(bar)\t\(baz)") } } } catch { print("Error: \(error)") exit(-1) } |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
do { try mysql.execute("DROP TABLE IF EXISTS samples") try mysql.execute("CREATE TABLE samples (id INT PRIMARY KEY AUTO_INCREMENT, created_at DATETIME, location POINT, reading JSON)") // ... Date // ... Point // ... Sample // ... Insert // ... Query } catch { print("Error: \(error)") exit(-1) } |
To insert a date into the database using a SQL statement we will need to appropriately format it:
|
1 2 3 4 5 |
// ... Date let now = Date() let formatter = DateFormatter() formatter.dateFormat = "yyyy-MM-dd HH:mm:ss" // MySQL will accept this format let created_at = formatter.string(from:date) |
Now let’s create a POINT as a Swift tuple:
|
1 2 |
// ... Point let location = (37.20262, -112.98785) // latitude, longitude |
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.
|
1 2 3 4 5 6 7 8 9 10 |
// ... Sample let sample:[String:Any] = [ "heading":90, "gps":[ "latitude":37.20262, "longitude":-112.98785 ], "speed":82, "temperature":200 ] |
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:
|
1 2 |
let sampleData = try Jay(formatting:.minified).dataFromJson(any:sample) // [UInt8] let sampleJSON = String(data:Data(sampleData), encoding:.utf8) |
Now we have our date, point, and JSON string (sample), let’s insert the data into the samples table:
|
1 2 3 |
// ... Insert let stmt = "INSERT INTO samples (created_at, location, sample) VALUES ('\(created_at)', POINT\(point), '\(sampleJSON)')" try mysql.execute(stmt) |
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:
|
1 |
INSERT INTO samples (created_at, location, sample) VALUES ('2016-09-21 22:28:44', POINT(37.202620000000003, -112.98784999999999), '{"gps":{"latitude":37.20262,"longitude":-112.98785},"heading":90,"speed":82,"temperature":200}') |
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.
|
1 2 3 4 5 6 7 8 9 10 |
// ... Query let results = try mysql.execute("SELECT created_at,sample FROM samples where JSON_EXTRACT(sample, '$.speed') > 80") for result in results { if let sample = result["sample"]?.object, let speed = sample["speed"]?.int, let temperature = sample["temperature"]?.int, let created_at = result["created_at"]?.string { print("Time:\(created_at)\tSpeed:\(speed)\tTemperature:\(temperature)") } } |
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.