Defining a VARCHAR Column in Vapor 4

This Vapor tip is similar to the previous one, though a bit simpler. Of course, one of the most common data types to store in a database backend is text of some kind or other. The Vapor 4 documentation on models encourages you to use Swift’s String type in the model definition:

Sources/App/Models/Galaxy.swift

final class Galaxy: Model {
    // Name of the table or collection.
    static let schema = "galaxies"

    // Unique identifier for this Galaxy.
    @ID(key: .id)
    var id: UUID?

    // The Galaxy's name.
    @Field(key: "name")
    var name: String

    // Creates a new, empty Galaxy.
    init() { }

    // Creates a new Galaxy with all properties set.
    init(id: UUID? = nil, name: String) {
        self.id = id
        self.name = name
    }
}

And the migration documentation encourages you to use Vapor’s .string type in the database column definition:

Sources/App/Migrations/CreateGalaxy.swift

struct CreateGalaxy: Migration {
    // Prepares the database for storing Galaxy models.
    func prepare(on database: Database) -> EventLoopFuture<Void> {
        database.schema("galaxies")
            .id()
            .field("name", .string)
            .create()
    }

    // Optionally reverts the changes made in the prepare method.
    func revert(on database: Database) -> EventLoopFuture<Void> {
        database.schema("galaxies").delete()
    }
}

The .string type here, though, gets translated to an SQL column type of TEXT, which allows strings of unlimited length. I found that odd as many other ORMs, and indeed previous versions of Vapor, default to using a VARCHAR column type. I asked in the Vapor Discord about that a while ago and I was told that the change was made in order to maximize compatibility across database implementations; SQLite, in particular, doesn’t have a concept of a VARCHAR type¹ (although it does alias that name to TEXT so you won’t get an error if you try to define a VARCHAR column). It was also pointed out that in many modern SQL database implementations, TEXT and VARCHAR columns are stored in the same way so there isn’t much of a performance penalty for using a TEXT column. (PostgreSQL’s documentation on text types calls this out specifically.)

All of this is indeed true, but there are nevertheless perfectly valid reasons to prefer VARCHAR columns over TEXT. Often, limiting the length of a string is highly desirable, especially in the context of a system that accepts user input. Knowing that certain strings will never be longer than n characters can be very helpful in laying out the UI and also helpfully limits the scope of edge case testing. It’s also relevant to indexing decisions as many index types are limited to the number of bytes that can be indexed per row.²

With all that background out of the way, how then do we define a VARCHAR column in Vapor 4? Vapor 4 doesn’t include a .varchar type in the same way that it has the .string type, so we’ll need to work around that. The model definition doesn’t need to change; Swift doesn’t particularly care if the string length is limited on the backend. The change, then, needs to be made in the migration, and it really is quite a simple change:

Sources/App/Migrations/CreateGalaxy.swift

struct CreateGalaxy: Migration {
    // Prepares the database for storing Galaxy models.
    func prepare(on database: Database) -> EventLoopFuture<Void> {
        database.schema("galaxies")
            .id()
            .field("name", .custom("VARCHAR(100)"))
            .create()
    }

    // Optionally reverts the changes made in the prepare method.
    func revert(on database: Database) -> EventLoopFuture<Void> {
        database.schema("galaxies").delete()
    }
}

As you can see, all we did was change .string to .custom("VARCHAR(100)"). The .custom type here allows us to pass in arbitrary SQL, so you will need to make certain that (1) the syntax is correct for your chosen database engine (you are using the same database engine in development as in production, right?) and (2) that you aren’t including user-defined input (that would be an exceptionally strange thing to do in a migration anyway).

All code tested with Swift 5.2, Vapor 4.5, Fluent 4.0.0-rc2.2, and PostgreSQL 12 on both macOS 10.15 and Ubuntu Linux 18.04.