What are the steps to create a transactional migration in Bun.js

1. Create a migration file with the `.tx.up.sql` extension

For example, create a file named `20230501120000_create_users_table.tx.up.sql` with the following contents:

sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE
);

This will create a transactional migration that creates a `users` table.

2. Register the migration using `Discover`

In your `main.go` file, import the migrations and use the `Discover` method to register the SQL-based migrations:

go
//go:embed *.sql
var sqlMigrations embed.FS

func init() {
    if err := Migrations.Discover(sqlMigrations); err != nil {
        panic(err)
    }
}

This will automatically discover and register all SQL-based migrations in the same directory.

3. Run the migrations

To run the migrations, you can use the `migrate` command:

bash
bun migrate

This will execute all pending migrations, including the transactional migration you created, within a transaction. If any part of the migration fails, the entire transaction will be rolled back.

4. Rollback the migration

To rollback the migration, you can use the `migrate rollback` command:

bash
bun migrate rollback

This will undo the last migration group, which includes the transactional migration you created.

By using the `.tx.up.sql` extension and the `Discover` method to register the migrations, Bun will automatically wrap the migration statements inside a transaction, ensuring data consistency and integrity. If any part of the migration fails, the entire transaction will be rolled back to maintain a stable database state.