search

erikbra / grate

grate - the SQL scripts migration runner
MIT License
209 stars 40 forks source link

[FEATURE] : Auto Create ALTER TABLE scripts when an existing table adds column to existing SQL Script. #575

Closed bheemvennapureddy closed 3 months ago

bheemvennapureddy commented 3 months ago

Is your feature request related to a problem? Please describe.

No. But coming from SQL Server world of Dacpac or deploy script creation where we maintain a Single TABLE file for all table where we just add a new column or index and dotnet used to take care of creating a diff script which only contains the new changes.

Describe the solution you'd like Like

Screenshot 2024-08-18 at 11 19 54 PM

Here when i add CanAccessSalesDashboard column grate should automatically create ALTER TABLE script appneded to single deploy script which we can run on a table.

Additional context Major Advantage with this approach is Single Schema file for their respective tables and Single Deploy script which can be generated for deploy.

Happy to discard the idea if this feature is too much of a ask here.

PagesplitPeter commented 3 months ago

My thoughts on this is that Grate is a tool to make sure we can deploy the sql scripts we make in a way that we can replicate on a new platform and perform the same way every time.

Grate is not a tool to figure out a way to change your tables/database that they leave up to you and you need to keep the changes in different files. This might be confusing in the beginning but looking for a change in the database its a lot easier when you have all the changes in different files then if you have you just have what the database looks like now in the repository.

There is a tool flyway that you can use that will let you develop in a state based manner like you do with dacpac but it will then create migration based scripts. The tool exists both as opensource and as a paid version from RedGate. I can't remember if you get the create migration script functionality with the opensource version.

The biggest problem I see with having tools decide how you should migrate a database/table when you make changes to the schema is that it might break things regarding the data depending on how they do it, so you might end up with dataloss.

We had this problem with dacpac that we had to do a lot of manualy work anyway to be able to deploy the dacpac to keep the data safe. This was the reason we moved to Grate. This way when we have do changes to the schema and it will impact the data in the database/table we have to handle it in the change script.

So even if I like the idea I think its out of the scope of Grate in is current form.

bheemvennapureddy commented 3 months ago

Thanks for the insights closing this.we use sqlpackage to deploy dacpac which lets you stop the data loss with /p:BlockOnPossibleDataLoss=false.