Database

A Fluent database is responsible for managing connections to your underlying data store and sending queries to the implementation-specific driver you have chosen.

Drivers

By default, Fluent includes in-memory and SQLite drivers. There are several drivers available to add to your Vapor application.

Available

Type	Key	Package	Class	Official
Memory	memory	Fluent Provider	Fluent.MemoryDriver	Yes
SQlite	sqlite	Fluent Provider	Fluent.SQLiteDriver	Yes
MySQL	mysql	MySQLProvider	MySQLDriver.Driver	Yes
PostgreSQL	postgresql	PostgreSQLProvider	PostgreSQLDriver.Driver	No
MongoDB	N/A	MongoProvider	N/A	No

Click on the provider package for more information about how to use it.

You can search for a list of available Vapor database providers on GitHub.

Droplet

You can access the database from the Droplet.

drop.database // Database?

Preparations

Most databases, like SQL databases, require the schema for a model to be created before it is stored. Adding a preparation to your model will allow you to prepare the database while your app boots.

extension User: Preparation {
    /// Prepares a table/collection in the database
    /// for storing Users
    static func prepare(_ database: Database) throws {
        try database.create(self) { builder in
            builder.id()
            builder.string("name")
            builder.int("age")
        }
    }

    /// Undoes what was done in `prepare`
    static func revert(_ database: Database) throws {
        try database.delete(self)
    }
}

The above prepare statement results in SQL similar to the following:

CREATE TABLE `users` (`id` INTEGER PRIMARY KEY NOT NULL, `name` TEXT NOT NULL, `age` INTEGER NOT NULL)

Once you have created you preparation, add it to the Config's prepratations array.

config.preparations.append(User.self)

Create

The following methods are available on while creating and modifing the database.

Method	Type
id	Primary Identifier
foreignId	Foreign Identifier
int	Integer
string	String
double	Double
bool	Boolean
bytes	Data
date	Date + Time

You can use any of these methods on a builder inside .create().

try database.create(self) { builder in
    builder.double("latitude")
    builder.double("longitude")
}

Foreign Keys

Foreign keys are automatically added with .foreignId(). To add a foreign key manually, use the .foreignKey method.

try database.create(self) { builder in
    builder.foreignKey("user_id", references: "id", on: User.self)
}

To disable automatic foreign keys, set autoForeignKeys to false in the Config/fluent.json file.

{
    "autoForeignKeys": false
}

Modifier

Existing schema can be modified using the .modify() property. All of the methods from .create() are available here as well.

try database.modify(self) { builder in
    builder.string("name")
    builder.delete("age")
}

Migrations

Other times, you may want to make some modifications to your data set while migrating to a new version or just performing general cleanup.

struct DeleteOldEntries: Preparation {
    static func prepare(_ database: Database) throws {
        try Log.makeQuery().filter(...).delete()
    }

    ...
}

Run

Your preparations will run every time you run your application. You can run your preparations without booting your server by calling:

vapor run prepare

Revert

Use the revert method to undo any work you did in the prepare method.

extension User: Preparation {
    ...


    static func revert(_ database: Database) throws {
        try database.delete(self)
    }
}

You can run the reversions by calling:

vapor run prepare --revert

This will revert the latest batch of preparations. To revert the entire database, run the following:

vapor run prepare --revert --all

Log

Logging queries is a great way to find optimizations for your application and track down bugs.

The easiest way to log queries is to enable logging in your fluent.json file.

Config/fluent.json

{
    ...,
    "log": true,
    ...
}

This will emit info-level logs for all database queries.

Custom

You can also hook into the database's query logging callback to execute custom logic.

drop.database?.log = { query in
    print(query)
}

You can assign a closure to the log property on the database. Any time a query is run, the closure will be called with a QueryLog object containing a string describing the statement and the time it ran.

Transactions

Transactions allow you to group multiple queries into one single unit of work. If any one of the queries experiences a problem, the entire transaction will be rolled back.

drop.database?.transaction { conn in
    try user.pets.makeQuery(conn).delete()
    try user.makeQuery(conn).delete()
}

Drivers that do not support transactions will throw an error if this method is called.

You can use the .makeQuery(_: Executor) method to create queries that will run on the connection supplied to the closure.

Warning

You must use the connection supplied to the closure for queries you want to include in the transaction.

Indexes

An index is a copy of selected columns of data from a table that can be searched very efficiently.

You can add them to your database by calling .index()

try database.index("name", for: User.self)

You can delete them by calling .deleteIndex()

try database.deleteIndex("name", for: User.self)