ory/cli

Provide command to manage pure SQL migrations

Closed this issue · 2 comments

Preflight checklist

Context and scope

The Ory CLI provides a command to manage fizz migrations. However, in most projects, we moved away from fizz migrations and are now writing plain SQL files containing the migration SQL commands.

This design doc aims to describe a set of commands to manage these SQL migrations in Ory projects.

The names of SQL migration files in Ory projects typically follow a set schema. These names contain information about the time of the creation of the migration, as well as some context about the content of the migration. The schema is YYYYMMDDHHmmSS000000_<name>.[engine.]up|down.sql

For example:
The migration 20220926173820000000_recovery_code.up.sql would be a migration created on the 26th, September 2022 at 17:38:20 (5:38:20 pm) and it contains SQL to create the table for recovery codes.

Additionally, the up part after the "name" of the migration indicates that the files contains the statements to execute the migration.

Each migration also consists of another down version that should contain the statements to "undo" the migration (where possible). This is needed in case the change needs to be reverted and the state before the change needs to be restored.

Optionally, in most Ory projects, migrations can also be written to target a specific database engine. This is done by adding its identifier (e.g. mysql, postgres, sqlite3 or cockroach) between the "name" of the migration, and it's "type" (up or down). So 20220926173820000000_recovery_code.mysql.up.sql would only target MySQL databases.

Goals and non-goals

Goals:

  • Provide an easy-to-use way to manage and maintain SQL migrations over the course of the development of a feature/PR/etc.

Non-Goals:

  • A testing environment for SQL migrations

The design

Working with SQL migrations can be tedious and repetitive, so the SQL command should provide a set of commands to work with these migrations:

  • A command to easily create new migration files with the current timestamp given a name
  • A command to copy an existing migration to a specific database engine
  • A command to update the timestamp of a specific migration to the current time (very useful when working on larger codebases over a longer timespan, where the timestamp is outdated after a while)

APIs

No response

Data storage

No response

Code and pseudo-code

No response

Degree of constraint

No response

Alternatives considered

No response

I would add that 0000 is a sequence number. Since some DBs can’t mix DDL and DSL statements in the same transaction, we need one file for DSL and one for DDL. So maybe add a flag of how many of these files we want?

Hello contributors!

I am marking this issue as stale as it has not received any engagement from the community or maintainers for a year. That does not imply that the issue has no merit! If you feel strongly about this issue

  • open a PR referencing and resolving the issue;
  • leave a comment on it and discuss ideas on how you could contribute towards resolving it;
  • leave a comment and describe in detail why this issue is critical for your use case;
  • open a new issue with updated details and a plan for resolving the issue.

Throughout its lifetime, Ory has received over 10.000 issues and PRs. To sustain that growth, we need to prioritize and focus on issues that are important to the community. A good indication of importance, and thus priority, is activity on a topic.

Unfortunately, burnout has become a topic of concern amongst open-source projects.

It can lead to severe personal and health issues as well as opening catastrophic attack vectors.

The motivation for this automation is to help prioritize issues in the backlog and not ignore, reject, or belittle anyone.

If this issue was marked as stale erroneously you can exempt it by adding the backlog label, assigning someone, or setting a milestone for it.

Thank you for your understanding and to anyone who participated in the conversation! And as written above, please do participate in the conversation if this topic is important to you!

Thank you 🙏✌️