LabKey includes a database schema management system that module writers use to automatically install and upgrade schemas on the servers that deploy their modules, providing convenience and reliability to the server admins. This eliminates the need for DBAs to manually run scripts when upgrading the server.
The scripts run sequentially, each building on the schema that the previous script left behind. They may run on an existing schema that already contains data. For example, the initial script might create a schema that contains one table. A second script might alter the table to add new columns. A third script might create a second table with some of the same columns, copy any existing data from the original table, and then drop the now-duplicated columns from the original table.
For all sql scripts, we recommend that they conform to the following SQL conventions
A Word of Caution
Module writers should author their SQL scripts carefully, test them on multiple databases, and follow some simple rules to ensure compatibility with the script runner. Unlike most code bugs, a SQL script bug has the potential to destroy data and permanently take down a server. Read this page completely before attempting to write module SQL scripts. If you have any questions, please contact the LabKey team.
Multiple test and development servers will run SQL scripts shortly after they are pushed to a Git repository. SQL scripts will never be re-run, so consider pushed scripts immutable; instead of changing them, create a new upgrade script to make further changes. See the Hints and Advanced Topics
section below for ways to make this process easier.
Schemas get upgraded at server startup time if (and only if) a module's schema version number previously stored in the database is less than the current schema version in the code. The module version in the database is stored in core.Modules; the module version in code is returned by the getSchemaVersion() method in each Module class (Java module) or specified by the "SchemaVersion" property in module.properties (file-based module).
When a module is upgraded, the SQL Script Manager automatically runs the appropriate scripts to upgrade to the new schema version. It determines which scripts to run based on the version information encoded in the script name. The scripts are named using the following convention: <dBschemaName>-<fromVersion ##.###>-<toVersion ##.###>.sql
A module can manage multiple schemas. If so, all of them use a single module schema version number progression. If a module manages multiple database schemas, be extra careful about versioning and naming and you should expect to see many gaps between each schema's script files.
Modules are upgraded in dependency order, which allows schemas to safely depend on each other.
Schema Versions and LabKey Conventions
This document describes the conventions that LabKey uses to version schemas and SQL scripts in the modules we manage. You can adopt different conventions for what version numbers to use; you simply need to use floating point numbers with up to three decimal places.
As of 2020, LabKey versions schemas based the current year plus an increasing three-digit counter for each schema. With every schema change (typically via a SQL script) the counter increases by one. As an example:
- At the beginning of 2020, the query schema version is set to 20.000 by setting QueryModule.getSchemaVersion() to return 20.000.
- In January, a change to the query schema is needed, so a developer creates and pushes a query-20.000-20.001.sql script and bumps the QueryModule.getSchemaVersion() return value to 20.001. The SQL Script Runner runs that script and records the module's new schema version.
- Another change is needed later in the year, so query-20.001-20.002.sql is pushed along with the schema version getting bumped to 20.002.
- At the beginning of 2021, the query schema version is then set to 21.000.
Some modules will have many SQL scripts and schema bumps in a given year; others won't have any.
- foo-0.00-20.000.sql: Upgrades foo schema from version 0.00 to 20.000
- foo-20.000-20.001.sql: Upgrades foo schema from version 20.000 to 20.001
- foo-20.001-20.002.sql: Upgrades foo schema from version 20.001 to 20.002
The script directories can have many incremental & full scripts to address a variety of upgrade scenarios, but LabKey is moving toward a single bootstrap script (e.g., foo-0.00-20.000.sql) plus a series of incremental scripts.
When creating a new incremental script, follow these steps to create a SQL script (assuming module "Foo" that uses schema "foo"):
- Finalize and test your SQL script contents.
- Git pull to sync with files in your repo and help avoid naming conflicts.
- Find the current version number returned by the FooModule getSchemaVersion() method (or SchemaVersion in module.properties, if that's used instead). Let's say it's 20.003.
- Name your script "foo-20.003-20.004.sql".
- Bump the schema version to 20.004.
- Build, test, and commit/push your changes.
Everyone who syncs to your repository (e.g., all the developers on your team, your continuous integration server) will update, build, start their servers, and automatically run your upgrade script, resulting in Foo module schema version 20.004 successfully installed. After you commit and push there's no going back; you can't change scripts once they've been run. Instead, you must check in a new incremental script that produces the appropriate changes (or rolls back your changes, etc.).
If you're testing an extensive schema change you may want to check in a script but not have it run on other developers' machines immediately. This is simple; check in the script but don't bump the version number in code. When you're done testing, bump the version and everyone will upgrade.
The above guidelines eliminate most, but not all, problems with script naming. In particular, if multiple developers are working on the same module (perhaps on different branches) they must coordinate with each other to ensure scripts don't conflict with each other.
Java Upgrade Code
Some schema upgrades can't be implemented solely in SQL, and require Java code. One option is to implement and register a class in your module that implements UpgradeCode and invoke its methods from inside a script via the core.executeJavaUpgradeCode stored procedure. This works well for self-contained code that assumes a particular schema structure; the code is run once at exactly the right point in your upgrade sequence.
You can annotate a Java UpgradeCode method with @DeferredUpgrade to defer its running until after initial upgrade of all modules is complete. This can be useful if the upgrade code needs to call library methods that change based on the current schema. Be very careful here; the schema could be in a completely unknown state (if the server hasn't upgraded in a while then your code could execute after two years of future upgrade scripts have run).
Specifying Additional Metadata
In addition to these scripts, if you want to specify metadata properties on the schema, such as Caption, Display Formats, etc, you will need to create a schema XML file
. This file is located in the /scripts folder of your module. There is one XML file per schema. This file can be auto-generated for an existing schema. To get an updated XML file for an existing schema, go to the Admin Console then pick 'Check Database'. There will be a menu to choose the schema and download the XML. If you are a Site Administrator, you can use a URL along these lines directly:
Simply replace the domain name & port with the correct values for your server. Also put the name of your schema after 'dbSchema='. Note: Both the schema XML file name and 'dbSchema=' value are case-sensitive
. They must match the database schema name explicitly.
Manually Resetting the Schema
Bootstrapping refers to installing a module from a blank slate, before its schema has been created. On a developer machine: shut down your server, run "gradlew bootstrap", and restart your server to initiate a full bootstrap on your currently selected database server. This is a great way to test SQL scripts on a clean install. Use "gradlew pickPg" and "gradlew pickMSSQL" to test against the other database server.
When developing a new module, schemas can change rapidly. During initial development, it may be useful to completely uninstall / reinstall a module in order to rebuild the schema from scratch, rather than make changes via a large number of incremental scripts. Uninstalling a module requires several steps: drop the schema, delete the entry in the core.Modules table, delete all the associated rows in the core.SqlScripts table. The "Module Details" page (from the Admin Console) provides a quick way to uninstall a module; when your server is restarted, the module will be reinstalled and the latest scripts run. Use extreme caution… deleting a schema or module should only be done on development machines. Also note that while this is useful for development, see warnings above about editing scripts once pushed to GitHub and/or otherwise made available to other instances of LabKey.
Hints and Advanced Topics
- Modules can (optionally) include two special scripts for each schema: <schema>-create.sql and <schema>-drop.sql. The drop script is run before all module upgrades and the create script is run after that schema's scripts are run. The primary purpose is to create and drop SQL views in the schema. The special scripts are needed because some databases don't allow modifying tables that are used in views. So LabKey drops all views, modifies the schema, and re-creates all views on every upgrade.
- The Admin Console provides other helpful tools. The "SQL Scripts" link shows all scripts that have run and those that have not run on the current server. From there, you can choose to "Consolidate Scripts" (e.g., create bootstrap scripts, <schema>-0.00-##.000.sql). While viewing a script, you have the option to "Reorder" the script, which attempts to parse and reorder all the statements to group all modifications to each table together. This can help streamline a script (making redundant or unnecessary statements more obvious), but is recommended only for advanced users.
- LabKey offers automated tests that will compare the contents of your schema XML file with the actual tables present in the DB. To run this test, visit a URL similar to: http://localhost:8080/labkey/junit-begin.view, but substitute the correct host name and port. Depending on your server configuration, you may also need to omit "/labkey" if labkey is run as the root webapp. This page should give a list of all junit tests. Run the test called "org.labkey.core.admin.test.SchemaXMLTestCase".