Generic Column

[hartbit]

I was looking at SQLite.swift and one thing they have which is pretty sweet is a generic/typed Expression, the equivalent of GRDB's Column. It should allow more robust, type-checked expressions for the query language. I guess this must has already been discussed. Is there a reason is hasn't been implemented? Lack of time? Design flaw?

[groue]

A design flaw in SQLite.swift, you mean?

You're right: the rigidity of typed expressions has unfortunate consequences:

• Typed expressions don't match the fundamentally dynamic typing of SQLite.

  SQLite can store values of any type in any column. Of course it looks like one should not do that. And indeed you should not store a string in an integer column.

  But SQLite will accept it, and some databases exist in the wild that contain such value soups. There's no point not supporting them.

  Moreover, SQLite will store integers or floats in columns that have the numeric affinity (affinity should not be confused with declared types in a table definition - check the previous link). Such numeric columns are frequent: the types NUMERIC, DATETIME, DECIMAL, BOOLEAN, etc. all give columns a numeric affinity (see doc).

  That last point is the cause of a long-standing issue in SQLite.swift, that doesn't look even close to a solution, and prevents this library to correctly handle a whole class of databases. GRDB handles this situation as gracefully as you can imagine (read: you could use GRDB for years without even knowing such a problem could exist).

• Typed expressions prevent users from using raw SQL to overcome the limitations of the query builder.

  OK, not "prevent". But they make it a real PITA.

  Indeed, support for raw SQL in SQLite.swift suffers from a cruel lack of love. Quoting the documentation:

  let stmt = try db.prepare("SELECT id, email FROM users")
  for row in stmt {
      for (index, name) in stmt.columnNames.enumerate() {
          print ("\(name)=\(row[index]!)")
          // id: Optional(1), email: Optional("[email protected]")
      }
  }

  You may not see it at first glance. But since "type-safe" expressions are, by design, only usable in the query builder, one eventually realizes that consuming raw SQL queries gives you raw SQLite values (limited to Int64, String, Blob, Double, Null), and that all conversions have to be done application-side (no convenience conversion from Int64 to Int or Bool, from String to Date, etc.)

  Besides, the full row information (column names and values) is split into the statement (column names), and the row (values).

  Unfortunate consequence: you can't, with SQLite.swift, build a custom type initializer which accepts both rows from the query builder, and rows from raw SQL statements.

  Again, GRDB handles this situation as gracefully as you can imagine, and lets you use the query interface or raw SQL, at our convenience, with the same ease, to fuel your application models.

Shiny typed expressions could be introduced in GRDB, along untyped expressions. But they have an insidious and poisonous seduction to the unaware developers. "Type safety" is all the rage, with Swift. Everybody would rush on typed columns. And when a developer gets the kiss of the spider (the two caveats listed above), it's too much work to refactor the app with saner, untyped, columns.

That's why I have decided not to introduce them in GRDB.

Does it mean that GRDB has no type safety?

Of course not! GRDB is a SQLite library with a focus on application development. That's why it walks on two equally important legs: SQL, and Records. SQL because we're professionals. Records because no developper likes to deal with raw database rows.

The type-safety of GRDB lies in the types of records' properties:

class User : RowConvertible {
    var id: Int64               // here
    var email: String           // here
    var registrationDate: Date  // and here
    
    init(row: Row) {
        id = row.value(named: "id")
        email = row.value(named: "email")
        registrationDate = row.value(named: "registrationDate")
    }
}

let users = try User.fetchAll(db, "SELECT * FROM users")

Type-safe expressions have no purpose in the example above. And yet GRDB manages to be quite safe, and prevents data loss or corruption.

Now, the Query Interface has introduced the Column type:

let user = try User.filter(Column("email") == "[email protected]").fetchOne(db)

That Column type can also be used in records, in order to avoid some repetition:

class User : RowConvertible {
    var id: Int64
    var email: String
    var registrationDate: Date
    
    static let idCol = Column("id")
    static let emailCol = Column("email")
    static let registrationDateCol = Column("registrationDate")
    
    init(row: Row) {
        id = row.value(User.idCol)
        email = row.value(User.emailCol)
        registrationDate = row.value(User.registrationDateCol)
    }
}

let user = try User.filter(User.emailCol == "[email protected]").fetchOne(db)

Now, I agree that many parts of GRDB are still "stringly typed", and could be improved. Suggestions are welcome.

But typed expressions are not part of these improvements.

[groue]

I consider the Column type to be a necessary evil.

I don't like code snippets like the following:

// Nothing really happens here
let idCol = Column("id")
let emailCol = Column("email")
let registrationDateCol = Column("registrationDate")

It pleases masochistic personalities who enjoy feeding the monster (their database library), instead of taking advantages from the library:

// Each line has an effect
class User : RowConvertible {
    var id: Int64
    var email: String
    var registrationDate: Date
    
    init(row: Row) {
        id = row.value(named: "id")
        email = row.value(named: "email")
        registrationDate = row.value(named: "registrationDate")
    }
}

Now the query interface needs to build expressions, and to build expressions we need columns:

let wines = try Wine
    .filter(originCol == "Burgundy")
    .order(priceCol)
    .fetchAll(db)

So the Column type came to life. Even if it has some developers carefully write their unattractive list of column declarations.

That was the price of the query builder.

[groue]

Another design flaw in SQLite.swift is that the same type (generic Expression) is involved in both table definition, and table consumption.

Consequence: users use the same expressions for both table definition and consumption, in the name of DRY:

// Look, ma! I don't repeat myself!

let users = Table("users")
let id = Expression<Int64>("id")
let email = Expression<String>("email")
let name = Expression<String>("name")

try db.run(users.create { t in
    t.column(id, primaryKey: true)
    t.column(email, unique: true)
    t.column(name)
})

for user in try db.prepare(users) {
    print("id: \(user[id]), email: \(user[email]), name: \(user[name])")
}

Why is it a problem? Because database schemas evolve over time. Quoting this comment:

SQLite.swift shares table and column (expression) objects between regular queries, and the schema setup. As you notice quite well, this is a problem as soon as the application code is updated and does not reflect any longer the actual schema of the database file.

Other database libraries like Core Data, Ruby's ActiveRecord, or GRDB.swift, don't do that: they make sure that the code that defines and migrates the database schema is strictly separated from the regular operations. In order, precisely, to avoid this very problem you are facing.

Your solution is thus to bring back this isolation yourself, since SQLite.swift does not do it for you: Don't share tables and expressions between migrations and regular operations. Isolate your migrations. Let them use obsolete tables and columns, since this is precisely their job.

Your application code will be much cleaner, since the future database schema updates will not need already written migrations to be changed. Your code will also be more verbose, because you'll have to declare table and column expressions several times. That's the consequence of an unfortunate design decision - but your application maintenance is more important than a little code duplication.

This was another example of the poison that inherently lies in typed expressions.

[groue]

I admit that some flaws that I have attributed above to typed columns are more flaws in SQLite.swift itself. Some of them could be fixed, with more of less difficulties. It remains that typed columns belong to the "cathedral" type of libraries, when I prefer GRDB to belong to the "screwdriver" family of libraries. I value straightforward and to-the-point code over pretty abstract constructions that lose touch with reality. It has well served GRDB and its users so far.

[hartbit]

@groue Thanks again for taking the time to explain your design desisions. It really shows how well designed and thought out GRDB is 👍

[groue]

Thanks for asking, because writing things down help a lot!