SQLite as a globally distributed DB — how Turso + libSQL is transforming serverless edge architecture

// libSQL client — HTTP mode (serverless/edge environments)
import { createClient } from "@libsql/client";
 
const client = createClient({
  // Actual format: libsql://[db-name]-[org-name].turso.io
  // https:// protocol also works as an HTTP fallback, but libsql:// is the standard format
  url: "libsql://my-db-myorg.turso.io",
  authToken: process.env.TURSO_AUTH_TOKEN,
});
 
const result = await client.execute(
  "SELECT * FROM users WHERE active = 1 LIMIT 10"
);
console.log(result.rows);

// libSQL client — Embedded replica mode (Node.js server / Fly.io, etc.)
import { createClient } from "@libsql/client";
 
const client = createClient({
  url: "file:local.db",                          // Local SQLite file
  syncUrl: "libsql://my-db-myorg.turso.io",      // Remote primary
  authToken: process.env.TURSO_AUTH_TOKEN,
  syncInterval: 60,                              // Auto-sync every 60 seconds
});
 
// Reads: local disk speed (~200 nanoseconds)
const result = await client.execute("SELECT * FROM products");
 
// Writes: automatically routed to remote primary
await client.execute(
  "INSERT INTO orders (user_id, total) VALUES (?, ?)",
  [userId, total]
);

// Cloudflare Workers + D1
export default {
  async fetch(request: Request, env: Env): Promise<Response> {
    const { results } = await env.DB.prepare(
      "SELECT * FROM posts WHERE published = 1 ORDER BY created_at DESC LIMIT 20"
    ).all();
 
    return Response.json(results);
  },
};

// Automated per-tenant DB provisioning via Turso Platform REST API
// An official management SDK (@turso/api) is also available — check latest docs for package name
async function provisionTenantDatabase(tenantId: string, region: string) {
  const org = process.env.TURSO_ORG!;
  const apiToken = process.env.TURSO_API_TOKEN!;
  const baseUrl = "https://api.turso.tech/v1";
 
  // Create tenant DB
  const createRes = await fetch(`${baseUrl}/organizations/${org}/databases`, {
    method: "POST",
    headers: {
      Authorization: `Bearer ${apiToken}`,
      "Content-Type": "application/json",
    },
    body: JSON.stringify({
      name: `tenant-${tenantId}`,
      group: "production",
      location: region, // Region matching the tenant's location
    }),
  });
  const { database } = await createRes.json();
 
  // Issue access token (isolated per tenant)
  const tokenRes = await fetch(
    `${baseUrl}/organizations/${org}/databases/${database.Name}/auth/tokens`,
    {
      method: "POST",
      headers: { Authorization: `Bearer ${apiToken}` },
    }
  );
  const { jwt } = await tokenRes.json();
 
  return {
    url: `libsql://${database.Hostname}`,
    authToken: jwt,
  };
}
 
// Tenant connection info is stored in a separate meta-DB (e.g., PostgreSQL, Redis)
// Saved at initial provisioning and looked up on each subsequent request
async function getTenantCredentials(tenantId: string): Promise<{ url: string; authToken: string }> {
  const meta = await metaDb.findOne({ tenantId });
  if (!meta) throw new Error(`DB info for tenant ${tenantId} not found.`);
  return { url: meta.dbUrl, authToken: meta.dbToken };
}
 
// Handle tenant request
async function handleTenantRequest(tenantId: string, query: string) {
  const { url, authToken } = await getTenantCredentials(tenantId);
  const client = createClient({ url, authToken });
  return await client.execute(query);
}

// Schema migration — apply to all tenants in bulk
async function migrateAllTenants(migrationSql: string) {
  const tenants = await getAllTenants();
 
  await Promise.allSettled(
    tenants.map(async (tenant) => {
      const client = createClient({
        url: tenant.dbUrl,
        authToken: tenant.dbToken,
      });
      await client.executeMultiple(migrationSql);
    })
  );
}

pnpm add @libsql/client drizzle-orm
pnpm add -D drizzle-kit

// lib/db.ts — Drizzle + libSQL setup
import { drizzle } from "drizzle-orm/libsql";
import { createClient } from "@libsql/client";
 
const client = createClient({
  url: process.env.TURSO_DATABASE_URL!,
  authToken: process.env.TURSO_AUTH_TOKEN!,
});
 
export const db = drizzle(client);

// schema.ts
import { sqliteTable, text, integer } from "drizzle-orm/sqlite-core";
 
export const posts = sqliteTable("posts", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  title: text("title").notNull(),
  content: text("content").notNull(),
  authorId: integer("author_id").notNull(),
  publishedAt: integer("published_at", { mode: "timestamp" }),
});

// app/posts/page.tsx — Server Component (reads)
import { db } from "@/lib/db";
import { posts } from "@/schema";
import { desc, isNotNull } from "drizzle-orm";
 
export default async function PostsPage() {
  const publishedPosts = await db
    .select()
    .from(posts)
    .where(isNotNull(posts.publishedAt))
    .orderBy(desc(posts.publishedAt))
    .limit(20);
 
  return (
    <ul>
      {publishedPosts.map((post) => (
        <li key={post.id}>{post.title}</li>
      ))}
    </ul>
  );
}

// app/posts/actions.ts — Server Action (writes)
"use server";
 
import { db } from "@/lib/db";
import { posts } from "@/schema";
import { revalidatePath } from "next/cache";
 
export async function createPost(formData: FormData) {
  const title = formData.get("title") as string;
  const content = formData.get("content") as string;
 
  // Boundary validation — in production, use a schema validation library like zod
  if (!title?.trim() || !content?.trim()) {
    throw new Error("Title and content are required.");
  }
 
  await db.insert(posts).values({
    title,
    content,
    authorId: 1, // In practice, retrieved from session
  });
 
  revalidatePath("/posts");
}

// src/index.ts — Hono + D1
import { Hono } from "hono";
import { cors } from "hono/cors";
 
type Bindings = {
  DB: D1Database;
};
 
const app = new Hono<{ Bindings: Bindings }>();
 
app.use("/*", cors());
 
app.get("/api/products", async (c) => {
  const { results } = await c.env.DB.prepare(
    "SELECT id, name, price FROM products WHERE active = 1"
  ).all();
 
  return c.json(results);
});
 
// Sessions API — guarantees read-after-write consistency
// "first-primary" is a literal defined by Cloudflare that forces
// all queries in the session to run on the primary node
app.post("/api/orders", async (c) => {
  const body = await c.req.json();
 
  const session = c.env.DB.withSession("first-primary");
  await session
    .prepare("INSERT INTO orders (user_id, product_id, amount) VALUES (?, ?, ?)")
    .bind(body.userId, body.productId, body.amount)
    .run();
 
  // Read with the same session — guaranteed to see the data just written
  const { results } = await session
    .prepare(
      "SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC LIMIT 1"
    )
    .bind(body.userId)
    .all();
 
  return c.json(results[0]);
});
 
export default app;

# wrangler.toml
name = "my-api"
main = "src/index.ts"
compatibility_date = "2026-01-01"
 
[[d1_databases]]
binding = "DB"
database_name = "my-database"
database_id = "your-database-id-here"

// AI agent DB setup — including vector index
const initSchema = `
  CREATE TABLE IF NOT EXISTS agent_memory (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    agent_id TEXT NOT NULL,
    content TEXT NOT NULL,
    embedding F32_BLOB(1536),  -- OpenAI text-embedding-3-small
    created_at INTEGER DEFAULT (unixepoch())
  );
 
  CREATE INDEX IF NOT EXISTS memory_embedding_idx
    ON agent_memory (libsql_vector_idx(embedding));
`;
 
await client.executeMultiple(initSchema);

// Store memory
async function storeMemory(
  agentId: string,
  content: string,
  embedding: number[]
) {
  await client.execute({
    sql: `
      INSERT INTO agent_memory (agent_id, content, embedding)
      VALUES (?, ?, vector32(?))
    `,
    args: [agentId, content, JSON.stringify(embedding)],
  });
}
 
// Search similar memories (RAG)
// vector_top_k returns rowid and distance, so use aliases to clarify columns
async function recallSimilarMemories(
  agentId: string,
  queryEmbedding: number[],
  topK = 5
) {
  const result = await client.execute({
    sql: `
      SELECT m.content, v.distance
      FROM vector_top_k('memory_embedding_idx', vector32(?), ?) AS v
      JOIN agent_memory AS m ON m.rowid = v.id
      WHERE m.agent_id = ?
      ORDER BY v.distance
    `,
    args: [JSON.stringify(queryEmbedding), topK, agentId],
  });
 
  return result.rows;
}

pnpm add @prisma/client
pnpm add -D prisma
npx prisma init

// prisma/schema.prisma
datasource db {
  provider     = "mysql"
  url          = env("DATABASE_URL")
  relationMode = "prisma" // On Vitess, Prisma manages referential integrity at the application layer instead of FK constraints
}
 
generator client {
  provider = "prisma-client-js"
}
 
model Post {
  id          Int       @id @default(autoincrement())
  title       String
  content     String    @db.Text
  published   Boolean   @default(false)
  publishedAt DateTime?
  authorId    Int
 
  @@index([authorId])
}

# .env — PlanetScale connection string
DATABASE_URL="mysql://username:password@aws.connect.psdb.cloud/mydb?sslaccept=strict"

// lib/db.ts — PlanetScale + Prisma (singleton for serverless environments)
import { PrismaClient } from "@prisma/client";
 
const globalForPrisma = globalThis as unknown as { prisma: PrismaClient };
 
export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient();
 
if (process.env.NODE_ENV !== "production") globalForPrisma.prisma = prisma;

// app/posts/page.tsx — Server Component
import { prisma } from "@/lib/db";
 
export default async function PostsPage() {
  const posts = await prisma.post.findMany({
    where: { published: true },
    orderBy: { publishedAt: "desc" },
    take: 20,
  });
 
  return (
    <ul>
      {posts.map((post) => (
        <li key={post.id}>{post.title}</li>
      ))}
    </ul>
  );
}