SQL Source Control

CLI for scripting SQL objects into a flat file structure for use with source control systems.

Features
Installation
Usage
Configuration
Examples
- Connections
- Files
- Data
- Output
- Idempotency
- Defaults
Development

Features

😎 Works with any source control system like Git, SVN, Mercurial, etc.
✨ Supports all recent version of Microsoft SQL Server.
🎁 Free and open source!

Installation

npm install -g sql-source-control

Usage

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.

`ssc init`

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

`ssc list`

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`

`ssc pull [conn]`

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
    ...

`ssc push [conn]`

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

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, or false.
delete-and-reseed, delete, truncate, or false.

includeConstraintName (boolean): Optional. Indicates if constraint names should be scripted. Default is false.

Examples

Connections

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"
}

Files

Only include certain files.

{
  // ...
  "files": ["dbo.*"]
}

Exclude certain files.

{
  // ...
  "files": ["*", "!dbo.*"]
}

Data

Only include certain tables.

{
  // ...
  "data": ["dbo.*"]
}

Exclude certain tables.

{
  // ...
  "data": ["*", "!dbo.*"]
}

Output

Override default options.

{
  // ...
  "output": {
    "root": "./my-database",
    "procs": "./my-procs",
    "triggers": false
  }
}

Idempotency

Override default options.

{
  // ...
  "idempotency": {
    "triggers": false,
    "views": "if-not-exists"
  }
}

Defaults

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"
  }
}

Development

Clone the repo and run the following commands in the sql-source-control directory:

npm install
npm link
npm run build

patelriki13/sql-source-control

SQL Source Control

Table of Contents

Features

Installation

Usage

`ssc init`

`ssc list`

`ssc pull [conn]`

`ssc push [conn]`

Configuration

Examples

Connections

Files

Data

Output

Idempotency

Defaults

Development