Deploying using the schema model
Published 11 October 2021
Early Access
We are currently working on the capability to deploy from the schema model being part of the flyway commandline so it works consistently across all databases that we have comparison technology for (SQL Server, Oracle, PostgreSQL, MySQL). If you are interested in this capability, please get in touch with the Development Team.
You can also use the schema-model folder on disk to deploy database schema changes using our comparison technology (SQL Compare or Schema Compare for Oracle) in a state-based deployment. You can use these technologies via the user interface or the command line. The schema-model folder should contain a RedGateDatabaseInfo.xml file alongside a series of folders containing SQL scripts for your database objects.
If you want to deploy the latest version or a specific version from version control, then check this version out to a working folder and use this as the "Scripts folder" source.
Command line example for SQL Server
The example below specifies the command for updating a target database (/Database2) from a scripts folder using the default Flyway Desktop comparison options.
sqlcompare /Scripts1:<enter folder> /Database2:<enter database connection string> /Synchronize /Options:Default,NoSQLPlumbing,IgnoreTSQLT,ConsiderNextFilegroupInPartitionSchemes,IgnoreUsers,IgnoreCollations,IgnoreDataCompression,IgnoreReplicationTriggers,IgnoreStatistics,IgnoreNotForReplication,IgnoreWithNocheck,DoNotOutputCommentHeader
For more information, see the:
- SQL Server - SQL Compare command line
- Oracle - Schema Compare for Oracle command line
If you need more control over your deployment scripts, we recommend using migration scripts. Flyway Enterprise can generate versioned (and optionally undo) migration scripts from your schema model. These scripts can then be edited, if needed, and saved in version control. This is the exact script that will be run against your other environment and gives you full control over the script that will be used in your deployments. This also allows you to include non-schema changes in your migration scripts as well (e.g., a one-off transactional data update). Flyway tracks which migration scripts have run on which databases and can tell when and how long they took. Flyway can then apply any pending migration scripts. For more details about migrations-based deployments, see Deploying database changes via a pipeline.
