6. Configuring a Database

Written by Tim Condon

Databases allow you to persist data in your applications. In this chapter, you’ll learn how to configure your Vapor application to integrate with the database of your choice.

This chapter, and most of the book, uses Docker to host the database. Docker is a containerization technology that allows you to run independent images on your machine without the overhead of virtual machines. You can spin up different databases and not worry about installing dependencies or databases interfering with each other.

Why use a database?

Databases provide a reliable, performant means of storing and retrieving data. If your application stores information in memory, it’s lost when you stop the application. It’s good practice to decouple storage from your application as this allows you to scale your application across multiple instances, all backed by the same database. Indeed, most hosting solutions don’t have persistent file storage.

Choosing a database

Vapor has official, Swift-native drivers for:

• SQLite
• MySQL
• PostgreSQL
• MongoDB

There are two types of databases: relational, or SQL databases, and non-relational, or NoSQL databases. Relational databases store their data in structured tables with defined columns. They are efficient at storing and querying data whose structure is known up front. You create and query tables with a structured query language (SQL) that allows you to retrieve data from multiple, related tables. For example, if you have a list of pets in one table and list of owners in another, you can retrieve a list of pets with their owners’ names with a single query.

While relational databases are good for rigid structures, this can be an issue if you must change that structure. Recently, NoSQL databases have become popular as a way of storing large amounts of unstructured data. Social networks, for example, can store settings, images, locations, statuses and metrics all in a single document. This allows for much greater flexibility than traditional databases.

MySQL and PostgreSQL are examples of relational databases. MongoDB is an example of a non-relational database. Fluent supports both types of databases with different underlying drivers. Be warned though you can’t make full use of the database you choose directly with Fluent. Fluent has to support both types and provide equal features to every database. However you can extend Fluent’s functionality for a specific database, for example to add support for PostGIS. It’s also easy to perform raw queries if needed.

SQLite

SQLite is a simple, file-based relational database system. It’s designed to be embedded into an application and is useful for single-process applications such as iOS applications. It relies on file locks to maintain database integrity, so it’s not suitable for write-intensive applications. This also means you can’t use it across servers. It is, however, a good database for both testing and prototyping applications.

MySQL

MySQL is another open-source, relational database made popular by the LAMP web application stack (Linux, Apache, MySQL, PHP). It’s become the most popular database due to its ease of use and support from most cloud providers and website builders.

PostgreSQL

PostgreSQL — frequently shortened to Postgres — is an open-source, relational database system focused on extensibility and standards and is designed for enterprise use. Postgres also has native support for geometric primitives, such as coordinates. Fluent supports these primitives as well as saving nested types, such as dictionaries, directly into Postgres.

MongoDB

MongoDB is a popular open-source, document-based, non-relational database designed to process large amounts of unstructured data and to be extremely scalable. It stores its data in JSON-like documents in human readable formats that do not require any particular structure.

Configuring Vapor

Configuring your Vapor application to use a database follows the same steps for all supported databases as shown below.

• Add the Fluent Provider as a dependency to the project.
• Configure the database.

Each database recipe in this chapter starts with TILApp as you left it in Chapter 5, “Fluent & Persisting Models”. You’ll also need to have Docker installed and running. Visit https://www.docker.com/get-docker and follow the instructions to install it. The toolbox allows you to choose which database to support, but you’ll learn how to choose a different one manually.

SQLite

Unlike the other database types, SQLite doesn’t require you to run a database server since SQLite uses a local file. Open Package.swift in your project directory. Replace the contents with the following:

// swift-tools-version:5.2

import PackageDescription

let package = Package(
  name: "TILApp",
  platforms: [
    .macOS(.v10_15)
  ],
  dependencies: [
    .package(
      url: "https://github.com/vapor/vapor.git", 
      from: "4.0.0"),
    .package(
      url: "https://github.com/vapor/fluent.git", 
      from: "4.0.0"),
    // 1
    .package(
      url: "https://github.com/vapor/fluent-sqlite-driver.git", 
      from: "4.0.0")
  ],
  targets: [
    .target(
      name: "App",
      dependencies: [
        .product(name: "Fluent", package: "fluent"),
        // 2
        .product(
          name: "FluentSQLiteDriver", 
          package: "fluent-sqlite-driver"),
        .product(name: "Vapor", package: "vapor")
      ],
      swiftSettings: [
        .unsafeFlags(
          ["-cross-module-optimization"], 
          .when(configuration: .release))
      ]
    ),
    .target(name: "Run", dependencies: [.target(name: "App")]),
    .testTarget(name: "AppTests", dependencies: [
      .target(name: "App"),
      .product(name: "XCTVapor", package: "vapor"),
    ])
  ]
)

Here’s what this does:

1. Specify FluentSQLiteDriver as a package dependency.
2. Specify that the App target depends on FluentSQLiteDriver to ensure it links correctly.

Like the other databases, database configuration happens in Sources/App/configure.swift. To switch to SQLite, replace the contents of the file with:

import Fluent
// 1
import FluentSQLiteDriver
import Vapor

// configures your application
public func configure(_ app: Application) throws {  
  app.databases.use(.sqlite(.memory), as: .sqlite)
  
  app.migrations.add(CreateAcronym())
  
  app.logger.logLevel = .debug
  
  try app.autoMigrate().wait()
  
  // register routes
  try routes(app)
}

The changes are:

1. Import FluentSQLiteDriver.
2. Configure the application to use an in-memory SQLite database with the .sqlite identifier.

You can configure SQLite to use an in-memory database — this means the application creates a new instance of the database at every run. The database resides in memory, it’s not persisted to disk and is lost when the application terminates. This is useful for testing and prototyping.

If you want persistent storage with SQLite, provide SQLiteDatabase with a path as shown below:

app.databases.use(.sqlite(.file("db.sqlite")), as: .sqlite)

This creates a database file at the specified path, if the file doesn’t exist. If the file exists, Fluent uses it.

Make sure you have the deployment target set to My Mac, then build and run your application.

Look for the migration messages in the console.

MySQL

To test with MySQL, run the MySQL server in a Docker container. Enter the following command in Terminal:

docker run --name mysql \
  -e MYSQL_USER=vapor_username \
  -e MYSQL_PASSWORD=vapor_password \
  -e MYSQL_DATABASE=vapor_database \
  -e MYSQL_RANDOM_ROOT_PASSWORD=yes \
  -p 3306:3306 -d mysql

Here’s what this does:

• Run a new container named mysql.
• Specify the database name, username and password through environment variables.
• Set MYSQL_RANDOM_ROOT_PASSWORD which sets the required root password to a random value.
• Allow applications to connect to the MySQL server on its default port: 3306.
• Run the server in the background as a daemon.
• Use the Docker image named mysql for this container. If the image is not present on your machine, Docker automatically downloads it.

To check that your database is running, enter the following in Terminal to list all active containers:

docker ps

Now that MySQL is running, set up your Vapor application. Open Package.swift; replace its contents with the following:

// swift-tools-version:5.2
import PackageDescription

let package = Package(
  name: "TILApp",
  platforms: [
    .macOS(.v10_15)
  ],
  dependencies: [
    .package(
      url: "https://github.com/vapor/vapor.git", 
      from: "4.0.0"),
    .package(
      url: "https://github.com/vapor/fluent.git", 
      from: "4.0.0"),
    // 1
    .package(
      url: "https://github.com/vapor/fluent-mysql-driver.git", 
      from: "4.0.0")
  ],
  targets: [
    .target(
      name: "App",
      dependencies: [
        .product(name: "Fluent", package: "fluent"),
        // 2
        .product(
          name: "FluentMySQLDriver", 
          package: "fluent-mysql-driver"),
        .product(name: "Vapor", package: "vapor")
      ],
      swiftSettings: [
        .unsafeFlags(
          ["-cross-module-optimization"], 
          .when(configuration: .release))
      ]
    ),
    .target(name: "Run", dependencies: [.target(name: "App")]),
    .testTarget(name: "AppTests", dependencies: [
      .target(name: "App"),
      .product(name: "XCTVapor", package: "vapor"),
    ])
  ]
)

Here’s what this does:

1. Specify FluentMySQLDriver as a package dependency.
2. Specify that the App target depends on FluentMySQLDriver to ensure it links correctly.

Next, open configure.swift. To switch to MySQL, replace the contents with the following:

import Fluent
// 1
import FluentMySQLDriver
import Vapor

// configures your application
public func configure(_ app: Application) throws {
  // 2
  app.databases.use(.mysql(
    hostname: Environment.get("DATABASE_HOST") ?? "localhost",
    username: Environment.get("DATABASE_USERNAME") 
      ?? "vapor_username",
    password: Environment.get("DATABASE_PASSWORD") 
      ?? "vapor_password",
    database: Environment.get("DATABASE_NAME") 
      ?? "vapor_database",
    tlsConfiguration: .forClient(certificateVerification: .none)
  ), as: .mysql)
  
  app.migrations.add(CreateAcronym())
  
  app.logger.logLevel = .debug
  
  try app.autoMigrate().wait()
  
  // register routes
  try routes(app)
}

The changes are:

1. Import FluentMySQLDriver.
2. Register the database with the application using the .mysql identifier. You provide the credentials for the database using environment variables. If the environment variables don’t exist, the configuration uses the same hard-coded values you provided to docker.

Note: MySQL uses a TLS connection by default. When running in Docker, MySQL generates a self-signed certificate. Your application doesn’t know about this certificate. To allow your app to connect you need to disable certificate verification. You must not use this for a production application. You should provide the certificate to trust for a production application.

Make sure you have the deployment target set to My Mac, then build and run your application.

Look for the migration messages in the console.

MongoDB

To test with MongoDB, run the MongoDB server in a Docker container. Enter the following command in Terminal:

docker run --name mongo \
  -e MONGO_INITDB_DATABASE=vapor \
  -p 27017:27017 -d mongo

Here’s what this does:

• Run a new container named mongo.
• Specify the database name through an environment variable.
• Allow applications to connect to the MongoDB server on its default port: 27017.
• Run the server in the background as a daemon.
• Use the Docker image named mongo for this container. If the image is not present on your machine, Docker automatically downloads it.

To check that your database is running, enter the following in Terminal to list all active containers:

docker ps

Now that MongoDB is running, set up your Vapor application. Open Package.swift; replace its contents with the following:

// swift-tools-version:5.2
import PackageDescription

let package = Package(
  name: "TILApp",
  platforms: [
    .macOS(.v10_15)
  ],
  dependencies: [
    .package(
      url: "https://github.com/vapor/vapor.git", 
      from: "4.0.0"),
    .package(
      url: "https://github.com/vapor/fluent.git", 
      from: "4.0.0"),
    // 1
    .package(
      url: "https://github.com/vapor/fluent-mongo-driver.git", 
      from: "1.0.0")
  ],
  targets: [
    .target(
      name: "App",
      dependencies: [
        .product(name: "Fluent", package: "fluent"),
        // 2
        .product(
          name: "FluentMongoDriver", 
          package: "fluent-mongo-driver"),
        .product(name: "Vapor", package: "vapor")
      ],
      swiftSettings: [
        .unsafeFlags(
          ["-cross-module-optimization"], 
          .when(configuration: .release))
      ]
    ),
    .target(name: "Run", dependencies: [.target(name: "App")]),
    .testTarget(name: "AppTests", dependencies: [
      .target(name: "App"),
      .product(name: "XCTVapor", package: "vapor"),
    ])
  ]
)

Here’s what this does:

1. Specify FluentMongoDriver as a package dependency.
2. Specify that the App target depends on FluentMongoDriver to ensure it links correctly.

Next, open configure.swift. To switch to MongoDB, replace the contents with the following:

import Fluent
// 1
import FluentMongoDriver
import Vapor

// configures your application
public func configure(_ app: Application) throws {
  // 2
  try app.databases.use(.mongo(
    connectionString: "mongodb://localhost:27017/vapor"), 
    as: .mongo)
  
  app.migrations.add(CreateAcronym())
  
  app.logger.logLevel = .debug
  
  try app.autoMigrate().wait()
  
  // register routes
  try routes(app)
}

The changes are:

1. Import FluentMongoDriver.
2. Register the database with the application using the .mongo identifier. MongoDB uses a connection URL as shown here. The URL specifies the host — in this case localhost — the port and the path to the database. The path is the same as the database name provided to Docker. By default, MongoDB doesn’t require authentication, but you would provide it here if needed.

Make sure you have the deployment target set to My Mac, then build and run your application.

Look for the migration messages in the console.

PostgreSQL

The Vapor app from Chapter 5, “Fluent & Persisting Models” you created already uses PostgreSQL. Remember, you created a PostgreSQL database in Docker with the following command in Terminal:

docker run --name postgres \
  -e POSTGRES_DB=vapor_database \
  -e POSTGRES_USER=vapor_username \
  -e POSTGRES_PASSWORD=vapor_password \
  -p 5432:5432 -d postgres

Here’s what this does:

• Run a new container named postgres.
• Specify the database name, username and password through environment variables.
• Allow applications to connect to the Postgres server on its default port: 5432.
• Run the server in the background as a daemon.
• Use the Docker image named postgres for this container. If the image is not present on your machine, Docker automatically downloads it.

To check that your database is running, enter the following in Terminal to list all active containers:

docker ps

To understand how your Vapor application uses PostgreSQL. open Package.swift. It will look similar to the following:

// swift-tools-version:5.2
import PackageDescription

let package = Package(
  name: "TILApp",
  platforms: [
    .macOS(.v10_15)
  ],
  dependencies: [
    // 💧 A server-side Swift web framework.
    .package(
      url: "https://github.com/vapor/vapor.git", 
      from: "4.0.0"),
    .package(
      url: "https://github.com/vapor/fluent.git", 
      from: "4.0.0"),
    .package(
      url: 
        "https://github.com/vapor/fluent-postgres-driver.git",
      from: "2.0.0")
  ],
  targets: [
    .target(
      name: "App",
      dependencies: [
        .product(name: "Fluent", package: "fluent"),
        .product(
          name: "FluentPostgresDriver", 
          package: "fluent-postgres-driver"),
        .product(name: "Vapor", package: "vapor")
      ],
      swiftSettings: [
        .unsafeFlags(
          ["-cross-module-optimization"], 
          .when(configuration: .release))
      ]
    ),
    .target(name: "Run", dependencies: [.target(name: "App")]),
    .testTarget(name: "AppTests", dependencies: [
      .target(name: "App"),
      .product(name: "XCTVapor", package: "vapor"),
    ])
  ]
)

You can see your app depends upon FluentPostgresDriver. Database configuration happens in configure.swift, like all the other database types. Your configure.swift should contain the following:

import Fluent
// 1
import FluentPostgresDriver
import Vapor

// configures your application
public func configure(_ app: Application) throws {
  // 2
  app.databases.use(.postgres(
    hostname: Environment.get("DATABASE_HOST")
      ?? "localhost",
    username: Environment.get("DATABASE_USERNAME")
      ?? "vapor_username",
    password: Environment.get("DATABASE_PASSWORD")
      ?? "vapor_password",
    database: Environment.get("DATABASE_NAME") 
      ?? "vapor_database"
  ), as: .psql)

  // 3
  app.migrations.add(CreateAcronym())

  app.logger.logLevel = .debug

  // 4
  try app.autoMigrate().wait()

  // register routes
  try routes(app)
}

Here’s what this does:

1. Import FluentPostgresDriver.
2. Configure the PostgreSQL database with the .psql identifier. This either uses credentials passed as environment variables or hard-coded credentials that match those passed to Docker.
3. Add CreateAcronym to the app’s list of migrations.
4. Run the migrations automatically on application launch.

When you run your app for the first time, you’ll see the migrations run:

Where to go from here?

In this chapter, you’ve learned how to configure a database for your application. The next chapter introduces CRUD operations so you can create, retrieve, update and delete your acronyms.