Git Product home page Git Product logo

sql-patch's Introduction

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

sql-patch's People

Contributors

chbrown avatar

Watchers

James Cloos avatar

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.