mcp-patterns/database-queries

Database Queries

Use Case: Connect an MCP server to a Turso SQLite database with idempotent table creation, parameterized queries, and safe connection lifecycle. This pattern is used for persisting chat sessions, tool results, and agent state.

Prerequisites

Node.js 18+ and a TanStack Start project
Turso database URL and auth token (from turso db create)
@libsql/client installed (npm install @libsql/client)

Setup & Configuration

Store connection details in environment variables. Never hardcode the auth token.

bash

TURSO_DATABASE_URL=libsql://my-db.turso.io
TURSO_AUTH_TOKEN=eyJ...

Create a shared database client module that reuses the connection across server functions:

typescript

// src/lib/db.ts
import { createClient } from "@libsql/client";

let client: ReturnType<typeof createClient> | null = null;

export function getDb() {
  if (!client) {
    client = createClient({
      url: process.env.TURSO_DATABASE_URL!,
      authToken: process.env.TURSO_AUTH_TOKEN!,
    });
  }
  return client;
}

Core Implementation

Server function for an MCP tool that queries user sessions. The function is callable from the client and from agent tool definitions.

typescript

import { createServerFn } from "@tanstack/react-start";
import { getDb } from "../lib/db";
import { z } from "zod";

const QuerySchema = z.object({
  sessionId: z.string().uuid(),
  limit: z.number().int().min(1).max(100).default(20),
});

export const getSessionQueries = createServerFn({ method: "GET" })
  .validator((d: unknown) => QuerySchema.parse(d))
  .handler(async ({ data }) => {
    const db = getDb();
    const result = await db.execute({
      sql: "SELECT * FROM queries WHERE session_id = ? ORDER BY created_at DESC LIMIT ?",
      args: [data.sessionId, data.limit],
    });
    return result.rows;
  });

Idempotent schema migration pattern - run on first connection to ensure tables exist:

typescript

export async function ensureSchema() {
  const db = getDb();
  await db.execute(`
    CREATE TABLE IF NOT EXISTS queries (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      session_id TEXT NOT NULL,
      prompt TEXT NOT NULL,
      response TEXT,
      tool_calls TEXT,
      created_at TEXT DEFAULT (datetime('now')),
      duration_ms INTEGER
    )
  `);
  await db.execute(`
    CREATE INDEX IF NOT EXISTS idx_queries_session
    ON queries(session_id, created_at)
  `);
}

Deployment Notes

Rate limits: Turso has a 100 req/s burst limit on the free tier. Batch queries or cache frequently-accessed data.
Connection pooling: Use a single createClient instance per serverless invocation - creating a new client per query leaks connections.
Auth token rotation: Turso tokens expire. Wrap client.execute() in a retry with token refresh.
Edge case: Empty result sets return { rows: [] }, not null. Check rows.length before accessing rows[0].
Serverless cold starts: The first connection after inactivity takes ~500ms. Keep a warm connection via a scheduled ping or live-region replica.