Appwrite migration tool

[mbos2]

Based on a small conversation on discord and since this idea was mentioned a few times in the past, let's talk about appwrite migration tool idea.

A CLI tool that could:

• Database:

  • Create/update/delete database, or parts of a database out of file Schemas
  • Sync database data between appwrite projects should database be identical
  • Create database schema backups
  • Create database data backups
  • Seed database with new data
  • Update/Delete specific database rows/documents

• Storage:

  • Storage backups
  • Storage uploads from folders
  • Storage file name changes

• Same type of CLI tool for different languages/environments, like node.js, kotlin, PHP and others ... (write them in the comments, I don't know them all :D )

There could also be a UI-based, self-hosted tool for all of this.
If some member of the community is already working on some UI-based tool, it could be improved with these features.

Example of possible solutions: https://laravel.com/docs/8.x/migrations

Comment your thought, ideas, etc. :D

[tannermeade]

Part of the discussion was about moving db data from a dev server to production. For that use case...

You can backup and restore the MariaDB information using this tutorial: https://dev.to/appwrite/appwrite-in-production-backups-and-restores-4beg

Part of that tutorial is making a MySQL dump of your data. You should be able to create a dump of your MariaDB data and then restore it the same way they do in that tutorial.

I think the only thing missing to get that script working is just a command to dump the correct data. Instead of
docker-compose exec mariadb sh -c 'exec mysqldump --all-databases --add-drop-database -u"$MYSQL_USER" -p"$MYSQL_ROOT_PASSWORD"' > ./dump.sql
It needs something else instead of the --all-databases.

[stnguyen90]

FYI, since the password is the root password, the user should be root.

[eldadfux]

A migration tool is something we would love to see. Both between different Appwrite instances, and from other platforms into Appwrite. One of the challenges is to understand the packaging format, potential dependencies between objects, the order of import, and how to scale the solution for very big exports.

This is for sure something the core team would like to tackle. We will keep this discussion up-to-date with any progress we make.

[tannermeade]

I've been working on creating a solution for this by talking with a lot of people on discord to understand the use cases, investigating the architectural concerns, and familiarizing myself with the relevant parts of Appwrite.

Three conclusions I've come to are:

• The migration tool should be available across local machines and remote machines.
• There is a solid need here for a good GUI that would be hard for a CLI to properly solve (basic use cases like deploying data to an appwrite instance can be done well in a CLI though)
• It ought to be conceptually distinct from an individual Appwrite instance

Something to figure out is where this tool ought to sit between all the Appwrite tools. I'm putting together a basic MVP that hopefully will be a good place for us to investigate how this could work well.

I'll post an update here when I've got a complete and cohesive solution.

[tannermeade]

Small Update:

I've designed most of the system's architecture and have a barebones MVP almost working. I'll create a separate repo for it where I'll describe the architecture at length. I'll make another update once that super early alpha version is complete.

Some details about it

There is no open-source tool to convert data between a variety of database schemas. This tool tries to solve this problem by targeting Appwrite users migrating their data into Appwrite while keeping the larger database migration problem in general in mind. Before trying to scale to petabytes of data, it'll focus on scales that are often done on a single computer.

The core functionality of the tool is to map a source schema to a destination schema, then perform the conversion on the data keeping the variety of data types in mind. Data validation and handling use cases such as dates/times being stored as strings in a variety of formats are some use cases that are kept in mind for the type adapters that are a part of the conversion process. Those type adapters will be easily extendable as well, eventually extendable through the UI too.

Several data origin adapters can be developed to connect the tool to a variety of sources/destinations. Initially, CSV to Appwrite is what will be used however adapters such as JSON, Firebase, MariaDB, MySQL, and Postgres are kept in mind for when the core functionality is complete.

Where I'm at currently

I've developed the CSV adapter and most of the conversion system. Now I am focusing on building out the Appwrite adapter so you can:

• authenticate with Appwrite
• choose a project within Appwrite
• automatically import existing schemas (not just define news ones by the source schema)
• create collections
• it'll upload data in bundles to the Appwrite storage as it converts them
• it'll insert them by creating a cloud function to insert the data

[roman-petrov]

@tannermeade have you seen Prisma? This open source product has interesting concepts and I would love to see something like that in Appwrite; there are some intersections between Prisma the migration tool being discussed.

[tannermeade]

@tannermeade have you seen Prisma? This open source product has interesting concepts and I would love to see something like that in Appwrite; there are some intersections between Prisma the migration tool being discussed.

Looks like a cool ORM! Which Prisma concepts do you find interesting that intersect with the migration tool?

[roman-petrov]

In Prisma I like the concept when we have database schema defined in file(s), automated migration capabilities and ability to generate client code to provide typed access to database. Hopefully some (or all) these features might become part of Appwrite some day.

[roman-petrov]

Not sure if it is a right place to ask/discuss... What I am missing in Appwrite is tooling and documented preferred workflow for entire development & maintenance cycle available out-of-the-box. This includes:

• Ability to easily push code & database updates from different environments like Dev -> QA -> Staging -> Production using automated workflow like GitHub actions.
• Ability to easily backup/restore all Appwrite data; ability to make periodic automated backups with rotation
• Ability to easily move data between Appwrite instances as mentioned in above comments

I think my question may be closely related to "infrastructure as a code" concept. I'm a huge fan of fully automated release cycles, backups, migrations and infrastructure.

So my main question is - are there any long term plans to design & implement such tooling and workflow in Appwrite?

UPD: Just found an RFC for CLI 2.0 https://github.com/appwrite/rfc/blob/cli-2.0/018-cli-2.0/018-cli-2.0.md#-the-appwritejson-file. As I understand, CLI 2.0 will solve some cases I listed above.

[tannermeade]

I'm not on the core team -- however, there is a lot of work being put into CLI 2.0 that will solve many of the dev/prod deployment use cases.

Regarding the other concerns, I've talked to a lot of people about their needs beyond basic CLI tools to deploy between dev/prod environments. I've made some MVPs to explore the ideas and some potential solutions. For solutions that already exist, here is an article on using Terraform and Ansible to deploy Appwrite: https://dev.to/rizkyrajitha/deploy-appwrite-to-aws-using-terraform-and-ansible-4af

I don't know about the core team's long-term plans beyond what you can see in the RFCs, but I'm investigating solutions beyond just migration (which this discussion is focused on migrations) by making tooling using TCP/SSH, Ansible, and Terraform automated. As far as real progress, I've ported Appwrite's UI library (alpine) to Flutter to explore solutions that use a GUI, can support SSH and other tools, and aren't tightly coupled to an individual instance of Appwrite.

[roman-petrov]

Thank you for explanations!

I am just trying to make a decision about using Appwrite in my project and it would by extremely useful to know core team long-term plans for this like Flutter Roadmap shows...

If it makes sense, my use case is: I am not too good in backend development and DevOps, and not familiar with Ansible/Terraform yet. I am just trying to find easy to use out-of-box BaaS solution for Flutter application with ready to use workflow & tooling, preferably based on GitHub actions and plan to use dedicated VPS (and possibly migrate to cloud in future).

I am excited about the work you are doing and I will try to follow your progress:) Thank you for making Appwrite better!

[tannermeade]

@roman-petrov Sounds like Appwrite is probably what you should use. I personally wouldn't bother with Ansible/Terraform for your situation. If you're going to spend time learning, just put Appwrite out on a server and start using it or learn how scale your backend with MariaDB Spider, MariaDB Galera, and ProxySQL/MaxScale (or just wait a bit and they'll release Appwrite Cloud that'll handle scaling for you). Appwrite is also one the best at pushing out quality updates out of all the BaaS options.

[roman-petrov]

Thank you, I am definitely considering using Appwrite and it is the solution number one in my list. But I do not have a team for my project right now and don't plan to scale in near future. Right now the most important for me is workflow simplicity and automation everywhere. That's why I asked about the out-of-box tooling and workflow bundled into Appwrite.

[tannermeade]

Data Migrator Open Sourced

The Data Migrator is now officially open sourced. A lot of effort was put into making sure that this project provides real value to the Appwrite community and is built to scale well. I hope you appreciate it and the time it saves you. This is the first publicly available alpha version (so keep this in mind) however, the main use cases of uploading and migrating data from CSV to Appwrite works well.

This app is targeting the need of uploading data into Appwrite first. Once some more of the core features are finished, data backup will also be implemented in the tool. The tool isn't far off already from being able to backup and restore database schemas as well.

https://github.com/tannermeade/data-migrator

[laggingreflex]

@basatS7T I got it working on Windows a while back, just install flutter (via choco or directly) and follow the same instructions as on mac (i.e. run flutter run).

[tannermeade]

@basatS7T @laggingreflex I just recently updated DataMigrator to work with the most recent Appwrite version, added Windows support, and just pushed a commit that adds the ability to upload users. I had to focus on work over the past little while, so sorry about not getting back. I've got some more time to dedicate to this now and hoping to get it much further! Thanks for the feedback!

[tannermeade]

@basatS7T I am building it with the web in mind and the UI doesn't need to be migrated as Flutter compiles directly to web. However, there are limits on what web browsers can do that would make it not as appealing (example: very limited storage space available which affects the buffers that are needed in the data pipeline). It is definitely possible, and I'll make it deployable to the web but I'm focusing on the desktop first. The main limitation was I didn't have a linux/windows installation handy at the time even though the code was almost ready for them. I believe some people have got it working on linux already with minimal work. Here in a bit, I'll get a linux distro working and add official linux support too.

Eventually, the core of DataMigrator will probably be a stand-alone dart package. At this time, I'm convinced that the problem space does require a decent UI for configuration, however, there is a need for being deployable onto a server cluster/LAN. I'm toying with different ideas for this use case. Right now, I'm going to be gearing it toward being easily deployable via terminal and maybe importing a configuration made on the Flutter app. The use case here is basically for situations where the source and destination are on the same LAN/cluster but no desktop is on that network. It wouldn't make sense to run a separate desktop in a different network to move a lot of data around on a LAN.

[Ruboka]

@tannermeade is your project on hold or did you move the development to a other place?
I am interested in migrating from firestore to appwrite. I thought maybe I could leverage existing solutions like from supabase which create json files per document and import that to appwrite.
Your tool only accepts csv if I understand it right. My biggest problem are the nested documents and how to deal with them
Any new feedback you could give if you are still working at this tool?

[tripolskypetr]

@Ruboka Hey! That tool supports document-per-file json export

https://github.com/react-declarative/appwrite-backup-tool

[maeddin]

In general, an official script for backing up and restoring an entire Appwrite instance would probably be very useful. In other words, a script that simply stores an SQL dump for MariaDB and a volume backup in the same folder and can then load everything from this folder back onto a new AppWrite instance. Then you only have to worry about a place for the backup and not about several scripts for the different Docker containers.

[tannermeade]

That basically already exists by using standard docker/sql commands as detailed here: https://dev.to/appwrite/appwrite-in-production-backups-and-restores-4beg

I just updated DataMigrator to work with the most recent Appwrite version, added Windows support, and just pushed a commit that adds the ability to upload users too.

[chhinsras]

What is the best way to migrate from local instance to another server instance

[rafagazani]

What is the best way to migrate from local instance to another server instance

https://discord.com/channels/564160730845151244/1082652391867944980

[tripolskypetr]

There is a set of scripts which help to backup/restore data, dump appwrite.json schema and diff collection changes
https://github.com/react-declarative/appwrite-backup-tool