sql-source-control

    3.0.0 • Public • Published

    NPM Version CI Build status Dependency Status Dev Dependency Status

    SQL Source Control

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

    Table of Contents

    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
    1. if-exists-drop, if-not-exists, or false.
    2. delete-and-reseed, delete, truncate, or false.

    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.

    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

    To use local docker container:

    npm run docker:up
    ./docker/restore.ps1
    
    ssc pull -c ./docker/config.json

    Install

    npm i sql-source-control

    DownloadsWeekly Downloads

    21

    Version

    3.0.0

    License

    MIT

    Unpacked Size

    188 kB

    Total Files

    45

    Last publish

    Collaborators

    • justinlettau