SqlDatabase

Command-line tool and PowerShell module for MSSQL Server, PostgreSQL and MySQL allows to execute scripts, database migrations and export data.

Table of Contents

• SqlDatabase
  • Table of Contents
  • Installation
    • PowerShell, from gallery
    • PowerShell, manual release download
    • Dotnet sdk tool
  • Target database type selection
  • Execute script(s)
  • Export data from a database to sql script (file)
  • Create a database
  • Migrate an existing database
  • Scripts
  • Variables
    • Example
    • Example how to hide variable value from a log output
    • Predefined variables
  • *.zip files
  • VS Package manager console
  • Examples
  • License

Installation

PowerShell module is compatible with Powershell Core 6.1+ and PowerShell Desktop 5.1.

.net tool is compatible with .net sdk 7.0, 6.0, 5.0 and .net core 3.1.

Command-line tool is compatible with .net runtime 7.0, 6.0, 5.0, .net core runtime 3.1 and .net framework 4.5.2+.

PowerShell, from gallery

PS> Install-Module -Name SqlDatabase

PowerShell, manual release download

PS> Import-Module .\SqlDatabase.psm1

Dotnet sdk tool

$ dotnet tool install --global SqlDatabase.GlobalTool

Back to ToC

Target database type selection

The target database/server type is recognized automatically from provided connection string:

here is target MSSQL Server (keywords Data Source and Initial Catalog):

$ SqlDatabase [command] "-database=Data Source=server;Initial Catalog=database;Integrated Security=True"

PS> *-SqlDatabase -database "Data Source=server;Initial Catalog=database;Integrated Security=True"

here is target PostgreSQL (keywords Host and Database):

$ SqlDatabase [command] "-database=Host=server;Username=postgres;Password=qwerty;Database=database"

PS> *-SqlDatabase -database "Host=server;Username=postgres;Password=qwerty;Database=database"

here is target MySQL (keywords Server and Database):

$ SqlDatabase [command] "-database=Server=localhost;Database=database;User ID=root;Password=qwerty;"

PS> *-SqlDatabase -database "Server=localhost;Database=database;User ID=root;Password=qwerty;"

Back to ToC

Execute script(s)

execute script from file "c:\Scripts\script.sql" on [MyDatabase] on server [MyServer] with "Variable1=value1" and "Variable2=value2"

$ SqlDatabase execute ^
      "-database=Data Source=server;Initial Catalog=database;Integrated Security=True" ^
      -from=c:\Scripts\script.sql ^
      -varVariable1=value1 ^
      -varVariable2=value2

PS> Execute-SqlDatabase `
      -database "Data Source=server;Initial Catalog=database;Integrated Security=True" `
      -from c:\Scripts\script.sql `
      -var Variable1=value1,Variable2=value2 `
      -InformationAction Continue

See more details here.

Back to ToC

Export data from a database to sql script (file)

export data from sys.databases view into "c:\databases.sql" from "MyDatabase" on "server"

$ SqlDatabase export ^
      "-database=Data Source=server;Initial Catalog=database;Integrated Security=True" ^
      "-fromSql=SELECT * FROM sys.databases" ^
      -toFile=c:\databases.sql

PS> Export-SqlDatabase `
      -database "Data Source=server;Initial Catalog=database;Integrated Security=True" `
      -fromSql "SELECT * FROM sys.databases" `
      -toFile c:\databases.sql `
      -InformationAction Continue

See more details here.

Back to ToC

Create a database

create new database [MyDatabase] on server [MyServer] from scripts in [Examples\CreateDatabaseFolder] with "Variable1=value1" and "Variable2=value2"

$ SqlDatabase create ^
      "-database=Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=True" ^
      -from=Examples\CreateDatabaseFolder ^
      -varVariable1=value1 ^
      -varVariable2=value2

PS> Create-SqlDatabase `
      -database "Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=True" `
      -from Examples\CreateDatabaseFolder `
      -var Variable1=value1,Variable2=value2 `
      -InformationAction Continue

See more details here.

Back to ToC

Migrate an existing database

upgrade existing database [MyDatabase] on server [MyServer] from scripts in Examples\MigrationStepsFolder with "Variable1=value1" and "Variable2=value2"

$ SqlDatabase upgrade ^
      "-database=Data Source=server;Initial Catalog=MyDatabase;Integrated Security=True" ^
      -from=Examples\MigrationStepsFolder ^
      -varVariable1=value1 ^
      -varVariable2=value2

PS> Upgrade-SqlDatabase `
      -database "Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=True" `
      -from Examples\MigrationStepsFolder `
      -var Variable1=value1,Variable2=value2 `
      -InformationAction Continue

See more details here.

Back to ToC

Scripts

• .sql a text file with sql scripts
• .ps1 a text file with PowerShell script, details are here
• .dll or .exe an .NET assembly with a script implementation, details are here

Back to ToC

Variables

In a sql text file any entry like {{VariableName}} or $(VariableName) is interpreted as variable and has to be changed (text replacement) with a value before script execution. The variable name is

• a word from characters a-z, A-Z, 0-9, including the _ (underscore) character
• case insensitive

Example

-- script.sql
PRINT 'drop table {{Schema}}.{{Table}}'
DROP TABLE [{{Schema}}].[{{Table}}]

# execute script.sql
$ SqlDatabase execute -from=script.sql -varSchema=dbo -varTable=Person
PS> Execute-SqlDatabase -from script.sql -var Schema=dbo,Table=Person -InformationAction Continue

# log output
script.sql ...
   variable Schema was replaced with dbo
   variable Table was replaced with Person

-- script at runtime
PRINT 'drop table dbo.Person'
DROP TABLE [dbo].[Person]

Example how to hide variable value from a log output

If a name of variable starts with _ (underscore) character, for instance _Password, the value of variable will not be shown in the log output.

-- script.sql
ALTER LOGIN [sa] WITH PASSWORD=N'{{_Password}}'

# execute script.sql
$ SqlDatabase execute -from=script.sql -var_Password=P@ssw0rd
PS> Execute-SqlDatabase -from script.sql -var _Password=P@ssw0rd -InformationAction Continue

# log output
script.sql ...
   variable _Password was replaced with [value is hidden]

-- script at runtime
ALTER LOGIN [sa] WITH PASSWORD=N'{{P@ssw0rd}}'

A non defined variable`s value leads to an error and stops script execution process.

The variable value is resolved in the following order:

1. check command line
2. check environment variables (Environment.GetEnvironmentVariable())
3. check configuration file

Predefined variables

• DatabaseName - the target database name, see connection string (-database=...Initial Catalog=MyDatabase...)
• CurrentVersion - the database/module version before execution of a migration step
• TargetVersion - the database/module version after execution of a migration step
• ModuleName - the module name of current migration step, empty string in case of straight forward upgrade

Back to ToC

*.zip files

Parameters -from and -configuration in the command line interpret .zip files in the path as folders, for example

• -from=c:\scripts.zip\archive\tables.zip\demo
• -from=c:\scripts.zip\archive\tables.zip\table1.sql
• -configuration=c:\scripts.zip\app.config

Back to ToC

VS Package manager console

For integrating SqlDatabase into the Visual studio package manager console please check this example.

Back to ToC

Examples

• create ms sql server linux docker image
• execute script(s)
• export data
• create a database
• upgrade an existing database
• how to use SqlDatabase in the VS Package manager console
• configuration file
• assembly script

Back to ToC

License

This tool is distributed under the MIT license.

Back to ToC