Sqlutils2 NPM | npm.io

SQLUtils2

Note: You must have a little knowledge of SQL

Instalation

Using npm:

npm i sqlutils2

In Node.js:

//Better-SQLite3
const Database = require("better-sqlite3")
const db = new Database("my.db")

const utilDB = require("sqlutils2").addDB(db, "better-sqlite3") //You could also use: "bsql3"

//SQLite3 And MySQL
On a future update!

NOTE All DBs should be added inside your index file!!!

Methods

If you want to get the db in another file, you can always use:

Getting a DB: .db(String)

const utilDB = require("sqlutils2").db("my.db")

Creating a table: .newTable(String, Boolean = False)

String -> The name of the table Boolean -> If true, creates the table if not exists

const table = utilDB.newTable("tableName", true)
// .add(String name, String dataType, String extra)
table.add("columnOne", "INTEGER", "PRIMARY KEY")
     .add("columnTwo", "TEXT", "NOT NULL")
     .add("columnThree", "TEXT")
// After this, RUN your query! .run(callback)
table.run(function (res) => { ... })

Inserting row into a table: .startInsert(String)

String -> The name of the table

const table = utilDB.startInsert("tableName")
// .insert(String key, String value)
table.insert("columnOne", 1)
     .insert("columnTwo", "Hello!")
     .insert("columnThree", null)
// After this, RUN your query! .run(callback)
table.run(function (res) => { ... })

Getting row(s) of a table: .startGet(String)

String -> The name of the table

const table = utilDB.startGet("tableName")
// .where(String key, String value)
table.where("columnOne", 1)
table.get("columnOne").get("columnTwo")

// After this, RUN your query! .run(callback)
table.run(function (res) => { ... })

Update row(s) of a table: .startUpdate(String)

String -> The name of the table

const table = utilDB.startUpdate("tableName")

// .set(String key, String value)
table.set("columnTwo", "Bye!")
     .set("columnThree", "Now ain't null")

// .where(String key, String value)
table.where("columnOne", 1)

// After this, RUN your query! .run(callback)
table.run(function (res) => { ... })

Verifying data in a table: .verifyData(String)

String -> The name of the table

const verify = utilDB.verifyData("tableName")

verify.exists("columnThree") //Verify if columnOne of all rows that "where" clause returns are not null
      .verify("columnTwo", "Hey!") //Verify if columnTwo of all rows that "where" clause returns are equals to "Hey!"
      .where("columnOne", 1)
      .run(function (res) => { ... })

Delete row(s) of a table: .startGet(String, Boolean)

String -> The name of the table Boolean (True) -> If true, prevents all the rows delete

const table = utilDB.startDelete("tableName")
// .where(String key, String value)
table.where("columnOne", 1)

// After this, RUN your query! .run(callback)
table.run(function (res) => { ... })

Deleting a table .deleteTable(String, Function)

String -> The name of the table Callback (null) -> If null, returns promise, otherwise callback is executed

utilDB.deleteTable("tableName", (res) => { ... })

**MIGRATION:*

If you want to migrate dbs, I am currently working on these options!

Object -> Better-SQlite3

  //First of all, create the DB as previously said
  const utilDB = require("sqlutils2").db("my.db")

  
  // .insertObject(tableName STRING, bj OBJECT, columns ARRAY = null, columnsType ARRAY = null, columnsData ARRAY = null)   returns Promise
  // On columns array, if set first element MUST BE key, the column that stores: "key1", "key2", "key3", etc.
  // All the parameters that are null by default may be omitted. If that happens, this module automatically organizes columns knowing the input object
  /* Object should be something like this: {
    "key1": {
      "column1": "",
      "column2": 0
    },
    "key2": {...},
    "key3": {...}
  }*/
  utilDB.insertObject("user_data", {
    "1": {
      "fullname": "Michael",
      "nickname": "Micky",
      "age": 28
    },
    "2": {
      "fullname": "John",
      "nickname": null,
      "age": 17
    }
  })
  //If you want to manually set-up columns...
  utilDB.insertObject("user_data", {
    "1": {
      "fullname": "Michael",
      "nickname": "Micky",
      "age": 28
    },
    "2": {
      "fullname": "John",
      "nickname": null,
      "age": 17
    }
  }, ["fullname", "nickname", "age"], ["TEXT", "TEXT", "INTEGER"], ["PRIMARY KEY", "", "NOT NULL"])

EXAMPLE:

Using better-sqlite3, I want to create a table which store users data (id, fullname, nickname, age) and do some things

const sqlutils = require("sqlutils2")
const Database = require('better-sqlite3');
const db = new Database('user_data.db', { verbose: console.log });

sqlutils.addDB(db, "bsql3");

const utilDB = sqlutils.db("user_data.db")
utilDB.newTable("info", true)
.add("id", "INTEGER", "PRIMARY KEY")
.add("fullname", "TEXT", "NOT NULL")
.add("nickname", "TEXT")
.add("age", "INTEGER", "NOT NULL")
.run((res) => {
    if(!res.completed) return console.error(res.out)
    console.log("Executed without errors!")
})

//Now I will do a function that lets me insert data to the table
function insertData(id, fullname, nickname = null, age) {
    utilDB.startInsert("info")
      .insert("id", id)
      .insert("fullname", fullname)
      .insert("nickname", nickname)
      .insert("age", age)
      .run((res) => {
        if(!res.completed) return console.error(res.out)
        console.log("Executed without errors!")
      })
}
//So now you can insert data easily!
insertData(0, "Michael", "Mick", 28)
insertData(1, "John", null, 17)

//I want to get fullname and nickname of every row where age equals 17...
utilDB.startGet("info")
  .get("fullname")
  .get("nickname")
  .where("age", 17)
  .run((res) => {
    if(!res.completed) return console.error(res.out)
    console.log("Executed without errors!")
    const data = res.out; //Array with obtained rows
  })

//Now I want to get the nicknames of every user that have at least 18 years...
utilDB.startGet("info")
  .get("nickname")
  .where("age", 18, {
    operation: ">="
  })
  .run((res) => {
    if(!res.completed) return console.error(res.out)
    console.log("Executed without errors!")
    const data = res.out; //Array with obtained rows
  })

//Oops! My fault, John is not 17, he is 18!!
utilDB.startUpdate("info")
  .set("age", 18)
  .where("id", 1)
  .run((res) => {
    if(!res.completed) return console.error(res.out)
    console.log("Executed without errors!")
  })

//I want to know if Michael's nickname is not null ...
utilDB.verifyData("info")
  .exists("nickname")
  .where("id", 0)
  .run((res) => {
    if(!res.completed) return console.error(res.out)
    const exists = res.out; //True
    console.log("Executed without errors!")
  })

//I want to know if Michael's nickname is Micky ...
utilDB.verifyData("info")
  .verify("nickname", "Micky")
  .where("id", 0)
  .run((res) => {
    if(!res.completed) return console.error(res.out)
    const exists = res.out; //False, his nickname is not Micky, it is Mick
    console.log("Executed without errors!")
  })


//Well... Michael isn't my user anymore, so bye!!
utilDB.startDelete("info")
  .where("id", 0)
  .run((res) => {
    if(!res.completed) return console.error(res.out)
    console.log("Executed without errors!")
  })