Tutorial: How to useΒ MySQL

In this tutorial you’ll learn how to 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


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

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 for the first question about the VALIDATE PASSWORD PLUGIN say no. Then when asked for a password I went for this tutorial for root (you can use whatever makes you feel save 😊). Next you will be asked whether you want to remove the anonymous user, say yes. Now when you’re asked to disallow root login from remote say yes again. And also say yes to remove the test database. Finally say yes to reload privilege tables now. 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 stands 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’d have to add the MySQLProvider as a dependency and also change the package name within Package.swift:

// swift-tools-version:4.0
import PackageDescription
let package = Package(
name: "projectName", // changed
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/leaf-provider.git", .upToNextMajor(from: "1.1.0")),
.package(url: "https://github.com/vapor/fluent-provider.git", .upToNextMajor(from: "1.3.0")),
.package(url: "https://github.com/vapor/mysql-provider.git", .upToNextMajor(from: "2.0.0")) // added
],
targets: [
.target(name: "App", dependencies: ["Vapor", "LeafProvider", "FluentProvider", "MySQLProvider"],
exclude: [
"Config",
"Public",
"Resources",
]),
.target(name: "Run", dependencies: ["App"]),
.testTarget(name: "AppTests", dependencies: ["App", "Testing"])
]
)

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
β”‚ β”‚ β”œβ”€β”€ Routes/
β”‚ β”‚ β”‚ └── Routes.swift
β”‚ β”‚ └── Setup/
β”‚ β”‚ β”œβ”€β”€ Config+Setup.swift
β”‚ β”‚ └── Droplet+Setup.swift
β”‚ └── Run/
β”œβ”€β”€ Tests/
β”œβ”€β”€ Config/
β”œβ”€β”€ Resources/
β”œβ”€β”€ Public/
β”œβ”€β”€ Dependencies/
└── Products/

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

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

4. Configure your project to useΒ MySQL

First thin we need is a configuration file that holds our database information. Create a new Folder within Config/ and name it secrets/ and within here create a file named mysql.json and insert the following configuration:

{
"hostname": "127.0.0.1",
"user": "root",
"password": "root",
"database": "mycooldb"
}

Then we will tell fluent to use mysql as a driver. So in Config/fluent.json:

{
...
  "driver": "mysql",
  ...
}

Lastly we will add the MySQLProvider within Setup/Config+Setup.swift:

import LeafProvider
import FluentProvider
import MySQLProvider // added
extension Config {
public func setup() throws {
try setupProviders()
try setupPreparations()
}
  private func setupProviders() throws {
try addProvider(LeafProvider.Provider.self)
try addProvider(FluentProvider.Provider.self)
try addProvider(MySQLProvider.Provider.self) // added
}
  private func setupPreparations() throws {
preparations.append(User.self)
}
}

Our final cmd+r or run and refresh of /user and that’s it! You successfully configured your project to use a mysql database πŸŽ‰ πŸš€ πŸŽ‰


Thank you a lot for reading! If you have any questions or improvementsβ€Šβ€”β€Šwrite a comment! I would love to hear from you! 😊