Deployments: Generate Schema File and Content Migrations

[1R053]

I think it would be great, if collections, fields, auth and probably other settings could be defined descriptively in code. This would make staging and testing apps much safer and efficient. At least for me it would allow directus to be used in much more complex use cases.

The parse platform implements this in an interesting way: http://docs.parseplatform.org/js/guide/#schema

I think, a similar definition (with probably some additions that parse does not support) should be possible for directus. And with some diffing of the schema it should then be possible, to automatically generate any necessary migrations or settings changes.

Such a solution would also solve issues like #3608

[danielbidala]

@1R053 Absolutly agree. Directus app's UI is very impressive, but for a developer it's a nightmare to setup the schema with 'clicks'

[benhaynes]

There's no automation for this yet, but we're working on it.

[Kematia]

What is the best way to migrate my schema to another environment? Currently we are building and testing in Dev environment, but I would like to transfer all the models and permissions to Prod environment in an easy way, not to create/update models and permissions manually.

I use knex migrations, but instead of creating db tables I run commands from the js-sdk (basically using it only for the migration up/down cli).

Not ideal but it works for the development and the initial setup!

You can setup the initial collection via the UI, dump it to JSON and then tweak it from there.

[tylerforesthauser]

I use knex migrations, but instead of creating db tables I run commands from the js-sdk (basically using it only for the migration up/down cli).

Do you happen to have anything else you'd be willing to share that expands on this?

[Kematia]

Sure! I'm still working on it but I'm happy to share!

ping me on discord: kematia8232

[arpijoy]

What @1R053 mentioned regarding migrations makes sense, but setting up the schema "with clicks" can be avoided by building your schema in the database directly. The raw SQL makes it editable and portable.

I tried to create the schema from the database. I did see the collections show up on UI, what I was unable to do was correctly do one-to-many or many-to-many mapping. Unfortunately I had to revert the changes and then create the collections via UI, then alter the tables in the back-end.

[Kematia]

The main issue that I still haven't run into (yet) is manipulating already existing data.

Let's take an "events" system, made up of three collections:

• event_categories: title, description, category_image, category_icon
• event_dates: partecipants, max_partecipants, date, start_time,end_time
• event_items (the actual event): cover_image, name, notes, price, category (relational to event_categories), dates (relational to event_dates)

My development workflow for now is relatively simple:

1. Spin up a clean directus install
2. Use the Admin App to setup the initial collections, both for the Api and the Admin App
3. Populate the collections with some dummy "seed" data
4. Dump the "schema" and the "seeds" to a json file
5. Write the initial migration with the js-sdk, which looks something like this https://gist.github.com/Kematia/cac419267d44ed86a23c9e0ab382a185 (ignore the directus.actions/directus.fields, it's a custom helper for creating fields)

For small changes to the Admin App (ie: adding another option to a dropdown) you can probably safely dump the Admin App fields and reupload them again. But for meatier changes we don't have the tools to safely migrate the data.

Let's say I want to move the event price from the event_item to the event_date, so my client has more flexibility. I would write another migration with the directus js-sdk that would look something like this (pseudo-code warning):

const {prices} = await directus.sdk.items(event_items).read()

await directus.sdk.fields.create(event_dates, {newPriceField})

for await (price of prices) {
    await directus.sdk.items(event_dates).create(price)
}

await directus.sdk.fields.delete(event_items, price)

which is not the end of the world, but it might get muuuch trickier and verbose for more complex stuff like relationships.

It would be an absolute lifesaver to have a way to safely write migrations, either using the js-sdk or a custom tool.

[arpijoy]

@Kematia or others, anyone migrating from Directus8 to Directus9?

[rijkvanzanten]

@arpijoy see https://github.com/directus-community/migration-tool, might be helpful 👍🏻

[arpijoy]

Thanks, this is super helpful

[benhaynes]

Duplicate Ticket From: @AlexisWalravens

I asked yesterday on the discord how would be the best way to make a CI/CD to deploy Directus.
I mean the extensions folder is easy to deploy with some git pipeline, but what about the modification of the database ?

I was said that I had to write the migration myself.
I could be nice to be able to generate migrations for the changes made in the database from the app, I think a lot of people use Directus because it's easy to create content-type from the UI and so on, they don't want to write the migration for every change they made in the app.

It would be a lot easier to make changer on a local env, push to some git and deploy and execute the migrations on production server.

---

Response from @rijkvanzanten

Yup! 😄 That's the check-everything-into-git approach, but also limits the dynamic nature of setting it up through the GUI. #2927 is more closely aligned with what you're asking for 👍🏻

[benhaynes]

"How to Handle Production Deployments?" @ddcollins

There were a few common threads in the Discord channel that I thought worth opening a discussion about.

Are there any recommended ways (or built in tooling that can support it) to deploy a Directus staging environment to production?

I'd appreciate anyone sharing how they currently handle this process!

Threads from Discord:

One question, do we have automatic way to update Directus on different environments? I have data on a pre-production site, but I made changed to my staging, so I need to sync the configuration, because it is pre-production I could copy the database but I do not want to do that and I cannot see and I think that could be "Import/Export Endpoints" right or is there another approach or any suggestion to deal with it right now?

I am also wondering about this. We want to be able to "push" changes from staging environment out to production. Is that at all possible? 🤔

I second this. We also have dev/staging/prod environments and currently publishing new releases of the DB is very tricky and delicate

I ended up building a "snapshot" system around the directus_ tables we cared to carry through our various environments (Collections, Fields, Relations, Webhooks), and created migrations for my data models with Laravel. The snapshots are JSON files that we then version control.

So as we deploy we just run the laravel migrations, then run a "snapshot" load which overwrites the Directus tables.

Have to reset the table sequencing to avoid any issues with incrementing ids, but other than that was a pretty smooth implementation.

---

From @doodlemania2

I think it'd be fantastic if we had a documented procedure for how to set up GitHub actions to deploy to ____ (AWS, Azure, GCP if we must)...

---

From @iksent

This is a very important discussion for us too!
The main problem is:

Having Production & Test
Copy current Production to Test
Making changes and process testing at Test
And... How to copy Test and not to lose Productions changes, that can be made until this time?

---

From @cj

I think something like how https://hasura.io/ creates migrations for meta data, any information changed locally gets a migration file created for it.

I am currently trying to wrap my head around having dev, staging, pull requests and main (production) apps running, but keeping all the database tables, columns and directus_ settings in sync per version. Normally in the case of RoR this is all handled via migrations.

I am contemplating using something like https://github.com/djrobstep/migra for now.

---

CC: @devMls @cesarvarela

[benhaynes]

"Thoughts on (system) database migrations" @WoLfulus

To make sure we have a known state in the database and avoid failing migrations between releases, I'd suggest we remove the "seeds" from the current migration process, since that makes the "fresh installation" process somewhat different than the migration process.

System (Directus) Migrations

Base migration process

• Directus checks if directus_migrations table exists
  • If it doesn't exists, creates the migration table
• If the migration table needs structural changes, apply them (how I'm not sure - needs a separate discussion and if it's going to be needed or not)
• Compare all the existing migrations with the ones registered in the database - list and store all the pending ones
• If there's no pending migrations, exit 0

If running migrate:up [--step]

Runs all the pending migrations

• Fetches the MAX(batch) from the migrations table column into batch
• If running with --step
  • For each pending migration:
    • Add 1 to the batch value
    • Execute the migration and insert a new migration row with the new batch value
• Else
  • Add 1 to the batch value
  • For each pending migration:
    • Execute the migration and insert a new migration row with the same batch value

If running migrate:up --single

Runs only the next pending migration

• Fetches the MAX(batch) from the migrations table column into batch adding 1 to that value
• Gets the next migration, executes it and insert a new migration row with the batch value

Rollbacks

If running migrate:rollback

• For each migration entry in the migrations table (with batch = MAX(batch))
  • Rollback the transaction
  • Remove the entry from the table

Notes

Some side notes on writing migrations in order to keep processes reliable

• We should never write migrations that does more than one thing (each database change is a new migration file), otherwise if one fails, we'll end up in a broken state and probably won't be fixed without manual intervention
• Write idempotent down functions eg. DROP TABLE x vs DROP TABLE IF EXISTS x
• Never delete a column, always rename to be able to restore them
• Never add non nullable columns after the initial release
• Once a migration goes to main branch, it should NEVER be modified

---

User Migrations

Basically the same flow as the system one, but stored in a different table and an additional step to backup/restore data. The idea is to provide developers with more stablish development flows to automate deployments to their staging/production servers.

If running migrate:up --user [--step]

Runs all the pending migrations

• Fetches the MAX(batch) from the migrations table column into batch
• If running with --step
  • For each pending migration:
    • Add 1 to the batch value
    • Dump all the current information (fields & collections + any additional required data)
    • Execute the migration and insert a new migration row with the new batch value and the dump in schema column
• Else
  • Add 1 to the batch value
  • For each pending migration:
    • Dump all the current information (fields & collections + any additional required data)
    • Execute the migration and insert a new migration row with the same batch value and the dump in schema column

If running migrate:up --single

Runs only the next pending migration

• Fetches the MAX(batch) from the migrations table column into batch adding 1 to that value
• Dump all the current information (fields & collections + any additional required data)
• Gets the next migration, executes it and insert a new migration row with the batch value and the dump in schema column

Rollbacks

If running migrate:rollback

Rollbacks all the batch = MAX(batch) migrations (complies with --step)

• For each entry found (batch = MAX(batch)), in reverse order
  • Rollback the migration
  • Restore the data stored in the schema column
  • Remove the entry from the table

Additional commands

migration:create --data

Creates a new user migration file that contains a dump of the current collections & fields + any required additional data within the current environment

Thoughts

This should enable both Directus and the end-user to have a development flow with multiple environments. The migration:create --data allows the user to control when to deploy changes to the fields/collections itself.

An example flow:

• In Development (local):

  • Creates several migrations that creates and edits some tables
  • Reconfigure Directus with new field and collection settings
  • Create another migration file that creates an additional table
  • Configure the new table in Directus
  • Feature is complete and ready to deploy, execute migration:create --data
    • A new migration is added with the Directus settings "dump"
  • Push a release

• Production

  • Deployment starts
  • User migrations runs
  • The last migration (created by the migration:create command) restores the settings the developer was using locally

Note: This is an alternative version to the process I described here https://github.com/directus/api-next/issues/137

Refs:

• https://laravel.com/docs/8.x/migrations
• https://github.com/laravel/framework/tree/8.x/src/Illuminate/Database/Console

---

Moving from old repository (this is an old discussion but might be relevant here, so I copied everything)

Migrations

To enable migrations between different environments, we need to have a way to register all the changes the user makes to its tables. I don't think we should migrate data though, this is ONLY for structural changes, because data most of the time won't match between environments. If they match, it would be easier to just backup the database and restore in the target environment.

I'd suggest having something like the idea "event sourcing" for having that kind of stuff implemented, so we can replay the changes on another database.

Some points I'm concerned about:

1. Should we somehow have an integrity check to make sure we are running the migration over something we understand?

Users might change tables outside Directus (which is where all the freedom of Directus comes from).

I thought of having Directus running in two different modes, a managed and a unmanaged mode, in which in managed, Directus assumes all changes to tables are made over Directus api/app, while in unmanaged, Directus won't be managing any kind of migration between environments, and won't be able to do migrations.

Another option is having a manual migration step, in which changes to database can be manually registered in Directus so it can run for the user later. For example, user can add a step that executes an UPDATE query. Keep in mind that this is fragile, because most of the time it's not going to be cross-database compatible, and user won't be able to execute this migration if using sqlite for development and postgres for staging/production for example.

2. Directus upgrades should be treated as a migration step?

There's always the case that the user made for example, 5 changes in the development environment, then upgraded Directus to another version, then made 7 more changes. I can see this breaking some installations depending on what changes from one version to another.

I'm unsure what to think about this one. It's ok to not do this if you think about user data, as Directus isn't supposed to touch user tables, but I fear that metadata (for configs and stuff) can become out of sync or behave weirdly if this is not enforced.

3. How do we "connect" Directus instances so they know where/how to execute migrations?

There are some ways that I could think of implementing this: one is having Directus projects to know each other, for for example project-dev can point to project-staging, and project-staging point to project-prod, and we can from one project, push the changes to another project, because now they know where to connect/execute the changes. We can also do the same thing, but as pull changes instead, in which the connections are made in reverse (prod knows about staging, and staging knows about dev) - I'd personally prefer this one because of the access levels.

Another way of doing this, is exporting all migration steps to a file, then importing to another project (this is a lot easier to implement and will also work for external/cloned projects too)

---

@rijkvanzanten commented on Feb 24

To enable migrations between different environments

Can you elaborate on this?

You're talking about only migrating the schema, not the data?

---

@WoLfulus commented on Feb 24

Yes, in this case only the schema/database structure. Data doesn't make sense to be migrated (unless it's directus data like interface configs and etc)

---

@rijkvanzanten commented on Feb 24

Wouldn't having just the before and after be enough in that case? That would allow us to calculate the diff and apply that to the old database to get to the new?

---

@WoLfulus commented on Feb 24

Not really, because more stuff might happen in the middle of the process, and diffing databases can be error-prone. Some cases for example might need additional processing like setting a field to "required" which needs columns to have been updated first to get rid of any null values and such. Another scenario is the user renaming field a to b, then creating a new field a. Now all the values that was first at a would be empty on column b if we "diff" the database to make the changes.

---

@rijkvanzanten commented on Feb 24

And this flow is to update one existing database to the state of another database, correct? Eg making sure production is up to date with staging

---

@WoLfulus commented on Feb 24

Yes, we'd make changes on one stage/environment, then apply them to another stage/environment.

---

@rijkvanzanten commented on Feb 24

I'm pretty sure we won't be able to accurately track all the changes if done directly through the database, so I think we'll have to slap a big fat warning on here 🤔

Otherwise we'd have to do a snapshot of the "current state" at a certain interval so we'd be able to track manual changes?

---

@WoLfulus commented on Feb 24

I don't think we'll ever be able to track manual changes at all, that's why I mentioned that we'd allow the user to insert changes manually as custom steps, or have a managed/unmanaged mode.

---

@rijkvanzanten commented on Feb 24

I think a warning "Make sure you mirror all out-of-directus changes manually before proceeding" is enough for a MVP

---

@WoLfulus commented on Feb 24

Yes, and as we're trying to cover too many use cases, both from experienced and non experienced users, having this managed/unmanaged makes sense because power users will have their own migrations/workflow anyways (most of the time, can't assume 100%).

---

@carlobeltrame commented on Jun 7

I'd like to propose a slightly different approach: Using the existing migration system from Laravel. It would work something like this:
When a developer changes something about the data structure through the Directus UI (e.g. sets a table to managed, configures an interface to manage a column, etc.), Directus generates a Laravel migration php file that describes that change. So in a way, I propose using Laravel migration files as a "log" or "event sourcing storage". When deploying Directus to another server, the developer would need to make sure that these generated migrations are also copied over and then applied via the Laravel migration tool (artisan migrate), in the correct order as dictated by the "timestamps" in the migration file names.

To address the questions above:

1. Should we somehow have an integrity check to make sure we are running the migration over something we understand?
   I don't think we can do anything about the developer manually changing the database. But we can encourage the developer to only modify the database via his own Laravel migrations. If he does that, he gets the guarantee that Directus will continue working with his custom database as he intended in future Directus versions.
   So in a way, my proposal is like your manual mode, except all the steps are described as Laravel migrations, so they are not fragile and are invariant to different DBMS (sqlite, postgres, etc.).

2. Directus upgrades should be treated as a migration step?
   This could be solved by enforcing the correct ordering of migrations during a Directus upgrade, via the migration timestamps. So during a Directus upgrade, we'd need to change the timestamps of all new migrations from the upgrade to the current time (plus preserve their order of course).

3. How do we "connect" Directus instances so they know where/how to execute migrations?
   It sounds very neat to be able to make some changes to the database structure locally via Directus, then deploy it to staging or production at the push of a button in the Directus UI. But this would be very dangerous, because it might break any API client running in staging or production. Directus is not a deployment tool, and shouldn't force a specific deployment workflow on the developer in my opinion. Deployments should run only when the code has been properly committed, unit tested, static analysed, manually tested, approved, grouped, tagged, or whatever else the developer's workflow might include. Saving any critical changes to disk for version control is in my eyes the easiest and best supported option for deploying a project to another environment.

4. More stuff might happen during a migration than is evident from an SQL diff
   When setting a column to required (NOT NULL) via the Directus UI, we need enforce setting a default / fallback value, and that default should be reflected in the generated migration file via an UPDATE statement.
   When the developer sets a column to required using his own custom Laravel migration, he is responsible for filling in the empty values of the column in his migration.

5. Both experienced and non-experienced users should be supported
   For non-experienced users it is easy to use the Directus UI to get their DB to the state they need, and they can still deploy via SQL dump if they don't have SSH access to their shared hosting.
   Experienced users can create their DB on their own using Laravel migrations if they so desire. Admittedly, this is effectively a lock-in to the Laravel migration system, as long as one wants to use Directus. In my opinion that is a limitation worth making, since the Laravel migration system is tried and tested, in contrast to reinventing the wheel in a Directus-specific custom migration system.

---

@WoLfulus commented on Jun 7

Hey! Thanks for joining the discussion!

I agree with your points and while the next version of Directus are making way more easy and flexible for developers to use, we still need to cover the general use > case of Directus where most people don't touch code.

With Laravel migrations most of these issues are solved and we'll definitely support the proposed Directus migrations though in a different way/format.

I think the main "issue" is in cases where the end user is using Directus in a way they can't/won't deal with code (using directus as an app only for example) - these > must cover a way to export the changes made to the database (using Directus) and then imported to another Directus installation/server to apply the same structural > changes/steps.

Dumps doesn't work in these cases because as mentioned, you'll probably have different data in different environments.

---

@carlobeltrame commented on Jun 8

Thanks for the explanation, sounds good to me. So if I understand correctly, you do see Directus a kind of migrations tool / database management and deployment tool, kind of like a glorified phpmyadmin. I hadn't thought about it that way. As long as there is a well thought-out system for keeping changes under version control, that's fine with me (even though I'd really prefer to have just one migrations format and one migrations tool).

---

@WoLfulus commented on Jun 8

Right now it's not a migration tool, but we feel like it should support some kind of export/migration system because it's a frequent request on how to transition the > changes between environments.

I experienced this myself, and transitioning the changes from my local development environment to the production server is always a pain (not the schema, but the > Directus configs itself).

We'll iterate a lot more on this topic soon.

---

@WoLfulus commented on Jul 25

FWD from Discussions:

I haven't iterated on this feature just yet. but I was playing with something related last week.

While I still want to provide some kind of migration system within Directus itself, something 
like this (https://github.com/directus/api-next/blob/wolfulus/examples/blog.yml) can be used 
to reconfigure Directus metadata through `php artisan directus:import blog.yml`, while 
Laravel migrations can be used to migrate the database structure. 

I'm also planning on exposing some traits/methods from Directus itself to be written inside 
user's Laravel migrations (that might be the best solution for more advanced users, while 
an import/export might work just fine for non-tech people)

---

Contents of mentioned blog.yml

schema:
  collections:
    - name: posts
      note: All the blog posts.
      fields:
        - name: id
          type: integer
          interface: text-field
          note: The post ID.
          attributes:
            primary: true
            autoincrement: true
        - name: slug
          type: string
          interface: slug
          note: The post slug.
          attributes:
            unique: true
        - name: title
          type: string
          interface: text-field
          note: The post title.
        - name: contents
          type: text
          interface: text-field
          note: The post contents.
        - name: tags
          type: json
          interface: list
          note: The post tag list.
          attributes:
            default: ["general"]
        - name: created_at
          type: date
          interface: datepicker
          note: The time the post was first created.
        - name: updated_at
          type: date
          interface: datepicker
          required: false
          note: The last time the post was updated.
          attributes:
            nullable: true

[benhaynes]

"Use a yaml file to define fields and relations" @mattiaz9

Now fields and relations are stored in the database and we must define them via the app visually. This is great for simple projects and for non technical users, but it can be tedious for large projects that uses multiple environments, because you have to manually updated each fields in production as you have in development.

I was thinking that, in conjunction with the database, we could have a yaml file in the project folder to define manually all the fields and relations.

Benefits:

• This would allow us to define our data model along side migrations
• We could deploy in productions changes to the data model easily

[benhaynes]

"Data model as code under version control" @carlobeltrame

Hi, first of all I'd like to say thank you for this fantastic project! From trying it out it looks really solid, I love to see your dedication and I'd like to build some solutions with it. I found one big open question though which keeps us from using Directus comfortably.
We are building a website with user-customizable content. We currently have 5 clients who will each have their own instance of this website, which they can customize a little and add their content to. The basic models, table structure and fields are the same for each of these website instances. Now I am trying to figure out a way to develop the common ground in a git repository, then deploy a Directus instance with these settings for each client.

I saw the entry in the FAQ talking about this, but I really cannot believe this is the whole story. When you develop a Directus site for a client at Ranger Studio, do you not give them any testing or integration environment that is set up identically to the production? Do your clients really test and modify their sites on production?

What I am looking for is something like the model settings in Strapi. I understand that in Directus, you use your system tables instead of JSON files, which is fine, except for the lack of version control for the model settings.

One "solution" that I found so far is to have a DB dump under version control which includes the database structure and the contents of the Directus system tables (but empty content tables). This could be done with an external migration tool, which would however potentially conflict with the Directus-internal migrations.
I also had a look at adding the data model in my own Phinx migrations to the Directus-internal migrations. I stopped when Directus complained that the model classes for my new content tables (e.g. groups, events, restaurants, categories, etc.) are missing. So maybe the correct way would be to use an external migration tool to set up the data model tables, and then use Directus-internal Phinx migrations to pre-fill the Directus system tables with my desired settings..?
Any way we turn it, this kind of migration-based approach can break easily if the system tables are changed somehow in a Directus update.

Another approach would be to have an official way to write extensions / plugins that can set up the system table entries. As far as I can tell, none of the documented extension types can do this easily. Granted, I could write an independent piece of code that reads my desired data model config from a file and then executes appropriate API requests to set this up in Directus. This seems like something that should really be built in to the framework though. I'd be happy to help work on something like this, but I think the initiative and architectural decisions would have to come from the Directus team.

Maybe there are also improvements in this direction planned in api-next based on Laravel? Maybe I could just define my models as Laravel models then? Should we start building our sites on the api-next alpha?

I'd love to hear your thoughts on this subject.

CC @web7evgheni

[casey-chow]

This is personally my favored approach, having worked in Wagtail before. Using code to handle schema changes helps with deploy confidence a ton. That said, it would be awesome to use Directus only to verify that the shape is as expected and not necessarily handle migrations--I have Directus installed on a production app that already has its own migration scheme.

[rijkvanzanten]

That said, it would be awesome to use Directus only to verify that the shape is as expected and not necessarily handle migrations

Could you elaborate on this a little? You're saying you'd want to have Directus throw an error on startup when the schema doesn't match the database?

[1R053]

I see a few questions that need to be answered to arrive at a high level tech design, and I'll give my first guess suggestion as start for a discussion:

• Where is the single source of truth for the current schema state of a directus instance?
  -> the Directus system tables
• How can a code-first schema definition be obtained for the current state?
  -> an export of system tables to JSON state for internal processing and optionally then a YAML state for human readibility
• How should we arrive at a schema for the JSON state tree model?
  -> follow the normalized entity model to define data model, ui, auth etc.
• How should a code first model be applied to an existing db state?
  1. convert human readable (YAML?) to JSON state
  2. Export existing state to state model
  3. Diff existing and new state models and obtain list of changed objects for all object types
  4. Generate required update actions based on the type of difference for objects and their properties
  5. apply changed update actions in order of their dependencies

In case of applying a system to system migration (e.g. dev -> test -> prod) you can simply leave out the YAML/JSON conversion.

The core design work probably needs to go into

• figuring out a good future proof schema
• defining the translations from diff result to update action
• make sure the dependency path of the update actions is safe
• maybe have a fail safe / rollback mechanism in case something goes wrong during a migration

[WoLfulus]

FWIW, other tools that rely solely on a file for the database schema, like Prisma, seem to handle it more closely to what I mentioned: generate the migrations, and let it up to the end user to make sure these edge cases are handled https://www.prisma.io/docs/guides/database/developing-with-prisma-migrate/customizing-migrations

But this also goes against the mirroring feature doesn't it?

[WoLfulus]

TBH, just by talking about migrations that isn't related to the system data itself already goes against the live mirroring feature.

[rijkvanzanten]

Yeah they don't do that at all. Just bringing it up as they exclusively deal with "schema snapshots" as compared to keeping track of individual changes. Their solution is "We generate a good default. For more specific use-cases, make sure to update the migrations yourself".

it's up to you if you're going to mess it up or not lol

I think by going with this "Either automatic or manual w/ changes" approach, we have a good middleground. We can do it automatically using some opinionated defaults (create/drop instead of rename by default), and if you need some more specific operations, you can "eject" go in and upgrade it yourself at your own risk. This feels very "Directus" to me, as it's very similar to things like Webhooks (good enough for most, with opinionated features) vs Hooks (do whatever you want at your own risk).

[cj]

I would spin up a hasura.io instance with this setup https://hasura.io/docs/latest/graphql/core/migrations/migrations-setup.html#migrations-setup and see how they do it, it works really really well. As you make changes in the UI, for the database it will create an up migration and for metadata stuff it updates the corresponding yml file.

[rijkvanzanten]

@cj that ties back into the

[...] we need to keep in mind that Directus supports "database mirroring", so we must also assume that some changes won't be made via Directus (App/API/SDK/CLI)...

part of the discussion though 🤔 We can't exclusively rely on changes made in just Directus, as you might very well make changes to the schema from a third party service or directly in the database

[mcbloch]

If I would simply describe my use case it would be:

1. Start with a certain Directus project. This could be one created from scratch via Directus or by connecting to an existing database.
2. Make a bunch of changes to the database structure: adding columns, deleting columns, adding relations, ...
3. Save the changes as a migration file that encompasses all the info needed to upgrade a Directus project from the state in step 1. to the state now.
4. Commit this change file (sql, yml, ...) into VCS so I can version my database schema, keep track of the project or apply the migration to a production version.

Not sure if there's any concrete implementation or design already on the way?

[benhaynes]

I believe this is more or less the plan, the main issue with using change logs instead of a schema snapshot is that Directus supports database mirroring. That means that developers should always have access to update the database schema directly... not exclusively through Directus. And that would mean the changes would not be tracked.

[Mahjouba91]

Hi there, I am basically facing the same challenges as you, having multiple environments (development, staging, production), need to do some fields update from time to time in dev and propagate safely those changes. So instead of doing it manually through the database, I was thinking of a config file storing the model where you could keep it in Git, and deploy it through your CI/CD pipeline.

As an example, this is the scenario I use in a WordPress project where I use the ACF Pro plugin, this plugin allows you to create advanced custom fields (like Directus supports natively). It stores the fields information in the database (MySQL), but it also allow you to store a synced JSON file in your repo, so at each field update, you have a new timestamp on this file and a new diff, meaning you can deploy the changed to your other environments and it will run a database migration in the background to update the fields.

I think that's the only missing feature that make it a bit hard to use in a company level right now, otherwise it's really good, thanks for the involvement.

[rijkvanzanten]

This is the only bit that we can't solve for:

so at each field update, you have a new timestamp on this file

We'll have to assume the user can make changes in the database directly, so we'll have to make a snapshot of the whole schema on demand, and generate the diff on the fly by comparing the before and after

[rallisf1]

A bit late to the party and really new to Directus, still instead of struggling to implement a feature that goes against the current workflow, someone can use a 3rd party script to manage migrations. Github did this way: https://github.blog/2020-02-14-automating-mysql-schema-migrations-with-github-actions-and-more/
or you can setup an aws lambda function with this: https://github.com/DBDiff/DBDiff

[1R053]

@rallisf1 looking at the blog you mentioned Github is using skeema to derive migrations from an SQL based schema. This is in essence the suggestion, but in a more generic way.
Since Directus is database agnostic it is not possible to use a tool targeting only one specific database dialect (MySql in the Github case). Also I doubt, that diffing the SQL will be a good approach, as the dialects may vary across databases. I'd prefer to use a generic schema definition syntax for the Directus use case.

[tspvivek]

Can we have a button to dump data of directus_collections, directus_field & directus_relations table into a file from one environment.
And have a option to import them into destination environment and use the imported data to check the presence of collection and fields by comparing imported data and schema.
if there is collection missing in schema then create that collection or if field missing in table schema then create the field.

[rijkvanzanten]

That's the summary of everything said above 😄

[tspvivek]

Incase someone is using postgres DB, you can use this script to migrate between servers. I wrote this for my convenience, seems working so far. Suggestions for improvement welcomed.

[cynova]

Just going to add an idea here based on what we'd use it for:

Currently we have 2 workflows:

• Edit Schema on prod, sync to dev (hard copy of the entire DB) - work on feature - deploy code for feature
• Edit Schema on dev - work on feature - make same change on prod - deploy code

Now I'm not even sure i'd want a completely automated migration because there might be simultaneous changes on Prod and dev (or another dev's dev) - and the result could be quite unpredictable.
What would help immensely though is to be able to export changes made in one environment (to disk for example), and import them somewhere else - and then selectively apply. Basically there would be a list like "activity" that shows:
"Max created column in "
The changes would not be generated by diffing but just from the audit logs (so they only capture changes made through App/API)

These changes could be applied (in order, with a warning when skipping changes, and preventing applying the same change twice), but manually. This way we can only copy the "good" changes and still not waste time redoing them manually (and less risk of mistakes in setting up interfaces/displays).

I think this approach would fit well with Directus' 80/20 principle (i'd argue that admins that make changes in SQL directly would probably want their own migration system anyways - and even then it should still work).
However: Renaming columns. Will/could this at some point be a possible through Directus?
Renaming in DB directly is more dangerous as you might forget about some system tables...

[rijkvanzanten]

However: Renaming columns. Will/could this at some point be a possible through Directus?

Yeah, it's a tricky one as there's so many different aspects to just renaming the column, that being said, I can totally see a dedicated rename endpoint that'll allow for that 👍🏻

[martinemmert]

I am currently writing custom migrations for all of my collections. It works, but it is tedious.
We have to keep reminding ourselves that changes to the data model in the UI are not persistent if not added to the migrations.
It is tedious since the UI of directus is a superb editing experience, but that's currently the only way of having consistent environments across all installations. (We use three different deployments (prod, stage, dev)).

Today I had the following idea. It is a different approach, and I'd like to explore it a bit further with you.

We know what each click does in the directus UI, and it should be pretty possible to "record" those clicks into migration files that could then be used to recreate the current state of the application.
I like the Idea of no-code tools and figure that this could open a whole new set of possibilities.

Any thoughts on this?

/ edit:
While having a closer look at your comments, I found the idea to be brought up by others. Is there already something in the works where I can contribute to?

[arpijoy]

@martijnboland this is exactly what I am doing. 1) Create a separate Directus9 schema, 2) Dump the Create/Alter/insert SQL from Directus (8) schema prod 3) After deploying with directus9 pointing to directus9 schema, create the collections (barebone) using the UI 4) run the create/alter/insert scripts in the DB. 5) Map the mapping using UI manually 6) Updated the index.js to use node config for configuration management for DB connection and custom hooks 7) Test using automated test scripts. It is not messy, however many steps before I can say it looks good, but seems to work for me.

[rijkvanzanten]

and it should be pretty possible to "record" those clicks into migration files that could then be used to recreate the current state of the application.

The problem with this approach is that we also support (and want to keep supporting) making edits in the database directly 🤔 I think the approach that works with all use cases in mind as of right now is to export to a schema file, and then have a system that can compare two of those snapshots and generate migrations on the fly

[LasseRosenow]

Just wanted to through in here, that Prisma.

• It could be used as a format to export into and import from.
• It could be a pretty straight forward way for developers to write their schemas instead of using the gui but without the pain that comes with writing plain SQL
• It automatically generates amazing auto migration files for the schema

The CMS KeystoneJS just recently migrated to it with great success benefitting from auto schema migration etc. :)

[dstoyanoff]

I wanted to share my opinion on this topic.

First of all, I think that the Directus UI is amazing and we should definitely be able to leverage it.

What we are doing now are totally manual migrations. While this is fine for creating custom collections, managing the directus system table is rather painful. I see it as a generally wrong pattern to perform modification on a system that you do not own. What's happening is that we can forget to add/remove a certain value from the system tables, which might later on cause some troubles.

Another problem of that approach is that you need to manually sync the changes from the UI to migrations, which is challenging and often error prone.

I can think of 2 approaches. One is achievable in the short term and one would require more work and possibly re-thinking of the architecture of the product.

Short Term - Migrations API

The idea here is simple. It's similar to the JS API, but done on the database level. I've tried to do something similar in our codebase, but I strongly believe it should be part of the core app. I imagine it having the same structure as the API - we invoke directus.createCollection and Directus generates the migration by creating the new table as well as adding all required entries in the system tables.

Benefits: this would streamline the developer experience and reduce the mistakes, as well as incompatibilities when Directus makes changes to the system tables
Drawbacks: This approach is still rather manual as it requires manual code writing, instead of using the Directus UI

Long Term - Collections Schema

This suggestion is influences by Strapi. What I like there is that you do changes in the UI and Strapi describes your data using JSON files. Then it would automatically migrate your database based on those JSON files. I haven't checked into details how they are doing it to be honest, but the overall experience is great - you can write JSON or you can use the UI, both produce the same result.

Another option would be to use the event system, where every system change would be added to a sequence of events and then a migration can be generated based on those events. Perhaps this approach is closer to what Directus has now. The way I see it:

• you have a checkpoint, which represents the last generated migration
• as the user performs actions in the UI (creation of collections, fields, adjustment of layout, general settings and etc), those events will be put in an event log
• when the user has finished with the work in the UI, he will just run a command to generate a migration. This phrase should create some optimizations (e.g. user created a field and then deleted it, this should be removed from the log) to avoid doing unnecessary work.

[rijkvanzanten]

@alejandro-tss Just described that last option in great detail over here: #7396 🙂

[gkielwasser]

In case that could gave some inputs. I am used to use the Strapi config sync extension. This one is more about "configuration synchronization" between envs. Anything that is configuration stored in database.

It is really usefull to configure stuff like permissions on each collection on a development environnment and then to replicate it on another evnironnement.

Basically, from the source env, it detects changes with the DB and allow you to export a file. From the target env, it detect changes between DB and file and you can apply changes from the file to the db.

[rijkvanzanten]

Happy to report that this was implemented in #7939