cgillum
commented
2 years ago The plan is to support both automatic and manual schema migrations. The code for executing the schema migration can be found here. We don't yet have documentation for this because we only planned to support schema migration after the v1.0 release, but you can expect we'll have documentation ready when it's time for the v1.1 release (or whichever release requires the first schema migration). At that point, you can expect there will also be instructions for how DBAs can do this without requiring the app to have elevated permissions.
The intended design is that we'll add new schema script files every time we do a release that involves a schema change. For example, we may add a schema-1.1.0.sql file which performs ALTER TABLE statements to add new columns. Existing schema files (like schema-1.0.0.sql) will remain unchanged.
IGx89
What is the intended method for production apps to keep their SQL schema up-to-date when new releases with schema changes come out? The table schema documentation says that the functions host automatically provisions the database upon startup, which I'm guessing involves migrations as well. That requires the app to be using a database user with full schema privileges however, which the docs further down (and every DBA :p) says to not do, instead using a low-privilege user with only the dt_runtime role. That seems to negate the previous guidance.
I looked at the schema script, schema-1.0.0.sql, and it doesn't appear to be doing anything related to schema versions. Running that script manually seems like probably the best approach in production, but if that doesn't support migrations (such as if v1.1 adds a new column to a table) then that won't work either.