slite-change

A simple change management system for SQLite3 written in pure JavaScript/TypeScript. This is for on-server programs like backend services or utilities.

Getting started

Installing

From within your project directory where package.json is found, simply run this.

npm install slite-change

This library reads a sequence of SQL files and applies those as changes to an SQLite3 database.

Only new changes are applied. A change log table maintains the tracking of what changes are already applied.

Change files are simple SQL, because XML sucks!

Formating Of Change Sets

Each change must begin with a comment line starting with --changeset <Author Name>:<change ID>

Each change set file is read in the order of directory listing. Change ID values are integers. They must never repeat. They are applied in the order they are found in the changeset file.

NOTE: Always start the SQL with BEGIN; and end it with COMMIT;. This provides transactions so that no changes are made if there is an error in the SQL.

This is an example of a simple change set.

--changeset Royce:1
BEGIN;
CREATE TABLE 'users' (
  'id' int NOT NULL,
  'firstname' VARCHAR(50),
  'lastname' VARCHAR(50),
  'username' VARCHAR(50),
  'passwd' VARCHAR(50),
  'emailAddress' VARCHAR(50),
  PRIMARY KEY ('id'));

-- Comment lines are supported
INSERT INTO 'users' (id, firstname, lastname, username, passwd, emailAddress) VALUES (0, 'admin', 'admin', 'admin', 'password', 'roo@localhost');

CREATE TABLE 'config' (
  'parameter' VARCHAR(45) NULL,
  'value' VARCHAR(45) NULL,
  PRIMARY KEY ('parameter'));

INSERT INTO 'config' ('parameter','value') VALUES ('db_version', '1');
COMMIT;

Once a change set has been applied to a database, no more changes should be made to that change ID, additional changes should be added to a new changeset file with the next value of change ID.

Example changes can be found in db-changes

Usage

Put SQL files into a resources directory and create an instance of slite-change with the arguments of the DB instance and change directory.

import SliteChange from 'slite-change';
...
  this.Sql = new sqlite.Database('my_database.db', (err) => {
    if (err) {
      console.log('Could not connect to database', err)
    } else {
      console.log('Connected to database');
      new SliteChange(this.Sql, './src/resources/db-changes/', (ReturnMsg) => {
        if(ReturnMsg != '') {
          console.log('Error: %s', ReturnMsg);
        }
      });
    }
  });

Using Stored Procedures

Sqlite does not support stored procedures but slite-change does. Stored procedures are written to the SQL files and can be called using the tag @Procedure followed by the function name and arguments to be passed to the function.

The function called is responsible for parsing the argument string.

-- Sample SQL to call "AddUser" function with a string of arguments.
@Procedure AddUser guest guest guest guest guest

import SliteChange from 'slite-change';
...
  this.Sql = new sqlite.Database('my_database.db', (err) => {
    if (err) {
      console.log('Could not connect to database', err)
    } else {
      console.log('Connected to database');
      new SliteChange(this.Sql, './src/resources/db-changes/', (ReturnMsg) => {
        if(ReturnMsg != '') {
          console.log('Error: %s', ReturnMsg);
        }
      });
    }
  }, [
    { procName: 'AddUser', procFunc: (args: string) => { this.AddUser(args); } }
  ]);
...

	private AddUser(args: string) {
		let argsArray = args.split(' ');
		this.Sql.all('INSERT INTO users (firstname, lastname, username, password, emailAddress) \
			VALUES (?, ?, ?, ?, ?)', argsArray, (error) => {
				if(error) {
					console.error(error.message);
				}
		});
	}

NOTE: Procedure calls are made after the commit.

Support

Send emails to [email protected]

Buttons generated by Shields IO

Contributing

Anyone is welcome to fork this project on GitLab slite-change

Submit a pull request and it will be reviewed.

Authors and acknowledgment

SQLite3 team!!! 🍺🍺🍺

License

Copyright (C) 2021 Silicon Tao Technology Systems

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; GPL-2.0-only.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program; if not, write to

Free Software Foundation
51 Franklin Street, Fifth Floor
Boston, MA 02110
USA

Project status

This is a new project and the author's first time posting to NPM. If something is not working please send an email to the contact above.

Unit tests have been added to validate features.

Developing This Project

One-time only install and setup.

npm install --global np
npm i -g typescript -D
npm i -g typings -D
tsc --init

tsc is configured to compile in package.json in scripts.

To compile

tsc

Commit to GitLab.com

git commit
git push

Publishing is done by np

np

To publish a beta version for testing a branch in development.

npm run beta

Testing testing with Mocha. Installing globally because the library user will have their own tests.

npm install mocha -g
npm install ts-mocha -g
npm install --save-dev @types/mocha

To run the tests.

npm run test

# Or as defined in package.json
ts-mocha test/*.spec.ts

How to Unit Test with NodeJS? TypeScript wrapper for Mocha