grate

https://erikbra.github.io/grate
Admin
grate is a SQL scripts migration runner that uses plain SQL, offering full flexibility and control...
Tokens:10,658
Snippets:37
Trust Score:8.3
Update:1 year ago
Show doc for...
Tokens:
Raw
### Grate Default Directory Run Order

Source: https://erikbra.github.io/grate/getting-started

Details the default order in which Grate processes directories for database migrations. This order ensures deterministic script execution. Customization of this order is possible.

```APIDOC
Default Directory Run Order:
  -1. dropDatabase (Anytime scripts): For custom DROP DATABASE scripts when using the --drop flag.
  0. createDatabase (Anytime scripts): For custom CREATE DATABASE scripts.
  1. beforeMigration (Everytime scripts): For tasks before migrations, like logging or backups.
  2. alterDatabase (Anytime scripts): For altering database configuration (e.g., recovery modes).
  3. runAfterCreateDatabase (Anytime scripts): Processed only if the database was created by Grate; for tasks like adding user accounts.
  4. runBeforeUp (Anytime scripts): Scripts to run before the main 'up' migrations.
  5. up (One-time scripts): The primary directory for migration scripts (e.g., adding tables, columns).
  6. runFirstAfterUp (One-time scripts): For scripts that need to run out of the normal order, prior to other anytime folders.
  7. functions (Anytime scripts): For database functions, ensuring alphabetical order for dependencies.
  8. views (Anytime scripts): For database views, ensuring alphabetical order for dependencies.
  9. sprocs (Anytime scripts): For stored procedures, ensuring alphabetical order for dependencies.
  10. triggers (Anytime scripts): For database triggers.
  11. indexes (Anytime scripts): For database indexes.
  12. runAfterOtherAnyTimeScripts (Anytime scripts): For scripts to run after other anytime scripts have been processed.
```

--------------------------------

### Grate Script Types

Source: https://erikbra.github.io/grate/getting-started

Defines the three types of scripts supported by Grate: One-time scripts run exactly once per database. Anytime scripts run whenever they are changed. Everytime scripts run every time Grate executes.

```APIDOC
Script Types:
  One-time Scripts: Run exactly once per database.
  Anytime Scripts: Run whenever they are changed.
  Everytime Scripts: Run every time Grate executes.
```

--------------------------------

### Custom Folder Configuration Example

Source: https://erikbra.github.io/grate/folder-configuration

This example demonstrates how to specify a custom list of folders with detailed configurations for each folder, including path, migration type, and connection type.

```bash
--folders folder1=path:a/sub/folder/here,type:Once,connectionType:Admin;folder2=type:EveryTime;folder3=type:AnyTime
```

--------------------------------

### .NET Global Tool Installation

Source: https://erikbra.github.io/grate/getting-grate

Installs the grate .NET global tool. Requires .NET 6 or later. Provides troubleshooting steps for common installation errors.

```bash
dotnet tool install -g grate
```

--------------------------------

### Winget Installation

Source: https://erikbra.github.io/grate/getting-grate

Installs grate using the Windows Package Manager (winget).

```bash
winget install erikbra.grate
```

--------------------------------

### grate Script Processing Logic

Source: https://erikbra.github.io/grate/getting-started

grate processes SQL scripts based on a directory structure and script types. Different folders and script types (e.g., Sproc, update) are handled differently. 'update' scripts run once, while others like Sproc scripts re-run if their definition changes. Environment-specific scripts are also supported.

```SQL
/* grate processes scripts in a fixed order based on directory structure and alphabetical order within directories. */

-- Example: Sproc scripts re-run if definition changes
-- CREATE PROCEDURE MyProc AS BEGIN SELECT 1; END

-- Example: 'up' scripts run once only
-- CREATE TABLE MyTable (Id INT PRIMARY KEY);

-- Example: Environment-specific scripts (e.g., for test data)
-- IF @Environment = 'Test' THEN INSERT INTO MyTable (Id) VALUES (1);
```

--------------------------------

### Docker: Start SQL Server Database

Source: https://erikbra.github.io/grate/getting-grate

Starts a SQL Server database instance within a Docker network. This is a prerequisite for running grate migrations using Docker.

```bash
docker network create grate_network && docker run -e SA_PASSWORD=gs8j4AS7h87jHg -e ACCEPT_EULA=Y --name db --network grate_network -d mcr.microsoft.com/mssql/server:2019-latest
```

--------------------------------

### Homebrew Installation

Source: https://erikbra.github.io/grate/getting-grate

Installs grate using Homebrew cask.

```bash
brew install --cask erikbra/cask/grate
```

--------------------------------

### Custom Folder Name Example

Source: https://erikbra.github.io/grate/folder-configuration

This example shows how to specify custom folder names, where the folder name is used as the path relative to the sqlfilesdirectory.

```bash
--folders folder1=my/first/scripts;folder2=the/last/ones;folder3=something/i/forgot
```

--------------------------------

### Grate Response File Example

Source: https://erikbra.github.io/grate/response-files

This snippet demonstrates the content of a `.rsp` file used with grate to pass command-line arguments. It includes common options like connection string, folder, environment, drop, version, and token replacement.

```bash
# Custom RSP for a local development drop/create migration
-cs SomeConnectionString
-f ./db
--env DEV
--drop
--version 1.0
--ut=my_token=myvalue

```

--------------------------------

### Custom Folder Type Example

Source: https://erikbra.github.io/grate/folder-configuration

This example illustrates specifying only the migration type for custom folders. Grate will then infer the folder names based on the provided names.

```bash
--folders folder1=Once;folder2=EveryTime;folder3=AnyTime
```

--------------------------------

### Docker: Run Grate Migration

Source: https://erikbra.github.io/grate/getting-grate

Executes a grate database migration using a Docker container. It mounts local migration scripts and configures the database connection string.

```bash
docker run -v ./examples/docker/db:/db  -e APP_CONNSTRING="Server=db;Database=grate_test_db;User Id=sa;Password=gs8j4AS7h87jHg;TrustServerCertificate=True" --network grate_network erikbra/grate
```

```bash
# run with database type, accept: sqlserver, postgresql, mariadb, sqlite, oracle
docker run -v ./examples/docker/db:/db -e DATABASE_TYPE=sqlserver -e CREATE_DATABASE=true -e ENVIRONMENT=Dev -e TRANSACTION=true -e APP_CONNSTRING="Server=db;Database=grate_test_db;User Id=sa;Password=gs8j4AS7h87jHg;TrustServerCertificate=True" --network grate_network erikbra/grate
```

--------------------------------

### Docker: Cleanup Resources

Source: https://erikbra.github.io/grate/getting-grate

Cleans up Docker resources including stopping and removing the database container and network.

```bash
docker kill db  || docker network rm grate_network  || docker rm $(docker ps -f status=exited | awk '{print $1}')
```

--------------------------------

### Grate Folder Configuration File

Source: https://erikbra.github.io/grate/folder-configuration

This example shows the content of a Grate folder configuration file. The file allows specifying custom folder mappings for different script types, separated by newlines or semicolons. Grate will read these configurations to locate the appropriate migration scripts.

```plaintext
up=ddl
views=projections
beforemigration=preparefordeploy
```

--------------------------------

### User Defined Tokens Example

Source: https://erikbra.github.io/grate/token-replacement

Demonstrates how to define and use user-defined tokens via the command line for token replacement in Grate scripts. User-defined tokens are specified using the `--ut` or `--usertoken` switch followed by a `key=value` pair.

```bash
--ut=MyTablePrefix=local --ut=MyOtherToken=OtherValue
```

--------------------------------

### Idempotent Stored Procedure Example

Source: https://erikbra.github.io/grate/script-types/anytime

Demonstrates how to write an idempotent stored procedure that can be safely altered across multiple migrations. It includes a check for existence before creating and then alters the procedure.

```SQL
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('[ss].[usp_GetAllThis]'))
EXECUTE('CREATE PROC [ss].[usp_GetAllThis] AS SELECT * FROM sys.objects') -- Create a dummy placeholder object if needed

GO
-- You can then alter the proc as needed here over repeat migrations without losing permissions etc.

ALTER PROCEDURE [ss].[usp_GetAllThis] 
/* your procedure guts here */

```

--------------------------------

### Customizing Grate Folder Configuration

Source: https://erikbra.github.io/grate/folder-configuration

This example demonstrates how to adjust the default Grate folder configuration to use custom directory names for specific script types. It shows how to specify these changes directly on the command line, ensuring proper quoting for shells that might interpret semicolons as command separators.

```bash
--folders 'up=ddl;views=projections;beforemigration=preparefordeploy'
```

--------------------------------

### Grate Token Replacement API

Source: https://erikbra.github.io/grate/token-replacement

Details the functionality and usage of token replacement within Grate, referencing the `TokenProvider` class for supported tokens. It highlights that token replacement is not case-sensitive and provides examples of how tokens are substituted in SQL scripts.

```APIDOC
Token Replacement:
  - Allows configuration values to be substituted within scripts.
  - Not case-sensitive.
  - Example: `ALTER DATABASE ` becomes `ALTER DATABASE Bob` if the database name is `Bob`.

Supported Tokens:
  - Defined in the `TokenProvider` class in `grate.core.Infrastructure.TokenProvider.cs`.
  - The set of supported tokens is expected to improve over time.

User Defined Tokens:
  - Supported via the `--ut` or `--usertoken` command-line switch.
  - Defined as `key=value` pairs.
  - Multiple user-defined tokens can be specified by repeating the switch.
  - Example Usage:
    `--ut=MyTablePrefix=local --ut=MyOtherToken=OtherValue`
  - Example in Script:
    `ALTER TABLE _TableName` becomes `ALTER TABLE local_TableName` if `MyTablePrefix` is set to `local`.

Warnings:
  - Using token replacement, especially user-defined tokens, should be done sparingly as it can lock scripts to Grate and lead to unexpected behavior if not handled carefully.
  - User-defined tokens are implemented simplistically and may fail with invalid or complex input.
```

--------------------------------

### Barebone Grate Usage

Source: https://erikbra.github.io/grate/configuration-options

Demonstrates the minimal configuration required to run Grate, which involves providing a connection string to the target database. Grate will then look for SQL scripts in the current directory.

```bash
grate --connectionstring="Server=(localdb)\MSSQLLocalDB;Integrated Security=true;Database=grate_test"
```

--------------------------------

### Grate Command-Line Arguments and Options

Source: https://erikbra.github.io/grate/migrating-from-roundhouse

This section details the command-line interface differences between grate and RoundhousE, highlighting case sensitivity, argument formats, and new/removed options.

```APIDOC
grate --help
  - Displays the full set of allowed command-line options.

Command line arguments are now case-sensitive on all operating systems.
Support for `/argument` on windows has been removed; use `-a` or `--argument` instead.

Key arguments and changes:
- `-cs`/`--connstring`: Single mandatory argument for connection string. Replaces `--database`, `--server` etc.
- `--schema=RoundhousE`: Use to maintain existing version information in the 'RoundhousE' schema.
- `--ut`: UserTokens are now passed multiple times for multiple tokens, instead of a single ';'-delimited string.
- `--drop`: Merged with the `DropCreate` mode. Executes drop, creation, and migration in a single run.
- `--verbosity`: Replaces `--verbose`. Accepts values: <Critical|Debug|Error|Information|None|Trace|Warning>.

Note: Not all previously supported tokens are available yet. Refer to Token Replacement docs for details.
```

--------------------------------

### Contributing to Grate

Source: https://erikbra.github.io/grate/index

Information on how to contribute to the Grate project, including a link to the GitHub repository and the CONTRIBUTING.md file.

```APIDOC
Contributing:
  - GitHub Repository: https://github.com/erikbra/grate
  - Contribution Guidelines: CONTRIBUTING.md (located in the repository)
```

--------------------------------

### Grate Configuration Options

Source: https://erikbra.github.io/grate/configuration-options

This section details the command-line arguments for configuring the Grate database migration tool. It covers essential options for managing database connections, script execution, and migration behavior.

```APIDOC
Grate Configuration Options:

--connectionstring | --connstring <connectionstring>
  - REQUIRED: Specifies the entire connection string for the database. ServerName and Database are obsolete.

--adminconnectionstring | --adminconnstring <adminconnectionstring>
  - Specifies the connection string for an administrative database, typically used for creating new databases (e.g., 'master' for SQL Server).

--files | --sqlfilesdirectory <sqlfilesdirectory>
  - The directory where SQL scripts are located. Defaults to the current directory.

--folders
  - Configures folder structure for migration scripts. Refer to Grate's Getting Started and Folder Configuration documentation for details.

--outputPath <outputPath>
  - Specifies the directory where migration-related artifacts (backups, logs, etc.) are stored. Defaults to %LOCALAPPDATA%/grate.

--accesstoken <token>
  - Provides an access token for connecting to SQL Server.

--commandtimeout <commandtimeout>
  - Sets the timeout for regular command execution. Defaults to 60s.

--admincommandtimeout <admincommandtimeout>
  - Sets the timeout for administrative commands (excluding restore). Defaults to 300s.

--databasetype | --dbt | --dt <mariadb | oracle | postgresql | sqlite | sqlserver>
  - Specifies the type of database Grate is running against. Defaults to 'sqlserver'.

--transaction | --trx
  - Runs the migration process within a single transaction. Defaults to false.

--schema | --schemaname <schemaname>
  - Defines the schema used for Grate's migration tables. Defaults to 'grate'. Useful for RoundhousE upgrades.

--drop
  - Instructs Grate to drop the target database before running migration scripts. Defaults to false.

--environment | --env
  - Specifies an environment name for environment-aware script execution (e.g., 'LOCAL' for 'something.ENV.LOCAL.sql'). Defaults to an empty value.

--warnononetimescriptchanges
  - Logs a warning for changed one-time scripts that have already been run, but allows them to execute. Defaults to false.

--warnandignoreononetimescriptchanges
  - Ignores changed one-time scripts that have already been run and updates their hash, logging a warning. Defaults to false.

--disabletokenreplacement | --disabletokens
  - Prevents Grate from performing token replacement within scripts. Defaults to false.

--usertokens | --ut <usertokens>
  - Allows custom token replacement using key=value pairs (e.g., '--ut=my_token=myvalue'). Can be specified multiple times.

--donotstorescriptsruntext
  - Prevents Grate from storing the full script text in the database. Defaults to false.

--forceanytimescripts | --runallanytimescripts
  - Ensures that 'any time' scripts are executed on every Grate run, regardless of changes. Defaults to false.

--baseline
  - Marks scripts as run without executing them. Useful for synchronizing with existing migrations.

--dryrun
  - Logs intended actions without executing them against the database. Useful for previewing migration steps.

--restore
  - Specifies the path to a database backup file (.bak) to restore from. If not provided, no restore is performed.

--noninteractive | --silent
  - Runs Grate without prompting for user input. Defaults to false.

--repositorypath | --repo
  - Specifies the path to the repository for tracking versioning. Defaults to null.

--version
  - Directly sets the current database migration version. Defaults to '1.0.0.0'.

--uptodatecheck | --isuptodate
  - Checks and outputs whether the database is up to date (i.e., if any non-everytime scripts would be run).

--verbosity <Critical|Debug|Error|Information|None>
  - Controls the level of detail in the output logs.
```

--------------------------------

### Grate Command Line Equivalent

Source: https://erikbra.github.io/grate/response-files

This snippet shows the equivalent command-line execution for the provided `.rsp` file content. It highlights how grate processes the arguments specified in the response file.

```bash
grate @./grate_settings.rsp
```

--------------------------------

### Grate Command Line Execution

Source: https://erikbra.github.io/grate/response-files

This snippet represents the direct command-line execution that is equivalent to using the `.rsp` file. It shows the expanded arguments passed to the grate tool.

```bash
grate -cs SomeConnectionString -f ./db --env DEV --drop --version 1.0 --ut=my_token=myvalue
```

--------------------------------

### Grate Command Line Options

Source: https://erikbra.github.io/grate/configuration-options

Displays help and usage information for the Grate command-line interface. Includes options for setting verbosity levels.

```APIDOC
Grate Command Line Options:

-? | -h | --help
  - Description: Show help and usage information.

Verbosity Level:
  - Description: Controls the level of detail in the output, as defined by Microsoft's LogLevel (https://docs.microsoft.com/dotnet/api/Microsoft.Extensions.Logging.LogLevel).
  - Example Levels:
    - Trace
    - Information
    - Warning
    - Error
    - Critical
    - None
```

--------------------------------

### Specifying Target Environment via Command Line

Source: https://erikbra.github.io/grate/environment-scripts

Shows the command-line syntax for instructing Grate which environment to target during a migration process. This is crucial for activating environment-specific scripts.

```Bash
> grate --env=LOCAL
> grate --environment=TEST
```

--------------------------------

### Script Naming Conventions

Source: https://erikbra.github.io/grate/script-types/one-time

Provides recommended naming conventions for one-time scripts to ensure proper ordering and identification. Supports numeric or datetime prefixes.

```APIDOC
Script Naming:
  - Numeric prefix: `0001_somescript.sql`, `0002_nextscript.sql`
  - Datetime prefix: `20121026091400_somescript.sql`, `20121026091401_nextscript.sql`
  - Custom formats like `####.##.##.####` are also acceptable.
```

--------------------------------

### Supported Databases

Source: https://erikbra.github.io/grate/index

Grate supports a variety of database management systems, allowing developers to use the full capabilities of their chosen database.

```APIDOC
Database Support:
  - Microsoft SQL Server
  - PostgreSQL
  - MariaDB/MySQL
  - Sqlite
  - Oracle
```

--------------------------------

### Targeting Multiple Environments

Source: https://erikbra.github.io/grate/environment-scripts

Illustrates how a single script can be configured to run across multiple specified environments by including multiple environment names in its filename.

```SQL
-- Example: AddTestData.Env.TEST.UAT.sql
-- This script will run if grate is targeting either 'TEST' or 'UAT' environments.
```

--------------------------------

### Customizing Grate Folder Configuration

Source: https://erikbra.github.io/grate/folder-configuration

Demonstrates how to specify custom folder names for Grate migrations using the --folders argument. This allows users to deviate from the default folder structure by mapping specific script types to custom directory names.

```bash
$ grate --folders up=tables
```

--------------------------------

### Grate Response File Support

Source: https://erikbra.github.io/grate/response-files

Grate leverages System.CommandLine libraries for built-in support of Response (.rsp) files. These files are used to pass a standard set of command-line arguments to the tool, avoiding lengthy command lines.

```APIDOC
Grate:
  Response Files:
    - Description: Allows passing a standard set of command-line arguments via `.rsp` files.
    - Syntax: `grate @<path_to_rsp_file>`
    - Requirement: The `@` symbol before the file path is mandatory.
    - Example Usage:
      - Create a file named `grate_settings.rsp` with arguments like:
        ```
        -cs SomeConnectionString
        -f ./db
        --env DEV
        --drop
        --version 1.0
        --ut=my_token=myvalue
        ```
      - Execute using: `grate @./grate_settings.rsp`
      - This is equivalent to: `grate -cs SomeConnectionString -f ./db --env DEV --drop --version 1.0 --ut=my_token=myvalue`
    - Dependencies: Built-in support via `System.CommandLine` libraries.
```

--------------------------------

### Content for Up Folder

Source: https://erikbra.github.io/grate/script-types/one-time

Specifies the types of SQL statements that should be included in the 'up' folder for one-time scripts.

```APIDOC
Content:
  1. DDL (schema changes - database structure)
  2. DML (inserts/updates/deletes)
```

--------------------------------

### Everytime Script Naming Convention

Source: https://erikbra.github.io/grate/script-types/everytime

Demonstrates how to name SQL files to be treated as 'Everytime' scripts by Grate. These scripts run on every migration.

```SQL
Something.EVERYTIME.sql
EVERYTIME.something.sql
```

--------------------------------

### Prerequisites

Source: https://erikbra.github.io/grate/index

Grate is a self-contained executable built with .NET. The primary prerequisite is ICU, which is necessary for its operation.

```APIDOC
Prerequisites:
  - .NET runtime
  - ICU (e.g., libicu on Debian Linux)
```

--------------------------------

### Conditional Script Execution by Environment

Source: https://erikbra.github.io/grate/environment-scripts

Demonstrates how Grate executes SQL scripts based on environment-specific naming conventions. Files containing `.ENV.*` are only run if the environment name matches the specified `--env` argument.

```SQL
-- Example: LOCAL.permissionfile.env.sql
-- This script will only run if grate is targeting the 'LOCAL' environment.
```

--------------------------------

### Error Handling for One-Time Scripts

Source: https://erikbra.github.io/grate/script-types/one-time

Explains Grate's behavior when a one-time script is modified after execution. Includes information on the 'WarnOnOneTimeScriptChanges' configuration setting.

```APIDOC
Errors:
  - If a one-time script is changed after execution, Grate will shut down with errors.
  - Configuration Setting: `WarnOnOneTimeScriptChanges` (under Switches) allows running with warnings, though not recommended.
```

--------------------------------

### One-Time Script Folders

Source: https://erikbra.github.io/grate/script-types/one-time

Scripts placed in these folders are treated as one-time scripts by default. This includes schema changes and data modifications.

```APIDOC
Folders:
  - runAfterCreateDatabase
  - up
```
Tokens:
Raw