CLI for scripting SQL objects into a flat file structure for use with source control systems.
- ๐ Works with any source control system like Git, SVN, Mercurial, etc.
- โจ Supports all recent version of Microsoft SQL Server.
- ๐ Free and open source!
npm install -g sql-source-control
Commands are directory specific, so run all commands in the directory you want the scripts created in.
ssc --help
Note: Make sure to enable TCP/IP in "SQL Server Network Configuration" settings (instructions). If TCP/IP is not enabled, you may receive a "failed to connect" error on commands.
This will ask you a bunch of questions, and then write a config file for you.
If the current directory contains a Web.config
file with the connectionStrings
property, the
first node will be used for default values. Alternatively, a path to a Web.config
file can be
specified with the --webconfig
flag.
Options:
Option | Alias | Type | Description | Default |
---|---|---|---|---|
--force |
-f |
boolean |
Overwrite an existing config file, if present. | n/a |
--skip |
-s |
boolean |
Use defaults and not prompt you for any options | n/a |
--webconfig |
-w |
string |
Relative path to a Web.config file. |
n/a |
List all available connections specified in the configuration file.
Options:
Option | Alias | Type | Description | Default |
---|---|---|---|---|
--config |
-c |
string |
Relative path to config file. | ssc.json |
Generate SQL files for all tables, stored procedures, functions, etc. All scripts will be put in
the output.root
directory and SQL scripts will be organized into subdirectories (based on config
file).
Within the output.root
directory, cache.json
is automatically generated and is intended to be committed into source
repositories. This file stores checksums of each file for comparison, to reduce disk I/O.
Data can be included in the via the data
option in the configuration file. All tables included in the data
option
will result in a file that truncates the table and inserts all rows. Because a truncate is issued, it is recommended to
only include static data tables, like lookup tables, in the data
configuration.
Arguments:
Argument | Description | Default |
---|---|---|
conn |
Optional name of the connection to use. | First available connection from config. |
Options:
Option | Alias | Type | Description | Default |
---|---|---|---|---|
--config |
-c |
string |
Relative path to config file. | ssc.json |
Example output (see here for full example):
./_sql-database
./data
dbo.easy-lookup.sql
...
./functions
dbo.complex-math.sql
dbo.awesome-table-function.sql
...
./jobs
amazing-things.sql
...
./schemas
dbo.sql
...
./stored-procedures
dbo.people-read.sql
...
./tables
dbo.people.sql
...
./types
dbo.people-type.sql
...
./views
dbo.super-cool-view.sql
...
Execute all local scripts against the requested database.
Arguments:
Argument | Description | Default |
---|---|---|
conn |
Optional name of the connection to use. | First available connection from config. |
Options:
Option | Alias | Type | Description | Default |
---|---|---|---|---|
--config |
-c |
string |
Relative path to config file. | ssc.json |
--skip |
-s |
boolean |
Skip user warning prompt. | false |
Configuration options are stored in a ssc.json
file. The following properties are supported:
connections (object[]
, string
): Relative path to a Web.config
file with connectionStrings
, a
ssc-connections.json
file with an array of connections, or an array of connections with the following properties:
Property | Type | Description | Default |
---|---|---|---|
name |
string |
Connection name. | n/a |
server |
string |
Server name. | n/a |
database |
string |
Database name. | n/a |
port |
number |
Server port. | n/a |
user |
string |
Login username. | n/a |
password |
string |
Login password. | n/a |
files (string[]
): Optional. Glob of files to include/exclude during the pull
command. Default includes all files.
data (string[]
): Optional. Glob of table names to include for data scripting during the pull
command. Default
includes none.
output (object
): Optional. Defines paths where files will be scripted during the pull
command. The following
properties are supported:
Property | Type | Description | Default |
---|---|---|---|
root |
string |
Directory for scripted files, relative to config file. | ./_sql-database |
data |
string |
Subdirectory for data files. | ./data |
functions |
string |
Subdirectory for function files. | ./functions |
jobs |
string |
Subdirectory for jobs files. | ./jobs |
procs |
string |
Subdirectory for stored procedure files. | ./stored-procedures |
schemas |
string |
Subdirectory for schema files. | ./schemas |
tables |
string |
Subdirectory for table files. | ./tables |
triggers |
string |
Subdirectory for trigger files. | ./triggers |
types |
string |
Subdirectory for table valued parameter files. | ./types |
views |
string |
Subdirectory for view files. | ./views |
idempotency (object
): Optional. Defines what type of idempotency will scripted during the pull
command. The
following properties are supported.
Property | Type | Description | Default |
---|---|---|---|
data |
string (2) |
Idempotency for data files. | truncate |
functions |
string (1) |
Idempotency for function files. | if-exists-drop |
jobs |
string (1) |
Idempotency for job files. | if-exists-drop |
procs |
string (1) |
Idempotency for stored procedure files. | if-exists-drop |
tables |
string (1) |
Idempotency for table files. | if-not-exists |
triggers |
string (1) |
Idempotency for trigger files. | if-exists-drop |
types |
string (1) |
Idempotency for user defined table parameter files. | if-not-exists |
views |
string (1) |
Idempotency for view files. | if-exists-drop |
if-exists-drop
,if-not-exists
, orfalse
.delete-and-reseed
,delete
,truncate
, orfalse
.
includeConstraintName (boolean
): Optional. Indicates if constraint names should be scripted. Default is false
.
eol (string
): Optional. Line ending character (auto
, crlf
, or lf
). Default is auto
.
Note: See Git documentation for information about how Git handles line endings.
Basic connections.
{
"connections": [
{
"name": "dev",
"server": "localhost\\development",
"database": "awesome-db",
"port": 1433,
"user": "example",
"password": "qwerty"
}
]
}
Connections stored in Web.config
file. The Web.config
should be an XML .NET config file.
{
"connections": "./Web.config"
}
Connection strings can follow any of the following formats:
<connectionStrings>
<add name="Example1" connectionString="server=MySqlServer;database=MySqlDb;uid=MyUsername;password=MyPassword;" />
<add name="Example2" connectionString="server=MySqlServer;database=MySqlDb;uid=MyUsername;pwd=MyPassword;" />
<add name="Example3" connectionString="server=MySqlServer,1433;database=MySqlDb;uid=MyUsername;pwd=MyPassword;" />
</connectionStrings>
Connections stored in separate JSON file. Storing connections in a separate JSON can be used in conjunction with a
.gitignore
entry to prevent user connections or sensitive data from being committed.
{
"connections": "./ssc-connections.json"
}
Only include certain files.
{
// ...
"files": ["dbo.*"]
}
Exclude certain files.
{
// ...
"files": ["*", "!dbo.*"]
}
Only include certain tables.
{
// ...
"data": ["dbo.*"]
}
Exclude certain tables.
{
// ...
"data": ["*", "!dbo.*"]
}
Override default options.
{
// ...
"output": {
"root": "./my-database",
"procs": "./my-procs",
"triggers": false
}
}
Override default options.
{
// ...
"idempotency": {
"triggers": false,
"views": "if-not-exists"
}
}
Default configuration values.
{
"connections": [],
"files": [],
"data": [],
"output": {
"root": "./_sql-database",
"data": "./data",
"functions": "./functions",
"jobs": "./jobs",
"procs": "./stored-procedures",
"schemas": "./schemas",
"tables": "./tables",
"triggers": "./triggers",
"types": "./types",
"views": "./views"
},
"idempotency": {
"data": "truncate",
"functions": "if-exists-drop",
"jobs": "if-exists-drop",
"procs": "if-exists-drop",
"tables": "if-not-exists",
"triggers": "if-exists-drop",
"types": "if-not-exists",
"views": "if-exists-drop"
}
}
Clone the repo and run the following commands in the sql-source-control
directory:
npm install
npm link
npm run build
To use local docker container:
npm run docker:up
./docker/restore.ps1
ssc pull -c ./docker/config.json