Querying Database

FumaDB provides a Prisma-like query interface for interacting with consumer's database.

Basic Usage

To interact with the ORM:

Get the active schema version.
Get the ORM instance for the given version.

const version = await client.version();

const orm = client.orm(version);

It's useful when you have multiple versions and want to ensure backward compatibility.

const version = await client.version();

if (version === "1.0.0") {
  const orm = client.orm(version);
  // ...
} else if (version === "1.1.0") {
  const orm = client.orm(version);
  // ...
}

Create

const createdUser = await orm.create("users", {
  name: "fuma",
});

Create Many

await orm.createMany("users", [
  { id: "alfon", name: "alfon" },
  { id: "bob", name: "bob" },
]);

Find Many

const allUsers = await orm.findMany("users", {
  select: true, // or ["id", "name"]
  orderBy: [users.name, "asc"],
});

select: true, or array of column names
where: (builder) => condition
orderBy: [[column, "asc" | "desc"]]
limit, offset
join: (builder) => builder.relation(...)

Find First

Same as findMany but only return the first result in the query.

const user = await orm.findFirst("users", {
  where: (b) => b("id", "=", "alfon"),
});

Update Many

await orm.updateMany("users", {
  set: { name: "Bob" },
  where: (b) => b("id", "=", "alfon"),
});

Upsert

await orm.upsert("users", {
  where: (b) => b("id", "=", "bob"),
  create: { id: "bob", name: "Bob is sad" },
  update: { name: "Bob is happy" },
});

The query is executed when awaited. To receive the created/updated row, call forceReturning() instead:

const user = await orm
  .upsert("users", {
    where: (b) => b("id", "=", "bob"),
    create: { id: "bob", name: "Bob is sad" },
    update: { name: "Bob is happy" },
  })
  .forceReturning();

Databases that can return the affected row (such as PostgreSQL, SQLite and MS SQL Server) obtain it from the write itself, the others run one extra query for it.

Count

const count = await orm.count("users", {
  where: (b) => b.isNotNull("name"),
});

Delete Many

await orm.deleteMany("users", {
  where: (b) => b("id", "=", "bob"),
});

Filtering & Conditions

The query builder (b) supports logical operators and comparisons:

const filtered = await orm.findMany("users", {
  where: (b) => b.and(b("name", "contains", "al"), b.or(b("age", ">", 100), b.isNull("email"))),
});

Supported operators include:

=
!=
>
>=
<
<=
in
not in
contains
starts with
ends with
not contains
not starts with
not ends with

Joins (Relations)

For relations, make sure to declare relations in your schema first.

// Get users with their messages
const result = await orm.findMany("users", {
  join: (b) => b.messages(),
});

// Nested join with select and where
const result = await orm.findMany("users", {
  join: (b) =>
    b.messages({
      select: ["content"],
      limit: 1,
      where: (b) => b("content", "contains", "alfon"),
      join: (b) => b.author(),
    }),
});

Querying Database

On this page