SQL query builder

Tymber provides a basic SQL query builder.

import { sql } from "@tymber/core";

API

SELECT

select()

select()
  .from("users")
// SELECT * FROM users

select(["id", "name"])
  .from("users")
// SELECT id, name FROM users

.distinct()

select(["id", "name"])
  .distinct()
  .from("users")
// SELECT DISTINCT id, name FROM users

.where()

With an object:

select()
  .from("users")
  .where({
    id: 1,
    name: "John"
  })
// SELECT * FROM users WHERE id = $1 AND name = $2

With a WHERE clause:

const { select, isNull } = sql;

select()
  .from("users")
  .where(isNull("id"))
// SELECT * FROM users WHERE id IS NULL

.groupBy()

select()
  .from("users")
  .groupBy(["id", "name"])
// SELECT * FROM users GROUP BY id, name

.having()

const { select, raw } = sql;

select()
  .from("users")
  .groupBy(["id", "name"])
  .having(raw("SUM(value) > ?", [1]))
// SELECT * FROM users GROUP BY id, name HAVING SUM(value) > $1

.orderBy()

select()
  .from("users")
  .orderBy(["id", "name DESC"])
// SELECT * FROM users ORDER BY id, name DESC

.limit()

select()
  .from("users")
  .limit(5)
// SELECT * FROM users LIMIT $1

.offset()

select()
  .from("users")
  .limit(5)
  .offset(10)
// SELECT * FROM users LIMIT $1 OFFSET $2

.innerJoin()

select()
  .from("users")
  .innerJoin("sessions", {
    "users.id": "sessions.user_id",
  })
// SELECT * FROM users INNER JOIN sessions ON users.id = sessions.user_id

.leftJoin()

select()
  .from("users")
  .leftJoin("sessions", {
    "users.id": "sessions.user_id",
  })
// SELECT * FROM users LEFT JOIN sessions ON users.id = sessions.user_id

.rightJoin()

select()
  .from("users")
  .rightJoin("sessions", {
    "users.id": "sessions.user_id",
  })
// SELECT * FROM users RIGHT JOIN sessions ON users.id = sessions.user_id

.fullOuterJoin()

select()
  .from("users")
  .fullOuterJoin("sessions", {
    "users.id": "sessions.user_id",
  })
// SELECT * FROM users FULL OUTER JOIN sessions ON users.id = sessions.user_id

.forUpdate()

select()
  .from("users")
  .forUpdate()
// SELECT * FROM users FOR UPDATE

INSERT

insert()

.into()

.values()

insert()
  .into("users")
  .values([
    {
      id: 1,
      name: "John"
    },
    {
      id: 2,
      name: "Joe"
    },
  ])
// INSERT INTO users (id, name) VALUES ($1, $2), ($3, $4)

.select()

insert()
  .into("new_users")
  .select(select(["name"]).from("users"))
// INSERT INTO new_users (name) SELECT name FROM users

.returning()

insert()
  .into("users")
  .values([
    { name: "John" }
  ])
  .returning()
// INSERT INTO users (name) VALUES ($1) RETURNING *

insert()
  .into("users")
  .values([
    { name: "John" }
  ])
  .returning(["id", "name"])
// INSERT INTO users (name) VALUES ($1) RETURNING id, name

UPDATE

update()

.set()

update("users")
  .set({
    name: "John"
  })
// UPDATE users SET name = $1

.where()

With an object:

update("users")
  .set({
    name: "Joe"
  })
  .where({
    id: 1,
    name: "John"
  })
// UPDATE users SET name = $1 WHERE id = $2 AND name = $3

With a WHERE clause:

const { update, isNull } = sql;

update("users")
  .set({
    name: "Joe"
  })
  .where(isNull("id"))
// UPDATE users SET name = $1 WHERE id IS NULL

DELETE

deleteFrom()

.where()

deleteFrom("users")
  .where({
    id: 1
  })
// DELETE FROM users WHERE id = $1

WHERE clauses

Condition	Output
eq("id", 1)	id = $1
notEq("id", 1)	id <> $1
lt("id", 1)	id < $1
lte("id", 1)	id <= $1
gt("id", 1)	id > $1
gte("id", 1)	id >= $1
isNull("id")	id IS NULL
isNotNull("id")	id IS NOT NULL
between("id", 1, 2)	id BETWEEN $1 AND $2
like("name", "Jo%")	name LIKE $1
ilike("name", "Jo%")	name ILIKE $1
in("id", [1, 2, 3])	id IN ($1, $2, $3)
and([eq("id", 1), eq("name", "John")])	id = $1 AND name = $2
or([eq("id", 1), eq("name", "John")]	id = $1 OR name = $2
not(eq("id", 1))	NOT id = $1
raw("custom_fn(?, ?)", [1, 2])	custom_fn($1, $2)

Notes

Mutability

Statements are mutable:

const query = select()
  .from("users")
  .where({
    id: 1,
    name: "John"
  });
const { text, values } = query.build();

is equivalent to (without chaining):

const query = select();
query.from("users");
query.where({
  id: 1,
  name: "John"
});

// no need to write
// query = query.where({
//   id: 1,
//   name: "John"
// });

const { text, values } = query.build();

Raw expressions

The API does not cover all SQL functions like SUM() or COUNT()̀. For this use case, you can use raw():

const { select, raw } = sql;

select(raw("COUNT(*)"))
  .from("users")
// SELECT COUNT(*) FROM users