Add Database Migration Support for SurrealDB

[gedw99]

What is the issue?

Currently, our project does not have a mechanism for managing database migrations for SurrealDB. Database migrations are essential for versioning and tracking changes to the database schema over time. As our project evolves, we need a way to apply schema changes consistently across different environments (development, staging, production) without manually executing SQL statements.

How can you resolve it step-by-step?

1. Evaluate Migration Libraries: We have two options to consider:

   • surrealdb-migrations: A migration library specifically designed for SurrealDB.
   • surrealgo_migrate: A pure Go migration library for SurrealDB.

2. Choose a Migration Library: After evaluating the features, documentation, and community support of both libraries, decide which one better suits our project's needs.

3. Install the Migration Library: Follow the installation instructions provided by the chosen library's documentation.

4. Set up the Migration Directory: Create a dedicated directory within our project to store migration files. A common convention is to have a migrations directory at the root level.

5. Create Initial Migration: Generate the initial migration file that represents the current state of the database schema. This migration file should be named with a descriptive prefix and a timestamp (e.g., 001_initial_schema.surreal).

6. Write Migration Files: As we make changes to the database schema, create new migration files with descriptive names and timestamps (e.g., 002_add_user_table.surreal, 003_create_index_on_users.surreal). These files should contain the SQL statements necessary to apply or revert the schema changes.

7. Integrate Migration Commands: Depending on the chosen migration library, integrate the necessary commands or scripts into our project's build process or deployment pipeline. This could involve running migration commands during application startup, deployment, or through dedicated scripts.

8. Document Migration Process: Update our project's documentation to include instructions on how to run migrations, create new migrations, and manage the migration history.

9. Test Migrations: Set up a testing environment and ensure that migrations can be applied and reverted successfully without any issues.

10. Commit and Push Changes: Once everything is working as expected, commit the changes to version control and push them to the remote repository.

References/Links

• surrealdb-migrations: A migration library for SurrealDB.
• surrealgo_migrate: A pure Go migration library for SurrealDB, with examples.
• Database Migrations: Wikipedia article explaining the concept of database migrations.

By following these steps, we can introduce database migration support for SurrealDB in our project, ensuring a consistent and maintainable approach to managing schema changes across different environments.

[sbshah97]

Hi, sorry what is it that you are looking to do ?

[gedw99]

sorry for not adding context.

I was think that we could setup this starter project to also include the migrations using that https://github.com/Odonno/surrealdb-migrations

there is also a pure golang migrations project for surreal DB that we could also look at: https://github.com/JohnKinyanjui/surrealgo_migrate.

It already has example setup of it at : https://github.com/JohnKinyanjui/surrealgo_migrate/tree/main/examples/database/migrations

Let me know what you think and we can work on it together if you wish.

[sbshah97]

This looks interesting and promising. Do you want to put out a MR?

[sbshah97]

Hi, I've updated the description by a bit, hopefully that helps.

[gedw99]

Yes it’s much better .

[gedw99]

This looks interesting and promising. Do you want to put out a MR?

Would like to but right now am chockers with work , so I added it so others could also pick it up