JSite Database

Installation

npm

npm install jsite-database

Usage

const path = require("path");
const JSiteDatabase = require("jsite-database");
 
/**
 * Initiate Container
 */
let Database1 = new JSiteDatabase();
 
// ...or with options,
let Database2 = new JSiteDatabase({
    format: {
        indent: "    "
    },
    mysql: {
        host: "localhost",
        user: "AzureDiamond",
        password: "hunter2",
        database: "reddit"
    },
    sql: {
        language: "mysql",
        options: {
            quoteIdentifiers: true
        }
    },
    audit: "column",
    files: {
        db: path.join(__dirname, "private", "mysql")
    }
});
 
/**
 * Start container
 */
Database1.ready()
    .then(() => {
        // Database ready
    })
    .catch(console.log);

Documentation

Options

Name	Type (default)	Description
format	Object ({})	Arguments for "sql-formatter" module
mysql	Object ({})	Arguments for "mysql" module
sql	Object ({ language: "sqlite", options: {} })	Language & arguments for "json-sql-builder2" module
audit	String ("row")	Desired auditing method (see "Options (audit)")
files	Object ({})	Custom file location

Options (audit)

JSiteDatabase has two built-in audit methods ("row" and "column"), "row" will setup full slowly changing dimension (SCD) tables - these take the core structure of SCD 4, with some improvements. "column" will track individual field changes - this is, most of the time, worse than "row". You can also pass "all" to enable both auditing methods.

Row (table__history)

Field	Description
id__history__last	ID of previous SCD row for this record
id__history	ID of current SCD row for this record
id__history__next	ID of next SCD row for this record
(column)__updated	Whether the (column) updated in this change (1/0)
(column)__last	Previous value for the (column) for this record
(column)	Current value for the (column) for this record
(column)__next	Next value for the (column) for this record

Column (table__changes)

Field	Description
id__changes__last	ID of previous changes row for this field in this record
id__changes	ID of current changes row for this field in this record
id__changes__next	ID of next changes row for this field in this record
id	Primary key for this record
field	Field name being changed in this record
value__last	Previous value for this field in this record
value	Current value for this field in this record
value__next	Next value for this field in this record

Both (table__history, table__changes)

Both auditing tables contain the following SCD columns,

Field	Description
scd__start	Datetime (or timestamp in SQLite) for this record being used (EffecitveFrom)
scd__end	Datetime (or timestamp in SQLite) for this record being used (EffectiveTo)
scd__duration	Number of seconds this record was active for
scd__event	SQL event that caused this record (insert, update, delete)

Files

By default the file location will be: <current dir>/private/db/ (the location can be changed with the "files" option, see above) inside of here, the module will create the following structure:

└───private
    └───db
        │   index.db
        │
        ├───sql
        │       alter.sql
        │       custom.sql
        │       tables.sql
        │       views.sql
        │
        ├───tables
        │       tables1.json
        │       tables2.json
        │       tables3.js
        │       tbl1.sql
        │       tbl2.sql
        │       tbl3.sql
        │       tbl4.sql
        │
        └───views
                view1.json
                view1.sql
                view2.json
                view2.sql
                view3.sql
                view4.js
                view4.sql

By default the tables directory will be empty, you can populate this with your table schema files, these should be in JSON format. You can prefix the file with an underscore (i.e. _tables3.json) if you do not want it to be built.

---

index.db will only exist if the container has been started using sqlite

---

By default the tables.sql file will contain all of the SQL required to make your database schema, each table and trigger will use "IF NOT EXISTS", to prevent duplication or accidental removal of tables in the database. You can inspect this file to see exactly what is being executed during the .ready() process.

---

By default the alter.sql file will not be created on first startup, this file is created when the module notices differences between your database and your schema files. This file will contain the SQL needed to alter your database from what exists to what the schema describes - beware that this is only basic, it cannot handle columns being renamed or datatypes changing (currently, datatypes is an enhancement I'm looking to add). You can inspect this file to see exactly what is being executed during the .ready() process.

Tables

Your tables schema files should be in JSON format and follow the json-sql-builder format for the .$createTable() method (see their documentation). An example of this format is the following:

{
    "$table": "my_table_name",
    "$define": {
        "column_name": {
            "$column": {
                "$type": "VARCHAR",
                "$size": 11
            }
        }
    }
}

Each JSON file can contain multiple tables:

[
    {
        "$table": "my_table_name1",
        "$define": {
            "column_name": {
                "$column": {
                    "$type": "VARCHAR",
                    "$size": 11
                }
            }
        }
    },
    {
        "$table": "my_table_name2",
        "$define": {
            "column_name": {
                "$column": {
                    "$type": "VARCHAR",
                    "$size": 11
                }
            }
        }
    }
]

You can ommit the $table and $define option, if your JSON file has only one table. This will set $define to your JSON and $table to the name of the JSON file (without the .json), as an example:

my_table_name3.json

{
    "column_name": {
        "$column": {
            "$type": "VARCHAR",
            "$size": 11
        }
    }
}

...will be turned into...

{
    "$table": "my_table_name3",
    "$define": {
        "column_name": {
            "$column": {
                "$type": "VARCHAR",
                "$size": 11
            }
        }
    }
}

Under the $define property you can supply a priority (named as $priority), this will sort the table in the produced SQL file/executed queries. This is supplied along with columns so that it can be used in the minimal file format, shown above.

Todo

List of things that I'm looking to add to the module, this list is not in priority order.

• Detection of datatype changes in alter

  • Method for noticing that a column has changed datatype between the existing database and the provided schema, and automatically updating this through the alter.sql file

• Support for further database software

  • json-sql-builder supports MySQL, MariaDB, PostgreSQL, SQLite, Oracle, and Microsoft SQL Server - currently this module only supports MySQL, MariaDB and SQLite
  • sql-formatter supports "Standard SQL", Couchbase N1QL, IMB DB2, and Oracle PL/SQL - currently this module only supports "Standard SQL"

• Further SCD support

  • Currently "audit" supports "row" and "column" - update the auditing to cover all SCD methods ("SCD2", "SCD3", etc.) to allow more flexibility with existing systems

• Built-in validation

  • Allow end-users to pass an object into the module and have it validated against the schema, for instance an object with the property "column_name" would have the value checked against the "column_name" in the table and checked for type, length, etc.

• Implementation of previous version "get", "put", "patch", "delete"

  • Previous versions of this module had methods for get/put/patch/delete that allowed data to be safely modified in the database by providing a table and then the changes requested - checking for SQL injection, column names, etc.