Automatic Updating Database Schema

Thu May 8 00:07:05 UTC 2025

hi Rudolf

I agree with you.    Your "If I were to do this again'" option is actually what we implemented a number of years back, after trying the other two and finding some the odd issue of not being able to rollback quickly .. so we expanded upon the second option  I mentioned to handle rollbacks. 

It has come in handy once in a while to roll back….so its a definite benefit — you write the rollback code at the same time you write the upgrade code so that you know how to get out of the mistake, should one be made, all while the upgrade code is familiar in your head.

It is probably a best practice if you have a lot of separate databases to upgrade and you need to be able to roll back to previous version quickly.

Doug Easterbrook
doug at artsman.com
Phone (403) 650-1978

> On May 7, 2025, at 12:12 PM, Rudolf Bargholz <rudolf at bargholz.ch> wrote:
> 
> Hi Andrew,
> 
> Here another option to those that Doug mentions below.
> 
> I wrote code to sync schemas in our app with DB2 many years ago. It adds and migrates columns or tables, adds, modifies, or removes sequences, functions, stored procedures, indexes, combined indexes, does runstats. Saved us a lot of time for the past over 20 years. All we need to do is add/modify a schema and then sync the schemas with the database. Was a lot of code, but makes database migrations very easy for us.
> 
> If I were to do this again, I would probably choose a different option.
> 
> Create a table in the database that contains the last datetime of a migration: char string yyyymmddhhnnss
> Create a class that contains methods. Each method is of the form yyyymmddhhnnss
> Each method has a "Switch" statement with " Case 'up' " and " Case 'down' "
> The "up" executes the explicit SQL for the actual migration. This allows you to not just add a new column, for example, but also initialize the values in that new column, in a probably more complex manner than would be possible in an automated workflow.
> The "down" reverts the change, optionally reverting previously executed additional SQL. 
> 
> After each migration, you update your database migration table by inserting the value of the just executed "up" datetime value. Reverting the migration will just delete all the migrations one by one in the table after the DOWN code has been executed.
> 
> You can create an admin window that allows you to either migrate UP or migrate DOWN again.
> 
> The format of the datetime allows you to order the methods ascending, so that you can migrate each method one by one in ascending order.
> 
> When you open your app, you can find the max value of the methods, and compare this to the value in your database, to check if there are any migrations to execute.
> 
> Regards
> 
> Rudolf Bargholz
> 
> -----Ursprüngliche Nachricht-----
> Von: omnisdev-en <omnisdev-en-bounces at lists.omnis-dev.com> Im Auftrag von Doug Easterbrook via omnisdev-en
> Gesendet: Mittwoch, 7. Mai 2025 18:31
> An: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com>
> Cc: Doug Easterbrook <doug at artsman.com>
> Betreff: Re: Automatic Updating Database Schema
> 
> hi Andrew.
> 
> you didn’t say what database you were using, so I’ll speak from the perspective of postgres.
> 
> we’ve done it a number of ways, each with their own advantage.
> 
> 
> Option 1: sync the schema’s
> 
> We always have a file class that matches the schema for each table in the database.  There are a number of reasons for that, such as
> - code completion in the IDE for variables
> - tooltip help for field names
> - definitions of variable type and length  (field.$type, and field.$subtype) as I recall
> - usage as temp global variables when merging with web pages and owrite and more
> 
> ok, with that in mind, you can:
> 1) read your entire database schema in postgres to get the tables and fields — all in one sql statement
> 2) do a make list of all variables in your file classes, the type and subtype
> 3) sort them
> 4) run a loop on the file list and see if it is in the database list (hint, we use TMOBJS bindary search to make this fast.  omnis $search will slow it down considerably)
> 
> if you find a missing field you can
> 1) create the SQL to alter table add field default ’some value’.     we default char to blank, dates to null, numbers to zero, boolean to false etc.
> 2) look at the omnis 'indexed' property for the file.variable — and then use that to create an index of you want.
> 
> This generally works, but does not have a couple of disadvantages.    It is the basis for a data dictionary in your application, and you can add stuff to your $info parameter on the field that can beef this up.  for example, if you want a different default value , you might put it in the $info in some json.
> 
> 
> NOTE: one of the topics to be covered at EUromnis 2025 will be a data dictionary — by Dan Ridinger.   I’m sure he’ll have a whole lot more during his presentation.  
> 
> 
> ———————
> second option that we use more often is:
> 
> we have a field in the database that indicates the current schema version.    lets say it is '5.6000'
> and in the program, we might say that the program version is '5.6001'
> 
> the table is read on start up to get the version of the database.
> 
> then we have startup code that compares the database to the program version.  should they be different, we run through a routine that does all the upgrade stuff required, since you may have:
> — new fields to add
> — old fields to delete
> — data migrations to happen (eg update all data in a new field or an empty field to be some specific value that could be fixed, or could be calculated)
> — missing indexes to add
> — new database functions to add
> — old functions to drop
> 
> or what have you.
> 
> in other words, this approach is a bit more scripted.   Upside, more flexibility in what you can do PLUS ability to roll back a version and rerun the updates..  Downside, you have to add a method call for each of the things you want to do.
> 
> example:
> 
> if databaseVersion < '5.6000'
>   do method Update56000
> end if
> if databaseVersion < '5.6001'
>   do method Update56001
> end if
> 
> 
> what might be in one of those updates:
> 
> do method AddTable('MyNewTable')  ## add all fields for the table
> do method Add Field('MyTable','NewFieldName','DefaultValueForTable','HowToBackfillData')
> do method AddIndex('myTable','MyFieldName')
> do method AddFunction('StringContainingFunction’)
> do method DropFunction(’NameOfFunction’)
> do method MigrateDataForTable('xxyy')
> do method VaccumTable(’myTable’)  ## sometimes important for postgres
> 
> etc.
> 
> 
> it gives a nice replayable set of SQL that lets you update things, plus it documents what you wanted to do.. and if you make a mistake in some update, you can then fix the routine and re-do it in a future update.
> 
> since you are only giving constants for table and field names, you can get the values form the file class for ’type' and 'subtype'
> 
> 
> 
> Final thought, if you are using omnis database workers, I might suggest a futher refinement (which we do)
> 
> 1) figure out the SQL for each of the calls.  eg it might be 'alter table mytable add column mycolumn integer default 0'
> 2) add that SQL to a table called SQLJobs (name is suggested)
> 3) then have something later on work through the SQLJOBS as a background worker so that it can be done separately from the program
> 
> 
> what might be in SQLJObs
> job_seq
> job_status (not done, done, error)
> job_version
> job_description
> job_sql
> job_preconditions  (i.e. job can’t run until job_seq is 'done')
> 
> meaning you can’t index a field or backfill it until the job is run that creates the field.
> 
> 
> 
> We’ve run this approach for about 20 years….   works well for us.    helps allow things to run 24x7 so people can use the app while we update the schema in the background.
> 
> 
> 
> 
> 
> Doug Easterbrook
> doug at artsman.com
> Phone (403) 650-1978
> 
>> On May 7, 2025, at 8:55 AM, Andrew Stolarz <stolarz at gmail.com> wrote:
>> 
>> Hello Everyone,
>> 
>> I'm wondering how others are doing this.
>> 
>> When you add new columns to an omnis Schema, how are you adding the columns
>> to your SQL database?
>> 
>> Is there any sleek way you guys have come up with that will look at the
>> Omnis schema and add the new columns into the DB automatically?
>> 
>> Any advice or direction would be appreciated!
>> 
>> 
>> Andrew
>> _____________________________________________________________
>> Manage your list subscriptions at https://lists.omnis-dev.com
>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
> 
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com