Add support for PostgreSQL-style ALTER COLUMN syntax

Niols · Niols · commit d75eac197132 · 2026-05-01T20:11:40.000+02:00
PostgreSQL (and standard SQL) uses `ALTER COLUMN` instead of MySQL's `CHANGE COLUMN` / `MODIFY COLUMN` for modifying existing columns. This adds parsing and schema evaluation for the five main sub-commands:

- `ALTER COLUMN col TYPE newtype`
- `ALTER COLUMN col SET NOT NULL`
- `ALTER COLUMN col DROP NOT NULL`
- `ALTER COLUMN col SET DEFAULT expr`
- `ALTER COLUMN col DROP DEFAULT`

These can be combined with other actions in a single `ALTER TABLE` statement, eg.:

```
ALTER TABLE t
  DROP COLUMN old_col,
  ALTER COLUMN col TYPE SMALLINT,
  ALTER COLUMN col SET NOT NULL;
```

Migration generation (inverse actions + SQL fragments) is also supported for all five variants.
diff --git a/lib/dialect.ml b/lib/dialect.ml
@@ -394,7 +394,8 @@ and analyze_alter_action acc actions k = match actions with
     | `Default_or_convert_to (_, collation) -> 
         let acc = check_collation_opt collation @ acc in
         analyze_alter_action acc rest k
-    | `Drop _ | `RenameTable _ | `RenameColumn _ | `RenameIndex _ | `AddIndex _ | `DropIndex _ | `AddPrimaryKey _ | `DropPrimaryKey | `AddConstraint _ | `DropConstraint _ | `None ->
+    | `Drop _ | `RenameTable _ | `RenameColumn _ | `RenameIndex _ | `AddIndex _ | `DropIndex _ | `AddPrimaryKey _ | `DropPrimaryKey | `AddConstraint _ | `DropConstraint _ | `None
+    | `AlterColumnType _ | `AlterColumnSetNotNull _ | `AlterColumnDropNotNull _ | `AlterColumnSetDefault _ | `AlterColumnDropDefault _ ->
 
         analyze_alter_action acc rest k
 
diff --git a/lib/sql.ml b/lib/sql.ml
@@ -817,6 +817,11 @@ type alter_action = [
     | `AddConstraint of string option
     | `DropConstraint of string
     | `Default_or_convert_to of (charset_name option * string located option)
+    | `AlterColumnType of string * Source_type.kind collated located
+    | `AlterColumnSetNotNull of string
+    | `AlterColumnDropNotNull of string
+    | `AlterColumnSetDefault of string * expr located
+    | `AlterColumnDropDefault of string
     | `None ] [@@deriving show {with_path=false}]
 
 type stmt =
diff --git a/lib/sql_lexer.mll b/lib/sql_lexer.mll
@@ -194,6 +194,7 @@ let keywords =
    "algorithm", ALGORITHM;
    "copy", COPY;
    "recursive", RECURSIVE;
+   "type", TYPE;
   ] in (* more *)
   k := !k @ List.map (fun s -> s, INTERVAL_UNIT s) [ "microsecond"; "second"; "minute"; "hour"; "day"; "week"; "month"; "quarter"; "year" ];
   let all token l = k := !k @ List.map (fun x -> x,token) l in
diff --git a/lib/sql_parser.mly b/lib/sql_parser.mly
@@ -36,7 +36,7 @@
        CONCAT_OP LEFT RIGHT FULL INNER OUTER NATURAL CROSS REPLACE IN GROUP HAVING
        UNIQUE PRIMARY KEY FOREIGN AUTOINCREMENT ON CONFLICT DO NOTHING TEMPORARY IF EXISTS
        PRECISION SIGNED UNSIGNED ZEROFILL VARYING CHARSET NATIONAL ASCII UNICODE COLLATE BINARY CHARACTER
-       DATETIME_FUNC DATE TIME TIMESTAMP ALTER RENAME ADD COLUMN CASCADE RESTRICT DROP
+       DATETIME_FUNC DATE TIME TIMESTAMP ALTER RENAME ADD COLUMN CASCADE RESTRICT DROP TYPE
        GLOBAL LOCAL REFERENCES CHECK CONSTRAINT IGNORED AFTER INDEX FULLTEXT SPATIAL FIRST
        CASE WHEN THEN ELSE END CHANGE MODIFY DELAYED ENUM FOR SHARE MODE LOCK
        OF WITH NOWAIT ACTION NO IS INTERVAL SUBSTRING DIV MOD CONVERT LAG LEAD OVER
@@ -366,6 +366,11 @@ alter_action: ADD COLUMN? col=maybe_parenth(column_def) pos=alter_pos { `Add (co
             | DROP CHECK name=IDENT { `DropConstraint name }
             | CHANGE COLUMN? old_name=IDENT column=column_def pos=alter_pos { `Change (old_name,column,pos) }
             | MODIFY COLUMN? column=column_def pos=alter_pos { `Change (column.Alter_action_attr.name,column,pos) }
+            | ALTER COLUMN? col=IDENT TYPE t=located_sql_type { `AlterColumnType (col, t) }
+            | ALTER COLUMN? col=IDENT SET NOT NULL { `AlterColumnSetNotNull col }
+            | ALTER COLUMN? col=IDENT DROP NOT NULL { `AlterColumnDropNotNull col }
+            | ALTER COLUMN? col=IDENT SET DEFAULT e=default_value { `AlterColumnSetDefault (col, make_located ~value:e ~pos:($startofs, $endofs)) }
+            | ALTER COLUMN? col=IDENT DROP DEFAULT { `AlterColumnDropDefault col }
             | SET IDENT IDENT { `None }
             | ALGORITHM EQUAL algorithm { `None }
             | LOCK EQUAL lock { `None }
diff --git a/lib/syntax.ml b/lib/syntax.ml
@@ -1466,6 +1466,16 @@ let rec eval (stmt:Sql.stmt) =
         Tables.drop_primary_key name
       | `AddPrimaryKey cols ->
         Tables.add_primary_key name ~cols
+      | `AlterColumnType (col, new_type) ->
+        Tables.alter_column_type name ~col_name:col ~new_kind:new_type.value.collated
+      | `AlterColumnSetNotNull col ->
+        Tables.alter_column_set_not_null name ~col_name:col
+      | `AlterColumnDropNotNull col ->
+        Tables.alter_column_drop_not_null name ~col_name:col
+      | `AlterColumnSetDefault (col, _) ->
+        Tables.alter_column_set_default name ~col_name:col
+      | `AlterColumnDropDefault col ->
+        Tables.alter_column_drop_default name ~col_name:col
       | `RenameIndex _  | `AddIndex _ | `DropIndex _ | `AddConstraint _ | `DropConstraint _ -> () (* indices are not tracked yet *)
       | `Default_or_convert_to (cs, collation) ->
         let old = Tables.get_charset name in
diff --git a/lib/tables.ml b/lib/tables.ml
@@ -124,6 +124,52 @@ let add_primary_key name ~cols:col_names =
       { c with attr = { c.attr with extra = Sql.Constraints.add pk c.attr.Sql.extra } }
     else c))
 
+let alter_column_type name ~col_name ~new_kind =
+  alter name (fun cols ->
+    List.map (fun c ->
+      if c.attr.Sql.name = col_name then
+        let new_t = Sql.Alter_action_attr.kind_to_type_kind new_kind in
+        { attr = { c.attr with domain = { c.attr.domain with Sql.Type.t = new_t } };
+          source_kind = Some new_kind }
+      else c
+    ) cols)
+
+let alter_column_set_not_null name ~col_name =
+  alter name (fun cols ->
+    List.map (fun c ->
+      if c.attr.Sql.name = col_name then
+        { c with attr = { c.attr with
+            domain = { c.attr.domain with Sql.Type.nullability = Strict };
+            extra = Sql.Constraints.add NotNull c.attr.extra } }
+      else c
+    ) cols)
+
+let alter_column_drop_not_null name ~col_name =
+  alter name (fun cols ->
+    List.map (fun c ->
+      if c.attr.Sql.name = col_name then
+        { c with attr = { c.attr with
+            domain = { c.attr.domain with Sql.Type.nullability = Nullable };
+            extra = Sql.Constraints.remove NotNull c.attr.extra } }
+      else c
+    ) cols)
+
+let alter_column_set_default name ~col_name =
+  alter name (fun cols ->
+    List.map (fun c ->
+      if c.attr.Sql.name = col_name then
+        { c with attr = { c.attr with extra = Sql.Constraints.add WithDefault c.attr.extra } }
+      else c
+    ) cols)
+
+let alter_column_drop_default name ~col_name =
+  alter name (fun cols ->
+    List.map (fun c ->
+      if c.attr.Sql.name = col_name then
+        { c with attr = { c.attr with extra = Sql.Constraints.remove WithDefault c.attr.extra } }
+      else c
+    ) cols)
+
 let print ch tables = let out = IO.output_channel ch in List.iter (Sql.print_table out) tables; IO.flush out
 let print_all () = print stdout (List.map (fun (n, cols) -> (n, columns_to_schema cols)) !store)
 let print1 name = print stdout [get @@ Sql.make_table_name name]
diff --git a/src/gen_migrations.ml b/src/gen_migrations.ml
@@ -111,6 +111,28 @@ let inverse_action table_name (columns : Tables.column list) (action : Sql.alter
       | None -> None, None
     in
     `Default_or_convert_to (cs, collation)
+  | `AlterColumnType (col_name, _) ->
+    let entry = find_column columns col_name in
+    let old_type = Sql.Alter_action_attr.from_attr entry.attr |> enrich_with_source_kind entry.source_kind in
+    let old_kind = match old_type.Sql.Alter_action_attr.kind with
+      | Some k -> k
+      | None -> Sql.make_located ~pos:(0,0) ~value:(Sql.make_collated ~collated:(Sql.Source_type.Infer entry.attr.domain.Sql.Type.t) ())
+    in
+    `AlterColumnType (col_name, old_kind)
+  | `AlterColumnSetNotNull col_name -> `AlterColumnDropNotNull col_name
+  | `AlterColumnDropNotNull col_name -> `AlterColumnSetNotNull col_name
+  | `AlterColumnSetDefault (col_name, _) -> `AlterColumnDropDefault col_name
+  | `AlterColumnDropDefault col_name ->
+    if Sql.Constraints.mem WithDefault (find_column columns col_name).attr.extra then
+      let entry = find_column columns col_name in
+      let col = Sql.Alter_action_attr.from_attr entry.attr in
+      let default_expr = List.find_map (fun (c : Sql.Alter_action_attr.constraint_ Sql.located) ->
+        match c.value with Sql.Alter_action_attr.Default e -> Some e | _ -> None
+      ) col.extra in
+      match default_expr with
+      | Some e -> `AlterColumnSetDefault (col_name, e)
+      | None -> `None
+    else `None
   | `None -> `None
 
 let action_to_sql_fragment (action : Sql.alter_action) =
@@ -156,6 +178,16 @@ let action_to_sql_fragment (action : Sql.alter_action) =
     | [] -> "(* unsupported: unknown previous charset *)"
     | _ -> String.concat " " parts
     end
+  | `AlterColumnType (col_name, new_type) ->
+    sprintf "ALTER COLUMN %s TYPE %s" (quote_id col_name) (source_type_kind_to_sql new_type.Sql.value.collated)
+  | `AlterColumnSetNotNull col_name ->
+    sprintf "ALTER COLUMN %s SET NOT NULL" (quote_id col_name)
+  | `AlterColumnDropNotNull col_name ->
+    sprintf "ALTER COLUMN %s DROP NOT NULL" (quote_id col_name)
+  | `AlterColumnSetDefault (col_name, _) ->
+    sprintf "ALTER COLUMN %s SET DEFAULT (* unknown *)" (quote_id col_name)
+  | `AlterColumnDropDefault col_name ->
+    sprintf "ALTER COLUMN %s DROP DEFAULT" (quote_id col_name)
   | `None -> "(* unsupported: index/constraint operation *)"
 
 let alter_to_sql table_name actions =
diff --git a/test/alter.sql b/test/alter.sql
@@ -8,3 +8,19 @@ CREATE INDEX `foo_unique` ON `foo` (`col1`, `col2`, `col3`);
 ALTER TABLE `foo`
 DROP INDEX `foo_unique`,
 ADD UNIQUE `foo_unique` (`col1`, `col3`);
+
+CREATE TABLE "bar" (
+  "id" INTEGER NOT NULL,
+  "role" SMALLINT NOT NULL,
+  "role_new" INTEGER NOT NULL,
+  "omniscience" INTEGER NOT NULL
+);
+
+ALTER TABLE "bar"
+  DROP COLUMN "role",
+  ALTER COLUMN "role_new" TYPE SMALLINT,
+  ALTER COLUMN "role_new" SET NOT NULL,
+  ALTER COLUMN "omniscience" TYPE BOOLEAN,
+  ALTER COLUMN "omniscience" SET NOT NULL;
+
+ALTER TABLE "bar" RENAME COLUMN "role_new" TO "role";
diff --git a/test/out/alter.xml b/test/out/alter.xml
@@ -13,6 +13,25 @@
   <in/>
   <out/>
  </stmt>
+ <stmt name="create_bar" sql="CREATE TABLE &quot;bar&quot; (&#x0A;  &quot;id&quot; INTEGER NOT NULL,&#x0A;  &quot;role&quot; SMALLINT NOT NULL,&#x0A;  &quot;role_new&quot; INTEGER NOT NULL,&#x0A;  &quot;omniscience&quot; INTEGER NOT NULL&#x0A;)" category="DDL" kind="create" target="bar" cardinality="0">
+  <in/>
+  <out/>
+ </stmt>
+ <stmt name="alter_bar_4" sql="ALTER TABLE &quot;bar&quot;&#x0A;  DROP COLUMN &quot;role&quot;,&#x0A;  ALTER COLUMN &quot;role_new&quot; TYPE SMALLINT,&#x0A;  ALTER COLUMN &quot;role_new&quot; SET NOT NULL,&#x0A;  ALTER COLUMN &quot;omniscience&quot; TYPE BOOLEAN,&#x0A;  ALTER COLUMN &quot;omniscience&quot; SET NOT NULL" category="DDL" kind="alter" target="bar" cardinality="0">
+  <in/>
+  <out/>
+ </stmt>
+ <stmt name="alter_bar_5" sql="ALTER TABLE &quot;bar&quot; RENAME COLUMN &quot;role_new&quot; TO &quot;role&quot;" category="DDL" kind="alter" target="bar" cardinality="0">
+  <in/>
+  <out/>
+ </stmt>
+ <table name="bar">
+  <schema>
+   <value name="id" type="Int"/>
+   <value name="role" type="Int"/>
+   <value name="omniscience" type="Bool"/>
+  </schema>
+ </table>
  <table name="foo">
   <schema>
    <value name="col1" type="Int"/>
