Detect up migrations in SQL migration files

[kvch]

During testing pgroll with ORMs, I read SQL statements from migrations files. sql2pgroll package can transform a single SQL operation into a pgroll migration (if implemented). So each SQL statement in a migration file was translated individually. However, there are migration file where multiple statements can be translated into a single pgroll migrations.

The example below adds a new column to an existing table, and sets a backfill value. At the moment pgroll translates this into an add_column operation, and expects the user to edit the up attribute of the migration. Then the UPDATE statement is kept as a raw SQL migration.

-- AlterTable
ALTER TABLE "table1" ADD COLUMN "new_column" TEXT;

-- Update all rows
UPDATE "table1" SET "new_column" = gen_random_uuid() WHERE "new_column" IS NULL;

At the moment this is translated into the following migrations:

[
   {
     "add_column": {
       "column": {
         "name": "new_column",
         "nullable": true,
         "type": "text"
       },
       "table": "table1",
       "up": "TODO: Implement SQL data migration"
     }
   }
 ]

and

[
  {
     "sql": {
       "up": "UPDATE \"table1\" SET \"new_column\" = gen_random_uuid() WHERE \"new_column\" IS NULL"
     }
   }
]

This behaviour can be improved. pgroll could read the whole migration file, and find the appropriate up migration in the file. This could reduce the friction of converting ORM migrations to pgroll migrations.

Improved output where up migration is included in the operation:

[
   {
     "add_column": {
       "column": {
         "name": "new_column",
         "nullable": true,
         "type": "text"
       },
       "table": "table1",
       "up": "gen_random_uuid()"
     }
   }
 ]