Giter Club home page Giter Club logo

max-ieremenko / sqldatabase Goto Github PK

View Code? Open in Web Editor NEW
13.0 3.0 7.0 9.05 MB

Command-line tool and PowerShell module for MSSQL Server and PostgreSQL.

License: MIT License

C# 88.92% Shell 1.22% PowerShell 8.72% TSQL 0.67% Dockerfile 0.47% Smalltalk 0.01%
sqlserver migration-tool c-sharp command-line-tool miration-step sql-script sql-database database-migrations sqlcmd export-data sql-server powershell sqldatabase postgresql postgresql-tool mysql mysql-database

sqldatabase's Introduction

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 6.1+ and PowerShell Desktop 5.1.

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

Command-line tool is compatible with .net runtime 8.0, 7.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

sqldatabase's People

Contributors

max-ieremenko avatar parmcoder avatar qoniac-max avatar

Stargazers

avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar

Watchers

avatar avatar avatar

Forkers

manhnguyenv justo1 sbthofra zahidsqldba parmcoder ithhlvn

sqldatabase's Issues

Powershell: Directory not found. 

PS> cd "c:\my-scripts"
PS> New-SqlDatabase -Database ... -from .\new\
Directory C:\Program Files\PowerShell\7\.\new not found.

Expected:
New-SqlDatabase runs scripts from c:\my-scripts\new folder

.ps1 scripts support

Implement powershell (.ps1) scripts support for commands execute, create and upgrade, like .dll

For example

$ SqlDatabase execute ^
      "-database=Data Source=server;Initial Catalog=database;Integrated Security=True" ^
      -from=c:\Scripts\script.ps1 ^
      -varTableName=dbo.DemoTable

PS> Execute-SqlDatabase `
      -database "Data Source=server;Initial Catalog=database;Integrated Security=True" `
      -from c:\Scripts\script.ps1 `
      -var TableName=dbo.DemoTable `
      -InformationAction Continue

where script.ps1

[CmdletBinding(SupportsShouldProcess=$true)] # indicates that the script implementation supports -WhatIf scenario
param (
    $Command, # instance of SqlCommand, $null in case -WhatIf
    $Variables # access to variables
)

if (-not $Variables.TableName) {
    throw "Variable TableName is not defined."
}

if ($WhatIfPreference) {
    # handle -WhatIf scenario
    return
}

Write-Information "start execution"

$Command.CommandText = ("print 'current database name is {0}'" -f $Variables.DatabaseName)
$Command.ExecuteNonQuery()

$Command.CommandText = ("drop table {0}" -f $Variables.TableName)
$Command.ExecuteNonQuery()

Write-Information "finish execution"

Log file option

For all commands add optional parameter -log "path\to\log\file.log"

$ SqlDatabase execute|create|upgrade|export -log=c:\log.txt

Expectation is to see output in c:\log.txt

Handle dataselection in .sql scripts

example scripts.sql

CREATE TABLE dbo.Demo(Id INT, Name NVARCHAR(20))
GO

INSERT INTO dbo.Demo VALUES(1, 'name 1')
INSERT INTO dbo.Demo VALUES(2, 'name 2')
GO

PRINT 'dbo.Demo content'
SELECT * FROM dbo.Demo

run script

$ SqlDatabase execute|create|upgrade -from=c:\Scripts\script.sql ...

Expectation is to see in the log output values, provided by "SELECT * FROM dbo.Demo"

dbo.Demo content // already supported
1, name 1
2, name 2

Postgres: run scripts as a non-superuser to check if they can do something silly

It is related to the discussion in the PR #50.

Created by @parmcoder

Use-case

Somehow, I want to be able to run some scripts as a non-superuser to check if they can do something silly.
Not sure if I did that correctly, I wrote something like SqlDatabase -database='Host=localhost;Username=myuser;Password=qwerty; Database=MyDatabase' -fromSql='SQL' [command].

Problem

I technically wanna quickly create a database from a new cluster and run some scripts using myuser, but it will instead log me into the myuser database.
That database exists because I used your script to create it at first. Then, when I try to create new databases using another user, it breaks.

Type [System.DateTimeOffset] is not supported

Hello @max-ieremenko,

I started to use your cool library for exporting data from MSSQL database, however I got into this error in tables using DateTimeOffset column.

Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64)
        Jun 15 2019 00:26:19
        Copyright (C) 2017 Microsoft Corporation
        Express Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19041: ) (Hypervisor)

export from1.sql ...
Type [System.DateTimeOffset] is not supported.
   at SqlDatabase.Export.SqlWriter.Value(Object value) in D:\Work\SqlDatabase\Sources\SqlDatabase\Export\SqlWriter.cs:line 145
   at SqlDatabase.Export.DataExporter.Export(IDataReader source, String tableName) in D:\Work\SqlDatabase\Sources\SqlDatabase\Export\DataExporter.cs:line 47
   at SqlDatabase.Commands.DatabaseExportCommand.ExportScript(IDataExporter exporter, IScript script, Int32& readerIndex) in D:\Work\SqlDatabase\Sources\SqlDatabase\Commands\DatabaseExportCommand.cs:line 90
   at SqlDatabase.Commands.DatabaseExportCommand.ExecuteCore() in D:\Work\SqlDatabase\Sources\SqlDatabase\Commands\DatabaseExportCommand.cs:line 44
   at SqlDatabase.Commands.DatabaseCommandBase.Execute() in D:\Work\SqlDatabase\Sources\SqlDatabase\Commands\DatabaseCommandBase.cs:line 21
   at SqlDatabase.Program.ExecuteCommand(ICommandLine cmd, ILogger logger) in D:\Work\SqlDatabase\Sources\SqlDatabase\Program.cs:line 47

Any options how to suppress output from Execute-SqlDatabase or run silently?

I am developing some pester scripts and I want to suppress the output of the Execute-SqlDatabase function. Any ideas on how to do that? I am running the following Powershell

$PSVersionTable.PSVersion
Major Minor Build Revision

5 1 17763 316

Script 1.7.6 SqlDatabase {Invoke-SqlDatabase, New-SqlDatabase, Update-SqlDatabase, Create-SqlDatabase...}

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.