Tutorial: How to use MySQL

In this tutorial you’ll learn how to install and use MySQL with your project 🚀

You can find the result of this tutorial on Github here

This tutorial is a natural follow-up of How to write Controllers. You can either go for that tutorial first and come back later or be a rebel, skip it and read on 😊

Index

1. Install Homebrew
2. Install MySQL
3. Create and generate a new project
4. Configure your project to use MySQL
5. Where to go from here

1. Install Homebrew

If you don’t have it yet I highly recommend to get it. It makes it super easy for you to install dependencies like MySQL. To install Homebrew execute the following in your terminal:

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

We will also install brew services. It will make it incredibly easy to start the MySQL server and let it start alongside with your mac! That’s awesome ✨!

brew tap homebrew/services

Now whenever you want to know what services are running just execute:

brew services list

2. Install MySQL

Installing MySQL with Homebrew is so easy, what am I even here for 😄?

brew install mysql@5.7

We’re installing version 5.7 because only saying brew install mysql installs most likely version 10 which won’t “just work” out of the box right now.

That’s it. Now to start mysql just execute the following command:

brew services start mysql

See how easy brew services makes it? mysql now starts alongside your mac!

We will now set a password for the root user by simply executing:

mysql_secure_installation

It’s a weird command but go for it and answer as the following:

• VALIDATE PASSWORD PLUGIN: no
• When asked for a password I used: root (use whatever feels save 😊)
• Remove anonymous user: yes
• Disallow root login from remote: yes
• Remove the test database: yes
• Reload privilege tables now: yes

That’s it for the installation!

Now let’s create a database that we want to be used by our project. To create a new database we will need to login into our mysql server with:

mysql -uroot -proot

NOTE: -u stands for user and -p for password. I know, captain obvious 👨🏼‍🚀

Now that we are within our mysql server create a database with:

CREATE DATABASE `mycooldb` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

You could have created a database also by just typing:

CREATE DATABASE `mycooldb`;

But we use the command with utf8 in it. It’s considered best practice 🤓

Terminal Cheatsheet:

connect to mysql server:  mysql -uroot -proot
within server list databases:  SHOW DATABASES;
within server to exit server:  exit

Finally you’d have to install cmysql which is needed for the MySQLProvider:

brew install vapor/tap/cmysql

Alrighty! You are ready for step 3. Create and generate a new project 😊

3. Create and generate a new project

We will use the outcome of the tutorial mentioned in the beginning as a template to create our new project:

vapor new projectName --template=vaporberlin/my-first-controller

Before we generate an Xcode project we will have to replace FluentSQLite by FluentMySQL and adjust the target as well as we will change the package name within Package.swift:

// swift-tools-version:4.0
import PackageDescription
let package = Package(
    name: "projectName",  // changed
    dependencies: [
        // 💧  A server-side Swift web framework.
        .package(url: "https://github.com/vapor/vapor.git", from: "3.0.0"),
        .package(url: "https://github.com/vapor/leaf.git", from: "3.0.0-rc"),
        .package(url: "https://github.com/vapor/fluent-mysql.git", from: "3.0.0-rc")  // changed
    ],
    targets: [
        .target(name: "App", dependencies: ["Vapor", "Leaf", "FluentMySQL"]),
        .target(name: "Run", dependencies: ["App"]),
        .testTarget(name: "AppTests", dependencies: ["App"]),
    ]
)

Now in the terminal at the root directory projectName/ execute:

vapor update -y

It may take a bit fetching the dependency, but when done you should have a project structure like this:

projectName/
├── Package.swift
├── Sources/
│   ├── App/
│   │   ├── Controllers/
│   │   │   └── UserController.swift
│   │   ├── Models/
│   │   │   └── User.swift
│   │   ├── app.swift
│   │   ├── boot.swift
│   │   ├── configure.swift
│   │   └── routes.swift
│   └── Run/
│       └── main.swift
├── Tests/
├── Resources/
├── Public/
├── Dependencies/
└── Products/

Before we can run our project we will have to configure MySQL — let’s go 🚀

4. Configure your project to use MySQL

Our first step is to change everything FluentSQLite related to FluentMySQL and register the database information within our configure.swift:

import Vapor
import Leaf
import FluentMySQL  // added
public func configure(
  _ config: inout Config,
  _ env: inout Environment,
  _ services: inout Services
) throws {
  // Register routes to the router
  let router = EngineRouter.default()
  try routes(router)
  services.register(router, as: Router.self)
  let leafProvider = LeafProvider()
  try services.register(leafProvider)
  config.prefer(LeafRenderer.self, for: ViewRenderer.self)
  try services.register(FluentMySQLProvider())  // changed
  let mysqlConfig = MySQLDatabaseConfig(
    hostname: "127.0.0.1",
    port: 3306,
    username: "root",
    password: "root",
    database: "mycooldb"
  )
  services.register(mysqlConfig)
  var migrations = MigrationConfig()
  migrations.add(model: User.self, database: .mysql)  // changed
  services.register(migrations)
}

The second step before we can run our project is to adjust our User model in User.swift:

import FluentMySQL  // changed
import Vapor
final class User: MySQLModel {
  var id: Int?
  var username: String
  init(id: Int? = nil, username: String) {
    self.id = id
    self.username = username
  }
}
extension User: Content {}
extension User: Migration {}

If you now cmd+r or run we should be able to access the /users route 😊!

Note: make sure to select Run as a scheme next to your button before running the app

And that’s it! You successfully configured your project to use mysql 🎉 🚀 🎉

5. Where to go from here

You can find a list of all tutorials with example projects on Github here:
👉🏻 https://github.com/vaporberlin/vaporschool

I am really happy you read my article! If you have any suggestions or improvements of any kind let me know! I’d love to hear from you! 😊

Twitter / Github / Instagram