pgroll convert guidance for SQL Data Migrations (up/down) with drizzle-kit

[SchahinRohani]

Environment Details:

• pgroll version: v0.10.0
• drizzle-kit version: v0.30.6

I'm trying to set up a minimal pgroll example for converting a basic drizzle-kit table migration and then altering a column. Below is my initial migration with drizzle-kit:

import { pgTable, serial, varchar } from 'drizzle-orm/pg-core';

export const users = pgTable(
  'users',
  {
    id: serial('id').primaryKey(),
    username: varchar('username', { length: 50 }).notNull().unique(),
  },
);

The generated drizzle-kit sql migration is:

CREATE TABLE "users" (
	"id" serial PRIMARY KEY NOT NULL,
	"username" varchar(10) NOT NULL,
	CONSTRAINT "users_username_unique" UNIQUE("username")
);

Then i use pgroll to convert the generated drizzle-kit migration:

pgroll convert /path/to/drizzle-migration.sql > pgroll.json
pgroll start pgroll.json

The generated output is:

{
  "name": "20250331041140",
  "operations": [
    {
      "create_table": {
        "columns": [
          {
            "name": "id",
            "pk": true,
            "type": "serial"
          },
          {
            "name": "username",
            "type": "varchar(50)"
          }
        ],
        "constraints": [
          {
            "columns": [
              "username"
            ],
            "name": "users_username_unique",
            "type": "unique"
          }
        ],
        "name": "users"
      }
    }
  ]
}

Next, I change the length of the username column:

import { pgTable, serial, varchar, timestamp } from 'drizzle-orm/pg-core';

export const users = pgTable(
  'users',
  {
    id: serial('id').primaryKey(),
    username: varchar('username', { length: 10 }).notNull().unique(),
  },
);

After running pgroll again:

pgroll convert /path/to/drizzle-migration.sql > pgroll-2.json

The generated output is:

{
  "name": "20250331031939",
  "operations": [
    {
      "alter_column": {
        "column": "username",
        "down": "TODO: Implement SQL data migration",
        "table": "users",
        "type": "varchar(10)",
        "up": "TODO: Implement SQL data migration"
      }
    }
  ]
}

This clearly indicates a need for manual data migration commands for both the up and down steps, but there’s no documentation on what these should be. This limitation is noted in the v0.10.0 release notes, but I'm looking for further guidance.

My Questions:

1. How should I structure the SQL data migration commands for the up and down operations in this scenario?
2. Are there any best practices or examples available that could help me achieve a smooth migration when altering column definitions?

[SchahinRohani]

I found the definitions of the up and down keys here.

So in this scenario i could just use the SQL Statement: "LEFT(username, 10)".

Thank you anyway!

[exekias]

Thank you for opening this and sharing your steps to resolution. This really helps us improving the experience!