node-sql-connection

A module that execute migration between sql and config file.
You can create an SQL database from a config file.

A valid config file

The config file is a json file (in the future releases it will support more types of files)
The config file has two sections:

1. database
2. tables

the database interface

export interface DBConfig {
    database: string,  // the db name
    tables: EntityConfig[]  // an array of tables
}

the table interface

export interface TableConfig {
    MTDTable: {
        name: string, // the object name
        plural?:string, //the name for a list of connected items to a single one
        sqlName: string,  // the name of the table in sql
        description?: string, // a small description
        defaultColumns?: string[], // the default column/s to show when the entity is connected
        orderColumns?:string[] // the default order of the columns when quering it from the db
    },
    columns: ColumnConfig[]  // an array of columns
}

the column interface

export interface ColumnConfig {
    name: string,  // the property name
    sqlName: string, //the column's name in the SQL
    type: {
        type: string, //the sql type of the column
        allownull: boolean, //allow null
        max?: number,  // when the type is an NVARCHAR, you can add the max chracters number in a string 
        default?: any // a default value fr the column when no data has been defined 
    },
    primaryKey?: boolean, // primary key - false by default
    unique?: boolean, // unique - false by default
    isIdentity?: {
        offset: number,  // the offset number for identity column
        interval: number // the interval decrement number for identity column
    },
    foreignKey?: {
        ref_table: string,  // the referenced entity name 
        ref_column: string, // the references property name 
    }
}

config example

{
    "database":"USA",
    "tables":[
        {
            "MTDTable":{
                "name":"city",
                "sqlName":"tbl_city",
                "defaultColumns":["name", "country"],
                "orderColumns":["country", "name"]
            },
            "columns":[
                {"name":"id",
                "sqlName":"Id",
                "primaryKey":"true",
                "type":{
                    "type":"INT",
                    "allownull":"false"
                },
                "isIdentity":{
                    "offset":1,
                    "interval":1
                }
                }, {
                    "name":"name",
                    "sqlName":"Name",
                    "type":{
                        "type":"NVARCHAR",
                        "allownull":false,
                        "max":"20"
                    },
                    "unique": true
                 }, {
                    "name":"country",
                    "sqlName":"Country",
                    "type":{
                        "type":"NVARCHAR",
                        "allownull":false,
                        "max":"20"
                    }
                }
            ]
        }
    ]
}

createDB

the createDB function gets a valid json file to build the datbase in the sql add to the environment variables of your app the following variables

SQL_SERVER // the server name
SQL_USERNAME // the server username
SQL_PASSWORD  // the server password
SQL_PORT // the port
DATABASE // the default database to get to the sql (usualy master)

getObjects

the function returns data from the database, mapped to objects
params: {entityName: string, skip?:number, count?:number, url:string}

• entityName: the entityname
• url: the config file url
• skip: how many records to skip - optional 0 by default
• count: how many records to return - optional 50 by default

const data = await getObjects({entityName: 'city', skip:10, count:140, url:'./config.json'})

the function returns 140 records from the db. the query will skip the first 10 records.

getObjectsByCondition

the funcition returns records from the database by condition. the condition is a key, value object, and expand is a boolean param to indicate if each returned object should contain its connected entities. the expand param is optional, and by default it is false. the url is the address of the config file.

params: {entityName: string, condition:{[key:string]:any}, skip?:number, count?:number, url:string, expand:boolean}

const data = await getObjectsByCondition({entityName: 'city', condition:{country: 'Florida'}, count:20, url:'./config.json', expand: true})

the function will return the first 20 cities in Florida

countData

the function returns how many records fulfill to a certain condition
params: {entityName: string, condition?:{[key:string]:any}, url: string}

const count = await countData({entityName:'city', url:'./config.json'})

the function returns how many cities are in the db

const count = await countData({entityName:'city', condition:{country:'Florida', url:'./config.json'}})

the function returns how many cities are in Florida

insertItem

the function will insert a new record to the db, and if needed, it will add connected entities to the database with a transaction
params: {entityName: string, object:{[key:string]:any}, url: string}

const obj = await insertItem({entityName: 'city', object:{name:'New York', country: 'New York'}, url:'./config.json'})

updateItem

the function updates an existing object in the database
params: {entityName: string, object:{[key:string]:any}, url:string}
the object is the updated data of an object

const obj = await updateItem({entityName:'city', object:{id:3, name:'New-York'}, url:'./config.json'})

the New York city will be updated to New-York

updateItems

the function updates items that fulfill to the given condition.
params: {entityName: string, object:{[key:string]:any}, condition:{[key:string]:any}, url:string}
the object is the updated data fro all the objects

const obj = await updateItems({entityName:'city', condition:{country: 'florida'} object:{country:'Florida'}, url:'./config.json'})

all the cities that their country was florida, will be updated to Florida.

usage

run command from your app to create the database

"scripts":{
    "postbuild": "run-func node-sql-connection createDB ./data/config"
}

use the function inside the app

import {insertItem} from 'node-sql-connection'

const create = async({name, country}) => {
    
    const response = await insertItem({name, country})
    return response

}