miqro · @miqro/core · @miqro/parser · @miqro/query · @miqro/jsx · @miqro/jsx-dom · @miqro/jsx-node · @miqro/request · @miqro/runner · @miqro/test · @miqro/test-http
query builder and ORM for node:sqlite, pg, pg-pool.
see also: db.ts · migration folder in the miqro README
import { Database } from "@miqro/query";
// node:sqlite (built-in, no deps)
const db = new Database({ dialect: "node:sqlite", storage: "db.sqlite3" });
// postgres — npm install pg
const db = new Database({ dialect: "pg", connectionString: "postgresql://..." });
// postgres pool — npm install pg
const db = new Database({ dialect: "pg-pool", connectionString: "postgresql://..." });
await db.connect();
await db.disconnect();
db.transaction() opens a new connection to storage, so ":memory:" gives the
transaction its own empty database and everything inside it fails with no such table.
Use a file for anything that runs a transaction, tests included.
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, blob
column options: primaryKey, autoIncrement, allowNull, defaultValue, unique, index,
references
const Comment = defineModel(db, "comments", {
id: { type: "integer", primaryKey: true, autoIncrement: true },
email: { type: "string", unique: true, index: true },
postId: { type: "integer", references: { table: "posts", column: "id", onDelete: "CASCADE" } },
data: { type: "blob", allowNull: true }
});
index: true emits a CREATE INDEX IF NOT EXISTS "idx_<table>_<column>" alongside the
CREATE TABLE. onDelete/onUpdate accept CASCADE, SET NULL, SET DEFAULT, RESTRICT,
NO ACTION. sqlite only enforces foreign keys with PRAGMA foreign_keys = ON.
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"] });
the model thunk is resolved lazily, the first time the association is used, so two
models can name each other and the definition order does not matter:
const User = defineModel(db, "users", {
id: { type: "integer", primaryKey: true, autoIncrement: true },
name: { type: "string" }
}, {
posts: { type: "hasMany", model: () => Post, foreignKey: "userId" }
});
const 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, belongsToMany
// many-to-many through a join table
Student.belongsToMany("courses", Course, {
through: "enrollments",
foreignKey: "studentId", // join-table column pointing at Student
otherKey: "courseId" // join-table column pointing at Course
});
// nested includes use dotted paths
const users = await User.findAll(undefined, { include: ["posts", "posts.comments"] });
users[0].posts[0].comments // Comment[]
associations use separate queries + in-memory stitch — not JOINs. limit/offset count root
rows, and association filters are resolved before the limit is applied, so a filtered page is
still a full page.
order() on an association column works for belongsTo/hasOne (resolved with a JOIN) and
throws for hasMany — a root with many children has no single value to sort by.
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
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 });
reads and writes both return ModelRowInstance rows — findAll, create, updateAll
and deleteAll alike.
The types do not say so. ModelRow<T> is currently an alias for T, so the row is
typed as a plain object and every instance method is a compile error even though it works
at runtime. Cast until that is fixed:
import { ModelRowInstance } from "@miqro/query";
const [row] = await Post.findAll(Post.where().eq("id", 1));
row.title // column access — typed, no cast needed
row.title = "new title";
const post = row as unknown as ModelRowInstance<typeof row>;
post.toJSON() // plain object, associations included
post.changed() // ["title"] after an assignment
post.isDirty()
await post.save(); // UPDATE ... SET "title"=? WHERE "id"=? (only changed columns)
await post.destroy(); // DELETE ... WHERE "id"=?
toJSON() is also what JSON.stringify uses. Reach for it rather than spreading or
Object.keys, which expose the instance's own dataValues / _persisted / _model
fields.
await Post.updateAll(
{ published: true },
Post.where().eq("id", 1)
);
await Post.deleteAll(Post.where().eq("id", 1));
const total = await Post.count();
const published = await Post.count(Post.where().eq("published", true));
where is optional.
create table if not exists:
await Post.sync(); // create if not exists
await Post.sync(true); // drop and recreate
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 () => { }
});
lower-level than ORM. use when you need JOINs, raw SQL, or untyped queries.
const rows = await db.select()
.from("posts")
.column("id") // omit every column() for SELECT *
.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();
db.where(schema?, associations?) // both are typing only, no runtime effect
.eq("col", value) // value null renders IS NULL
.neq("col", value) // value null renders IS NOT NULL
.gt("col", value)
.gte("col", value)
.lt("col", value)
.lte("col", value)
.like("col", "val%")
.notLike("col", "val%")
.ilike("col", "val%") // case-insensitive on both dialects
.between("col", from, to)
.isNull("col")
.isNotNull("col")
.in("col", [1, 2, 3]) // an empty list matches nothing
.nin("col", [4, 5]) // an empty list excludes nothing
.literal("col > now()")
.order("col", "ASC") // ASC or DESC only
.limit(10)
.offset(0)
.and(where)
.or(where)
.not(where)
passing a schema types the column names and value types, the same way Model.where() and
db.select(schema, assocs) do:
db.where(userSchema).eq("first_name", "alice"); // checked
db.where(userSchema).eq("first_nam", "alice"); // compile error
db.where(userSchema, assocs).eq("posts.title", "x") // checked against the association
with no schema, dotted names stay unchecked — they are also how joins address table aliases
(.eq("A.deleted", false)).
inside literal(), ?? is an escaped literal ? (needed for postgres' jsonb ? operator);
? inside a string literal is left alone.
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();
const rows = await db.insert("posts", schema?)
.values([{ title: "a" }, { title: "b" }])
.ignoreDuplicates()
.returning("id")
.yield();
the column list defaults to the union of the keys across the rows, and a row missing one
of them is inserted as null. name them explicitly with column() to insert a subset.
column() takes one name or an array, and columns() is an alias for it — note that
unlike Select.columns(...) neither is variadic, so column("a", "b") silently keeps
only "a".
await db.insert("posts", schema?)
.values([{ id: 1, title: "a", views: 10 }])
.onConflict("id") // ON CONFLICT("id")
.doUpdate("title") // DO UPDATE SET "title"=excluded."title"
.yield();
// doUpdate() with no columns overwrites every inserted column but the target
// onConflict() without doUpdate() is DO NOTHING
await db.update("posts", schema?)
.set("published", true)
.eq("id", 1)
.returning("id") // optional, same as insert/delete
.yield();
set(col, undefined) writes NULL, the same as set(col, null) and the same as insert
does for a column whose value is undefined. A where filter built from undefined is a
different matter and is refused by name — coercing it would change which rows are touched.
a column is NOT NULL only if the schema says allowNull: false; omitting allowNull
leaves it nullable in the database, though the inferred row type treats it as non-null.
await db.delete("posts")
.eq("id", 1)
.yield();
const rows = await db.select({ team: { type: "string" }, total: { type: "integer" } })
.from("scores")
.column("team")
.column({ fn: "SUM", column: "score", as: "total" }) // COUNT, SUM, AVG, MIN, MAX
.group("team")
.having(db.where().literal(`SUM("score") > 10`))
.yield();
const teams = await db.select().from("scores").distinct().column("team").yield();
const { count } = await db.count()
.from("posts")
.eq("published", true)
.yield();
// with group(), one row per group — yield() would be a single wrong number
const perTeam = await db.count(schema?, associations?)
.from("scores")
.group("team")
.yieldAll(); // [{ team: "a", count: 2 }, ...]
const rows = await db.query("SELECT * FROM posts WHERE id = ?", [1]);
const base = db.select().from("posts").eq("published", true);
const recent = await base.clone().order("createdAt", "DESC").limit(5).yield();
const old = await base.clone().order("createdAt", "ASC").limit(5).yield();
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
await db.alterTable("posts").rename("articles").yield();
await db.alterTable("posts").addColumn("slug", { type: "string" }).yield();
await db.alterTable("posts").renameColumn("slug", "permalink").yield();
await db.alterTable("posts").dropColumn("permalink").yield();
await db.transaction(async (tx) => {
await tx.insert("posts").values([{ title: "a" }]).yield();
// nested transactions use SAVEPOINTs — catching this leaves the outer work intact
try {
await tx.transaction(async (tx2) => {
await tx2.insert("posts").values([{ title: "b" }]).yield();
throw new Error("nope");
});
} catch (e) { }
});
RETURNING, ORDER BY direction, aggregate functions,
referential actions) are validated against an allowlist, never interpolated raw. anything else
goes through bind parameters." throughout.datetime maps to TIMESTAMPTZ on postgres and to ISO-8601 text on sqlite.datetime is now TIMESTAMPTZ (was TIMESTAMP). Tables created by ≤0.1.1 keep
the old type; migrate with
ALTER TABLE "t" ALTER COLUMN "at" TYPE TIMESTAMPTZ USING "at" AT TIME ZONE 'UTC';Date as ISO-8601 on every path. Raw db.query() used to store
Date.toString() ("Wed Jul 30 2026 …"), which is not range-comparable. Rows written that way
keep their old format.utils.transformRow / utils.transformRows are gone. They were unreferenced and threw on
any column missing from the schema.count() with group() throws and points at yieldAll(); it used to return the first
group's count as if it were the total.order() rejects any direction other than ASC/DESC, and order() on a hasMany
association column now throws instead of being silently dropped.offset(0) is honoured instead of being dropped when merging a where or rendering LIMIT.eq(col, null) renders IS NULL (and neq IS NOT NULL) instead of a never-true
col = NULL.findAll() returns ModelRowInstance rows, matching create()/updateAll()/deleteAll()
— at runtime. The types still say plain rows; see rows.// migration/001-create-posts.ts
import { Migration } from "@miqro/query";
export default {
up: async (db, logger) => {
await db.createTable("posts", {
id: { type: "integer", primaryKey: true, autoIncrement: true },
title: { type: "string" }
}).yield(logger);
},
down: async (db, logger) => {
await db.dropTable("posts").yield(logger);
}
} as Migration;
a migration is { up, down }. both are required — a module without down is rejected
when it is loaded, not when you run --migrate-down. the name and the database are not
declared in the module: the name is the file name, and the db is the one configured by
the service.
file naming. the sort key is the number before the first dash, so a file with no
dash — 001_create_posts.ts — is silently skipped and never runs. only .ts, .tsx,
.js and .mjs are picked up.
each migration runs once, tracked by name in a migrations table, so up is a no-op the
second time.
run via CLI:
miqro --migrate-up --service app/
miqro --migrate-down --service app/
or programmatically:
await app.migrate({ direction: "up" });
// name is the file name, extension included
await app.migrate({ direction: "down", name: "001-create-posts.ts", service: "app/", dbName: "mydb" });