Schema Compatibility Checking

Currently, persistent's migrations can determine whether or not the database is an exact match. It would be nice to have a feature where persistent can use the migration machinery to determine compatibility - eg, can persistent talk to the connected database without problems?

There are a few things I want to hash out here, so let's go over some scenarios for matching and mismatching, and what is actually compatible.

Foreign Key Cascade

mkPersist sqlSettings [persistLowerCase|

User
    name Text

Organization
    owner UserId

|]

CREATE TABLE user (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE organization (
    id SERIAL PRIMARY KEY,
    owner INT NOT NULL REFERENCES user(id) ON DELETE CASCADE
);

These are not an exact match because the organization table has an ON DELETE CASCADE, while the Organization model does not specify (and receives the default ON DELETE RESTRICT behavior). However, these definitions are compatible - persistent can read/write Users and Organizations just fine. The only difference in runtime behavior is that writing delete userId with an ON DELETE RESTRICT will cause a runtime error due to the schema constraint violation, while ON DELETE CASCADE will also delete the Organization that is owned by the User.

Extra Fields

With Default?

Foo
    name Text

-- foo.sql

CREATE TABLE foo (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT
);

These are not an exact match, but they are compatible -

1. Parsing a Foo out of the database should succeed, since a row will contain the id,name,age and we only need id,name.
2. Inserting a Foo should succeed, since age has an implicit NULL default value.

Currently, we use MigrationOnly to indicate "this column should exist on the database, but not in Haskell." But we uhhh don't do any checks to ensure that the resulting database schema can accept a NULL in the insert list, so that at least needs to be fixed. #918 tracks this, and this comment proposes what is IMO the best solution.

Without Default

Now, consider that foo has a NOT NULL constraint on age. This is now incompatible, because if we try to insert Foo { fooName = "Hello" }, we'll get a runtime error that we've violated a NOT NULL constraint for the column. This is implcitly a default=NULL, so really we should verify that we have a default of some sort in a column for compatibility to work out there.

Unique Key Names ?

What about constraint names?

We can name constraints. Do we use them anywhere? If so, for what operations? It clearly doesn't matter for some operations, but others may rely on the actual name of the constraint (thinking about postgres' upsertBy).

Others?

Start by reviewing persistent's migration machinery and how MigrationOnly columns are handled, then read the discussion in issue #918. Examine the PostgreSQL upsertBy constraint-name question as another compatibility case; done would require agreed compatibility rules and corresponding implementation and tests.