Database API

Keel provides an elegant, type-safe Model API for interacting with your models. Reach for that first before considering the Database API.

The Database API gives you direct control over SQL queries using Kysely (opens in a new tab), a type-safe query builder. Use it when you need complex queries that go beyond what the Model API offers: joins, aggregations, subqueries, or raw SQL.

import { useDatabase } from "@teamkeel/sdk";
 
const db = useDatabase();

When to use the Database API

The Model API handles most common operations, but the Database API is the right choice when you need:

Scenario	Example
Joins across tables	Fetching orders with their line items in one query
Aggregations	Counting, summing, or averaging values
GROUP BY queries	Sales totals by product category
Complex WHERE clauses	OR conditions, subqueries in filters
Raw SQL	Database-specific features or optimised queries
Bulk operations	Updating many records with a single query

⚠️

The Database API bypasses Keel's permission system. Use it carefully in functions where you've already verified the user has appropriate access.

Column naming

Column and table names are stored in snake_case at the database level, but you should use camelCase when writing queries. Keel automatically converts between the two.

// Use camelCase in your queries
const orders = await db
  .selectFrom("order")
  .select(["id", "createdAt", "customerId"]) // Not created_at, customer_id
  .execute();

SELECT queries

Basic select

import { GetOrder, useDatabase } from "@teamkeel/sdk";
 
export default GetOrder(async (ctx, inputs) => {
  const db = useDatabase();
 
  const order = await db
    .selectFrom("order")
    .selectAll()
    .where("id", "=", inputs.id)
    .executeTakeFirstOrThrow();
 
  return order;
});

Select specific columns

const orders = await db
  .selectFrom("order")
  .select(["id", "reference", "status", "createdAt"])
  .execute();

Multiple conditions

Chain .where() calls for AND conditions:

const pendingOrders = await db
  .selectFrom("order")
  .selectAll()
  .where("status", "=", "pending")
  .where("createdAt", ">=", new Date(2024, 0, 1))
  .execute();

OR conditions

Use the expression builder for OR logic:

const orders = await db
  .selectFrom("order")
  .selectAll()
  .where((eb) => eb.or([
    eb("status", "=", "pending"),
    eb("status", "=", "processing")
  ]))
  .execute();

Ordering and pagination

const recentOrders = await db
  .selectFrom("order")
  .selectAll()
  .orderBy("createdAt", "desc")
  .limit(10)
  .offset(20)
  .execute();

Joins

Inner join

Fetch orders with their customer details:

const ordersWithCustomers = await db
  .selectFrom("order")
  .innerJoin("customer", "customer.id", "order.customerId")
  .select([
    "order.id",
    "order.reference",
    "order.status",
    "customer.name as customerName",
    "customer.email as customerEmail"
  ])
  .execute();

Left join

Include orders even when related data might be missing:

const ordersWithShipments = await db
  .selectFrom("order")
  .leftJoin("shipment", "shipment.orderId", "order.id")
  .select([
    "order.id",
    "order.reference",
    "shipment.trackingNumber",
    "shipment.shippedAt"
  ])
  .execute();

Multiple joins

const orderDetails = await db
  .selectFrom("order")
  .innerJoin("customer", "customer.id", "order.customerId")
  .innerJoin("orderLine", "orderLine.orderId", "order.id")
  .innerJoin("product", "product.id", "orderLine.productId")
  .select([
    "order.reference",
    "customer.name as customerName",
    "product.name as productName",
    "orderLine.quantity",
    "orderLine.unitPrice"
  ])
  .execute();

INSERT queries

Insert a single row

const newProduct = await db
  .insertInto("product")
  .values({
    sku: "SKU-001",
    name: "Widget",
    price: 29.99,
    quantity: 100
  })
  .returningAll()
  .executeTakeFirstOrThrow();

Insert multiple rows

await db
  .insertInto("stockMovement")
  .values([
    { productId: "prod_1", quantity: 10, type: "receipt" },
    { productId: "prod_2", quantity: 5, type: "receipt" },
    { productId: "prod_3", quantity: 20, type: "receipt" }
  ])
  .execute();

Return specific columns

const result = await db
  .insertInto("order")
  .values({
    reference: "ORD-2024-001",
    customerId: inputs.customerId,
    status: "pending"
  })
  .returning(["id", "reference", "createdAt"])
  .executeTakeFirstOrThrow();

UPDATE queries

Update with conditions

await db
  .updateTable("order")
  .set({ status: "shipped" })
  .where("id", "=", inputs.orderId)
  .execute();

Update multiple columns

await db
  .updateTable("product")
  .set({
    price: 34.99,
    updatedAt: new Date()
  })
  .where("sku", "=", "SKU-001")
  .execute();

Bulk update

// Mark all pending orders older than 30 days as cancelled
const thirtyDaysAgo = new Date();
thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);
 
await db
  .updateTable("order")
  .set({ status: "cancelled" })
  .where("status", "=", "pending")
  .where("createdAt", "<", thirtyDaysAgo)
  .execute();

DELETE queries

Delete with condition

await db
  .deleteFrom("orderLine")
  .where("orderId", "=", inputs.orderId)
  .execute();

Delete with multiple conditions

// Remove declined wedding invitees
await db
  .deleteFrom("weddingInvitee")
  .where("status", "=", "declined")
  .where("weddingId", "=", inputs.weddingId)
  .execute();

Aggregations

Count records

import { sql } from "kysely";
 
const result = await db
  .selectFrom("order")
  .select((eb) => eb.fn.count("id").as("orderCount"))
  .where("status", "=", "completed")
  .executeTakeFirstOrThrow();
 
console.log(result.orderCount); // e.g., 42

Sum values

const result = await db
  .selectFrom("orderLine")
  .select((eb) => eb.fn.sum("quantity").as("totalQuantity"))
  .where("orderId", "=", inputs.orderId)
  .executeTakeFirstOrThrow();

Multiple aggregations

const stats = await db
  .selectFrom("order")
  .select((eb) => [
    eb.fn.count("id").as("totalOrders"),
    eb.fn.sum("total").as("totalRevenue"),
    eb.fn.avg("total").as("averageOrderValue")
  ])
  .where("status", "=", "completed")
  .executeTakeFirstOrThrow();

GROUP BY queries

Sales by category

const salesByCategory = await db
  .selectFrom("orderLine")
  .innerJoin("product", "product.id", "orderLine.productId")
  .innerJoin("category", "category.id", "product.categoryId")
  .select((eb) => [
    "category.name as categoryName",
    eb.fn.sum("orderLine.quantity").as("totalQuantity"),
    eb.fn.sum(sql`order_line.quantity * order_line.unit_price`).as("totalRevenue")
  ])
  .groupBy("category.name")
  .orderBy("totalRevenue", "desc")
  .execute();

Orders per customer

const orderCounts = await db
  .selectFrom("order")
  .innerJoin("customer", "customer.id", "order.customerId")
  .select((eb) => [
    "customer.id",
    "customer.name",
    eb.fn.count("order.id").as("orderCount")
  ])
  .groupBy(["customer.id", "customer.name"])
  .having((eb) => eb.fn.count("order.id"), ">", 5)
  .execute();

Subqueries

Subquery in WHERE

Find customers who have placed orders:

const customersWithOrders = await db
  .selectFrom("customer")
  .selectAll()
  .where((eb) =>
    eb.exists(
      eb.selectFrom("order")
        .select("order.id")
        .whereRef("order.customerId", "=", "customer.id")
    )
  )
  .execute();

Subquery in SELECT

Add computed columns:

const productsWithOrderCount = await db
  .selectFrom("product")
  .select((eb) => [
    "product.id",
    "product.name",
    eb.selectFrom("orderLine")
      .select((eb2) => eb2.fn.count("id").as("count"))
      .whereRef("orderLine.productId", "=", "product.id")
      .as("timesOrdered")
  ])
  .execute();

Raw SQL

When you need database-specific features or complex expressions, use the sql template tag:

import { sql } from "kysely";
 
// Raw SQL in SELECT
const result = await db
  .selectFrom("product")
  .select([
    "id",
    "name",
    sql<number>`EXTRACT(DAYS FROM NOW() - created_at)`.as("daysOld")
  ])
  .execute();

Full raw query

import { sql } from "kysely";
 
interface AuditLog {
  id: string;
  tableName: string;
  op: string;
  data: any;
  createdAt: Date;
}
 
const logs = await sql<AuditLog>`
  SELECT * FROM keel_audit 
  WHERE table_name = ${tableName}
  AND op = 'update'
  ORDER BY created_at DESC
  LIMIT 100
`.execute(db);
 
console.log(logs.rows);

⚠️

When using raw SQL, column names must be in snake_case since you're writing actual SQL. The camelCase conversion only applies to Kysely's query builder methods.

Transactions

Keel automatically wraps write functions in database transactions. When you use useDatabase() inside a write function, your queries participate in the same transaction as the Model API operations.

import { CreateOrderWithLines, models, useDatabase } from "@teamkeel/sdk";
 
export default CreateOrderWithLines(async (ctx, inputs) => {
  const db = useDatabase();
 
  // Create order using Model API
  const order = await models.order.create({
    reference: inputs.reference,
    customerId: inputs.customerId
  });
 
  // Create order lines using Database API
  // Both operations are in the same transaction
  await db
    .insertInto("orderLine")
    .values(
      inputs.lines.map((line) => ({
        orderId: order.id,
        productId: line.productId,
        quantity: line.quantity,
        unitPrice: line.unitPrice
      }))
    )
    .execute();
 
  return order;
});

If any operation fails, the entire transaction rolls back. For more details on transaction behaviour, see Transactions.

Type safety

Kysely provides type safety based on your database schema. When you use useDatabase(), the returned client knows about your tables and columns.

const db = useDatabase();
 
// TypeScript knows the available tables
const orders = await db
  .selectFrom("order") // Autocomplete shows your model names
  .select(["id", "reference"]) // Autocomplete shows column names
  .execute();
 
// Type errors for invalid columns
const invalid = await db
  .selectFrom("order")
  .select(["nonExistentColumn"]) // TypeScript error
  .execute();

Typing raw SQL results

For raw SQL queries, specify the return type:

import { sql } from "kysely";
 
interface OrderSummary {
  customerId: string;
  orderCount: number;
  totalSpent: number;
}
 
const summaries = await sql<OrderSummary>`
  SELECT 
    customer_id,
    COUNT(*) as order_count,
    SUM(total) as total_spent
  FROM "order"
  GROUP BY customer_id
`.execute(db);
 
// summaries.rows is typed as OrderSummary[]

Practical examples

Low stock report

import { LowStockReport, useDatabase } from "@teamkeel/sdk";
 
export default LowStockReport(async (ctx, inputs) => {
  const db = useDatabase();
 
  const lowStockItems = await db
    .selectFrom("stockItem")
    .innerJoin("location", "location.id", "stockItem.locationId")
    .select([
      "stockItem.sku",
      "stockItem.name",
      "stockItem.quantity",
      "stockItem.reorderLevel",
      "location.name as locationName"
    ])
    .where("stockItem.quantity", "<=", sql`stock_item.reorder_level`)
    .orderBy("stockItem.quantity", "asc")
    .execute();
 
  return { items: lowStockItems };
});

Order value analysis

import { OrderAnalysis, useDatabase } from "@teamkeel/sdk";
import { sql } from "kysely";
 
export default OrderAnalysis(async (ctx, inputs) => {
  const db = useDatabase();
 
  const analysis = await db
    .selectFrom("order")
    .innerJoin("orderLine", "orderLine.orderId", "order.id")
    .select((eb) => [
      sql`DATE_TRUNC('month', "order".created_at)`.as("month"),
      eb.fn.count("order.id").distinct().as("orderCount"),
      eb.fn.sum(sql`order_line.quantity * order_line.unit_price`).as("revenue")
    ])
    .where("order.status", "=", "completed")
    .where("order.createdAt", ">=", inputs.startDate)
    .groupBy(sql`DATE_TRUNC('month', "order".created_at)`)
    .orderBy("month", "asc")
    .execute();
 
  return { monthlyAnalysis: analysis };
});

Inventory valuation by warehouse

import { InventoryValuation, useDatabase } from "@teamkeel/sdk";
import { sql } from "kysely";
 
export default InventoryValuation(async (ctx, inputs) => {
  const db = useDatabase();
 
  const valuation = await db
    .selectFrom("stockItem")
    .innerJoin("location", "location.id", "stockItem.locationId")
    .innerJoin("product", "product.id", "stockItem.productId")
    .select((eb) => [
      "location.id as locationId",
      "location.name as locationName",
      eb.fn.count("stockItem.id").as("itemCount"),
      eb.fn.sum("stockItem.quantity").as("totalUnits"),
      eb.fn.sum(sql`stock_item.quantity * product.cost_price`).as("totalValue")
    ])
    .groupBy(["location.id", "location.name"])
    .orderBy("totalValue", "desc")
    .execute();
 
  return { warehouses: valuation };
});

Database API vs Model API

Use Model API when...	Use Database API when...
CRUD operations on a single model	Joining multiple tables
Simple filtering and ordering	Complex WHERE clauses with OR
You need permission checks	Aggregations (COUNT, SUM, AVG)
Working with relationships via IDs	GROUP BY queries
Standard create/update/delete	Bulk updates or deletes
	Raw SQL for database-specific features