Create database step - Githubissues

JaDuyve commented 8 months ago

I was wondering if there was a way to create the database with the grate tool, that grate doesn't use its own script for creating the database. I would like to provide a custom script that creates the database during the execution of the grate tool. The reason I would like to do this is that I want to be able to set the following settings at time of creation

location of mdf file
location of ldf file
initial size of database
file growth of database
collate settings

Describe the solution you'd like An optional create database step would be nice. And when no script in step is present, then grate runs as currently provided. The creation of the grate specific tables and schema, I would prefer that this is still handled by grate itself.

Describe alternatives you've considered I tried to add a script to the beforeMigration step that creates the database when it doesn't exist yet, but I receive the error that the database already exists. --createdatabase option set to false does not work for this because grate expects that the database already exists before executing the beforeMigration script from what I can see.

Database: Microsoft SQL Server

erikbra commented 8 months ago

Hi, @JaDuyve.

Customising creation of the database with a custom script is definitely possible. However, there is a special folder for this, called createDatabase. This is described in the Default folder configuration section of the documentation: https://erikbra.github.io/grate/folder-configuration/#default-folder-configuration

You shouldn't have to do any other special configuration, as setting the createdatabase option to false. Just leave those options as default.

(this is also in the test report, which is generated at every CI build, but expire quite quickly):

erikbra commented 8 months ago

Looking at the documentation, do you think this is well enough described, @JaDuyve? Or does the documentation need improvements?

JaDuyve commented 8 months ago

My apologies. I didn't notice there were other steps other than the steps described at the getting started page.

Thanks for the quick response!

erikbra commented 8 months ago

Lovely! Maybe we should make it clear on the "getting started page" that there are other, less used steps as well. If you have suggestions on how to make it easier to get started, please come with suggestions, or even better, submit a PR on the documentation :)

JaDuyve commented 8 months ago

Hi @erikbra, I have created a small PR (#463) to make the changes to the documentation.

I was also trying to use a custom folder configuration and I got some errors when I wanted to give the step createDatabase a custom folder. Am I correct in assuming that there is no support for providing different location for createDatabase script other than the default configuration?

Reproduce

Here are 2 zips that contain for both cases the folder structure that was used in tests below

I used docker for running the database

version: "3.9"
services:
  LocalDB:
    container_name: LocalDB
    image: mcr.microsoft.com/mssql/server:2022-latest
    environment:
      SA_PASSWORD: T3stPa55w0rd
      ACCEPT_EULA: Y
      TZ: "Europe/Brussels"
    ports:
      - "1433:1433"
    healthcheck:
      test: /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "T3stPa55w0rd" -Q "SELECT 1" || exit 1
      interval: 10s
      timeout: 3s
      retries: 10
      start_period: 10s

Test with default folder config

Directory structure

.
├── createDatabase
│   └── 20240304_01_CreateDatabase.sql
└── up
    └── 20240304_01_CreateTable.sql

Output command

grate --connstring="Server=localhost,1433;Database=TestDB;User ID=sa;Password=T3stPa55w0rd;TrustServerCertificate=True" --create true -t
Initializing connections.
Running grate v1.6.1 (build date 02/16/2024 07:52:45) against localhost,1433 - TestDB.
Looking in . for scripts to run.
Please press enter when ready to kick...

================================================================================
Setup, Backup, Create/Restore/Drop
================================================================================
  Running '20240304_01_CreateDatabase.sql'.
================================================================================
Grate Structure
================================================================================
================================================================================
Versioning
================================================================================
 Migrating TestDB from version 0.0.0.0 to 0.0.0.1.
 Versioning TestDB database with version 0.0.0.1.
================================================================================
Migration Scripts
================================================================================
Skipping 'BeforeMigration', beforeMigration does not exist.
Skipping 'AlterDatabase', alterDatabase does not exist.
Skipping 'Run After Create Database', runAfterCreateDatabase does not exist.
Skipping 'Run Before Update', runBeforeUp does not exist.

Looking for Update scripts in "./up". These should be one time only scripts.
--------------------------------------------------------------------------------
  Running '20240304_01_CreateTable.sql'.
--------------------------------------------------------------------------------

Skipping 'Run First After Update', runFirstAfterUp does not exist.
Skipping 'Functions', functions does not exist.
Skipping 'Views', views does not exist.
Skipping 'Stored Procedures', sprocs does not exist.
Skipping 'Triggers', triggers does not exist.
Skipping 'Indexes', indexes does not exist.
Skipping 'Run after Other Anytime Scripts', runAfterOtherAnyTimeScripts does not exist.
Skipping 'Permissions', permissions does not exist.
Skipping 'AfterMigration', afterMigration does not exist.

grate v1.6.1 (build date 02/16/2024 07:52:45) has grated your database (TestDB)! You are now at version 0.0.0.1. All changes

Test with custom folder config

Directory structure

.
├── ddl
│   ├── 00_CreateDatabase.AnyTime
│   │   └── 20240304_01_CreateDatabase.sql
│   └── 05_Up.OneTime
│       └── 20240304_01_CreateTable.sql
└── folder_config.txt

Folder config

createDatabase=./ddl/00_CreateDatabase.AnyTime
up=./ddl/05_Up.OneTime

Output command

$ grate --connstring="Server=localhost,1433;Database=TestDB;User ID=sa;Password=T3stPa55w0rd;TrustServerCertificate=True" --folders ./folder_config.txt --create true -t
Initializing connections.
Running grate v1.6.1 (build date 02/16/2024 07:52:45) against localhost,1433 - TestDB.
Looking in . for scripts to run.
Please press enter when ready to kick...

================================================================================
Setup, Backup, Create/Restore/Drop
================================================================================
================================================================================
Grate Structure
================================================================================
================================================================================
Versioning
================================================================================
 Migrating TestDB from version 0.0.0.0 to 0.0.0.1.
 Versioning TestDB database with version 0.0.0.1.
================================================================================
Migration Scripts
================================================================================

Looking for createDatabase scripts in "././ddl/00_CreateDatabase.AnyTime". These should be one time only scripts.
--------------------------------------------------------------------------------
  Running '20240304_01_CreateDatabase.sql'.
Error running script "20240304_01_CreateDatabase.sql": CREATE DATABASE statement not allowed within multi-statement transaction.
Rolling back changes.
Unhandled exception: grate.Exceptions.MigrationFailed: Migration failed due to errors:
 * CREATE DATABASE statement not allowed within multi-statement transaction.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): CREATE DATABASE statement not allowed within multi-statement transaction.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<InternalExecuteNonQueryAsync>b__210_1(IAsyncResult result)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult, Func`2, Action`1, Task`1, Boolean)
--- End of stack trace from previous location ---
   at grate.Migration.AnsiSqlDatabase.ExecuteNonQuery(DbConnection conn, String sql, Nullable`1 timeout)
   at grate.Migration.AnsiSqlDatabase.ExecuteNonQuery(DbConnection conn, String sql, Nullable`1 timeout)
   at grate.Migration.AnsiSqlDatabase.RunSql(String sql, ConnectionType connectionType, TransactionHandling transactionHandling)
   at grate.Migration.DbMigrator.RunTheActualSql(String sql, String scriptName, MigrationType migrationType, Int64 versionId, ConnectionType connectionType, TransactionHandling transactionHandling)
   at grate.Migration.DbMigrator.RunTheActualSql(String sql, String scriptName, MigrationType migrationType, Int64 versionId, ConnectionType connectionType, TransactionHandling transactionHandling)
   at grate.Migration.DbMigrator.<>c__DisplayClass27_0.<<RunSql>g__LogAndRunSql|0>d.MoveNext()
--- End of stack trace from previous location ---
   at grate.Migration.DbMigrator.RunSql(String sql, String scriptName, MigrationType migrationType, Int64 versionId, GrateEnvironment environment, ConnectionType connectionType, TransactionHandling transactionHandling)
   at grate.Migration.GrateMigrator.Process(DirectoryInfo root, MigrationsFolder folder, String changeDropFolder, Int64 versionId, ConnectionType connectionType, TransactionHandling transactionHandling)
   at grate.Migration.GrateMigrator.LogAndProcess(DirectoryInfo root, MigrationsFolder folder, String changeDropFolder, Int64 versionId, ConnectionType connectionType, TransactionHandling transactionHandling)
   at grate.Migration.GrateMigrator.Migrate()
ClientConnectionId:51e5002f-1f37-4770-ad5e-d953c0a8c697
Error Number:226,State:5,Class:16
   --- End of inner exception stack trace ---
   at grate.Migration.GrateMigrator.Migrate()
   at grate.Commands.MigrateCommand.<>c__DisplayClass0_0.<<-ctor>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at System.CommandLine.NamingConventionBinder.CommandHandler.GetExitCodeAsync(Object returnValue, InvocationContext context)
   at System.CommandLine.NamingConventionBinder.ModelBindingCommandHandler.InvokeAsync(InvocationContext context)
   at System.CommandLine.Invocation.InvocationPipeline.<>c__DisplayClass4_0.<<BuildInvocationChain>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass17_0.<<UseParseErrorReporting>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass12_0.<<UseHelp>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass19_0.<<UseTypoCorrections>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<UseSuggestDirective>b__18_0>d.MoveNext()
--- End of stack trace from previous location ---
   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass16_0.<<UseParseDirective>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<RegisterWithDotnetSuggest>b__5_0>d.MoveNext()
--- End of stack trace from previous location ---
   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass8_0.<<UseExceptionHandler>b__0>d.MoveNext()

Setup

Database: Microsoft SQL Server 2022
OS: MacOS 14.2.1 (23C71) - Intel
Grate: v1.6.1.0

erikbra / grate

Create database step #457

Reproduce

Test with default folder config

Directory structure

Output command

Test with custom folder config

Directory structure

Folder config

Output command

Setup