DbUp: Create Db if it doesn't exist

What I’d really like the is the ability to have DbUp have an option to create the database if it doesn’t exist. I tried to solve this by having my first script do this:

0001_InitialCreate.sql

IF NOT EXISTS ( SELECT [Name] FROM sys.databases WHERE [name] = 'MyDatabase' )
BEGIN
    CREATE DATABASE [MyDatabase]
END
GO

USE [MyDatabase]
GO

IF NOT EXISTS ( SELECT * FROM sysobjects where Name='MyTable' and xtype='U')
    CREATE TABLE dbo.MyTable (
        [Id] INT NOT NULL PRIMARY KEY IDENTITY,
        [Name] VARCHAR(50) NOT NULL,
        [CreatedBy] VARCHAR(50) NOT NULL,
        [CreatedDate] DATETIME2(7) NOT NULL DEFAULT SYSDATETIME())
GO

Subsequent scripts look like this:

0002_AddAgeColumnToMyTable.sql

USE [MyDatabase]
GO

ALTER TABLE dbo.MyTable
ADD [Age] INT NULL
GO

This approach works fine if you leave the database name out of the connection string, and just use USE statements in your scripts. The one problem, though, is that it doesn’t find the SchemaVersions table so it re-runs every script every time. (although it does actually find the table and insert the scripts into it).

About this issue

  • Original URL
  • State: closed
  • Created 10 years ago
  • Comments: 28 (19 by maintainers)

Commits related to this issue

Most upvoted comments

@JakeGinnivan, I have created pull request #114 to address this issue. I have implemented this as @dazinator requested above.

Let me know if I’m going about this correctly. I have been in software development for 30+ years and have been developing with .NET since 2000 in the pre-release days. However, I am new to GitHub – I’m a TFS VC junkie 👍 – and this is my very first attempt to contribute to a project. I’m sure I screwed something up… 😉