{"id":3237,"date":"2016-09-24T09:52:11","date_gmt":"2016-09-24T14:52:11","guid":{"rendered":"http:\/\/dev.iachieved.it\/iachievedit\/?p=3237"},"modified":"2016-09-24T09:52:11","modified_gmt":"2016-09-24T14:52:11","slug":"working-with-mysql-databases-using-swift-3-0","status":"publish","type":"post","link":"https:\/\/dev.iachieved.it\/iachievedit\/working-with-mysql-databases-using-swift-3-0\/","title":{"rendered":"Working with MySQL Databases using Swift 3.0"},"content":{"rendered":"

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<\/a>.<\/p>\n

Warning:<\/b> 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.<\/p>\n

Getting Started<\/h3>\n

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.<\/p>\n

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

Set up our database<\/h3>\n

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

\n# sudo mysql\n...\nmysql> create user swift;\nQuery OK, 0 rows affected (0.00 sec)\n\nmysql> create database swift_test;\nQuery OK, 1 row affected (0.00 sec)\n\nmysql> grant all on swift_test.* to 'swift'@'localhost' identified by 'swiftpass';\nQuery OK, 0 rows affected, 1 warning (0.00 sec)\n\nmysql> flush privileges;\nQuery OK, 0 rows affected (0.00 sec)\n\nmysql> quit\nBye\n<\/pre>\n
Create a Swift Package<\/h3>\n
On to the code!  Let’s create a package.<\/p>\n
\n# mkdir swift_mysql\n# swift package init --type executable\n<\/pre>\n
Update your Package.swift<\/code> to look like:<\/p>\n
\nimport PackageDescription\n\nlet package = Package(\n    name: \"swift_mysql\",\n    dependencies:[\n      .Package(url:\"https:\/\/github.com\/vapor\/mysql\", majorVersion:1)\n    ]\n)\n<\/pre>\n
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<\/code> directory called utils.swift<\/code> and add the following:<\/p>\n
\nimport Glibc\n\nclass Random {\n  static let initialize:Void = {\n    srandom(UInt32(time(nil)))\n    return ()\n  }()\n}\n\nfunc randomString(ofLength length:Int) -> String {\n  Random.initialize\n  let charactersString = \"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789\"\n  let charactersArray:[Character] = Array(charactersString.characters)\n  \n  var string = \"\"\n  for _ in 0.. Int {\n  Random.initialize\n  return Int(random() % 10000)\n}\n<\/pre>\n
Vapor MySQL<\/h2>\n
On to the real code, our main.swift<\/code> file which will use the Vapor MySQL module.<\/p>\n
Connecting to the Database<\/h3>\n
Add the following to Sources\/main.swift<\/code>:<\/p>\n
\nimport Glibc\nimport MySQL\n\nvar mysql:Database\ndo {\n  mysql = try Database(host:\"localhost\",\n                       user:\"swift\",\n                       password:\"swiftpass\",\n                       database:\"swift_test\")\n  try mysql.execute(\"SELECT @@version\")\n} catch {\n  print(\"Unable to connect to MySQL:  \\(error)\")\n  exit(-1)\n}\n<\/pre>\n
Here we are setting up our database handle mysql<\/code>.  The initializer Database(host:String, user:String, password:String, database:String)<\/code> is straightforward.  The statement try mysql.execute(\"SELECT @@version\")<\/code> is a test to ensure we connected properly and can communicate with the database.<\/p>\n
If our do<\/code> block succeeds with no errors we can proceed to interacting with our database!<\/p>\n
Ints and Strings<\/h3>\n
All of the calls made to MySQL will be through the execute(_:String)<\/code> method.  Note that this is different from using an abstraction API that provides methods like .create(table:String, ...)<\/code> or .insert(table:String, ...<\/code>.  execute<\/code> takes raw SQL and passes it down through the MySQL connector.<\/p>\n
\ndo {\n  try mysql.execute(\"DROP TABLE IF EXISTS foo\")\n  try mysql.execute(\"CREATE TABLE foo (bar INT(4), baz VARCHAR(16))\")\n\n  for i in 1...10 {\n    let int    = randomInt()\n    let string = randomString(ofLength:16)\n    try mysql.execute(\"INSERT INTO foo VALUES (\\(int), '\\(string)')\")\n  }\n\n  \/\/ Query\n  let results = try mysql.execute(\"SELECT * FROM foo\")\n  for result in results {\n    if let bar = result[\"bar\"]?.int,\n       let baz = result[\"baz\"]?.string {\n      print(\"\\(bar)\\t\\(baz)\")\n    }\n  }\n} catch {\n  print(\"Error:  \\(error)\")\n  exit(-1)\n}\n<\/pre>\n
Querying for results also uses the execute(_:String)<\/code> method, but now we treat the result of the call as an array of [String:Node]<\/code>.  The keys of the dictionary are the column names returned.<\/p>\n
The Node<\/code> type here is a Vapor data structure that is used for converting amongst different representations.  You can read more about it here<\/a>.  When using it with MySQL we take advantage of the Node<\/code> properties int<\/code>, string<\/code>, object<\/code> to convert from its agnostic representation to a Swift type.  Thus, let bar = result[\"bar\"]?.int<\/code> gives us an Int<\/code>.<\/p>\n
Moving On<\/h3>\n
Now let’s look at a more advanced example with creating a table that contains MySQL DATE, POINT, and JSON datatypes.<\/p>\n
Our table is called samples<\/code>.<\/p>\n
\ndo {\n  try mysql.execute(\"DROP TABLE IF EXISTS samples\")\n  try mysql.execute(\"CREATE TABLE samples (id INT PRIMARY KEY AUTO_INCREMENT, created_at DATETIME, location POINT, reading JSON)\")\n\n  \/\/ ... Date\n  \/\/ ... Point\n  \/\/ ... Sample\n  \/\/ ... Insert\n  \/\/ ... Query\n} catch {\n  print(\"Error:  \\(error)\")\n  exit(-1)\n}\n<\/pre>\n
To insert a date into the database using a SQL statement we will need to appropriately format it:<\/p>\n
\n\/\/ ... Date\nlet now              = Date()\nlet formatter        = DateFormatter()\nformatter.dateFormat = \"yyyy-MM-dd HH:mm:ss\" \/\/ MySQL will accept this format\nlet created_at       = formatter.string(from:date)\n<\/pre>\n
Now let’s create a POINT<\/code> as a Swift tuple:<\/p>\n
\n\/\/ ... Point\nlet location = (37.20262, -112.98785) \/\/ latitude, longitude\n<\/pre>\n
Finally, we want to utilize MySQL 5.7’s new JSON<\/code> datatype, and moreover we’ll use the Jay<\/a> package in Swift to quickly create a JSON-string from a Swift [String:Any]<\/code> dictionary.<\/p>\n
\n\/\/ ... Sample\n  let sample:[String:Any] = [\n    \"heading\":90,\n    \"gps\":[\n      \"latitude\":37.20262,\n      \"longitude\":-112.98785\n    ],\n    \"speed\":82,\n    \"temperature\":200\n  ]\n<\/pre>\n
Hint:<\/b>  You do not have to explicitly call out Jay as a dependency in the Package.swift<\/code> file because it is included transitively by the MySQL package.<\/p>\n
We now want to convert this to a String<\/code> suitable for giving to MySQL:<\/p>\n
\nlet sampleData = try Jay(formatting:.minified).dataFromJson(any:sample) \/\/ [UInt8]\nlet sampleJSON = String(data:Data(sampleData), encoding:.utf8)\n<\/pre>\n
Now we have our date, point, and JSON string (sample), let’s insert the data into the samples<\/code> table:<\/p>\n
\n\/\/ ... Insert\nlet stmt = \"INSERT INTO samples (created_at, location, sample) VALUES ('\\(created_at)', POINT\\(point), '\\(sampleJSON)')\"\ntry mysql.execute(stmt)\n<\/pre>\n
Note we did use a bit of a trick with the POINT<\/code> in that \\(point)<\/code> will expand in our string to (37.20262, -112.98785)<\/code>, thus the full string will be POINT(37.20262, -112.98785)<\/code> which is what MySQL will expect.  The entire statement string looks like:<\/p>\n
\nINSERT 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}')\n<\/pre>\n
Retrieving Results<\/h3>\n
Warning!<\/b>  As of this writing (September 22, 2016), there is a bug in Vapor MySQL 1.0.0<\/a> that crashes on reading a POINT<\/code> datatype, so we’ll have to make do and not use SELECT *<\/code> below.  We’ve filed this issue<\/a> against Vapor MySQL and will update the post once the issue is fixed.<\/p>\n
In the example below we’re going to take advantage of MySQL 5.7’s ability to include JSON data in the SELECT ... WHERE<\/code> clauses.  Here we are looking for those samples where the speed<\/code> field of the JSON data in the sample<\/code> is greater than 80.<\/p>\n
\n\/\/ ... Query\n  let results = try mysql.execute(\"SELECT created_at,sample FROM samples where JSON_EXTRACT(sample, '$.speed') > 80\") \n  for result in results {\n    if let sample      = result[\"sample\"]?.object,\n       let speed       = sample[\"speed\"]?.int,\n       let temperature = sample[\"temperature\"]?.int,\n       let created_at  = result[\"created_at\"]?.string {\n      print(\"Time:\\(created_at)\\tSpeed:\\(speed)\\tTemperature:\\(temperature)\")\n    }\n  }\n<\/pre>\n
A couple of notes here.  The JSON_EXTRACT<\/code><\/a> function is used to return data from a JSON document, selected from the parts of the document matched by the path arguments<\/i>.  In our case here we want to extract<\/i> the value at the path $.speed<\/code> from the sample<\/code> column.  To extract the longitude value from our JSON document we would use the path $.gps.longitude<\/code>.<\/p>\n
To iterate over our results we use the for result in results<\/code> construct in Swift, and then the if let<\/code> construct to validate our result data.  First, use let sample = result[\"sample\"]?.object<\/code> 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<\/code> object that needs to be handed to a JSON parser; that work is already done for you, so you can begin accessing the sample<\/code> dictionary directly.<\/p>\n
The remaining let<\/code>s give us our speed<\/code>, temperature<\/code>, and created_at<\/code>.  Note that created_at<\/code> is a MySQL DATETIME<\/code> which will read in as a String<\/code>.  To convert to a Date<\/code> in Swift you will need to use a DateFormatter<\/code> with .date(from:String)<\/code>.<\/p>\n
Get the Code!<\/h3>\n
If you want to get started with less typing, check out the code from Github<\/a>.  Build everything with swift build<\/code>, but before running the executable remember that you have to have the database, user, and grants already configured.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":3108,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-3237","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-swift"],"_links":{"self":[{"href":"https:\/\/dev.iachieved.it\/iachievedit\/wp-json\/wp\/v2\/posts\/3237"}],"collection":[{"href":"https:\/\/dev.iachieved.it\/iachievedit\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dev.iachieved.it\/iachievedit\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dev.iachieved.it\/iachievedit\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dev.iachieved.it\/iachievedit\/wp-json\/wp\/v2\/comments?post=3237"}],"version-history":[{"count":6,"href":"https:\/\/dev.iachieved.it\/iachievedit\/wp-json\/wp\/v2\/posts\/3237\/revisions"}],"predecessor-version":[{"id":3244,"href":"https:\/\/dev.iachieved.it\/iachievedit\/wp-json\/wp\/v2\/posts\/3237\/revisions\/3244"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dev.iachieved.it\/iachievedit\/wp-json\/wp\/v2\/media\/3108"}],"wp:attachment":[{"href":"https:\/\/dev.iachieved.it\/iachievedit\/wp-json\/wp\/v2\/media?parent=3237"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dev.iachieved.it\/iachievedit\/wp-json\/wp\/v2\/categories?post=3237"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dev.iachieved.it\/iachievedit\/wp-json\/wp\/v2\/tags?post=3237"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}