Tutorial: How to use MySQL

Martin Lasek
Jan 6, 2018 Β· 5 min read
Image for post
Image for post

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

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 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 --branch=vapor-2

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.0import PackageDescriptionlet 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 😊!

Image for post
Image for post
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! 😊

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch

Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore

Get unlimited access to the best stories on Medium β€” and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store