Implement sql2pgroll package
#504
Assignees
Labels
sql2pgroll
Issues relating to the sql2pgroll package
Comments
andrew-farries
added a commit
that referenced
this issue
Dec 3, 2024
Add a `sql2pgroll` package to convert SQL to `pgroll` migrations.
Add a (hidden for now) `pgroll sql` command that uses the package to
convert SQL strings on the command line to `pgroll` migrations.
The `sql2pgroll` package is incomplete, with almost all SQL falling back
to conversion using raw SQL migrations. Only some `CREATE TABLE`
statements and the `ALTER TABLE ... ALTER COLUMN ... SET NOT NULL`
statement are currently handled.
```bash
$ pgroll sql "create table foo(a serial primary key, b text unique)"
```
```json
[
{
"create_table": {
"columns": [
{
"name": "a",
"pk": true,
"type": "serial"
},
{
"name": "b",
"nullable": true,
"type": "text",
"unique": true
}
],
"name": "foo"
}
}
]
```
Part of #504
andrew-farries
added a commit
that referenced
this issue
Dec 3, 2024
Convert SQL statements of the form:
```sql
ALTER TABLE foo ALTER COLUMN a [SET DATA] TYPE text
```
to the equivalent `pgroll` migration:
```json
[
{
"alter_column": {
"column": "a",
"down": "TODO: Implement SQL data migration",
"table": "foo",
"type": "text",
"up": "TODO: Implement SQL data migration"
}
}
]
```
Part of #504
andrew-farries
added a commit
that referenced
this issue
Dec 4, 2024
…l` operation (#507) Convert SQL DDL of the form: ```sql "ALTER TABLE foo ADD CONSTRAINT bar UNIQUE (a)" ``` To the equivalent `pgroll` operation: ```json [ { "create_constraint": { "type": "unique", "table": "foo", "name": "bar", "columns": ["a"], "up": { "a": "...", }, "down": { "a": "..." } } } ] ``` We need to be conservative when converting SQL statements to `pgroll` operations to ensure that information present in the SQL is not lost during the conversion. There are several options possible as part of `ADD CONSTRAINT ... UNIQUE` statements that aren't currently representable by the `OpCreateConstraint` operation, for example: ```sql ALTER TABLE foo ADD CONSTRAINT bar UNIQUE NULLS NOT DISTINCT (a) ALTER TABLE foo ADD CONSTRAINT bar UNIQUE (a) INCLUDE (b) ``` In these cases we must resort to converting to an `OpRawSQL`. Tests are added to cover these unrepresentable cases. Part of #504
andrew-farries
added a commit
that referenced
this issue
Dec 4, 2024
…presentable (#508) Ensure that SQL statements of the form: ```sql ALTER TABLE foo ALTER COLUMN a SET DATA TYPE text COLLATE "en_US" ALTER TABLE foo ALTER COLUMN a SET DATA TYPE text USING 'foo' ``` are converted to raw SQL operations instead of an `OpAlterColumn` operation. The change of collation is not currently representable by an `OpAlterColumn` operation and neither is the `USING` clause. Part of #504
andrew-farries
added a commit
that referenced
this issue
Dec 5, 2024
Convert SQL statements of the form:
```sql
ALTER TABLE foo RENAME COLUMN a TO b
ALTER TABLE foo RENAME a TO b
```
to the equivalent `OpAlterColumn` operation:
```json
[
{
"alter_column": {
"table": "foo",
"column": "a",
"name": "b"
}
}
]
```
Part of #504
This was referenced Dec 5, 2024
andrew-farries
added a commit
that referenced
this issue
Dec 5, 2024
* Rename the test variables to match their operation type. * Rename files to match the operation type. Part of #504
andrew-farries
added a commit
that referenced
this issue
Dec 5, 2024
Convert SQL statements of the form:
```sql
ALTER TABLE foo RENAME TO bar
```
to the corresponding `OpRenameTable` operation:
```json
[
{
"rename_table": {
"from": "foo",
"to": "bar"
}
}
]
```
Part of #504
andrew-farries
added a commit
that referenced
this issue
Dec 5, 2024
Convert SQL statements like:
```sql
ALTER TABLE foo RENAME CONSTRAINT bar TO baz
```
to an `OpRenameConstraint` operation:
```json
[
{
"rename_constraint": {
"table": "foo",
"from": "bar",
"to": "baz"
}
}
]
```
Part of #504
ryanslade
added a commit
that referenced
this issue
Dec 11, 2024
ryanslade
added a commit
that referenced
this issue
Dec 11, 2024
Converts SQL in the following forms to the equivalent pgroll operation: ```sql DROP INDEX foo DROP INDEX schema.foo DROP INDEX foo RESTRICT DROP INDEX CONCURRENTLY foo DROP INDEX IF EXISTS foo ``` The following forms are left as raw SQL operations since we do not support them in pgroll yet: ```sql DROP INDEX foo CASCADE ``` Part of #504
ryanslade
added a commit
that referenced
this issue
Dec 12, 2024
Converts `DROP TABLE` statements in these forms: ```sql DROP TABLE foo DROP TABLE foo RESTRICT DROP TABLE foo.bar DROP TABLE IF EXISTS foo ``` These forms fall back to raw SQL: ```sql DROP TABLE foo CASCADE ``` Part of #504
ryanslade
added a commit
that referenced
this issue
Dec 13, 2024
…eign keys (#531) Supports translating statements in the following forms: ```sql ALTER TABLE foo ADD CONSTRAINT fk_bar_cd FOREIGN KEY (a, b) REFERENCES bar (c, d) ALTER TABLE foo ADD CONSTRAINT fk_bar_cd FOREIGN KEY (a, b) REFERENCES bar (c, d) ON DELETE NO ACTION ALTER TABLE foo ADD CONSTRAINT fk_bar_cd FOREIGN KEY (a, b) REFERENCES bar (c, d) ON DELETE RESTRICT ALTER TABLE foo ADD CONSTRAINT fk_bar_cd FOREIGN KEY (a, b) REFERENCES bar (c, d) ON DELETE SET DEFAULT ALTER TABLE foo ADD CONSTRAINT fk_bar_cd FOREIGN KEY (a, b) REFERENCES bar (c, d) ON DELETE SET NULL ALTER TABLE foo ADD CONSTRAINT fk_bar_c FOREIGN KEY (a) REFERENCES bar (c) ALTER TABLE schema.foo ADD CONSTRAINT fk_bar_c FOREIGN KEY (a) REFERENCES schema.bar (c) ``` The following fall back to raw SQL: ```sql ALTER TABLE foo ADD CONSTRAINT fk_bar_cd FOREIGN KEY (a, b) REFERENCES bar (c, d) ON UPDATE RESTRICT; ALTER TABLE foo ADD CONSTRAINT fk_bar_cd FOREIGN KEY (a, b) REFERENCES bar (c, d) ON UPDATE CASCADE; ALTER TABLE foo ADD CONSTRAINT fk_bar_cd FOREIGN KEY (a, b) REFERENCES bar (c, d) ON UPDATE SET NULL; ALTER TABLE foo ADD CONSTRAINT fk_bar_cd FOREIGN KEY (a, b) REFERENCES bar (c, d) ON UPDATE SET DEFAULT; ALTER TABLE foo ADD CONSTRAINT fk_bar_cd FOREIGN KEY (a, b) REFERENCES bar (c, d) MATCH FULL; ``` Part of #504
andrew-farries
added a commit
that referenced
this issue
Dec 16, 2024
Switch the `pg_query_go` dependency from https://github.com/pganalyze/pg_query_go to the fork at https://github.com/xataio/pg_query_go The forked version adds a new `DeparseExpr` function for deparsing expression nodes (see xataio/pg_query_go#1). This will expand the range of SQL DDL statements that `sql2pgroll` is able to convert to `pgroll` migrations. Part of #504
andrew-farries
added a commit
that referenced
this issue
Dec 16, 2024
Deparse column `DEFAULT` expressions using `pg_query_go.DeparseExpr` to allow conversion of arbitrary expressions in column `DEFAULT`s to `pgroll` migrations. Replace manual deparsing of literal nodes with use of `DeparseExpr` to allow deparsing of any column `DEFAULT` expression. Update test cases: * `ALTER TABLE foo ALTER COLUMN bar SET DEFAULT now()` is now representable as an `OpAlterColumn`; no need to fall back to raw SQL anymore. * Add a new testcase: `ALTER TABLE foo ALTER COLUMN bar SET DEFAULT (first_name || ' ' || last_name)` to demonstrate conversion of a column `DEFAULT` using a more complex expression. Part of #504
ryanslade
added a commit
that referenced
this issue
Dec 16, 2024
…ns (#536) Convert `DROP CONSTRAINT SQL` into an `OpDropMultiColumnConstraint`. Because we are unable to infer the columns involved, placeholder migrations are used. SQL statements like the following are supported: ```sql ALTER TABLE foo DROP CONSTRAINT constraint_foo ALTER TABLE foo DROP CONSTRAINT IF EXISTS constraint_foo ALTER TABLE foo DROP CONSTRAINT IF EXISTS constraint_foo RESTRICT ``` `CASCADE` is currently not supported and will fall back to raw SQL Part of #504
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Support for converting SQL DDL statements to
pgrollmigrations is partially complete. This issue tracks progress towards completion.Support matrix
create_tablerename_tabledrop_tableadd_columndrop_columnalter_column(rename column)alter_column(change type)alter_column(set not null)alter_column(drop not null)alter_column(set_unique)alter_column(set comment)alter_column(set default)alter_column(drop default)create_indexdrop_indexrename_constraintdrop_constraintset_replica_identitydrop_multicolumn_constraintcreate_unique_constraintcreate_check_constraintcreate_foreign_key_constraintNotes
[1]: remaining:
- multi-column primary keys
- foreign keys (single and multi-column)
- check constraints (single and multi-column)
- unique constraints (multi-column)
- default column values
There are also many `CREATE TABLE` options that are not representable by `OpCreateTable`; we need to ensure we fall back to raw SQL if any such options are present.[2] :
drop_constraintis deprecated,sql2pgrollshould generate onlydrop_multicolumn_constraintoperations.[3]: we probably don't want to support this; the default behaviour of generating a raw SQL operation is sufficient for this
[4]: the operation is deprecated, falling back to raw sql for the `SET REPLICA IDENTITY` syntax is acceptable.
[5]: Conversion of `ALTER TABLE ... ADD CONSTRAINT ... CHECK` statements currently uses placeholders for the names of the columns covered by the constraint and for keys (column names) in the `up`/`down` data migration map. We should investigate whether it is possible to walk the AST for the `CHECK` expression (possibly using reflection) to discover the names of the covered columns instead.
[6]: Dropping constraints are tricky because they may affect more than a single column. When creating a pgroll operation to do this (`OpDropMultiColumnConstraint`) we need to specify all the affected columns so that we can create the required triggers. A simple `DROP CONSTRAINT` expression does not contain enough context to allow us to infer the affected columns.
The text was updated successfully, but these errors were encountered: