Blog

Using Google Sheets as a Database in Deno Fresh

Build a Fresh island-based app that reads and writes data via the GKit SheetsAPI - with Deno KV caching and type-safe handlers.

Jun 28, 20265 min read

#deno #fresh #typescript #google-sheets #api

Why Google Sheets makes sense as a lightweight database

Most side projects and internal tools don't need Postgres. They need a place to store rows, let non-technical teammates edit the data directly, and expose it to an API without standing up a backend. Google Sheets already handles the first two. The GKit SheetsAPI handles the third.

This post walks through a complete Deno Fresh application that reads a product catalog from a Google Sheet and lets users submit new rows - all through a type-safe API layer and a Deno KV cache that keeps latency low without hammering the upstream API on every request.

Project setup

Start with a fresh Fresh project and add the GKit configuration to deno.json:

{
  "tasks": {
    "start": "deno run -A --watch=static/,routes/ dev.ts"
  },
  "imports": {
    "$fresh/": "https://deno.land/x/fresh@1.6.8/",
    "preact": "https://esm.sh/preact@10.19.2",
    "preact/": "https://esm.sh/preact@10.19.2/"
  },
  "env": {
    "GKIT_API_KEY": "",
    "GKIT_USER_KEY": "",
    "GKIT_SHEET_NAME": "products"
  }
}

Store your API key in a .env file - never commit it. Fresh reads .env automatically in development via Deno.env.

TypeScript interfaces for the GKit response

Before writing any fetch logic, define the shape of what the SheetsAPI returns. This gives you autocomplete throughout the codebase and catches shape mismatches at compile time rather than at runtime.

// lib/gkit.ts
 
export interface GKitMeta {
  total: number;
  limit: number;
  offset: number;
}
 
export interface GKitResponse<T> {
  data: T[];
  meta: GKitMeta;
}
 
export interface Product {
  id: string;
  name: string;
  price: number;
  sku: string;
  in_stock: boolean;
}
 
export interface GKitFetchOptions {
  limit?: number;
  offset?: number;
  search?: string;
  sort?: string;
  order?: "asc" | "desc";
  fields?: string;
}

The GKitResponse<T> generic keeps this reusable. Swap Product for any row shape without rewriting the fetch layer.

Server route with Deno KV caching

Fresh server routes run on the server and are the right place to make authenticated API calls - the Authorization header never touches the browser. The route below fetches the product list, caches it in Deno KV for 60 seconds, and returns JSON to the island.

// routes/products.ts
 
import { Handlers } from "$fresh/server.ts";
import type { GKitResponse, GKitFetchOptions, Product } from "../lib/gkit.ts";
 
const BASE_URL = "https://api.gkit.io/api/spreadsheets";
const USER_KEY = Deno.env.get("GKIT_USER_KEY") ?? "";
const SHEET_NAME = Deno.env.get("GKIT_SHEET_NAME") ?? "products";
const API_KEY = Deno.env.get("GKIT_API_KEY") ?? "";
const CACHE_TTL_MS = 60_000;
 
const kv = await Deno.openKv();
 
async function fetchProducts(opts: GKitFetchOptions = {}): Promise<GKitResponse<Product>> {
  const cacheKey = ["products", JSON.stringify(opts)];
  const cached = await kv.get<GKitResponse<Product>>(cacheKey);
 
  if (cached.value !== null) {
    return cached.value;
  }
 
  const url = new URL(`${BASE_URL}/${USER_KEY}/${SHEET_NAME}`);
  if (opts.limit !== undefined) url.searchParams.set("limit", String(opts.limit));
  if (opts.offset !== undefined) url.searchParams.set("offset", String(opts.offset));
  if (opts.search) url.searchParams.set("search", opts.search);
  if (opts.sort) url.searchParams.set("sort", opts.sort);
  if (opts.order) url.searchParams.set("order", opts.order);
  if (opts.fields) url.searchParams.set("fields", opts.fields);
 
  const res = await fetch(url.toString(), {
    headers: { Authorization: `Bearer ${API_KEY}` },
  });
 
  if (!res.ok) {
    throw new Error(`GKit API error: ${res.status} ${res.statusText}`);
  }
 
  const json: GKitResponse<Product> = await res.json();
 
  await kv.set(cacheKey, json, { expireIn: CACHE_TTL_MS });
 
  return json;
}
 
export const handler: Handlers = {
  async GET(req) {
    const url = new URL(req.url);
    const limit = Number(url.searchParams.get("limit") ?? 20);
    const offset = Number(url.searchParams.get("offset") ?? 0);
    const search = url.searchParams.get("search") ?? undefined;
 
    try {
      const result = await fetchProducts({ limit, offset, search });
      return Response.json(result);
    } catch (err) {
      return Response.json({ error: (err as Error).message }, { status: 502 });
    }
  },
 
  async POST(req) {
    const body = await req.json();
    const rows: Partial<Product>[] = body.rows ?? [];
 
    const res = await fetch(`${BASE_URL}/${USER_KEY}/${SHEET_NAME}`, {
      method: "POST",
      headers: {
        Authorization: `Bearer ${API_KEY}`,
        "Content-Type": "application/json",
      },
      body: JSON.stringify({ rows }),
    });
 
    if (!res.ok) {
      return Response.json({ error: "Failed to append rows" }, { status: 502 });
    }
 
    // Bust the default-view cache entry so the island sees new data immediately.
    await kv.delete(["products", JSON.stringify({})]);
 
    return Response.json({ ok: true });
  },
};

The kv.set call accepts an expireIn option in milliseconds. Deno KV handles expiry automatically - no cron job or manual cleanup needed.

Fresh Island for client interaction

Islands are Preact components that hydrate in the browser. This one fetches the product list from the route above, renders a table, and includes a form to append new rows - all without exposing the API key to the client.

// islands/ProductCatalog.tsx
 
import { useEffect, useState } from "preact/hooks";
import type { GKitResponse, Product } from "../lib/gkit.ts";
 
export default function ProductCatalog() {
  const [result, setResult] = useState<GKitResponse<Product> | null>(null);
  const [loading, setLoading] = useState(true);
  const [submitting, setSubmitting] = useState(false);
 
  async function load(search?: string) {
    setLoading(true);
    const url = new URL("/products", location.href);
    if (search) url.searchParams.set("search", search);
    const res = await fetch(url.toString());
    const json: GKitResponse<Product> = await res.json();
    setResult(json);
    setLoading(false);
  }
 
  useEffect(() => {
    load();
  }, []);
 
  async function handleAdd(e: Event) {
    e.preventDefault();
    const form = e.target as HTMLFormElement;
    const data = Object.fromEntries(new FormData(form));
    setSubmitting(true);
    await fetch("/products", {
      method: "POST",
      headers: { "Content-Type": "application/json" },
      body: JSON.stringify({
        rows: [
          {
            name: data.name,
            price: data.price,
            sku: data.sku,
            in_stock: "true",
          },
        ],
      }),
    });
    form.reset();
    setSubmitting(false);
    load();
  }
 
  return (
    <div>
      <input
        type="search"
        placeholder="Search products..."
        onInput={(e) => load((e.target as HTMLInputElement).value)}
      />
 
      {loading && <p>Loading...</p>}
 
      {result && (
        <>
          <p>{result.meta.total} products total</p>
          <table>
            <thead>
              <tr>
                <th>SKU</th>
                <th>Name</th>
                <th>Price</th>
                <th>In Stock</th>
              </tr>
            </thead>
            <tbody>
              {result.data.map((p) => (
                <tr key={p.id}>
                  <td>{p.sku}</td>
                  <td>{p.name}</td>
                  <td>${p.price}</td>
                  <td>{p.in_stock ? "Yes" : "No"}</td>
                </tr>
              ))}
            </tbody>
          </table>
        </>
      )}
 
      <form onSubmit={handleAdd}>
        <input name="name" placeholder="Name" required />
        <input name="sku" placeholder="SKU" required />
        <input name="price" type="number" placeholder="Price" required />
        <button type="submit" disabled={submitting}>
          {submitting ? "Adding..." : "Add product"}
        </button>
      </form>
    </div>
  );
}

Use the island in any page route by importing and rendering it. Fresh detects the islands/ directory and handles the hydration boundary automatically - the component renders on the server first, then hydrates in the browser.

How the Deno KV cache layer behaves

Deno KV is embedded in the Deno runtime - no external Redis instance, no separate process. In development it persists to a local SQLite file. On Deno Deploy it replicates globally, so the cache is warm near every user.

The caching strategy here is conservative: cache by serialized options, bust on write. The cache key includes the full options object (limit, offset, search) so different query combinations are stored independently. When a POST appends new rows, the handler deletes the default cache entry so the next GET sees fresh data.

For higher-traffic scenarios, a stale-while-revalidate pattern works well: return the cached value immediately, then trigger a background refresh with queueMicrotask so the next request gets updated data without waiting for the upstream API.

Deploying to Deno Deploy

Deno Deploy runs Fresh applications with zero configuration. Push to GitHub, connect the repo in the Deploy dashboard, and set the three environment variables (GKIT_API_KEY, GKIT_USER_KEY, GKIT_SHEET_NAME). Deno KV works in production without any additional setup or paid add-ons.

The GKit SheetsAPI runs on Cloudflare's global edge network. Deno Deploy also routes requests geographically. In practice this means the server-side fetch from your Fresh route to the SheetsAPI is fast from every region - not just from a single fixed location - which keeps overall response times low even without aggressive caching.

Next steps

This pattern - a Fresh server route as the authenticated proxy, Deno KV as the cache layer, an island for interactivity - scales to any sheet-backed resource. Add a second route for orders, customers, or inventory by reusing the same fetch pattern with a different generic type.

If you need write-heavy workloads, batch your rows in the POST body rather than submitting one at a time. The GKit SheetsAPI accepts arrays in rows, so a single request can append dozens of records and amortizes the round-trip cost significantly.

Ready to connect your own spreadsheet? Create a free account and generate your API key at gkit.io/signup.

All posts