Sqlbro builder

Tooling to quickly prototype Sqlite databases in Nodejs

Install

npm install @sqlbro/builder

Usage

Initialize the db

Define a database schema:

const schemas = [
  `CREATE TABLE "category" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "name" TEXT NOT NULL,
  "slug" TEXT NOT NULL UNIQUE,
  "icon" TEXT NOT NULL,
  "parent_id" INTEGER,
  FOREIGN KEY(parent_id) REFERENCES category(id)
)`,
  `CREATE TABLE "product" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "name" TEXT NOT NULL UNIQUE,
  "description"	TEXT NOT NULL,
  "props"	TEXT DEFAULT '{}',
  "category_id" INTEGER NOT NULL,
  FOREIGN KEY(category_id) REFERENCES category(id)
)`];

Initialize the database:

import DatabaseConstructor from "better-sqlite3";
import { readJsonFile, DbBuilder, execute } from "@sqlbro/builder";

const dbPath = "/some/path/db.sqlite";
const db = new DatabaseConstructor(dbPath);
const builder = new DbBuilder(db).init(schemas);

Insert data

Read Record<string, Array<Record<string, any>>> objects in a json file:

const jsonData = await builder.readJsonFileObjects(__dirname + "/data.json");

Or read Array<Record<string, any>> objects in a json file:

const jsonData = await builder.readJsonFileArray(__dirname + "/data.json");

Insert into the database from the json data:

builder.insertJson("category", jsonData["categories"]);

This will parse the json and convert the rows to an insert query. Possible data types:

• string: for TEXT fields
• number: for INTEGER or REAL fields
• object: any json array or object. Will be parsed into a TEXT field

Example json:

[
  {
    "id": 37,
    "name": "item",
    "props": {
      "foo": "bar"
    }
  }
]

Will be transformed into:

INSERT INTO mytable VALUES (37, "item", '{"foo": "bar"}')

Example

An example is available