sqlite3-example.js

// SQLite3 usage Examples

test.cb("sqlite3:open & close", t => {
  t.plan(2);
  let db = new sqlite3.Database(":memory:");
  db.on("open", () => {
    t.pass();
  });
  db.on("close", () => {
    t.pass();
    t.end();
  });
  db.close();
});

test.cb("sqlite3:error > open", t => {
  t.plan(1);
  let db = new sqlite3.Database("@:memory:");
  db.on("error", err => {
    t.truthy(err);
    t.end();
  });
});

test.cb("sqlite3:error > close", t => {
  t.plan(1);
  let db = new sqlite3.Database(":memory:");
  db.on("error", err => {
    t.truthy(err);
    t.end();
  });
  db.close(() => {
    db.close();
  });
});

test.cb("sqlite3:error > run", t => {
  let db = new sqlite3.Database(":memory:");
  db.run("RRRR", err => {
    t.truthy(err);
    t.end();
  });
  db.on("error", () => {
    t.fail();
  });
});

test.cb("sqlite3:error > prepare", t => {
  t.plan(1);
  let db = new sqlite3.Database(":memory:");
  let stmt = db.prepare("AAAAAA");
  stmt.on("error", err => {
    t.truthy(err);
    t.end();
  });
});

test.cb("sqlite3:trace", t => {
  t.plan(1);
  let db = new sqlite3.Database(":memory:");
  db.on("trace", sql => {
    t.truthy(sql);
    t.end();
  });
  db.run("CREATE TABLE lorem (info TEXT)");
});

test.cb("sqlite3:profile", t => {
  t.plan(2);
  let db = new sqlite3.Database(":memory:");
  db.on("profile", (sql, nsecs) => {
    t.truthy(sql);
    t.true(-1 < nsecs);
    t.end();
  });
  db.run("CREATE TABLE lorem (json JSON)");
});

test.cb("sqlite3:exec", t => {
  t.plan(2);
  let db = new sqlite3.Database(":memory:");
  db.on("trace", sql => {
    t.truthy(sql);
  });
  db.exec(`CREATE TABLE lorem (json JSON);
  CREATE TABLE lore2 (json JSON)`);
  db.close(() => {
    t.end();
  });
});

test.cb("sqlite3:get", t => {
  let db = new sqlite3.Database(":memory:");
  let sql = `create table users (name text);
    insert into users values ("john");
    insert into users values ("bob");`;
  db.on("trace", sql => {
    console.log(sql);
    t.truthy(sql);
  });
  db.exec(sql, err => {
    if (err) {
      console.error(err);
    } else {
      db.get("select name from users", (err, row) => {
        console.log(row);
        t.pass();
        t.end();
      });
    }
  });
});

test.cb("sqlite3:each", t => {
  let db = new sqlite3.Database(":memory:");
  let sql = `create table users (name text);
    insert into users values ("john");
    insert into users values ("bob");`;
  db.on("trace", sql => {
    console.log(sql);
    t.truthy(sql);
  });
  db.exec(sql, err => {
    if (err) {
      console.error(err);
    } else {
      let args = ["select name from users", (err, row) => {
        t.ifError(err);
        console.log(row);
      }, (err, count) => {
        t.ifError(err);
        t.is(count, 2);
        t.end();
      }];
      db.each.apply(db, args);
    }
  });
});

test.cb("sqlite3:prepare", t => {
  let db = new sqlite3.Database(":memory:");
  let sql = `create table users (name text);
    insert into users values ("john");
    insert into users values ("bob");`;
  db.on("trace", sql => {
    console.log(sql);
  });
  t.plan(2);
  db.exec(sql, err => {
    if (err) {
      console.error(err);
      t.end();
    } else {
      let stmt = db.prepare("select rowid, name from users where name = ?", "john", err => {
        if (err) {
          t.ifError(err);
          t.end();
        }
      });
      stmt.get((err, row) => {
        t.ifError(err);
        t.truthy(row);
        console.log("xxx", row);
        t.end();
      });
    }
  });
});