Fluent Part 4 - Persistence With MySQL And Fluent Patterns

Written by Tim on Tuesday, October 24, 2017. Last edited on Thursday, January 4, 2018.

This is the fifth tutorial of a series of tutorials on Vapor. For more, click here


In the last tutorial we rounded off the relationships by adding a new Category model and a sibling relationship between that and our Reminder model. In this tutorial we are going to finish talking about Fluent for now, talk about some of the conventions that are emerging and importantly talk about persistence.

Model Keys

Currently in our model code, we need to define the initialisers for Row and JSON. We also need to tell Fluent how to create the database tables for our models in our Preparation. Until Codable is supported in Fluent 3 we need to manually specify all of they keys for our models. One of the emerging patterns of Fluent is to use a struct to define our keys. This makes sense as generally having hardcoded strings lying around in our code is never really a good idea, especially when they are duplicated! If we create an inner struct in our Model to hold the responsibility for naming our keys, that becomes a lot safer. So in our Reminder model, create an inner struct inside our Model that looks something like:

struct Properties {
    static let id = "id"
    static let title = "title"
    static let description = "description"
    static let userID = "user_id"

We can then replace all the occurrences of our 'magic string' keys with our new Properties struct. So for instance. (Note we can use either a struct or an enum, but a struct with static keys reads a bit better.) Once we have replaced all of the occurrences, our new model will look like:

import FluentProvider

final class Reminder: Model {

    let storage = Storage()
    let title: String
    let description: String
    let userId: Identifier?

    struct Properties {
        static let id = "id"
        static let title = "title"
        static let description = "description"
        static let userID = "user_id"

    init(title: String, description: String, user: User) {
        self.title = title
        self.description = description
        self.userId = user.id

    init(row: Row) throws {
        title = try row.get(Properties.title)
        description = try row.get(Properties.description)
        userId = try row.get(User.foreignIdKey)

    func makeRow() throws -> Row {
        var row = Row()
        try row.set(Properties.title, title)
        try row.set(Properties.description, description)
        try row.set(User.foreignIdKey, userId)
        return row

extension Reminder: Preparation {
    static func prepare(_ database: Database) throws {
        try database.create(self) { builder in

    static func revert(_ database: Database) throws {
        try database.delete(self)

extension Reminder: JSONConvertible {
    convenience init(json: JSON) throws {
        let userId: Identifier = try json.get(Properties.userID)
        guard let user = try User.find(userId) else {
            throw Abort.badRequest
        try self.init(title: json.get(Properties.title), description: json.get(Properties.description), user: user)

    func makeJSON() throws -> JSON {
        var json = JSON()
        try json.set(Properties.id, id)
        try json.set(Properties.title, title)
        try json.set(Properties.description, description)
        try json.set(Properties.userID, userId)
        return json

extension Reminder: ResponseRepresentable {}

extension Reminder {
    var user: Parent<Reminder, User> {
        return parent(id: userId)

extension Reminder {
    var categories: Siblings<Reminder, Category, Pivot<Reminder, Category>> {
        return siblings()

No more magic strings! We can now do the same for our User and Category models (the keys are the same):

struct Properties {
    static let id = "id"
    static let name = "name"

Whilst this tidies up our code, it also gives us a big advantage when we want to refer to those keys outside of our model. For instance, in our controller we may want to filter on a column in our model (we will look at that in a later tutorial) and instead of having to remember what our column name is, and making sure we spell it right, we can use our Properties key and the compiler will provide the right string for us!

Improved Sibling Control

Currently in our app, when we want to create a relationship between a Reminder and a Category we have to ensure that we create a category first. Whilst this works when demonstrating how relationships work, it isn't the best user experience. A user who is creating a reminder won't want to go and make sure that the category they want to add to their reminder exists and then if it doesn't create it, all before they can create their reminder! To improve this, we'll borrow some logic from SteamPress - a user can enter any category in their reminder creation request and the app will automatically deal with it and create it if it needs to.

We will still allow user's to create a category separately if they would like, but now in our RemindersController when a user creates a reminder we will also create the category there as well. First, create a new function in our Category model addCategory(). This function will add a category to a provided Reminder, creating it if it needs to:

static func addCategory(_ name: String, to reminder: Reminder) throws {
    var category: Category

    let foundCategory = try Category.makeQuery().filter(Properties.name, name).first()

    if let existingCategory = foundCategory {
        category = existingCategory
    } else {
        category = Category(name: name)
        try category.save()

    try category.reminders.add(reminder)

In this function we see if the category exists in the database. If it doesn't, we'll create it there. Once we know it exists, we add our reminder to the category. We check by getting the category with that name using a Fluent query, which will return nil if one doesn't exist. We will take a look at queries in a later tutorial, but notice how we are using our new Properties key so we don't have 'stringly-typed' keys everywhere! Now we have this function, we can use it in our RemindersController. So edit the createReminder function so that when we loop through the categories array from the JSON, we simply cal our function:

func createReminder(_ req: Request) throws -> ResponseRepresentable {
    guard let json = req.json else {
        throw Abort.badRequest
    let reminder = try Reminder(json: json)
    try reminder.save()

    if let categories = json["categories"]?.array {
        for categoryJSON in categories {
            guard let categoryName = categoryJSON.string else {
                throw Abort.badRequest
            try Category.addCategory(categoryName, to: reminder)

    return reminder

Note that this will change our API slightly in that you no longer have to send the category as a full JSON object in an array with the ID, you simply send an array of strings with the category names - much simpler! So a request would now look something like:

  "description": "Shopping for the week",
  "title": "Shopping list",
  "user_id": 1,
  "categories": [


Up until now, all of the data for our app (such as any create reminders) has been done with Fluent's memory database. This is an in-memory SQLite database, which is great when developing and trying things out, however it isn't ideal for a real app since we would lose all of our data when the app restarts! Luckily Fluent supports a number of different databases, including MySQL officially as well as awesome community supported PostgreSQL and MongoDB Fluent providers. For this tutorial we will use MySQL, but because we are using Fluent the steps for choosing one of the others are almost identical!

Installing MySQL

If you already have MySQL installed locally you can skip ahead to the next section, just make sure you have the cmysql from the Vapor Brew tap installed.

To be able to test this, we need to be able to create a MySQL database to connect to. The simplest way to do this on a Mac is by using Homebrew. You will also need the MySQL C shim from Vapor to make the compiler work. Both of these can be installed with brew install mysql vapor/tap/cmysql.

This will install the MySQL server on your Mac. The first thing you should do is run the mysql_secure_installation command - this will set a root password (remember it!) and lock down your MySQL instance. Once you are set up, we need to create a user and a database. Start your database with brew services run mysql - this will start the MySQL server up. Then you can connect with mysql -uroot -p - this will prompt you for the root password you created. Once you are connected to the MySQL instance you should run:

> CREATE DATABASE reminders;

This will create the database we will use. Next we need to create a user that will be able to access our database (you shouldn't use root!):

> CREATE USER 'reminder_user'@'localhost' IDENTIFIED BY 'reminder_password';
> GRANT ALL ON reminders.* TO 'reminder_user'@'localhost';

These three commands will create a new user, reminder_user with the password reminder_password (you should obviously change this!) and then give them permission to use the reminders database. The FLUSH PRIVILEGES command ensures that these changes are applied. You should now be able to leave the MySQL CLI (with quit) and log back in with our new user to make sure everything is working correctly:

mysql -ureminder_user -p

Integrating With MySQL

Once MySQL is up and running we need to bring in the MySQL Provider as a dependency in our app. So edit the Package.swift manifest and add in the new dependency:

// swift-tools-version:4.0

import PackageDescription

let package = Package(
    name: "Reminders",
    products: [
        .library(name: "App", targets: ["App"]),
        .executable(name: "Run", targets: ["Run"])
    dependencies: [
        .package(url: "https://github.com/vapor/vapor.git", .upToNextMajor(from: "2.1.0")),
        .package(url: "https://github.com/vapor/fluent-provider.git", .upToNextMajor(from: "1.2.0")),
        .package(url: "https://github.com/vapor/mysql-provider.git", .upToNextMajor(from: "2.0.0")),
    targets: [
        .target(name: "App", dependencies: ["Vapor", "FluentProvider", "MySQLProvider"],
                exclude: [
        .target(name: "Run", dependencies: ["App"])

NOTE: In Swift 4 you also need to tell it which targets will use the MySQL Provider, you you need to add it as a dependency to the App target.

Once you have added the dependency, regenerate the project with vapor xcode -y. This will fetch the new dependency and set everything up in the Xcode project (you will need to build the project for Xcode to pick up the new module). In your Config+Setup.swift file, import MySQLProvider at the top and then add the provider in the setupProviders() function:

private func setupProviders() throws {
    try addProvider(FluentProvider.Provider.self)
    try addProvider(MySQLProvider.Provider.self)

And that is all of the code changes! You do however need to change a couple of config files. The first one you need to change is your fluent.json file and set the driver to use MySQL from the original memory:

    "driver": "mysql",

You will also need to add in a mysql.json configuration file. It is best to do this is a new secrets directory in Config as this won't be checked into Git. You don't want to be checking in passwords to your database into source control. So create the directory and the new file:

mkdir Config/secrets
touch Config/secrets/mysql.json

And then in that file, set it up with the following (obviously change the password with whatever you set it to be):

    "hostname": "localhost",
    "user": "reminder_user",
    "password": "reminder_password",
    "database": "reminders",
    "encoding": "utf8mb4"

With that done your app will now use MySQL for its database and everything will be persisted between restarts! If you want to stop the MySQL server, you can just run brew services stop mysql to stop it.

Developing With MySQL

Being able to run your app with MySQL is great, especially for production, but sometimes we don't want to use MySQL when developing as we want to be able to change things regularly. If we keep adding fields we have to either use migrations or revert or even manually destroy our database each time. What I tend to do is to is use MySQL when running in production mode, but when developing normally in a debug mode is use the in-memory database (with some seeds, but we'll talk about those later!). This way I get the best of both worlds. To do this, you just need two fluent.json files - your normal one and the production one.

First, change the driver in the fluent.json in your Config directory back to memory. Then create a new production directory and a new fluent.json file:

mkdir Config/production
touch Config/production/fluent.json

In your new fluent.json configuration file in the Production directory, add:

    "driver": "mysql"

With this it will use MySQL when running in production (you can do this with something like vapor build && vapor run --env=production) and use the in-memory database for development!

Wrapping Up

This will be the last tutorial focused on Fluent for a while and you should now have an understanding on how to use it properly, create relationships and get ready for persisting data. We have briefly touch some concepts like queries and we'll revisit them in a later tutorial as well as looking at how to do migrations and how to seed the database. In the next tutorial we will look at how to use Vapor on the front-end with Leaf.

All of the code for these tutorials can be found on Github, with this tutorial under the tutorial-5/fluent-4-persistence tags.