setupTable - nodef/extra-sql GitHub Wiki

Generates SQL commands to setup table (create, insert, index).

sql.setupTable(name, columns, [values], [options]);
// name:    table name
// columns: columns {name: type}
// values:  row objects {column: value}
// options: options for createTable/View/Index/, insertInto

const sql = require('extra-sql');

sql.setupTable('food', {code: 'TEXT', name: 'TEXT'});
// CREATE TABLE IF NOT EXISTS "food" ("code" TEXT, "name" TEXT);
 
sql.setupTable('food', {code: 'TEXT', name: 'TEXT'},
  [{code: 'F1', name: 'Mango'}, {code: 'F2', name: 'Lychee'}]);
// CREATE TABLE IF NOT EXISTS "food" ("code" TEXT, "name" TEXT);
// INSERT INTO "food" ("code", "name") VALUES
// ('F1', 'Mango'),
// ('F2', 'Lychee');
 
sql.setupTable('food', {code: 'TEXT', name: 'TEXT'},
  [{code: 'F1', name: 'Mango'}, {code: 'F2', name: 'Lychee'}],
  {index: true});
// CREATE TABLE IF NOT EXISTS "food" ("code" TEXT, "name" TEXT);
// INSERT INTO "food" ("code", "name") VALUES
// ('F1', 'Mango'),
// ('F2', 'Lychee');
// CREATE INDEX IF NOT EXISTS food_code_idx ON "food" ("code");
// CREATE INDEX IF NOT EXISTS food_name_idx ON "food" ("name");
 
sql.setupTable('food', {code: 'TEXT', name: 'TEXT'},
  [{code: 'F1', name: 'Mango'}, {code: 'F2', name: 'Lychee'}],
  {pk: 'code', index: true, tsvector: {code: 'A', name: 'B'}});
// CREATE TABLE IF NOT EXISTS "food" ("code" TEXT, "name" TEXT, PRIMARY KEY("code"));
// INSERT INTO "food" ("code", "name") VALUES
// ('F1', 'Mango'),
// ('F2', 'Lychee')
// ON CONFLICT ("code") DO NOTHING;
// CREATE OR REPLACE VIEW "food_tsvector" AS SELECT *, setweight(to_tsvector('english', "code"), 'A')||setweight(to_tsvector('english', "name"), 'B') AS "tsvector" FROM "food";
// CREATE INDEX IF NOT EXISTS "food_tsvector_idx" ON "food" USING GIN ((setweight(to_tsvector('english', "code"), 'A')||setweight(to_tsvector('english', "name"), 'B')));
// CREATE INDEX IF NOT EXISTS "food_name_idx" ON "food" ("name");