Connecting Vapor to MySQL and Returning a JSON Response

To get more familiar with Vapor, I will go a little beyond Hello World. This time, I will connect to a database, save data with a POST method, and return a JSON response.

That said, if all you want is to achieve the state described above, it is easy. When creating a new project with Vapor Toolbox, choosing to use Fluent automatically generates migration files, controllers, and so on.

So I created a new project.

I tried making it with the project name simple-api. At the time of project generation, an application using a simple table called Todo is already included. Looking at the code for the Todo model and controller may give you a rough sense of how things are configured.

$ vapor new simple-api
Cloning template...
name: simple-api
Would you like to use Fluent (ORM)? (--fluent/--no-fluent)
y/n> y
fluent: Yes
db: MySQL
Would you like to use Leaf (templating)? (--leaf/--no-leaf)
y/n> y
leaf: Yes
Generating project files

...

Setting Up Database Connection Information with Environment Variables

Looking at the generated code gives a general idea of how the database connection works.

Sources/App/configure.swift

import NIOSSL
import Fluent
import FluentMySQLDriver
import Leaf
import Vapor

public func configure(_ app: Application) async throws {
    ...

app.databases.use(DatabaseConfigurationFactory.mysql(
        hostname: Environment.get("DATABASE_HOST") ?? "localhost",
        port: Environment.get("DATABASE_PORT").flatMap(Int.init(_:)) ?? MySQLConfiguration.ianaPortNumber,
        username: Environment.get("DATABASE_USERNAME") ?? "vapor_username",
        password: Environment.get("DATABASE_PASSWORD") ?? "vapor_password",
        database: Environment.get("DATABASE_NAME") ?? "vapor_database"
    ), as: .mysql)

    app.migrations.add(CreateTodo())

    ...
}

I wondered whether environment variables could also be set from an .env file, and when I checked the documentation, it seemed possible.

• Vapor: Basics → Environment

So I configured it. For the database, I created one named vapor-sample-1 for the time being with CREATE SCHEMA vapor-sample-1 DEFAULT CHARACTER SET utf8mb4 ;.

DATABASE_HOST=localhost
DATABASE_PORT=3306
DATABASE_USERNAME=XXXXXXXX
DATABASE_PASSWORD=XXXXXXXX
DATABASE_NAME=vapor-sample-1

I set the local connection information. MySQL itself is running as a local Docker container.

Migration

The database still has no tables, but fluent has a migration feature, and you can create the initial table from the command line as follows.

$ swift run App migrate
Building for debugging...
[9/9] Linking App
Build complete! (2.10s)
Migrate Command: Prepare
The following migration(s) will be prepared:
+ App.CreateTodo on <default>
Would you like to continue?
y/n> y
[ INFO ] [Migrator] Starting prepare [database-id: mysql, migration: App.CreateTodo]
[ INFO ] [Migrator] Finished prepare [database-id: mysql, migration: App.CreateTodo]
Migration successful

This created the todo table in the database.

Inside the Todo Controller

Looking at the sample controller, you can see that routes named index, create, and delete have been created.

Sources/App/Controllers/TodoController.swift

import Fluent
import Vapor

struct TodoController: RouteCollection {
    func boot(routes: RoutesBuilder) throws {
        let todos = routes.grouped("todos")

        todos.get(use: self.index)
        todos.post(use: self.create)
        todos.group(":todoID") { todo in
            todo.delete(use: self.delete)
        }
    }

    @Sendable
    func index(req: Request) async throws -> [TodoDTO] {
        try await Todo.query(on: req.db).all().map { $0.toDTO() }
    }

    @Sendable
    func create(req: Request) async throws -> TodoDTO {
        let todo = try req.content.decode(TodoDTO.self).toModel()

        try await todo.save(on: req.db)
        return todo.toDTO()
    }

    @Sendable
    func delete(req: Request) async throws -> HTTPStatus {
        guard let todo = try await Todo.find(req.parameters.get("todoID"), on: req.db) else {
            throw Abort(.notFound)
        }

        try await todo.delete(on: req.db)
        return .noContent
    }
}

Starting the App and Creating One Todo Record

Start the app with $ swift run. I will try hitting the POST method with a REST client. This creates one record with the title My Title.

POST http://localhost:8080/todos HTTP/1.1
content-type: application/json

{
    "title": "My Title",
}

If everything is configured correctly, a response like the following is returned. The record was also created successfully in the database, which gives a satisfying feeling of having cleared a hurdle.

HTTP/1.1 200 OK
content-type: application/json; charset=utf-8
content-length: 64
connection: close
date: Tue, 27 Aug 2024 13:26:02 GMT

{
  "id": "4587E60A-E9ED-43EB-B692-294124BB4E13",
  "title": "My Title"
}