JSON TRANS SQLIFY
This library aims to abstract away some of the common functilnality involved when transforming and loading JSON into a Mysql database. By common functionality I'm specifically referring to (Validate, Transform, Insert/Update)
Contents
How does it works
npm install json-transqlify
You define how your entire TL(Transform Load) pipeline should look like by using a yaml
definition file.
Each definition file consistes of 2 main secions.
- Validator: which uses
json-schema
to validate the entity you are trying to TL. - Loaders: which does the actually Insert, Update to the specific tables
Assuming you have a bunch of user objects that you would like to insert to your db. Each user has the following fields
The user Table schema is
( `id` int(11) NOT NULL AUTO_INCREMENT, `fname` varchar(45) NOT NULL, `lname` varchar(45) NOT NULL, `age` int(11) NOT NULL, `country` varchar(45) NOT NULL, `city` varchar(45) NOT NULL, PRIMARY KEY (`id`))
A Simple definition file that TL(Transforms and Loads) user objects might look like this
version: 1.0validator: schema: # validate user object schema default: user-schema.jsonloaders: # notice loaders is an array - insert: label: InsertUser # name of this operation (can be anything) tableName: users # table to which the json will be inserted transform: columns: # map each column to appropreiate field on json - column: fname # insert into a column name fname value: $entity.name.split(' ')[0] # $entity refers to the user object we are inserting. - column: lname value: $entity.name.split(' ')[1] # grap last name - column: country value: $entity.address.country - column: city value: $entity.address.city - column: age value: $entity.age
The user-schema.json
uses json-schema
rules to validate each user object you are trying to insert.
All is left is to construct a json transqlifier object
const createFactory = createFactory; const db = host: 'localhost' user: 'root' password: '' database: 'json_transqlify_demos' connectionLimit: 2 const factory = const transqlifier = factory; const obj = name: "Harry Potter" age: 10 address: city: 'UK' country: 'Little Whinging' ; ;
Please refer to examples folder
API
The definition file consists of the following sections
Version
should be 1.0 for now
version: 1.0
Validator
The Validator filters out entities before they get handed to the Loaders. There are two kind of validators:
1. Schema
a schema validator can be defined using json files to describe how the entity schema should look like. Underneath the hood Json Transqlifier uses AJV implementation of Json Schema
The schema file for the entity should go under the default
section (refer to the example below). While any $ref
defniitions can used to load any additional definitions that the default schema might refer to.
For example, to write a validator for the following user object
We might break the validator into two schema definitions into User and Address1. user-definition.json```json2. address-definition.json```json
Then we can reference both schemas like this
version: 1.0validator: schema: default: user-schema.json #a json file containing the JSON-SCHEMA definition for root entity refs: id: Address file: address-schema.json
2. Func Validator
when a schema
validator is not enough you can have more control by providing a custom function validator.
The function should be defined in a seperate file and exposed as a default export
// is-odd.jsconst isOdd = num % 2moduleexports = isOdd
Then refeence the custom validator in the Transqlifier definition file
version: 1.0validator: func: is-odd.js
Transformers
Transformers are defined as part lof loaders
. They map the given $entity
to table columns.
1. columns
The columns transformers allows you to map $entity
to table columns by defining custom expressions.
For example, given the User
object mentioned earlier and users
table with (fname, lname, age, country, city) columns
transformer: columns: - column: fname value: _.head($entity.name.split(' ')) # $entity refers to the object we are trying to transform. You can define here any expression you like and it will be evaluated at run time. You have access to Lodash by using (_) - column: lname value: _.tail($entity.name.split(' ')).join(' ') - column: age value: $entity.age - column: country value: $entity.address.country - column: city value: $entity.address.city
$history: if the transformer was part of multiple loaders pipeline, the $history
can be used to access values transformed via a previous loader (more on this later)
2. Func Transformer
A Custom function trasformer can be used by providing a file with a default exported function that should return a promise
//custom-transformer.jsconst func = { // the transformer will be provided the following // $entity: the entity we are currently processing // $history: in case the transformer is part of multiple pipeline loaders, $history will containg previously transformer values // $conn a connection to the db return Promisemoduleexports = func}
transformer: func: customer-transformer.js
Loaders
Loaders handle massaging the JSON (entity) and Inserting/Updating the DB.
The loaders
section is an array, so you can insert the JSON into multiple tables by defining multiple loaders.
1.Insert
The insert loaders inserts entity to a given table. It requires a transformer
to be defined.
loaders: - insert: tableName: users # table to insert entity into label: insertUser # a custom name to the loader. trasformer: # refer to transformers doc columns: - column: fname value: $entity.name
2. Update
The Update loader is used to update an existing row in db. It requires a transformer
and update condition
loaders: - update: tableName: users transformer: columns: - column: fname value: $entity.name where: query: id = ? params: - $entity.id
3. Upsert
The Upsert loader is used to insert or update (on duplicate key error) existing record. It requires a transformer
, tableName
, and label
.
loaders: - upsert: tableName: courses primaryKey: id transformer: columns: - column: title # when the title column has a unique index constraint, the existing record will get updated value: $entity.title - column: difficulty value: $entity.difficulty
primaryKey
is an optional field. It pointes to the auto incremented column (if any) in db. In case of update, it will be needed to retrieve the id
of the affected row.
checkout the examples/upsert-example
for working demo
3. Batch Insert Loader
In cases where you want to insert a bulk of data in one go. Batch Insert Loader offers a great performance gain over multiple Insert Loader
. It requires you to define transformer
, tableName
, label
and source
.
- batchInsert: tableName: users # table to insert entity into source: $entity label: insertUser # a custom name to the loader. trasformer: # refer to transformers doc columns: - column: fname value: $entity.name
Source is an expression that should return an array of items that will be inserted. For example if $entity
is
items: 'item1' 'item1'
Then source
should be defined as
source: $entity.source
In cases were $entity is the array of items you wish to insert, then deine source
as
source: $entity
3. Batch Upsert Loader
In cases where you want to insert a bulk of data in one go. Batch Upsert Loader will insert and updated existing record in one transaction. It requires you to define transformer
, tableName
, label
and source
.
- batchUpsert: source: $entity tableName: courses primaryKey: id transformer: columns: - column: title # when the title column has a unique index constraint, the existing record will get updated value: $entity.title - column: difficulty value: $entity.difficulty
Preconditions
Preconditions validate $enitity
before executing the loader, and if it returns false, the loader does not get executed
1. Expression Precondition (exp)
Evalutes a given expression at runtime that can access $entity
and $history
objects. It can also use _
lodash
loaders: - insert: transformer: columns: - column: name value: $entity.name on: # pre conditions are defined here - exp: $entity.age < 30 # only insert uses who are below 30
2. Database Query (db)
Runs a query against the db and allows you to assert the returned result.
Forexample, we want to insert a course
but avoide duplicate titles
loaders: - insert: transformer: tableName: courses columns: - column: title value: $entity.title on: # pre conditions are defined here - db: query: SELECT 1 from courses WHERE title = ? params: - $entity.title expect: $rows.length === 0 # $rows refers to the result of query
3: Custom Precondition function (func)
Executed a custom precondition function. The function is expected to return a promise that resolves to true
or `false
// custom-precondition.jsconst func = { // the precondition function will be provided the following // $entity: the entity we are currently processing // $history: in case the transformer is part of multiple pipeline loaders, $history will containg previously transformer values // $conn a connection to the db Promise}moduleexports = func
loaders: - insert: transformer: tableName: courses columns: - column: title value: $entity.title on: # pre conditions are defined here - func: custom-precondition.js
Note: preconditions are defined inside an array object. Meaning, you can provide multiple preconditions that should all resolve to true
for the loader to execute.
$history
The $history object can be accessed inside transformers
and preconditions
. It contains the result of previous loaders. For example
loaders: - insert: tableName: table1 label: InsertTable1 transformer: columns: - column: title value: $entity.title - insert: tableName: table2 label: InsertTable2 transformer: columns: - column: table1_id value: $history.InsertTable1.$insertedId # the tranformed value of each previous loader is added to $history inside <LABEL> of the loader. For Insert loaders the inserted Id for AutoIncreament columns is added to $insertedId. - column: cap_title value: $history.InsertTable.title.toUperCase() # $history has access to the transformed $entity fields
note: In case there is a precondition defined in the First Loader, and that precondition happened to evalute to false, the loader result won't be added to $history
object
tableName
the table name field defined in loader
can either be a string refering to the table name.
or it can be an expression evaulated at run time. The expression has access to the following variables
_
, R
(ramda), $entity
, $source
(in case of batchInsert and batchUpsert loaders)
loaders: - insert: tableName: exp: $entity.tableName