This is like a cheat-sheet that is how to use MySQL on Vapor3 for me.

Vapor3 is still new and the information is a few on the internet. So I did many try and error😭

I would be happy if this article helps someone who wants to use Vapor3.

１．Edit swift package

Add FluentMySQL package at Package.swift.

Package.swift

let package = Package(
    name: "Vapor3Practice",
    dependencies: [
        .package(url: "https://github.com/vapor/vapor.git", from: "3.0.0"),
        .package(url: "https://github.com/vapor/fluent-mysql.git", from: "3.0.0")
    ],
    targets: [
        .target(name: "App", dependencies: ["FluentMySQL", "Vapor"]),
        .target(name: "Run", dependencies: ["App"]),
        .testTarget(name: "AppTests", dependencies: ["App"])
    ]
)

Dependencies and targets

I added 2 lines like below.

.package(url: "https://github.com/vapor/fluent-mysql.git", from: "3.0.0")

.target(name: "App", dependencies: ["FluentMySQL", "Vapor"]),

After you added those 2 lines, you need to update packages using this command.

vapor update

２．Create Model class

Create the Model class that is conformed to the protocol "MySQLModel".

final class User : Model {
    var id: Int?
    var name: String
    var email: String
    var passwordHash: String
}

extension User : MySQLModel {}
extension User : Migration { }

To conform the protocol, you just need to add "MySQLModel".

extension User : MySQLModel {}

If your model ID is String or UUID, there are other protocols for those.
The protocols are MySQLStringModel and MySQLUUIDModel.

３．DB setting

Add FluentMySQLProvider, DB settings, and migrations in "configure(...)" func.

configure.swift

public func configure(_ config: inout Config, _ env: inout Environment, _ services: inout Services) throws {
    //Register FluentMySQLProvider
    try services.register(FluentMySQLProvider())
    
    //DB settings
    let mySQLConfig : MySQLDatabaseConfig = MySQLDatabaseConfig(hostname: "127.0.0.1", port:3306, username: "root", password: "password", database: "database")
    services.register(mySQLConfig)
    
    //Add Migrations
    var migrations = MigrationConfig()
    migrations.add(model: User.self, database: .mysql)
    services.register(migrations)
}

Make MySQLDatabaseConfig object to set your DB's hostname, user, password, and database settings.

Then, you can now use MySQL on Vpoar3. Try "vapor run" command. If it's success, you'll see these messages.

[ INFO ] Migrating 'mysql' database (....)
[ INFO ] Migrations complete (....)

４．Queries

Create

let user = User(name: "name", email: "email", passwordHash: "ABC")
user.create(on: req)

    router.post("addUser") { req in
        let user = User(name: "name", email: "email", passwordHash: "ABC")
        return user.create(on: req)
    }

Select

Select is very simple. Just like do this.

User.query(on: req).all()

• all()
  • Obatain all items.
• first()
  • Obtain first items from you selected.
• chunk(max: 12)
  • Obtain 12(the number you want) items from you selected. But if the selection result is less than 12, return all items.

You can set 'Where' using with 'filter' funcs.

    router.get("user") { req in
        return User.query(on: req).filter(\User.email == "email").all()
    }

Update

 user.update(on: req)

    router.patch("user") { req in
        return user.update(on: req)
    }

Delete

router.delete("user") { req in
    let user = User()
    user.id = 12 //ID is for deleting you want to.

    user.delete(on: req)
 }

Where

"filter" makes results filtered like 'Where'. And don' forget import "FluentMySQL".

import FluentMySQL

e.g; "Select * from user where name = 'name';"

User.query(on: conn).filter(\User.name == "name").all()

column = value

This condition can be used like 'if' condition.

.filter(\User.name == "name") // column = value
.filter(\User.name != "name") // column != value
.filter(\User.name >= "name") // column >= value

//and so on...

In

If you want to use 'In', "~~" is that.

.filter(\User.role ~~ [UserRole.admin, UserRole.writer,])

Limit

If you want to obtain items from the result of 20th to 30th items, use 'range()' like this.

.range(20..<31)   // This means 

Order by

.sort(\User.id, .descending)

Join, etc...

I used "JOIN" but I'm still catching tip. So if I properly understand, I'll add it.
　

５．More useful tips

Fetch objects you select.

This is an example that you select some Article objects and show with template view.

router.get("user") { req in
            return  Article.query(on: conn)
            .filter(\Article.status == Status.published).all().flatMap(to:View.self) {
                    articles in

                   //Do something with [article]

                    return req.view().render("templatePath", articles)
            }
}

Save multiple objects

This is an example that is saving some user objects. You can change this part "model.create(on: req)" to "update", "delete", "save" and so on.

router.post("addUsers") { req in
        var models : [User] = []
        
        models.append(User(name: "user1", email: "email", passwordHash: "ABC"))
        models.append(User(name: "user2", email: "email", passwordHash: "ABC"))
        
        return  models.map{
            model -> Future<User> in

            return model.create(on: req) // <- You can change this line to update/save/delete etc.

            }.flatten(on: req).flatMap(to: View.self) {
                results in

               // results is [Article]

                return req.view().render("templatePath", results)
        }
}

Use 2 unwrapped result.

Public func "flatMap" lets you fetch some results of "EventLoopFuture<[Article]> ". So this example shows that obtain all users and articles, and do something with fetched items.
"flatMap" can accept maximum 4 arguments.

router.get("allitems") { req in
    let alllArticles : EventLoopFuture<[Article]> = Article.query(on: req).all()
    let allUsers : EventLoopFuture<[User]> = User.query(on: req).all()
    
    flatMap(to: View.self, allArticles, allUsers) {
        articles, users in
        
        //Do something.
        
        return req.view().render("templatePath", results)
    }
}