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
- Python
- SQL
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");
db.table("workers").where("id", "=", "w_001")
db.table("workers").where({"email": "ada@example.com"})
db.table("workers").where("email", "=", None)
db.table("workers").where("email IS NOT NULL")
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;
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
- Python
- SQL
db.table("workers").select(["id", "email"]).all();
db.table("workers").select("*").all();
db.table("workers").select(["id", "email"]).all()
db.table("workers").select("*").all()
SELECT id, email FROM workers;
SELECT * FROM workers;
Join Tables
Join a foreign-key relationship by relationship name.
- TypeScript
- Python
- SQL
db.table("sessions").join("worker").all();
db.table("sessions").join("worker").all()
SELECT sessions.*, workers.*
FROM sessions
JOIN workers ON sessions.worker_id = workers.id;
Sort Rows
Sort results by a column.
- TypeScript
- Python
- SQL
db.table("workers").orderBy("id", "desc").all();
db.table("workers").order_by("id", "desc").all()
SELECT *
FROM workers
ORDER BY id DESC;
Limit Rows
Restrict result count.
- TypeScript
- Python
- SQL
db.table("workers").limit(10).all();
db.table("workers").limit(10).all()
SELECT *
FROM workers
LIMIT 10;
Read All Rows
Execute the select query and return rows.
- TypeScript
- Python
- SQL
const rows = db.table("workers").all();
rows = db.table("workers").all()
SELECT * FROM workers;
Read First Row
Return the first matching row or no value.
- TypeScript
- Python
- SQL
const worker = db.table("workers").where("id", "=", "w_001").first();
worker = db.table("workers").where("id", "=", "w_001").first()
SELECT *
FROM workers
WHERE id = 'w_001'
LIMIT 1;
Insert Rows
Insert one row or many rows.
- TypeScript
- Python
- SQL
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" }
]);
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"}
])
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');
Update Rows
Update matching rows.
- TypeScript
- Python
- SQL
db.table("workers").where("id", "=", "w_001").update({ email: "ada@csdb.dev" });
db.table("workers").where("id", "=", "w_001").update({"email": "ada@csdb.dev"})
UPDATE workers
SET email = 'ada@csdb.dev'
WHERE id = 'w_001';
Delete Rows
Delete matching rows.
- TypeScript
- Python
- SQL
db.table("workers").where("id", "=", "w_001").delete();
db.table("workers").where("id", "=", "w_001").delete()
DELETE FROM workers
WHERE id = 'w_001';
Find By Primary Key
Find a row using the table's primary key index.
- TypeScript
- Python
- SQL
const worker = db.table("workers").byPrimaryKey("w_001");
const line = db.table("invoice_lines").byPrimaryKey(["inv_001", "line_1"]);
worker = db.table("workers").by_primary_key("w_001")
line = db.table("invoice_lines").by_primary_key(["inv_001", "line_1"])
SELECT *
FROM workers
WHERE id = 'w_001'
LIMIT 1;
SELECT *
FROM invoice_lines
WHERE invoice_id = 'inv_001' AND id = 'line_1'
LIMIT 1;