@miqro/query

very experimental query building wrapper for sqlite3 and pg. DO NOT USE only for testing.

• connection
  • sqlite3
  • postgres
• query
  • raw
  • select
  • insert
  • update
  • delete
  • create database
  • create table
  • drop table
  • clone
  • statement

connection

sqlite3

install the sqlite3 module.

npm install sqlite3 --save

import {Database} from "@miqro/query";

const db = new Database({
  dialect: "sqlite3",
  storage: "db.sqlite3"
});

//await db.disconnect();

postgres

install the pg module.

npm install pg --save

import {Database} from "@miqro/query";

const db = new Database({
  dialect: "pg",
  connectionString: "postgresql://postgres:example@localhost:5432/db"
});

//await db.disconnect();

query

raw

const result = await db.query("SELECT id, name FROM table WHERE deleted=?", [false]);

console.dir(result); // [{id: "..", name: ...}, ...]

select

const result = await db.select()
  .column({name: "name", as: "userName"})
  .column("id")
  .from({name: "users", as: "User"})
  .left({name: "posts", as: "Post"}, [
    db.where()
      .literal("Post.userId=id")
      .eq("deleted", false)
  ])
  .gte("age", 21)
  .lte("age", 80)
  .yield();

console.dir(result); // [{id: "..", userName: ...}, ...]

count

const {count} = await db.count()
  .from({name: "users", as: "User"})
  .yield();

console.log(count); // 100

insert

const insertedRows = await db.insert("table")
  .column("name") // or .columns(["name", "postCount"])
  .column("postCount")
  .values([
    {
      name: "name1",
      postCount: 1 
    },
    {
      name: "name2",
      postCount: 1 
    }
  ]).yield();

console.log(insertedRows[0].name); // "name1"

update

await db.update("table")
  .set("name", "newname")
  .eq("id", 1)
  .yield();

delete

await db.delete("table")
  .eq("id", 1)
  .eq("deleted", true)
  .yield();

clone

const baseQuery = await db.select()
  .from("table")
  .column("name")
  .column("id")
  .eq("domain", "somedomain");

const deletedQuery = baseQuery.clone().eq("deleted", true);

const notDeletedQuery = baseQuery.clone().eq("deleted", false);

console.dir(await baseQuery.yield());
console.dir(await deletedQuery.yield());
console.dir(await notDeletedQuery.yield());

prepare

const query = await db.select()
  .from("table")
  .column("name")
  .column("id")
  .eq("domain", "somedomain");

const stmts = query.prepare();
console.dir(stmts.all); // [{ sql: "...", values: [...] }]
console.dir(stmts.first.sql); // "SELECT name FROM table ...
console.dir(stmts.first.values); // ["somedomain"]
console.dir(await query.yield()); // [{...}]