Migration tool should support other constraints and altering types of columns

[mirolimsgd]

Description;

Migration tool only supports UNIQUE constraints (other constraints are not yet supported). Keep this in mind when mutating schemas.
Changes in configuration such as altering varchar(255) to varchar(500) or changing datetime from null to now() are not handled.
Which is error prone and may increase debugging time.

Alternative approach switch to third party tools like Flyway/Liquibase

Limitations of the Custom Tool

• Incomplete Feature Support: Feature set to properly detect and support (generate migration) for valid SQL definitions is strongly limited for example many types of constraints not supported, altering column type definitions not supported etc.
• Fragility & Bugs: Recent work highlighted the complexity and potential for errors in correctly parsing SQL, building schema state, comparing states, and generating valid DDL across different scenarios.
• High Maintenance Burden: Supporting the full range of PostgreSQL DDL features (indexes, functions, triggers, types, partitioning, etc.) and ensuring correctness requires significant ongoing development and maintenance effort for this custom tool.
• Error Prone: The current limitations mean automatically generated migrations may be incomplete or incorrect, increasing the risk of schema inconsistencies and requiring developers to manually verify, edit, or create migrations anyway, partially defeating the tool's purpose.

These limitations make schema evolution error-prone and increase the time spent debugging the tool itself rather than building application features.

Proposed Alternative: Flyway / Liquibase

Why switch? (Benefits)

• Maturity & Robustness: These tools are battle-tested across countless projects and databases. They reliably handle a vast array of schema changes.
• Comprehensive Feature Support: They understand complex DDL, constraints, indexes, data migrations, procedure/function changes, etc., often supporting database-specific syntax.
• Reduced Maintenance: We shift the burden of parsing SQL, managing schema state, and handling database specifics to the tool's maintainers/community. Our team focuses on defining the changes, not building the tool.
• Strong Community & Documentation: Extensive resources, tutorials, and community support are available.
• Standard Workflow: They promote standard migration practices (versioned SQL scripts for Flyway, declarative changesets for Liquibase) familiar to many developers.
• Database Support: Designed to work well with PostgreSQL and other databases.

[mirolimsgd]

@diyor28 wdyt?

[diyor28]

@mirolimsgd I like it, however will you be able to achieve the same programmatic usage with these tools that make migrate collect currently provides?
A very important case, you have sql schema files in your working directory, as well as from other modules that you import as libraries (not wd). Does liquibase allow this sort of usage?

[mirolimsgd]

@mirolimsgd I like it, however will you be able to achieve the same programmatic usage with these tools that make migrate collect currently provides? A very important case, you have sql schema files in your working directory, as well as from other modules that you import as libraries (not wd). Does liquibase allow this sort of usage?

The suggestion is to shift from Auto-Generation to Explicit Migrations. Instead of relying on the tool to figure out the necessary DDL by comparing states, developers manually create the required DDL in versioned migration files when they make a schema change.

Flyway: Create V{next_version}__{description}.sql containing the specific ALTER, CREATE, DROP statements needed for that change.
Liquibase: Add a new <changeSet> to a changelog file (.xml, .yaml, .json, or formatted .sql) containing declarative tags (<createTable>, <addColumn>) or raw SQL.

Locally, developers run flyway migrate or liquibase update to apply changes.
In CI/CD or via Makefiles, you execute the same commands. The programmatic part becomes triggering the execution of these tools via their CLI, rather than running a custom Go generator. make migrate apply (running flyway migrate or liquibase update) would replace make migrate collect.

Why to consider this flow:

• Reliability & Predictability: Manually written, version-controlled migrations are far less brittle than complex schema diffing/generation logic. You know exactly what SQL will run. This directly addresses the fragility and debugging time mentioned in the issue draft.
• Full SQL Support: This approach handles any database change (constraints, types, defaults, indexes, functions, stored procedures, data changes) because the developer writes the exact SQL or uses the tool's specific abstractions (Liquibase). The tool only needs to execute it reliably.
• Clarity and Review: Explicit migration scripts are much easier for the team (and potentially DBAs) to review and understand compared to verifying the output of a custom generation tool.
• Reduced Maintenance: The significant effort of maintaining and extending the schema/collector to support more PostgreSQL features and fix bugs is eliminated. The team maintains migration scripts, not the migration generator.
• Industry Standard: This workflow aligns with common industry practices, making onboarding easier.

[bektosh-iota]

I support this.
I think we'll save more time with the manual approach