xataio · kvch · May 8, 2025 · May 7, 2025
diff --git a/docs/config.json b/docs/config.json
@@ -32,10 +32,15 @@
       "noLink": true,
       "items": [
         {
-          "title": "Integrate `pgroll` into your project",
+          "title": "Integrate pgroll into your project",
           "href": "/guides/clientapps",
           "file": "docs/guides/clientapps.mdx"
         },
+        {
+          "title": "Use pgroll with ORMs",
+          "href": "/guides/orms",
+          "file": "docs/guides/orms.mdx"
+        },
         {
           "title": "Writing up and down migrations",
           "href": "/guides/updown",

diff --git a/docs/guides/orms.mdx b/docs/guides/orms.mdx
@@ -0,0 +1,303 @@
+# Use pgroll with ORMs
+
+    ORMs are popular tools to manage data schema from your application's code. They can translate objects from your code into database objects. Most ORMs can generate SQL migrations scripts. `pgroll` takes these generated statments and translates them into `pgroll` migrations. The generated migration can be saved to a file and you can run the `pgroll` migration using the `start` command as usual.
+
+## How to convert SQL migration scripts
+
+`pgroll` can translate SQL migration scripts generated by ORMs into pgroll migrations using the `convert` subcommand. It can read SQL statements from `stdin` or from a specified file. It has one flag `--name` to configure the name of the migration. If the flag is unset, the name is set to the current timestamp.
+
+```sh
+$ pgroll convert --help
+Convert SQL statements to a pgroll migration. The command can read SQL statements from stdin or a file
+
+Usage:
+  pgroll convert <path to file with migrations> [flags]
+Flags:
+  -h, --help          help for convert
+  -n, --name string   Name of the migration (default "{current_timestamp}")
+```
+
+## Examples
+
+You can use `pgroll` with any ORM that can generate raw SQL statements for its migrations. Let's look at a few examples.
+
+### Alembic
+
+[Alembic](https://alembic.sqlalchemy.org/en/latest/) is a database migration tool used in SQLAlchemy projects. You can generate SQL statements from its migrations in [its offline mode](https://alembic.sqlalchemy.org/en/latest/offline.html). If you add
+`a --sql` flag to the `upgrade` command of `alembic` it prints the SQL statements to stdout. You can pipe this output into `pgroll convert`:
+
+```sh
+$ alembic update {revision} --sql | pgroll convert --name {revision}
+{
+  "name": "{revision}",
+  "operations": [
+    {
+      "create_table": {
+        "name": "employees",
+        "colunms:" [
+          {
+            "name": "name",
+            "type": "varchar(100)"
+          },
+          {
+            "name": "joined",
+            "type": "timestamp with time zone"
+          },
+          {
+            "name": "email",
+            "type": "varchar(254)"
+          }
+        ]
+      }
+    }
+  ]
+}
+```
+
+### Django
+
+Django is the go-to tool for Python web development. It can connect to several databases, including PostgreSQL. If your web application uses PostgreSQL storage, you can leverage the `convert` subcommand.
+
+After you've defined y
8000
our migration in Python, you can use `manage.py` to extract the SQL script from Django. The [subcommand `sqlmigrate`](https://docs.djangoproject.com/en/5.1/ref/django-admin/#django-admin-sqlmigrate) prints
+the SQL statements to stdout.
+
+Create a new model for an `Employee` with a following simplified model:
+
+```python
+class Employee():
+    name = models.CharField(max_length=100)
+    joined = models.DateTimeField()
+    email = models.EmailField()
+```
+
+Then run `sqlmigrate` to generate the SQL statements and pipe the output into `pgroll convert`:
+
+```sh
+manage.py sqlmigrate my_app 0000 | pgroll convert -name 0000_init
+{
+  "name": "0000_init",
+  "operations": [
+    {
+      "create_table": {
+        "name": "employees",
+        "colunms:" [
+          {
+            "name": "name",
+            "type": "varchar(100)"
+          },
+          {
+            "name": "joined",
+            "type": "timestamp with time zone"
+          },
+          {
+            "name": "email",
+            "type": "varchar(254)"
+          }
+        ]
+      }
+    }
+  ]
+}
+```
+
+### Drizzle
+
+Drizzle is a popular ORM for Typescript projects. You can extract SQL statements using its [`generate` command](https://orm.drizzle.team/docs/drizzle-kit-generate).
+
+The following example schema in Drizzle can be translated using `pgroll`:
+
+```ts
+import { pgTable, timestamp, varchar } from 'drizzle-orm/pg-core';
+
+export const employees = pgTable('employees', {
+  name: varchar({ length: 100 }),
+  joined: timestamp({ withTimezone: true }),
+  email: varchar({ length: 254 })
+});
+```
+
+Run `pgroll convert` to get the appropriate pgroll migrations.
+
+```sh
+drizzle-kit generate --dialect postgresql --schema=./src/schema.ts --name=init
+pgroll convert 0000_init.sql --name 0000_init
+{
+  "name": "0000_init",
+  "operations": [
+      "create_table": {
+        "name": "employees",
+        "colunms:" [
+          {
+            "name": "name",
+            "type": "varchar(100)"
+          },
+          {
+            "name": "joined",
+            "type": "timestamp with time zone"
+          },
+          {
+            "name": "email",
+            "type": "varchar(254)"
+          }
+        ]
+      }
+    ]
+  }
+}
+```
+
+## Limitations
+
+The functionality still has some limitations. The generated pgroll migrations must be edited to provide `up` and `down` data migrations manually. You can find more details about writing these migrations in [this guide](/updown).
+
+Furthermore, the SQL statements are not aggregated into single pgroll operations. Some ORMs add unique constraints in a different statement when they are creating a table with a unique column. This leads to more pgroll operations than necessary. You can resolve this manually by removing the unique constraint operation from the pgroll migration, and add it to the list of `constraints` of `create_table`.
+
+```json
+{
+  "create_table": {
+    "name": "employees",
+    "columns": [
+      {
+        "name": "email",
+        "varchar(254)"
+      }
+    ]
+  }
+},
+{
+  "create_constraint": {
+    "name": "my_unique_email",
+    "type": "unique",
+    "columns": ["email"]
+    "up": {
+      "email": "TODO"
+    }
+  }
+}
+
+```
+
+can be simplified to
+
+```json
+{
+  "create_table": {
+    "name": "employees",
+    "columns": [
+      {
+        "name": "email",
+        "varchar(254)"
+      }
+    ],
+    "constraints": [
+      {
+        "name": "my_unique_email",
+        "type": "unique",
+        "columns": ["email"]
+      }
+    ]
+  }
+}
+```
+
+Also, the same applies to some cases when the ORM does the backfilling for new columns. For example, when you add a new column with a default value, an ORM produces these SQL statements:
+
+```sql
+ALTER TABLE "adults" ADD COLUMN "age" smallint DEFAULT 18 NULL CHECK ("age" >= 18);
+ALTER TABLE "adults" ALTER COLUMN "level" DROP DEFAULT;
+
+UPDATE adults SET age = 18;
+
+ALTER TABLE adults ALTER COLUMN age SET NOT NULL;
+
+UPDATE "adults" SET "age" = 18 WHERE "age" IS NULL;
+SET CONSTRAINTS ALL IMMEDIATE;
+
+ALTER TABLE "adults" ALTER COLUMN "age" SET NOT NULL;
+```
+
+Resulting in the following pgroll migration using the `convert` subcommand:
+
+```json
+{
+  "add_column": {
+    "column": {
+      "check": {
+        "constraint": "age >= 18",
+        "name": "age_check"
+      },
+      "default": "18",
+      "name": "age",
+      "nullable": true,
+      "type": "smallint"
+    },
+    "table": "adults",
+    "up": "TODO: Implement SQL data migration"
+  }
+},
+{
+  "alter_column": {
+    "column": "age",
+    "default": null,
+    "table": "adults",
+    "down": "TODO: Implement SQL data migration",
+    "up": "TODO: Implement SQL data migration"
+  }
+},
+{
+  "sql": {
+    "up": "update adults set age = 18"
+  }
+},
+{
+  "alter_column": {
+    "column": "age",
+    "nullable": false,
+    "table": "adults",
+    "up": "TODO: Implement SQL data migration"
+    "down": "TODO: Implement SQL data migration",
+  }
+},
+{
+  "sql": {
+    "up": "UPDATE \"adults\" SET \"age\" = 18 WHERE \"age\" IS NULL"
+  }
+},
+{
+  "sql": {
+    "up": "SET CONSTRAINTS ALL IMMEDIATE"
+  }
+},
+{
+  "alter_column": {
+    "column": "age",
+    "nullable": false,
+    "table": "adults",
+    "up": "TODO: Implement SQL data migration"
+    "down": "TODO: Implement SQL data migration",
+  }
+}
+```
+
+This can be written as a single pgroll migration:
+
+```json
+{
+  "add_column": {
+    "column": {
+      "check": {
+        "constraint": "age \u003e= 0",
+        "name": "age_check"
+      },
+      "default": "18",
+      "name": "age",
+      "nullable": true,
+      "type": "smallint"
+    },
+    "table": "adults",
+    "up": "18"
+    <
4A53
span class='blob-code-inner blob-code-marker ' data-code-marker="+">  }
+},
+```
+
+Also, SQL migration scripts usually start with `BEGIN` and `COMMIT` because the ORM runs the DDLs in a single transaction. These statements show up in the list of operations in the generated migrations. These operations are safe to be deleted from the list.