Skip to content

Convert schema to SQL (replacing schema.rb with structure.sql)

Andreas Brandl requested to merge ab/structuresql into master

What does this MR do?

This makes adjustments necessary to use a plain SQL schema management and also converts the existing schema from schema.rb to structure.sql.

Issue: #29465 (closed)

Benefits

The motivation behind this is the ability to use more advanced PostgreSQL features (e.g. triggers, partitions, table constraints, materialized views etc). Those are typically not covered by standard Rails schema (which abstracts away from the DBMS specifics). With a plain SQL schema, we can use all the features standard Postgres supports - since the checked-in schema is now a plain SQL schema dump from the development database.

In short:

  1. Ability to use any of the advanced PostgreSQL features with ease on the schema management side
  2. Possibly less schema conflicts (the dreaded conflict on the schema version should not happen that often anymore)

Impact on development

We're used to working with a Rails schema.rb schema using the high-level abstraction. With this change, we're going to start working with plain SQL to describe the schema. The migrations itself are unaffected by this - we can continue to use the Rails abstractions (e.g. create_table, add_column etc.) or use SQL DDL as well.

This means that whenever a migration runs locally, the resulting database schema is now reflected in db/structure.sql and not in db/schema.rb anymore. Everything else stays the same - the changes need to get checked in just like before.

At the moment, this doesn't add more complexity even though it might need some getting used to. More down the road - once we start using more advanced PostgreSQL features, the schema likely becomes more complex, too.

Complications

There are some complications around PG version compatibility: We test with PG 9.6 in CI, but ship PG 10 with gdk. There are minor syntactical differences between those versions which have been patched away. This should go away once we've standardized on PG >= 11 with %13.0 .

Alternatives

There are various gems out there to add support for some Postgres features. We would have to add a gem for each of the Postgres feature we wanted to use, if such gem already exists.

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

There is no impact to expect on GitLab.com production or any already existing GitLab installation.

What's missing/next?

Edited by 🤖 GitLab Bot 🤖

Merge request reports