# sql-patch

> SQL patch migration tool

Latest version **1.2.2** (published 2018-05-10) · MIT license · 0 weekly downloads

## Install

```sh
npm install sql-patch
pnpm add sql-patch
yarn add sql-patch
bun add sql-patch
```

Provides the command `sql-patch`.

## Health

**Score 15/100 (F)** — status: abandoned.

Positive: no vulnerabilities.

Warnings: low downloads; no types; no esm support.

Negative: abandoned; low maintenance score.

## Facts

| | |
|---|---|
| Version | 1.2.2 |
| Published | 2018-05-10 |
| First published | 2015-12-04 |
| Weekly downloads | 0 |
| License | MIT |
| TypeScript types | none |
| Module format | CommonJS |
| Dependencies | 3 |
| Unpacked size | 11.7 KB |
| Known vulnerabilities | 0 (+2 in 1 direct dependencies) |
| Install scripts | no |
| GitHub stars | 0 |
| Author | Christopher Brown |
| Maintainers | chbrown |
| Keywords | sql, migrate |

## Links

- npm: https://www.npmjs.com/package/sql-patch
- Repository: https://github.com/chbrown/sql-patch
- Issues: https://github.com/chbrown/sql-patch/issues
- npm.io page: https://npm.io/package/sql-patch

## Dependencies (3)

- [sqlcmd](https://npm.io/package/sqlcmd.md) ^3.0.3
- [minimist](https://npm.io/package/minimist.md) 1.2.0
- [sqlcmd-pg](https://npm.io/package/sqlcmd-pg.md) ^3.0.2

## Recent versions

- 1.2.2 (latest) — 2018-05-10
- 1.2.1 — 2018-05-10
- 1.2.0 — 2018-05-10
- 1.1.0 — 2018-02-08
- 1.0.1 — 2018-01-27
- 1.0.0 — 2018-01-27
- 0.1.6 — 2016-01-24
- 0.1.5 — 2015-12-30
- 0.1.4 — 2015-12-28
- 0.1.3 — 2015-12-24
- 0.1.2 — 2015-12-24
- 0.1.1 — 2015-12-04
- 0.1.0 — 2015-12-04

## README

# sql-patch

Up/down migrations with some arbitrary DSL/ORM are tedious.

This library (and command line script) provides a tool for applying all of the SQL commands in some directory in order, idempotently. I.e., given a database name and directory:

1. Create the database (if needed)
2. Create a table to track the patches that have been applied (if needed)
3. Read the `*.sql` files from the directory and execute them in order if they haven't been executed before
4. Record which files have been executed, so that subsequent calls won't execute them again

It's simpler than typical (say, Rails) migrations. Patches are one-way, and SQL only.


## CLI example

Create a new database if needed: `createdb buyersdb` (only PostgreSQL for now; see [To-do](#to-do) below)

`migrations/01-initial.sql`:

    CREATE TABLE customer (
      id SERIAL PRIMARY KEY,
      fullname TEXT,
      created TIMESTAMP DEFAULT current_timestamp
    );
    INSERT INTO customer (fullname) VALUES ('Lance Moore'), ('Phil Lester');

Running `sql-patch migrations/ --database buyersdb` will create a new table, if needed, to track migration execution. This meta-table will be created in the "buyersdb" database itself, and by default is named "\_schema\_patches". It will then execute the contents of the `migrations/01-initial.sql` file as a single query in that database.

Our table now looks like this:

| id | fullname    | created
|:---|:------------|:-------------------
| 1  | Lance Moore | 2015-12-23 21:45:09
| 2  | Phil Lester | 2015-12-23 21:45:09

Running `sql-patch migrations/ --database buyersdb` again will have no effect, even if you change the contents of `01-initial.sql`. Thus you should never change the contents of a migration once it's been used.

Suppose we later decide a single "fullname" field is sloppy.

`migrations/02-split-name.sql`:

    ALTER TABLE customer
      ADD COLUMN firstname TEXT,
      ADD COLUMN lastname TEXT;
    UPDATE customer SET firstname = substring(fullname from '(.*) '),
                        lastname  = substring(fullname from ' (.*)');
    ALTER TABLE customer DROP COLUMN fullname;

Running `sql-patch migrations/ --database buyersdb` now will read the "\_schema\_patches" table, find that `02-split-name.sql` has not been applied, and run it.

Our amended table now looks like this:

| id | created             | firstname | lastname
|:---|:--------------------|:----------|:--------
| 1  | 2015-12-23 21:45:09 | Lance     | Moore
| 2  | 2015-12-23 21:45:09 | Phil      | Lester


## API example

This example does pretty much the same thing, even creating the database if needed, from a Node.js script.

    const path = require('path');
    const Connection = require('sqlcmd-pg').Connection;
    const sqlPatch = require('sql-patch');

    const db = new Connection({
      user: 'postgres',
      database: 'buyersdb',
    });

    db.createDatabaseIfNotExists(function(err) {
      if (err) throw err;

      var patches_dirpath = path.join(__dirname, 'migrations');
      sqlPatch.executePatches(db, '_schema_patches', patches_dirpath, function(err) {
        if (err) throw err;

        console.log('DONE');
        process.exit();
      });
    });


## To-do

* [ ] Support engines other than PostgreSQL
* [ ] Run each patch within a transaction (if supported by the engine)


## License

Copyright 2015 Christopher Brown. [MIT Licensed](http://chbrown.github.io/licenses/MIT/#2015)

---
_Source: https://npm.io/package/sql-patch · Machine-readable twin of the npm.io package page. Health data is recomputed on every publish._