Blog

Using Google Sheets as a Database with Bun and ElysiaJS

Build a type-safe REST API with Bun and ElysiaJS that reads and writes Google Sheets data via the GKit SheetsAPI - with Eden Treaty client and response caching.

Jun 29, 20264 min read

#bun #elysia #typescript #google-sheets #api

Why Google Sheets as a database?

Not every project needs Postgres. Internal tools, product catalogs, content queues, and lightweight admin dashboards often live in Google Sheets because that is where the team already edits data. Turning that sheet into a proper REST API - with pagination, search, and typed responses - is what the GKit SheetsAPI is built for.

This post walks through a Bun + ElysiaJS service that reads and writes Google Sheets data through the GKit SheetsAPI, adds in-memory caching with TTL, and exposes a fully type-safe client via Elysia's Eden Treaty.

Project setup

bun create elysia sheets-api
cd sheets-api
bun add @elysiajs/eden

Add your GKit credentials to .env:

GKIT_USER_KEY=your_user_key
GKIT_API_KEY=sk_live_xxxxxxxxxxxxxxxxxxxx
GKIT_SHEET_NAME=products
PORT=3000

Bun reads .env automatically - no dotenv package needed. Values are available via process.env or Bun.env.

TypeScript types

Define the shape of a row and the GKit response envelope before writing any route logic:

// src/types.ts
 
export interface ProductRow {
  id: string;
  name: string;
  price: number;
  stock: number;
  category: string;
}
 
export interface GKitResponse<T> {
  data: T[];
  meta: {
    total: number;
    limit: number;
    offset: number;
  };
}

GKit SheetsAPI client

The GKit SheetsAPI base URL follows the pattern https://api.gkit.io/api/spreadsheets/{userKey}/{sheetName}. GET requests accept limit, offset, search, sort, and order as query parameters. POST requests accept a { rows: [...] } body to append rows.

Wrap those calls in a small module so route handlers stay clean:

// src/sheets.ts
 
import type { GKitResponse, ProductRow } from "./types";
 
const BASE = `https://api.gkit.io/api/spreadsheets/${process.env.GKIT_USER_KEY}/${process.env.GKIT_SHEET_NAME}`;
 
const headers = {
  Authorization: `Bearer ${process.env.GKIT_API_KEY}`,
  "Content-Type": "application/json",
};
 
export async function fetchProducts(params: {
  limit?: number;
  offset?: number;
  search?: string;
  sort?: string;
  order?: "asc" | "desc";
}): Promise<GKitResponse<ProductRow>> {
  const url = new URL(BASE);
  if (params.limit != null) url.searchParams.set("limit", String(params.limit));
  if (params.offset != null) url.searchParams.set("offset", String(params.offset));
  if (params.search) url.searchParams.set("search", params.search);
  if (params.sort) url.searchParams.set("sort", params.sort);
  if (params.order) url.searchParams.set("order", params.order);
 
  const res = await fetch(url.toString(), { headers });
  if (!res.ok) throw new Error(`GKit error: ${res.status}`);
  return res.json() as Promise<GKitResponse<ProductRow>>;
}
 
export async function appendProducts(rows: Partial<ProductRow>[]): Promise<void> {
  const res = await fetch(BASE, {
    method: "POST",
    headers,
    body: JSON.stringify({ rows }),
  });
  if (!res.ok) throw new Error(`GKit write error: ${res.status}`);
}

In-memory cache with TTL

A Map-based cache cuts down on upstream API calls for read-heavy endpoints without adding any dependencies:

// src/cache.ts
 
interface CacheEntry<T> {
  value: T;
  expiresAt: number;
}
 
const store = new Map<string, CacheEntry<unknown>>();
 
export function cacheGet<T>(key: string): T | null {
  const entry = store.get(key) as CacheEntry<T> | undefined;
  if (!entry || Date.now() > entry.expiresAt) {
    store.delete(key);
    return null;
  }
  return entry.value;
}
 
export function cacheSet<T>(key: string, value: T, ttlMs: number): void {
  store.set(key, { value, expiresAt: Date.now() + ttlMs });
}

A 30-second TTL works for product listings that update infrequently. Adjust to match your sheet's edit cadence.

The ElysiaJS application

Elysia uses a fluent builder API. Route schemas are validated at runtime and the same types power the Eden Treaty client - no code generation required.

// src/index.ts
 
import { Elysia, t } from "elysia";
import { fetchProducts, appendProducts } from "./sheets";
import { cacheGet, cacheSet } from "./cache";
 
const TTL = 30_000; // 30 seconds
 
const app = new Elysia()
  .get(
    "/products",
    async ({ query }) => {
      const key = JSON.stringify(query);
      const cached = cacheGet(key);
      if (cached) return cached;
 
      const result = await fetchProducts({
        limit: query.limit != null ? Number(query.limit) : 20,
        offset: query.offset != null ? Number(query.offset) : 0,
        search: query.search,
        sort: query.sort,
        order: query.order as "asc" | "desc" | undefined,
      });
 
      cacheSet(key, result, TTL);
      return result;
    },
    {
      query: t.Object({
        limit: t.Optional(t.String()),
        offset: t.Optional(t.String()),
        search: t.Optional(t.String()),
        sort: t.Optional(t.String()),
        order: t.Optional(t.String()),
      }),
    },
  )
  .post(
    "/products",
    async ({ body }) => {
      await appendProducts(body.rows);
      return { success: true };
    },
    {
      body: t.Object({
        rows: t.Array(t.Record(t.String(), t.Any())),
      }),
    },
  );
 
app.listen(Number(process.env.PORT) || 3000);
console.log(`Listening on port ${app.server?.port}`);
 
export type App = typeof app;

Exporting App is the only step needed to unlock the type-safe client. There is no schema file and no OpenAPI generation step.

Eden Treaty type-safe client

From any other file - or a separate frontend package - consume the API with full type inference:

// src/client.ts
 
import { treaty } from "@elysiajs/eden";
import type { App } from "./index";
 
const client = treaty<App>("http://localhost:3000");
 
// TypeScript knows the shape of `data` and `meta` from the route definition
const { data, error } = await client.products.get({
  query: { limit: "10", search: "keyboard" },
});
 
if (error) throw error;
console.log(data?.meta.total, data?.data[0]?.name);
 
// POST body is type-checked against the route schema
await client.products.post({
  rows: [{ name: "Trackpad", price: 79, stock: 50, category: "accessories" }],
});

No manual fetch, no URL construction, no casting to any. The contract flows from the Elysia route definition through to the client automatically.

Running it

bun run src/index.ts

Bun executes TypeScript directly - no compile step in development. For production:

bun build src/index.ts --outdir dist --target bun
bun dist/index.js

What you get

A spreadsheet your team edits in a browser becomes a paginated, searchable, cacheable REST API in roughly 100 lines of TypeScript. The GKit SheetsAPI handles Google OAuth, quota management, and row serialization so your application code stays focused on business logic.

The same pattern extends to any sheet - swap GKIT_SHEET_NAME and update the row type. Multiple sheets map cleanly to multiple route groups in the same Elysia app.

Sign up for a free GKit API key at gkit.io/signup and have your first sheet-backed endpoint running in under five minutes.

All posts