Lately, I’ve had to write little migration scripts while developing new features for LogueWorks — a transcription, translation and multimedia processing service I also manage on a daily basis. Along the way, I’ve learned a number of tips and tricks for using HeidiSQL, which has reduced the amount of time I’ve had to spend writing database queries and keeping local and test environment databases in sync.
A recently developed feature required adding a column to an already existing table, populating it with default values and then creating a migration script to repeat the table alteration when my changes were committed and pushed to our test environment. HeidiSQL made doing all of this really simple.
To add a new column to an already existing table, select the table you want to alter, click on the Table tab and then right-click in the Columns section and select Add column or use the shortcut key Ctrl+Ins.
Note, the new column will be placed after whatever column was previously selected. But HeidiSQL makes it easy to re-order your columns through the context menu/shortcut keys, and if you make any mistakes you can always trash your alterations by clicking the Discard button below the Columns section.
After adding a new column, you’ll need to give the column a name, select its datatype and enter any other attributes the column will need. For my new column, since I wanted to set a default value of zero for all entries — the data defined a yes/no flag — I click where it says NULL in the Default column, select Custom from the context menu and enter 0 as the default value and click OK.
Once I click the Save button, HeidiSQL will run my alter table command and update my local database’s table. I can then click on the Data tab and confirm the new column was added to my table as expected.
And now the best part is that to create my migration script all I have to do is click the ALTER code tab in HeidiSQL, copy the command from there and paste it into a new SQL file in my repository, which will be executed when the test environment is re-built.
Having a clear, easily accessible view of the commands used to make table alterations is great. It means I don’t have to re-write commands, possibly introducing human error, when I transfer them to SQL files to commit to my repository and push to my test environment. It means I don’t have to waste time looking up syntax rules if/when I need to make unusual database changes. Best of all, for front-end developers like me who don’t work with SQL often enough to always feel 100% confident when it comes to making larger database changes, being able to rely on HeidiSQL for accurately written code provides piece of mind.
Leave a Reply