More ALTER operations in Migrations

[Querschlag]

At the moment GRDB already covers the the most basic operations for adding new tables and columns. When it comes to altering an existing table or just a column it could mean a lot of boilerplate code. Say I have a table with a column that has a NOT NULL constraint and I want to drop it. To achieve this I need to create a temporary table with all the columns, constraints and checks of my current table and omit the NOT NULL constraint for said column. After that I need to copy all the data from the old table to the new one, drop the old, rename the new one and rebuild any indexes and triggers I had on the old table.
I understand that this is also a limitations in SQLite itself, lacking the support for proper alteration, but I wonder if we can do better. During my testing I used DB Browser for SQLite which allows me to perform changes on my tables without having to rename or drop them explicitly.

It would be great to see support for altering operations on columns that take the current state of the table and do the moving and recreation implicitly.

With that it would be possible to remove:

• NOT NULL
• (PRIMARY KEY)
• (AUTO INCREMENT)
• UNIQUE
• DEFAULT
• Checks (replace or remove)
• (FOREIGN KEY)

and of course entire COLUMNS.

[groue]

Hi again @Querschlag!

Yes, alterations that are not built in SQLite have the developer drop and recreate tables, alongside with their eventual indexes, foreign keys, views, and triggers. The technique is fully documented by SQLite, and supported by GRDB with the DatabaseMigrator.registerMigrationWithDeferredForeignKeyCheck method.

This means that users can already, today, alter their tables in any way they want. Even if, as you say, this involves "a lot of boilerplate code".

Now, you may have noticed how the alteration API looks like:

// ALTER TABLE player ADD COLUMN url TEXT
try db.alter(table: "player") { t in
    t.add(column: "url", .text)
}

It was already ready for extra alterations :-)

try db.alter(table: "player") { t in
    t.add(column: "url", .text)
    t.drop(column: "picture") // Future API?
    ...
}

OK, so let's see what we can say.

The technique documented by SQLite is not, unfortunately, a reproducible recipe that can maximally covers all kinds of database schema changes. Precisely speaking, it is difficult to 1. spot and 2. recreate views, triggers, and indexes that are impacted by custom alterations.

Indexes are easily found and introspected, but I'm not sure there exists a general recreation rule. Is a unique index that loses a column still unique? Generally speaking, indexes support performed queries, and we don't know which queries will be performed after the schema change. We can make educated guesses, but there will always be unexpected situations where the library user would have performed a different choice.

For views and triggers, for which SQLite offers no introspection API (I should check more about views), we would need to parse the raw SQL stored in the sqlite_master table. This means embedding an SQL parser right inside GRDB, and maintaining it over the years as the SQLite grammar evolves. As long as this lib has a single main contributor who works on his free time, this is simply out of reach, and, to put it simply, a non-goal.

What are we left with?

• We could just not recreate indexes at all. A clear documentation would help users run the high-level alteration, and recreate their indexes afterwards.

• We could just ignore views and triggers. Again, this is a rule that is easy to document, and users would know when they have to go low-level.

Now, what's next? Maybe a pull request with the first extra alteration? Everything starts with a real need: what was the last ALTER operation you missed?

[groue]

It would be great to see support for altering operations on columns that take the current state of the table and do the moving and recreation implicitly.

Unless I'm mistaken, you don't quite ask for individual alterations directly, but for "table diffs": given the state of the database, and the target schema, the lib would perform the necessary changes.

Many ORM out there actually don't require any schema definition at all. Instead, they look at the application runtime types (the records), and generate whatever SQL it takes until the schema can handle those types.

This is a non-goal here. I want the user to be able to leverage the full power of SQLite, and above all to control of the schema AND the evolution of the schema. So far, I don't know better tool at this job than migrations that are defined in strict isolation of the application runtime types.

I'm very keen to welcome more "basic" alterations. They will spare a lot of boilerplate as well. But they will not prevent users from defining their schema, because this is what the lib wants them to do.

Maybe our target API will look "diffy" - but for sure it will be designed for migrations, not around record types.

[Querschlag]

I was aware of registerMigrationWithDeferredForeignKeyCheck and used it when recreating my tables. However I did not notice the ability to drop columns. When I run your sample I get an compilation error about the drop(column:) method not being defined - I'm on GRDB 4.1.1.

try db.alter(table: "player") { t in
    t.drop(column: "picture")  // Value of type 'TableAlteration' has no member 'drop'
    ...
}

Many ORM out there actually don't require any schema definition at all. Instead, they look at the application runtime types (the records), and generate whatever SQL it takes until the schema can handle those types.

The issue is really just about individual alterations during a custom migration. Although I have already worked with schema diffs in Realm and Django I don't miss them too much.

Maybe our target API will look "diffy" - but for sure it will be designed for migrations, not around record types.

That's actually fine, but it would be good to have some predictable shorthands for common operations. Once a table schema gets bigger it will be more and more difficult and error prone to manually recreate the previous state without missing something.

Now, what's next? Maybe a pull request with the first extra alteration? Everything starts with a real need: what was the last ALTER operation you missed?

For a start the ability to drop columns would be sufficient, as this already shortens the code significantly and only requires manual specifying the new column properties. I assume it would be possible to first drop and then add the same column in one alteration?

Another more specific use case would be to remove NOT NULL and UNIQUE constraints, which should not cause any problems.

[groue]

Oops, drop(column:) is not implemented yet, that's the whole point :-)

For a start the ability to drop columns would be sufficient.

Yes, this is a good idea. Are you interested in contributing yourself?

[groue]

which should not cause any problems.

I can't wait until you try to implement the features you are requesting, so that you can grab an actual measure of their difficulty 😅

I now also wonder if we can fully recreate foreign keys with their ON DELETE and ON UPDATE actions, and their DEFERRED flag.

Same question about checks:

try db.create(table: "players") { t in
    t.column("name", .text)
        .notNull()
        .check { length($0) > 0 }
}

[groue]

@Querschlag, I have added a link to this issue in the CONTRIBUTING file, so that any user who wants to extend the table alteration APIs can find all the useful information that has been gathered here. Now we can let the usual growth process of GRDB live on: features are added when someone decides that it is time to add it.

If I were to sum up my own position on extra table alterations, I would say that it is not of high priority. It is already possible to perform any schema change in a safe and robust way. When I compare my own experience of such alterations with the constraints that are listed in the above discussion, I can see that "naive" alterations often fall short. I thus don't see any clear personal advantage on jumping in this task: my threshold has not been crossed yet. But I will warmly welcome pull requests, and provide the best guidance and support I can.

I have always been quite OK with the fact that GRDB lacks feature that some people take for granted, and will always be. If I weren't, GRDB would have never existed. It would have been impossible to ship the 192 (!) releases of the lib, each one steadily and regularly fulfilling needs of application developers, one after the other, over four years. The constant quality of the lib is that when a feature is not available as a high-level API, it is always possible to achieve it at a lower level. The lib never blocks anyone from reaching their application goals, that's what I'm very stubborn about.