node-sql-gen
    TypeScript icon, indicating that this package has built-in type declarations

    0.0.4 • Public • Published

    node-sql-gen

    Simple sql query generator. No orm, raw sql forever!! 🎉

    Install

    # yarn
    $ yarn add node-sql-gen
    
    # npm
    $ npm install node-sql-gen

    Documents

    for more details, check src/ dir

    Functions

    • function select (table: string, fields?: string, where?: Where, orderBy?: string, limit?: Limit | string): Sql
    • function insert (table: string, data: Data): Sql
    • function update (table: string, where: Where, setData: Data): Sql
    • function del (table: string, where: Where): Sql
    • function count (table: string, where?: Where): Sql

    Type Where

    Support = > < >= <= LIKE IN Operators

    const whereExample01 = {
      id: 1, // same with id: { "=": 1 }
      nick: { LIKE: "%ben%" }, 
      age: { ">": 10, "<=": 15, [OR]: { "IN": [1, 2, 3] } }, // means (10, 15] + 1 2 3
    }

    Type Data

    Support JSON type

    const setDataExample01 = {
      id: 1,
      nick: 'newNick',
      favorite: ['games', 'food']
    }

    Error Messages

    AvailableOperator = ["LIKE", "IN", ">", "<", ">=", "<=", "=", OR]
    
    ErrorWhereNull = "where cannot be null or empty"
    ErrorSetNull = "set data cannot be null or empty"
    ErrorWhereInEmptyArray = "where condition [in] value must be array (len>0)"
    ErrorUnknownOperator = "unknown operator, only " + JSON.stringify(AvailableOperator) + " is available"

    Usage

    Exec with mysql2

    const sqlGen = require('node-sql-gen')
    const mysql2 = require('mysql2')
    
    const dbClient = mysql2.createPool({ ... })
    
    async function query (gen) {
      return dbClient.query(gen.sql, gen.args)
    }
    
    query(sqlGen.select("user")).then(console.log).catch(console.error)

    Select

    const OR = sqlGen.OR
    let s0 = sqlGen.select('user')              // { sql: 'SELECT * FROM ??', args: [ 'user' ] }
    console.log(mysql2.format(s0.sql, s0.args)) // SELECT * FROM `user`
    
    let s1 = sqlGen.select('user', 'id,order', {
    	id: 1,
    	[OR]: {
    		nick: { LIKE: 'ben%' },
    		age: { '>=': 10 },
    	},
    }, 'id DESC', { offset: 0, size: 20 })
    console.log(mysql2.format(s1.sql, s1.args))
    // { sql: 'SELECT `id`, `order` FROM ?? WHERE `id` = ? OR (`nick` LIKE ? AND `age` >= ?) ORDER BY id DESC LIMIT 0, 20', args: [ 'user', 1, 'ben%', 10 ] }
    // SELECT `id`, `order` FROM `user` WHERE `id` = 1 OR (`nick` LIKE 'ben%' AND `age` >= 10) ORDER BY id DESC LIMIT 0, 20

    Insert

    const s2 = sqlGen.insert('user', {
    	id: 1,
    	nick: 'ben',
    	null_: null,
    	int_: 10,
    	datetime_: '1901-01-01 01:01:01',
    	json: {
    		t1: false,
    	},
    	json2: ['basketball', 'football'],
    })
    console.log(mysql2.format(s2.sql, s2.args))
    // {
    //   sql: 'INSERT INTO ?? SET ?',
    //   args: [
    //     'user',
    //     { id: 1, nick: 'ben', null_: null, int_: 10, datetime_: '1901-01-01 01:01:01', json: '{"t1":false}', json2: '["basketball","football"]'}
    //   ]
    // }
    // INSERT INTO `user` SET `id` = 1, `nick` = 'ben', `null_` = NULL, `int_` = 10, `datetime_` = '1901-01-01 01:01:01', `json` = '{\"t1\":false}', `json2` = '[\"basketball\",\"football\"]'

    Update

    const updateWhere = { id: null }
    const setData = { id: 1 }
    const s3 = sqlGen.update('user', updateWhere, setData) // { sql: 'UPDATE ?? SET ? WHERE `id` IS NULL', args: [ 'user', { id: 1 } ] }
    console.log(mysql2.format(s3.sql, s3.args))            // UPDATE `user` SET `id` = 1 WHERE `id` IS NULL

    Delete

    const deleteWhere = { id: null }
    const s4 = sqlGen.del('user', deleteWhere)  // { sql: 'DELETE FROM ?? WHERE `id` IS NULL', args: [ 'user' ] }
    console.log(mysql2.format(s4.sql, s4.args)) // DELETE FROM `user` WHERE `id` IS NULL

    Count

    const countWhere = { id: { IN: [1, 2, 3] } }
    const s5 = sqlGen.count('user', countWhere) // { sql: 'SELECT COUNT(1) AS `total` FROM ?? WHERE `id` IN ?', args: [ 'user', [ [ 1, 2, 3 ] ] ] }
    console.log(mysql2.format(s5.sql, s5.args)) // SELECT COUNT(1) AS `total` FROM `user` WHERE `id` IN (1, 2, 3)

    Install

    npm i node-sql-gen

    DownloadsWeekly Downloads

    0

    Version

    0.0.4

    License

    MIT

    Unpacked Size

    35.6 kB

    Total Files

    19

    Last publish

    Collaborators

    • mustard-mh