@miqro/query

query builder and ORM for node:sqlite, sqlite3, pg.

see also: db.ts · migration folder in miqro README

connection

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

// node:sqlite (built-in, no deps)
const db = new Database({ dialect: "node:sqlite", storage: "db.sqlite3" });

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

// postgres — npm install pg
const db = new Database({ dialect: "pg", connectionString: "postgresql://..." });

await db.connect();
await db.disconnect();

ORM

defineModel

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

const Post = defineModel(db, "posts", {
  id:        { type: "integer", primaryKey: true, autoIncrement: true },
  title:     { type: "string" },
  content:   { type: "string" },
  published: { type: "boolean" },
  createdAt: { type: "datetime" }
});

// TypeScript infers row types from schema
// { id: number, title: string, content: string, published: boolean, createdAt: Date }

column types: integer, string, boolean, datetime, json, real, bigint

findAll

const posts = await Post.findAll();

const published = await Post.findAll(
  Post.where().eq("published", true).order("createdAt", "DESC"),
  { limit: 10, offset: 0 }
);

// select specific columns
const titles = await Post.findAll(undefined, { columns: ["id", "title"] });

associations

const User = defineModel(db, "users", {
  id:   { type: "integer", primaryKey: true, autoIncrement: true },
  name: { type: "string" }
}, {
  posts: { type: "hasMany", model: () => Post, foreignKey: "userId" }
});

Post = defineModel(db, "posts", {
  id:     { type: "integer", primaryKey: true, autoIncrement: true },
  userId: { type: "integer" },
  title:  { type: "string" }
}, {
  user: { type: "belongsTo", model: () => User, foreignKey: "userId" }
});

// include associations
const users = await User.findAll(undefined, { include: ["posts"] });
users[0].posts  // Post[]

const posts = await Post.findAll(undefined, { include: ["user"] });
posts[0].user   // User | null

association types: hasMany, belongsTo, hasOne

associations use separate queries + in-memory stitch — not JOINs. correct pagination with limit/offset.

filter on association columns:

// users who have a post titled "hello"
const users = await User.findAll(
  User.where().eq("posts.title", "hello"),
  { include: ["posts"] }
);
// returns only users with a matching post
// users[0].posts contains only the matched post

create

const rows = await Post.create({ title: "hello", content: "..." });
const rows = await Post.create([{ title: "a" }, { title: "b" }]);

// ignore duplicates
await Post.create({ title: "x" }, { ignoreDuplicates: true });

updateAll

await Post.updateAll(
  { published: true },
  Post.where().eq("id", 1)
);

deleteAll

await Post.deleteAll(Post.where().eq("id", 1));

count

const total = await Post.count();
const published = await Post.count(Post.where().eq("published", true));

sync

create table if not exists:

await Post.sync();           // create if not exists
await Post.sync(true);       // drop and recreate

hooks

const Post = defineModel(db, "posts", schema, {}, {
  beforeCreate: async (values) => {
    return values.map(v => ({ ...v, createdAt: new Date() }));
  },
  afterCreate: async (rows) => { },
  beforeUpdate: async (values, where) => { },
  afterUpdate: async (rows) => { },
  beforeDelete: async (where) => { },
  afterDelete: async (rows) => { },
  beforeSync: async (force) => { },
  afterSync: async () => { }
});

query builder

lower-level than ORM. use when you need JOINs, raw SQL, or untyped queries.

select

const rows = await db.select()
  .from("posts")
  .column("id")
  .column("title")
  .eq("published", true)
  .order("createdAt", "DESC")
  .limit(10)
  .offset(0)
  .yield();

you can add typing with a schema

const rows = await db.select(postsSchema, postAssociations)
  .from("posts")
  .column("id")
  .column("title")
  .eq("published", true)
  .order("createdAt", "DESC")
  .limit(10)
  .offset(0)
  .yield();

where

db.where(schema?, assocs?)
  .eq("col", value)
  .neq("col", value)
  .gt("col", value)
  .gte("col", value)
  .lt("col", value)
  .lte("col", value)
  .like("col", "val%")
  .in("col", [1, 2, 3])
  .nin("col", [4, 5])
  .literal("col IS NOT NULL")
  .order("col", "ASC")
  .and(where)
  .or(where)

joins

const rows = await db.select()
  .from({ name: "users", as: "u" })
  .column("u.id")
  .column("p.title")
  .left({ name: "posts", as: "p" }, db.where().literal("p.userId = u.id"))
  .yield();

insert

const rows = await db.insert("posts", schema?, assocs?)
  .values([{ title: "a" }, { title: "b" }])
  .ignoreDuplicates()
  .returning("id")
  .yield();

update

await db.update("posts", schema?, assocs?)
  .set("published", true)
  .eq("id", 1)
  .yield();

delete

await db.delete("posts")
  .eq("id", 1)
  .yield();

count

const { count } = await db.count()
  .from("posts")
  .eq("published", true)
  .yield();

raw

const rows = await db.query("SELECT * FROM posts WHERE id = ?", [1]);

clone

const base = db.select().from("posts").eq("published", true);
const recent = base.clone().order("createdAt", "DESC").limit(5);
const old = base.clone().order("createdAt", "ASC").limit(5);

createTable / dropTable

await db.createTable("posts", {
  id:      { type: "integer", primaryKey: true, autoIncrement: true },
  title:   { type: "string" },
  content: { type: "string", allowNull: true }
}).yield();

await db.dropTable("posts").yield();
await db.dropTable("posts").ignoreDuplicates().yield();  // IF EXISTS

migrations

// migration/001_create_posts.ts
export default {
  name: "001_create_posts",
  dbName: "mydb",
  up: async (db) => {
    await db.createTable("posts", {
      id:    { type: "integer", primaryKey: true, autoIncrement: true },
      title: { type: "string" }
    }).yield();
  }
};

migrations run in filename order. each migration runs once — tracked in a migrations table.

run via CLI:

miqro --migrate-up --service app/
miqro --migrate-down --service app/

or programmatically:

await app.migrate({ direction: "up" });
await app.migrate({ direction: "down", name: "001_create_posts", service: "app/", dbName: "mydb" });