@sfutureapps/db-sdk

JS SDK for a ThinkPHP DB Gateway (MySQL).

It provides a chainable query builder for:

• selecting rows with filters, ordering, pagination
• joins, group/having, distinct
• aggregate helpers (count, sum, avg, min, max)
• inserts, updates, deletes (updates/deletes require filters)

Under the hood it calls the gateway endpoints:

• POST v3/api/query
• POST v3/api/insert
• POST v3/api/update
• POST v3/api/delete

Install

npm i @sfutureapps/db-sdk
# or
pnpm add @sfutureapps/db-sdk
# or
bun add @sfutureapps/db-sdk

Create a client

import { createClient } from "@sfutureapps/db-sdk";

const db = createClient("https://yourdomain.com/api", {
  // optional
  apiKey: "YOUR_API_KEY",
  // optional (recommended for SSR or custom storage)
  accessToken: () => localStorage.getItem("token"),
  // optional extra headers
  headers: { "X-App-Version": "1.0.0" },
});

Environment-variable configuration (optional)

If you prefer env vars (or you already have apps using them), the SDK also reads:

• API_URL (also supports VITE_API_URL, NEXT_PUBLIC_API_URL, REACT_APP_API_URL)
• API_KEY
• TOKEN_NAME (default: token)
• IS_AUTH (true/1/yes enables token requirement)
• INCLUDE_TOKEN_IN_GET (default: true)

If you pass baseUrl to createClient(...), it takes precedence over API_URL.

How to use

Select + pagination

const res = await db
  .from("booking_paginate_view")
  .select("*")
  .like("property_name", "%Kampot%")
  .order("createtime", { ascending: false })
  .page(1, 20)
  .withCount()
  .execute();

if (res.error) throw new Error(res.error.message);

console.log(res.data); // rows
console.log(res.paginate); // { page, limit, offset, total, last_page, ... }

Single row

const res = await db.from("bookings").select("*").eq("id", 123).single().execute();
if (res.error) throw new Error(res.error.message);
console.log(res.data); // object | null

Insert / Update / Delete

await db.from("bookings").insert({ property_id: 1, user_id: 2, total_amount: 50 });

// Update/Delete require at least one filter
await db.from("bookings").eq("id", 123).update({ status: "paid" });
await db.from("bookings").eq("id", 123).delete();

Aggregates

const totalPaid = await db.from("bookings").eq("status", "paid").sum("total_amount");
if (totalPaid.error) throw new Error(totalPaid.error.message);
console.log(totalPaid.data);

// Notes:
// - .withCount() = pagination total rows (res.paginate.total)
// - .count() = aggregate COUNT(*) result in res.data

Full example (TypeScript)

This is a complete end-to-end example covering read + paginate, joins, aggregates, and writes.

import { createClient } from "@sfutureapps/db-sdk";

type Booking = {
  id: number;
  property_id: number;
  user_id: number;
  status: "draft" | "paid" | "cancelled" | string;
  total_amount: number;
  createtime: string;
};

async function main() {
  const db = createClient("https://yourdomain.com/api", {
    apiKey: "YOUR_API_KEY",
    accessToken: () => {
      // Browser: read from localStorage (or cookie)
      // SSR/Node: return process.env.TOKEN
      return typeof window !== "undefined" ? localStorage.getItem("token") : null;
    },
  });

  // 1) List bookings (page 1, 20 per page) + total count
  const list = await db
    .from<Booking>("bookings")
    .select(["id", "property_id", "user_id", "status", "total_amount", "createtime"])
    .eq("status", "paid")
    .order("createtime", { ascending: false })
    .page(1, 20)
    .withCount()
    .execute();

  if (list.error) throw new Error(list.error.message);
  console.log("rows:", list.data.length);
  console.log("paginate:", list.paginate);

  // 2) Fetch a single booking
  const one = await db.from<Booking>("bookings").select("*").eq("id", 123).single().execute();
  if (one.error) throw new Error(one.error.message);
  console.log("booking:", one.data);

  // 3) Join example (join clause depends on your gateway/SQL conventions)
  // NOTE: 'on' is passed through to the gateway.
  const joined = await db
    .from<any>("bookings b")
    .select(["b.id", "b.total_amount", "p.name as property_name"])
    .join("properties p", "p.id = b.property_id", "LEFT")
    .gte("b.total_amount", 50)
    .limit(10)
    .execute();

  if (joined.error) throw new Error(joined.error.message);
  console.log("joined:", joined.data);

  // 4) Aggregates
  const sum = await db.from<Booking>("bookings").eq("status", "paid").sum("total_amount");
  if (sum.error) throw new Error(sum.error.message);
  console.log("total paid:", sum.data);

  // 5) Insert
  const inserted = await db.from<Booking>("bookings").insert({
    property_id: 1,
    user_id: 2,
    status: "draft",
    total_amount: 50,
  });

  if (inserted.error) throw new Error(inserted.error.message);
  console.log("inserted:", inserted.data);

  // 6) Update (requires filters)
  const updated = await db.from<Booking>("bookings").eq("id", 123).update({ status: "paid" });
  if (updated.error) throw new Error(updated.error.message);
  console.log("updated:", updated.data);

  // 7) Delete (requires filters)
  const deleted = await db.from<Booking>("bookings").eq("id", 123).delete();
  if (deleted.error) throw new Error(deleted.error.message);
  console.log("deleted:", deleted.data);
}

main().catch((err) => {
  console.error(err);
  process.exitCode = 1;
});