Provide command to manage pure SQL migrations
Closed this issue · 2 comments
Preflight checklist
- I could not find a solution in the existing issues, docs, nor discussions.
- I agree to follow this project's Code of Conduct.
- I have read and am following this repository's Contribution Guidelines.
- This issue affects my Ory Cloud project.
- I have joined the Ory Community Slack.
- I am signed up to the Ory Security Patch Newsletter.
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 🙏✌️