Create table based on Codable model

(Thomas Krajacic) #1

Is it possible to create a table based on a Codable model? I am playing around with the 4.0 branch and it looks fantastic.

Only it is supremely tedious to create tables for models with lots of properties. I have to match up all of the columns with the properties, basically recreating the things that are already defined in the model.

(Gwendal Roué) #2

Hello @tkrajacic,

Thanks for the compliment!

No, it is not currently possible to create a table right from a Codable record.

You have to do it separately:

// Define a codable record
struct Player: Codable, FetchableRecord, PersistableRecord {
    var uuid: String
    var name: String
    var score: Int
}

// Define migrations
var migrator = DatabaseMigrator()
migrator.registerMigration("player") { db in
    try db.create(table: "player") { t in
        t.column("uuid", .text).primaryKey()
        t.column("name", .text).notNull()
        t.column("score", .integer).notNull()
    }
}

// Apply the migrations to the database
let dbQueue = try DatabaseQueue("/path/to/db")
try migrator.migrate(dbQueue)

// Use the database
let players: [Player] = try dbQueue.read(Player.fetchAll)

GRDB focuses on database migrations, not on table creation. Migrations are string-based, and have better not refer to application records in any way, because a good migration is a migration that never changes once it has shipped. Only then it becomes a good migration that is testable. A good migration that survives changes in your record types, as new versions of your application ship. A good migration that even survives when record types are removed.

I can hear that is is "tedious" to create a full database table with all its columns. To repeat all the column names. To risk a typo when a column name does not match a property name.

Why is it needed, when other database libraries out there automatically generate table creation for you?

  1. I think that automatic database migration are not a good practice. As a matter of fact, very popular database libraries out there do not provide any automatic migration: ActiveRecord, Core Data, ...
  2. I did not feel enough public pressure for me to reconsider my position.
  3. I did not receive any pull request that I could consider.
2 Likes
(Thomas Krajacic) #3

Hehe, thx. Very nice explanation and reasonable position :slight_smile:

(Gwendal Roué) #4

On top of that, SQLite is not our best friend, because it does not make it easy to drop columns. You have to recreate a full table from scratch. And in the process recreate eventual attached triggers. Triggers that are involved, for example, in full-text search. It is not that I want to throw words at you, it's rather that I want to show that a robust support for automatic migrations would be very, very difficult to do well (if we really want to avoid breaking the precious user data, I mean).

1 Like
(Gwendal Roué) #5

:bulb: Tip

When you are developping your app, and your schema is in flux, it is helpful to ask the migrator to nuke the database and recreate it from scratch whenever it detects a schema change:

var migrator = DatabaseMigrator()
#if DEBUG
// Speed up development by nuking the database
// when migrations change
migrator.eraseDatabaseOnSchemaChange = true
#endif

With this eraseDatabaseOnSchemaChange option, you do not have to delete your app from your simulator or device whenever you fix or update your schema. You just hit Run, and this is pretty cool.

And with the #if DEBUG guard, you make sure your production application won't destroy your precious users' data.

I paste those few lines in all my GRDB-powered apps: try them :-)

See The eraseDatabaseOnSchemaChange Option for more information.

1 Like
(Thomas Krajacic) #6

Haha Gwendal you are awesome.

Of course this is the first thing I ran into.

I just resorted to the abomination that is "delete table if exists and rebuild" :stuck_out_tongue:

Your suggestion is of course a lot more elegant and will be swiftly explored.

(Gwendal Roué) #7

All credits go to @bellebcooper, in https://github.com/groue/GRDB.swift/issues/378!