TableQuery

TableQuery builds table-scoped selections and mutations. Query builder methods mutate the builder and return it.

Filter Rows

Add a predicate. Calls are combined with AND.

TypeScript

db.table("workers").where("id", "=", "w_001");
db.table("workers").where({ email: "ada@example.com" });
db.table("workers").where("email", "=", null);
db.table("workers").where("email IS NOT NULL");

SQL

SELECT * FROM workers WHERE id = 'w_001';
SELECT * FROM workers WHERE email = 'ada@example.com';
SELECT * FROM workers WHERE email IS NULL;
SELECT * FROM workers WHERE email IS NOT NULL;

JSON

{
  "database": "payroll",
  "command": {
    "kind": "select",
    "table": "workers",
    "columns": "*",
    "joins": [],
    "where": {
      "type": "and",
      "left": {
        "type": "comparison",
        "op": "=",
        "left": { "type": "identifier", "name": "id" },
        "right": { "type": "literal", "value": "w_001" }
      },
      "right": {
        "type": "is-null",
        "expr": { "type": "identifier", "name": "email" },
        "not": true
      }
    },
    "orderBy": [],
    "output": "objects"
  }
}

Supported comparison operators are =, !=, <>, >, >=, <, and <=. Use IS NULL or IS NOT NULL in SQL text when filtering null values.

Select Columns

Choose projected columns.

TypeScript

db.table("workers").select(["id", "email"]).all();
db.table("workers").select("*").all();

SQL

SELECT id, email FROM workers;
SELECT * FROM workers;

JSON

{
  "database": "payroll",
  "command": {
    "kind": "select",
    "table": "workers",
    "columns": ["id", "email"],
    "joins": [],
    "orderBy": [],
    "output": "objects"
  }
}

Join Tables

Join a foreign-key relationship by relationship name.

TypeScript

db.table("sessions").join("worker").all();

SQL

SELECT sessions.*, workers.*
FROM sessions
JOIN workers ON sessions.worker_id = workers.id;

JSON

{
  "database": "payroll",
  "command": {
    "kind": "select",
    "table": "sessions",
    "columns": "*",
    "joins": [{ "table": "worker", "relationship": "worker" }],
    "orderBy": [],
    "output": "objects"
  }
}

Sort Rows

Sort results by a column.

TypeScript

db.table("workers").orderBy("id", "desc").all();

SQL

SELECT *
FROM workers
ORDER BY id DESC;

JSON

{
  "database": "payroll",
  "command": {
    "kind": "select",
    "table": "workers",
    "columns": "*",
    "joins": [],
    "orderBy": [{ "column": "id", "direction": "desc" }],
    "output": "objects"
  }
}

Limit Rows

Restrict result count.

TypeScript

db.table("workers").limit(10).all();

SQL

SELECT *
FROM workers
LIMIT 10;

JSON

{
  "database": "payroll",
  "command": {
    "kind": "select",
    "table": "workers",
    "columns": "*",
    "joins": [],
    "orderBy": [],
    "limit": 10,
    "output": "objects"
  }
}

Read All Rows

Execute the select query and return rows.

TypeScript

const rows = db.table("workers").all();

SQL

SELECT * FROM workers;

JSON

{
  "database": "payroll",
  "command": {
    "kind": "select",
    "table": "workers",
    "columns": "*",
    "joins": [],
    "orderBy": [],
    "output": "objects"
  }
}

Read First Row

Return the first matching row or no value.

TypeScript

const worker = db.table("workers").where("id", "=", "w_001").first();

SQL

SELECT *
FROM workers
WHERE id = 'w_001'
LIMIT 1;

JSON

{
  "database": "payroll",
  "command": {
    "kind": "select",
    "table": "workers",
    "columns": "*",
    "joins": [],
    "where": {
      "type": "comparison",
      "op": "=",
      "left": { "type": "identifier", "name": "id" },
      "right": { "type": "literal", "value": "w_001" }
    },
    "orderBy": [],
    "limit": 1,
    "output": "objects"
  }
}

Insert Rows

Insert one row or many rows.

TypeScript

db.table("workers").insert({ id: "w_001", name: "Ada", email: "ada@example.com" });
db.table("workers").insert([
  { id: "w_002", name: "Grace", email: "grace@example.com" }
]);

SQL

INSERT INTO workers (id, name, email)
VALUES ('w_001', 'Ada', 'ada@example.com');

INSERT INTO workers (id, name, email)
VALUES ('w_002', 'Grace', 'grace@example.com');

JSON

{
  "database": "payroll",
  "command": {
    "kind": "insert",
    "table": "workers",
    "rows": [
      { "id": "w_001", "name": "Ada", "email": "ada@example.com" },
      { "id": "w_002", "name": "Grace", "email": "grace@example.com" }
    ]
  }
}

Update Rows

Update matching rows.

TypeScript

db.table("workers").where("id", "=", "w_001").update({ email: "ada@csdb.dev" });

SQL

UPDATE workers
SET email = 'ada@csdb.dev'
WHERE id = 'w_001';

JSON

{
  "database": "payroll",
  "command": {
    "kind": "update",
    "table": "workers",
    "set": { "email": "ada@csdb.dev" },
    "where": {
      "type": "comparison",
      "op": "=",
      "left": { "type": "identifier", "name": "id" },
      "right": { "type": "literal", "value": "w_001" }
    }
  }
}

Delete Rows

Delete matching rows.

TypeScript

db.table("workers").where("id", "=", "w_001").delete();

SQL

DELETE FROM workers
WHERE id = 'w_001';

JSON

{
  "database": "payroll",
  "command": {
    "kind": "delete",
    "table": "workers",
    "where": {
      "type": "comparison",
      "op": "=",
      "left": { "type": "identifier", "name": "id" },
      "right": { "type": "literal", "value": "w_001" }
    }
  }
}

Find By Primary Key

Find a row using the table's primary key index.

TypeScript

const worker = db.table("workers").byPrimaryKey("w_001");
const line = db.table("invoice_lines").byPrimaryKey(["inv_001", "line_1"]);

SQL

SELECT *
FROM workers
WHERE id = 'w_001'
LIMIT 1;

SELECT *
FROM invoice_lines
WHERE invoice_id = 'inv_001' AND id = 'line_1'
LIMIT 1;

JSON

{
  "database": "payroll",
  "command": {
    "kind": "by-primary-key",
    "table": "workers",
    "key": "w_001"
  }
}

{
  "database": "billing",
  "command": {
    "kind": "by-primary-key",
    "table": "invoice_lines",
    "key": ["inv_001", "line_1"]
  }
}