Verify schema at runtime

[axos88]

Is there a supported or third party solution to verify that the schema defined in code and the one we connect to at runtime match?

Eg. Tables have the correct columns, indices, constraints foreign keys?

This would help minimize the risk of pending, missing or incorrect migrations.

[weiznich]

There is currently no built-in way to perform such a schema verification automatically. I'm also not aware of any third party crate that offers this functionality.

The currently best way to ensure that your schema is always what you expect is to use the embed_migration!() macro and then apply the migrations on application startup.

That written we are interested in adding such a verification functionality to diesel at some point so if you are interested in working on this feature we can certainly discuss technical details.

[axos88]

Sure, but that wouldnt protect against unwritten or incorrect migrations though.

I'm not sure i can spend the resources to dive into this currently, but I'll let you know if i do. Maybe start as an independent crate with something small like verify if the columns are all there and take it from there.

I'll ping you if i decide to take it on in the future, but that won't happen for a while

[weiznich]

Sure, but that wouldnt protect against unwritten or incorrect migrations though.

Yes, you are correct there. You are still expected to test your migrations.

I'm not sure i can spend the resources to dive into this currently, but I'll let you know if i do. Maybe start as an independent crate with something small like verify if the columns are all there and take it from there.

I'll ping you if i decide to take it on in the future, but that won't happen for a while

I just had another look at what's already there and what's still missing for this feature and I believe it shouldn't be too hard to implement. What needs to be done is the following:

• Move this code to a shared crate out of diesel-cli. That implements querying the database for schema information. It's used by diesel-cli to generate your schema.rs files, but it should give you all the necessary information for schema verification.
• Add some helper trait for table that essentially give you:
  • The table name as string
  • Possible the schema name
  • A list of column information, consisting of a string for the column and the type information for the column type. The later can be requested by just calling HasSqlType::metadata for the corresponding SQL types of the columns for the backend in question.
• Automatically implement this new trait for each table! macro call here: https://github.com/diesel-rs/diesel/blob/master/diesel_derives/src/table.rs
• Write some logic that uses the infer-schema code to load all table information (there is existing code for this in diesel-cli) and compare that with the information from the helper trait. Comparing the expected and actual state of the schema shouldn't be harder than just check for each table in your schema.rs file that the table exists and each declared column has the right type.
• Finally write some tests.

I think it's reasonable to start this as a new crate in the diesel repo.