Migrations across workspaces

Hi Xata team!

I'd like to know if it would be possible to do DDL migrations between different workspaces. I'll explain: we are aiming to have a different workspaces, one for production, another for development, etc. And each workspace contains databases whose branches are for multi-tenant purposes (one customer has one branch).
I already have some code to update from the

main

main

main

main database to any other branch in that database (I do what we can see in Xata's UI, but in code, but wanted to know if we can do it across workspaces.

Thanks!!

exekias•8/21/23, 2:48 PM

Hi! If you use the Xata workflow to handle your schema (more info here: https://xata.io/docs/getting-started/workflow), you can connect as many databases as you want to your repo. Then the schema changes will happen to all connected databases every time you push a new migration to the repo.

exekias•8/21/23, 2:48 PM

would that work for you?

exekias•8/21/23, 2:49 PM

ah, I see you use other branches besides the main one. This would only work for the

main

main

branch

kostas•8/21/23, 3:07 PM

As @exekias suggested, the Xata Workflow will handle merging branches in multiple databases (no matter which workspace they are under) but it looks like you are actually performing migrations against multiple branches of the same database, which would require multiple PRs.

In this case, the migration can be scripted using the API https://xata.io/docs/api-reference/db/db_branch_name/schema/update (a single /schema/update/schema/update API call can apply multiple schema change operations in a branch)

Eusebio TrigoOP•8/21/23, 6:03 PM

Yes, that’s what we are after, performing multiple migrations against branches in the same database.

Our thought process was, we do our things in “dev” and then when ready, we migrate dev.dbName to prod.dbName and then to all the branches from prod.dbName.

Eusebio TrigoOP•8/21/23, 6:08 PM

Then we will need to find a way to keep all the operations applied from dev.dbName to its branches and then apply the ops to prod.dbName and its branches.

Eusebio TrigoOP•8/21/23, 6:09 PM

Because… there is no API to compare across Workspaces, right?

Eusebio TrigoOP•8/21/23, 6:10 PM

I’m aware this use case is quite specific and away from the general purpose.

kostas•8/22/23, 10:11 AM

The built-in workflow in the Web UI for migration requests is "database-scoped", enabling migration requests between branches under the same database.

You are correct that we do not currently provide a direct method for a cross-workspace/cross-db branch "diff". The compare branch schemas API runs under a common workspace and db: https://xata.io/docs/api-reference/db/db_branch_name/schema/compare/branch_name#compare-branch-schemas

The file-based migrations model, which our Branching Workflow is built upon, supports fetching schema history from another database, but that process is aimed to be used with our GitHub integration. In principle at least, it could be possible to use the /schema/history API https://xata.io/docs/api-reference/db/db_branch_name/schema/history#query-schema-history to fetch migrations from any database and then push them to your target database https://xata.io/docs/api-reference/db/db_branch_name/schema/push#push-migrations. However, a cross-workspace migration workflow isn't what we've been aiming at/designing for so this might trip on unpredictable conditions.

A safe bet is to create a migration request with your required schema changes and use the update branch schema API https://xata.io/docs/api-reference/db/db_branch_name/schema/update#update-branch-schema to apply it to any number of target workspaces/branches/databases.

Eusebio TrigoOP•8/22/23, 11:20 AM

I like this "might trip on unpredictable conditions"

Eusebio TrigoOP•8/22/23, 11:21 AM

I'll work on this, and let you know how it goes.

Eusebio TrigoOP•8/22/23, 11:21 AM

Thanks for your help!

Eusebio TrigoOP•8/22/23, 11:29 AM

Hold on, I forgot to ask this: Is there a recommended model to handle this, like keeping in synch the branches?

kostas•8/22/23, 11:50 AM

With the push migrations API, there is a logical continuum in the migration order, the most recent migration must be referenced as parentID. So all branches will need to be always in sync for the same migration IDs to work across.

kostas•8/22/23, 11:52 AM

Alternatively, you can use the GH workflow to issue PRs from an edited branch to multiple other target branches, if a GitHub-based approach suits your dev environment better

Eusebio TrigoOP•8/23/23, 8:16 AM

I´ll need to review the GitHub approach. But thing is we don´t know how many branches of a database we would eventually have, and it would be quite time intensive if we need to open PRs to all different branches out there.

Eusebio TrigoOP•8/23/23, 8:16 AM

Will check the migrations API

kostas•8/23/23, 8:53 AM

Understandable. Just noting, you are probably already aware of it, above 15 branches which come with the free tier, branches are a sales item: granting 5 branches per unit.

Eusebio TrigoOP•8/23/23, 9:47 AM

Yes, yes, we are aware of it, thanks for reminding it.

Eusebio TrigoOP•9/6/23, 5:34 PM

Just for your possible interest:

We were able to do a migration cross-workspaces:
- check if the database exists, otherwise create it with xataApiClient.database.createDatabase
- get the details of the sourceBranch using xataApiClient.branches.getBranchDetails and get the schema
- compare the previous schema using xataApiClient.migrations.compareBranchWithUserSchema, with the target branch in the target workspace, and get the schemaCompareResponse.edits
- apply those edits using xataApiClient.migrations.applyBranchSchemaEdit in the target database branch of the target workspace.

Migrations across workspaces

Similar Threads

Similar Threads

Similar Threads