| title | Getting Started |
|---|---|
| permalink | /getting-started/ |
| nav_order | 2 |
grate is an automated database deployment (change management) system that allows you to use plain old .sql scripts but gain much more. We don't believe in writing database migrations in C#, but don't care how you get your scripts on disk. Write them at dev time alongside the code for your new feature, or generate larger diff's using other tooling (SSDT/RedGate/etc), whatever works for you and your team! Include schema objects, data, server configuration, whatever you need!
Low Maintenance Migrations: It seeks to solve both maintenance concerns and ease of deployment. We follow some of the same idioms as other database management systems (SQL scripts), but we are different in that we think about future maintenance concerns. We want to apply certain scripts whenever there's changes (like functions, views, stored procedures, and permissions), so we don’t have to throw everything into our one-time only update change scripts.
What Version Is YOUR Database On?: We subscribe to the idea of versioning your database how you want. At this point it's very rare to find someone that doesn't think you should version your code, and few would argue against versioning your code in a way that can lead back to a specific point in source control history. However, most people don’t really think of doing the same thing with your database. We say version it using the same version as your code base by passing it a version number at runtime. We love versioning our applications based on source control revisions, and how sweet it is when you can pinpoint the exact state of your database and the exact code in your application all to a given point in space and time in your repo!
Use your whole DBMS: You're running a full DBMS, why not use all it's features! Table-Valued Parameters for efficient processing? Data compression for space savings? Fancy-pants filtered indexes? Replication? Fine-grained permission management? If you've ever had to fight with code-based database scaffolding then you'll appreciate the value of plain old sql scripts.
By the way, we currently support Sql Server (including Azure and localdb), SqLite, Postgres, MariaDb and Oracle!!
grate is based on a simple premise: you create a directory structure on disk with well-known folder names, and then add .sql files. grate processes these folders in a fixed order, and runs the scripts inside in alphabetical order. That's it!
But don't be fooled, there's power in this simplicity due to a couple of key factors:
- Different folders are treated differently depending on the scripts they contain, eg Sproc's are re-run whenver the definition has changed from that in the target database, while
upscripts are run once only... See theScript Typessection below. - grate allows you to specify an Environment for each run, and Environment Specific scripts will only be run if appropriate. This allows for the loading of test-data into non-prod environments while keeping production clean, or catering for systems where environments markedly diverge (maybe you have different versions of Sql or something?).
grate records your migration history in ScriptsRun, ScriptsRunErrors, and
Version. It also maintains three Grate* tables to track changes to the migration
tables themselves. See Migration tables for what each table
contains and where they are stored.
There are samples included in source control in the /examples/ directory, have a look and a play in there for some more info.
Scripts in grate are considered to be one of three types:
One-time scripts These are scripts that are run exactly once per database, and never again. More info here.
Anytime Scripts These scripts run when they are new or have changed. See Anytime scripts.
Everytime Scripts These scripts are run (you guessed it) every time grate executes :) More info here
By default, grate processes the files in a standard set of directories in a fixed order for deterministic processing.
As of grate 1.4, the folder structure is fully customisable. However,
if you don't have any special requirements (e.g. an existing set of SQL scripts in an existing, set folder structure),
we would recommend you going with the defaults.
Database setup comes first. When grate needs to create the database, scripts in createDatabase can replace the provider's default creation logic. See Default folder configuration for how grate handles creation and --drop.
After setup, grate processes the default migration folders in this order.
Use this folder for tasks that run before the other migration folders, such as logging or taking a backup.
If you have scripts that need to alter the database config itself (rather than the contents of the database) this is the place to do it. For example setting recovery modes, enabling query stores, etc etc
Grate processes this folder only on the run that creates the database. A dry run that previews database creation also includes this folder.
This is where the bulk of your 'migration' scripts end up, eg adding tables, removing columns, adding reference data etc
Note that there's no down! If you've dropped a column in your up scripts, how do you possibly script an undo for that operation? grate has support for running the migration inside a transaction, and will rollback on script failure.
Use this folder for scripts that need to run immediately after up, ahead of folders such as functions and sprocs.
If you have any functions that need to run prior to others, make sure they are alphabetically first before the dependent scripts.
If you have views any that need to run prior to others, make sure they are alphabetically first before the dependent scripts.
Stored procedures are found in a sprocs folder. If you have any that need to run prior to others, make sure they are alphabetically first before the dependent scripts.
This folder exists to allow you to run scripts after you have set up your anytime scripts. It's pretty open what you put in here, but remember that it is still an anytime folder.
If you have any that need to run prior to others, make sure they are alphabetically first before the dependent scripts. Permissions may contain auto-wiring of permissions, so they are run every time regardless of changes in the files.
Use this folder for tasks that run after the other migration folders, such as logging.
- You are not required to have every one of these folders...i.e. if you don't use triggers there's no need to have an empty
triggersdirectory.