Bart Simons

mysql

A 1 post collection


Using MySQL in Swift with Perfect

 •  Filed under swift, mysql, perfect

So I recently got a chance to play around with Perfect - a "web app framework" for Swift that provides you with multiple building blocks to get your project going. One of the really neat things about this framework is that it contains anything you need to get stuff connected. I came across their MySQL library that can be used in projects with or without the other Perfect dependencies involved. You can't get any more modular than this!


My development environment

I tend to always use Linux for my projects, and this little project is no exception. My dev box is equipped with the following stuff:

  • Ubuntu Server 16.04 LTS
  • MySQL Server
  • Swift 3.0.2

You also need to install the libmysqlclient-dev package through apt-get in order to use and build the Swift MySQL package.


Creating a new project

Create a new directory for this project on your server. I called mine mysql-app. Change your current directory to that directory and run swift package init --type executable to generate a blank Swift project. If you have a Mac and plan to use Xcode for your project: you can initialize an Xcode project file with swift package generate-xcodeproj


Adding Perfect-MySQL to the project

Edit your Package.swift file and include Perfect-MySQL as a dependency, like this:

import PackageDescription

let package = Package(  
    name: "mysql-app",
    dependencies: [
        .Package(url: "https://github.com/PerfectlySoft/Perfect-MySQL.git", majorVersion: 2)
    ]
)

Now it's time to write some code!


The database structure


The code

Just put the following code inside Sources/main.swift:

import MySQL

let sqlHost     = "127.0.0.1"  
let sqlUser     = "app_authentication"  
let sqlPassword = "testing01"  
let sqlDB       = "app_authentication"

func fetchData()  
{
    let mysql   = MySQL()
    guard mysql.connect(host: sqlHost, user: sqlUser, password: sqlPassword, db: sqlDB) else
    {
        print(mysql.errorMessage())
        return
    }

    defer {
        mysql.close()
    }

    let theStatement = MySQLStmt(mysql)

    _ = theStatement.prepare(statement: "SELECT * FROM users")
    _ = theStatement.execute()

    let theResults = theStatement.results()

    print("\r\n")

    _ = theResults.forEachRow {
        e in

        for i in 0..<e.count {
            let theFieldName = theStatement.fieldInfo(index: i)!.name
            print("\(theFieldName): \(e[i]!)")
        }

        print("\r\n")
    }
}

fetchData()  


Build and run

Just run swift build to build your project and run .build/debug/mysql-app to run your application. It should return this as the output in your terminal:


Extra information

I made this small tutorial after I had to fiddle around with this library for one whole day. The documentation suggests to use mysql.query to run queries, but there's too little information available about running prepared statements. Not only in the documentation of Perfect, but also on the whole web. Then - a couple hours later - I discovered the MySQLStmt class, after digging into the Swift Tests available on their Github repo.

Another reason to only use the MySQLStmt class is because the class contains a function called fieldInfo which returns the name of a column by ID. mysql.query simply doesn't have that...

Oh well, at least I got some of my code fixed today. Enjoy programming in Swift!