Skip to content

Latest commit

 

History

History
318 lines (225 loc) · 10.8 KB

README.md

File metadata and controls

318 lines (225 loc) · 10.8 KB
Raw

SqlDatabase

NuGet NuGet PowerShell Gallery GitHub release

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

Table of Contents

Installation

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

.net tool is compatible with .net sdk 9.0, 8.0 and 6.0.

Command-line tool is compatible with .net runtime 9.0, 8.0, 6.0 and .net framework 4.7.2+.

PowerShell, from gallery

PowerShell Gallery

PS> Install-Module -Name SqlDatabase

PowerShell, manual release download

GitHub release

PS> Import-Module .\SqlDatabase.psm1

Dotnet sdk tool

NuGet

$ 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

Back to ToC

License

This tool is distributed under the MIT license.

Back to ToC