Skip to content

Development change: Database schema handling with plain SQL (structure.sql)

With the change in !22808 (merged), we are changing the way we track the current database schema in the codebase for development purposes.

Going forward, we are going to use db/structure.sql to capture the current database schema with plain SQL. This issue serves as the communication point to track any issues or questions we are running into once this change has been merged.

What this means for development

Aside from working with SQL instead of the Rails abstraction, there are two changes (see below). The way we write database migrations and the development workflow is otherwise unaffected by this.

Schema tracking
  • Old: We use db/schema.rb and Rails abstractions to capture the current schema
  • New: We use db/structure.sql and plain SQL to capture the current database schema
Rake tasks
  • Old: We use rake db:schema:load, rake db:schema:dump to load and dump the database schema
  • New: We use rake db:structure:load, rake db:structure:dump to load and dump the database schema

Follow-ups

We might see conflicts in db/structure.sql for the INSERT INTO schema_migrations towards the end of the file. A follow-up issue is #212051 (closed) to reduce conflict potential and #211521 to detect schema inconsistencies in CI.

What do I have to do now?

  1. Please update your gdk!
  2. Pull in changes or rebase your feature branch onto master (ignore conflicts in db/schema.rb and discard their changes)
  3. If you have new migrations in your branch, reflect their changes in db/structure.sql

For (3), the cleanest way of doing this is to reset the local database schema (rake db:reset - also drops local database data) and re-run the migration on top of it. This results in relevant changes to db/structure.sql which should be committed (same as for schema.rb changes previously).

Alternatively, a rake db:structure:dump dumps the schema of your local database into db/structure.sql. It is up to you to make sure only relevant changes corresponding to database migrations are committed.

Why we are doing this

tl;dr: Flexibility in terms of using more advanced PostgreSQL features.

More details in #29465 (closed) and !22808 (merged).

Q&A

Please leave questions in this issue or reach out to #database on Slack.

I have a conflict in db/structure.sql - what do I do?

Please post the details as a comment on this issue. We'll use the examples to figure out if we can implement automatic conflict resolution for those.

How do I make sure I'm adding the correct changes to db/structure.sql for my migration?

The cleanest way is to perform a rake db:reset on master (this drops all data locally, too) and rake db:migrate on the feature branch. This should result in a diff in db/structure.sql that corresponds exactly to your migration.

Artifacts

This page may contain information related to upcoming products, features and functionality. It is important to note that the information presented is for informational purposes only, so please do not rely on the information for purchasing or planning purposes. Just like with all projects, the items mentioned on the page are subject to change or delay, and the development, release, and timing of any products, features, or functionality remain at the sole discretion of GitLab Inc.

Edited by 🤖 GitLab Bot 🤖