JSON in Turso: libSQL JSON Columns, Queries, and Edge Deployment

Written and reviewed by the Jsonic editorial team — every guide is verified against the official spec or runtime before publication.

Last updated: May 27, 2026

Turso is a distributed SQLite database powered by libSQL — a fork of SQLite that adds HTTP API access, replication, and embedded replicas. JSON support in Turso/libSQL uses SQLite's built-in JSON functions: json(), json_extract(), json_each(), json_object(), and json_array(). Store JSON in any SQLite TEXT column — SQLite has no dedicated JSON type. Query nested values with json_extract(metadata, '$.plan'). Filter rows: WHERE json_extract(metadata, '$.active') = 1. Turso's JavaScript SDK (@libsql/client) executes SQL statements and returns typed results. For type safety, use Drizzle ORM with the libSQL adapter — define a column as text('metadata', { mode: 'json' }) and Drizzle handles serialization and TypeScript types. Turso's edge-replica model means your SQLite database is replicated to hundreds of edge locations — JSON queries run locally at the edge with sub-millisecond read latency. This guide covers libSQL JSON functions, Turso JS SDK usage, Drizzle with libSQL for JSON columns, JSON path queries, json_each() for array iteration, and embedded replica patterns.

SQLite JSON Functions in libSQL/Turso

Bottom line: libSQL includes all of SQLite's JSON functions — json_extract(), json_set(), json_patch(), json_each(), and more — with no additional setup. These functions are available in every Turso database without any extension.

The core read function is json_extract(col, path): it returns the value at the given JSON path, or NULL if the path doesn't exist. Use dot notation for object keys ('$.plan', '$.address.city') and bracket notation for array elements ('$.tags[0]'). Build JSON inline with json_object('plan', 'pro', 'active', 1) (returns '{"plan":"pro","active":1}') or json_array('a', 'b', 'c') (returns '["a","b","c"]'). Validate a column value with json_valid(metadata) — returns 1 for valid JSON, 0 otherwise. Check the type of a path with json_type(metadata, '$.count') — returns one of 'integer', 'real', 'text', 'array', 'object', or 'null'. For partial updates, json_set() and json_remove() are atomic server-side operations — no read-modify-write round-trip needed. json_patch() applies an RFC 7396 merge patch: keys in the patch overwrite the document, and null values delete keys.

-- json_extract: read a nested value
SELECT json_extract(metadata, '$.plan') FROM products;
-- Returns: "pro"

-- json_object: build JSON inline
SELECT json_object('plan', 'pro', 'active', 1);
-- Returns: {"plan":"pro","active":1}

-- json_array: build a JSON array
SELECT json_array('a', 'b', 'c');
-- Returns: ["a","b","c"]

-- json_valid: check if a column contains valid JSON (1 = valid, 0 = invalid)
SELECT id FROM products WHERE json_valid(metadata) = 0;

-- json_type: check the type of a path
SELECT json_type(metadata, '$.count');
-- Returns: "integer" | "real" | "text" | "array" | "object" | "null"

-- json_set: atomic partial update — set $.plan to "enterprise"
UPDATE products
SET metadata = json_set(metadata, '$.plan', 'enterprise')
WHERE id = 42;

-- json_remove: delete a key atomically
UPDATE products
SET metadata = json_remove(metadata, '$.draft')
WHERE id = 42;

-- json_patch: RFC 7396 merge patch — overwrites plan, leaves other keys
UPDATE products
SET metadata = json_patch(metadata, '{"plan":"pro"}')
WHERE id = 42;

Turso JavaScript SDK: Executing JSON Queries

Bottom line: the @libsql/client SDK sends SQL to Turso over HTTP or WebSocket and returns structured results. Use named parameters to avoid SQL injection, and call JSON.stringify when inserting JSON and JSON.parse when reading it back.

Create a client with createClient({ url, authToken }) — the url is your Turso database URL (starts with libsql://) and the authToken is the JWT from the Turso dashboard or CLI. The client exposes 3 execution methods: client.execute() for a single statement, client.batch([]) for multiple statements in a single round-trip (atomic if the mode is 'write'), and client.transaction() for explicit BEGIN/COMMIT control. Results have shape { rows: Row[], columns: string[], rowsAffected: number }. Access row values by column name: row.metadata returns the raw JSON string — call JSON.parse(row.metadata as string) manually unless you use Drizzle. Each Turso database URL is unique per database — for multi-tenant apps, create a separate database per tenant (the free plan allows up to 500 databases in 2026).

import { createClient } from "@libsql/client"

const client = createClient({
  url:       process.env.TURSO_DATABASE_URL!,  // libsql://yourdb-org.turso.io
  authToken: process.env.TURSO_AUTH_TOKEN!,
})

// --- Insert JSON: JSON.stringify before passing as parameter ---
await client.execute({
  sql:  "INSERT INTO products (name, metadata) VALUES (?, ?)",
  args: ["Widget", JSON.stringify({ plan: "pro", active: true, tags: ["featured"] })],
})

// --- Query with json_extract in WHERE clause ---
const result = await client.execute({
  sql:  "SELECT * FROM products WHERE json_extract(metadata, '$.plan') = ?",
  args: ["pro"],
})

// --- Parse JSON on read ---
for (const row of result.rows) {
  const meta = JSON.parse(row.metadata as string)
  console.log(meta.plan, meta.tags)
}

// --- client.batch: multiple statements in one round-trip ---
// mode "write" makes the batch atomic (all or nothing)
await client.batch(
  [
    {
      sql:  "UPDATE products SET metadata = json_set(metadata, '$.plan', ?) WHERE id = ?",
      args: ["enterprise", 1],
    },
    {
      sql:  "INSERT INTO audit_log (product_id, action) VALUES (?, ?)",
      args: [1, "plan_upgrade"],
    },
  ],
  "write"  // atomic batch
)

// --- client.transaction: explicit transaction control ---
const tx = await client.transaction("write")
try {
  await tx.execute({
    sql:  "UPDATE products SET metadata = json_set(metadata, '$.active', ?) WHERE id = ?",
    args: [0, 42],
  })
  await tx.execute({
    sql:  "INSERT INTO events (type, payload) VALUES (?, ?)",
    args: ["deactivated", JSON.stringify({ productId: 42 })],
  })
  await tx.commit()
} catch (err) {
  await tx.rollback()
  throw err
}

Drizzle ORM with libSQL JSON Columns

Bottom line: Drizzle ORM's libSQL adapter provides text('col', { mode: 'json' }) which calls JSON.stringify on insert and JSON.parse on select automatically. Add .$type<T>() for TypeScript compile-time type safety.

Install with npm install drizzle-orm @libsql/client. Define a Drizzle schema using sqliteTable from "drizzle-orm/libsql" — JSON columns are declared as text('metadata', { mode: 'json' }). The mode: 'json' option is specific to SQLite/libSQL in Drizzle — PostgreSQL uses dedicated json()/jsonb() column types. Without mode: 'json', the column behaves as a plain TEXT column: no auto-serialization and the TypeScript type is string. With it, inserts accept any JavaScript object and reads return the parsed object. For JSON path queries, use Drizzle's sql tagged template since Drizzle has no built-in json_extract() helper. Drizzle does not auto-generate expression indexes on JSON paths — add them in a raw migration file.

// schema.ts
import { sqliteTable, integer, text } from "drizzle-orm/libsql"

interface ProductMetadata {
  plan: "free" | "pro" | "enterprise"
  features: string[]
  active: boolean
}

export const products = sqliteTable("products", {
  id:       integer("id").primaryKey({ autoIncrement: true }),
  name:     text("name").notNull(),
  // mode: "json" → Drizzle calls JSON.stringify on insert, JSON.parse on read
  metadata: text("metadata", { mode: "json" }).$type<ProductMetadata>(),
})

// db.ts
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)

// queries.ts
import { db } from "./db"
import { products } from "./schema"
import { sql, eq } from "drizzle-orm"

// --- Insert: pass a plain object — Drizzle calls JSON.stringify ---
await db.insert(products).values({
  name:     "Widget Pro",
  metadata: { plan: "pro", features: ["analytics", "api-access"], active: true },
})

// --- Select: metadata is typed as ProductMetadata | null ---
const rows = await db.select().from(products)
// rows[0].metadata?.plan  → "pro"  (already parsed, no JSON.parse needed)

// --- JSON path query via sql template ---
const proProducts = await db
  .select()
  .from(products)
  .where(sql`json_extract(${products.metadata}, '$.plan') = 'pro'`)

// --- Extract a path value as a typed select column ---
const plans = await db
  .select({
    name: products.name,
    plan: sql<string>`json_extract(${products.metadata}, '$.plan')`,
  })
  .from(products)
// plans[0].plan is typed as string

JSON Path Queries and json_each()

Bottom line: json_each() expands a JSON array into a virtual table with 1 row per element. Join it against your main table to filter rows by array contents — a pattern with no direct equivalent in standard SQL but expressible in a single query in libSQL.

json_each(col, path) is a table-valued function: it appears in the FROM clause and produces rows. Each row exposes 8 columns: key (0-based integer index for arrays, key name for objects), value (the element value), type (JSON type string), atom (primitive scalar value or NULL for objects/arrays), id (internal tree node id), parent, fullkey (full path like $.tags[2]), and path (path to the container). For filtering by array membership — "find products where tags contains 'featured'" — join json_each and add a WHERE je.value = 'featured' clause. For counting array elements without expanding them, use json_array_length(metadata, '$.features'). Expression indexes on JSON paths behave like B-tree indexes and are supported in SQLite 3.38+ (Turso uses 3.45+ as of 2026). A single expression index on json_extract(metadata, '$.plan') reduces plan-based queries from a full table scan to O(log n).

-- json_each: expand a JSON array to rows and filter by element value
-- Find all products where the tags array contains "featured"
SELECT products.id, products.name, je.value AS tag
FROM   products,
       json_each(products.metadata, '$.tags') AS je
WHERE  je.value = 'featured';

-- json_each columns available on each row:
-- key      → 0-based index (for arrays) or key name (for objects)
-- value    → the element value
-- type     → JSON type: "integer" | "real" | "text" | "array" | "object" | "null"
-- atom     → scalar value (NULL for objects/arrays)
-- fullkey  → full JSON path, e.g. $.tags[2]

-- Count array elements without expanding
SELECT id, json_array_length(metadata, '$.features') AS feature_count
FROM   products;

-- Aggregate: count products by plan
SELECT json_extract(metadata, '$.plan') AS plan,
       COUNT(*) AS total
FROM   products
GROUP  BY plan;

-- Find products where the first tag is "sale"
SELECT * FROM products
WHERE json_extract(metadata, '$.tags[0]') = 'sale';

-- Expression index on a JSON path (SQLite 3.38+, supported in Turso)
-- Equivalent to PostgreSQL's expression index on (metadata->>'plan')
CREATE INDEX idx_products_plan
  ON products (json_extract(metadata, '$.plan'));

-- After the index exists, this query uses it instead of a full table scan:
SELECT * FROM products
WHERE json_extract(metadata, '$.plan') = 'enterprise';

Edge Deployment Patterns with Turso

Bottom line: Turso's embedded replica downloads a local copy of your SQLite database to the edge worker's filesystem. JSON reads run against that local file with sub-millisecond latency — no network hop to the primary for reads.

Create an embedded replica client by adding a syncUrl and authToken alongside the local file URL: createClient({ url: 'file:local.db', syncUrl: remoteUrl, authToken }). Call await client.sync() at startup (or periodically) to pull the latest changes from the primary. After sync, all reads — including complex json_extract() and json_each() queries — execute against the local SQLite file. Write latency is still determined by the distance to the primary, since writes cannot be served locally. Embedded replicas add approximately 50–100 MB to your deployment artifact (the local SQLite file), so use them only when read latency is the bottleneck. For Cloudflare Workers, which do not support persistent local files, use the HTTP/WebSocket URL directly — each request creates a new connection to the nearest Turso PoP. Multi-tenant isolation is straightforward: create one Turso database per tenant via the Platform API (POST /v1/organizations/{org}/databases) and store the per-tenant URL in your user record. Each database is a fully independent SQLite file with its own replication.

import { createClient } from "@libsql/client"

// --- Embedded replica: local SQLite file + remote sync ---
// Use in environments with a writable filesystem (e.g. Node.js on Fly.io or Railway)
const client = createClient({
  url:       "file:local.db",          // local replica path
  syncUrl:   process.env.TURSO_DATABASE_URL!,   // remote primary
  authToken: process.env.TURSO_AUTH_TOKEN!,
})

// Sync before serving traffic — pulls latest writes from primary
await client.sync()

// All reads now run locally — sub-millisecond latency
const result = await client.execute({
  sql:  "SELECT * FROM products WHERE json_extract(metadata, '$.plan') = ?",
  args: ["pro"],
})

// --- Cloudflare Workers: use HTTP URL, no local file ---
// Workers don't support persistent file storage
export default {
  async fetch(request: Request, env: Env) {
    const client = createClient({
      url:       env.TURSO_DATABASE_URL,   // wss:// or https:// URL
      authToken: env.TURSO_AUTH_TOKEN,
    })

    const result = await client.execute({
      sql:  "SELECT name, json_extract(metadata, '$.plan') AS plan FROM products LIMIT 10",
      args: [],
    })

    return Response.json(result.rows)
  },
}

// --- Multi-tenant: create one database per tenant ---
// Call the Turso Platform API to provision a new database
async function createTenantDatabase(orgSlug: string, tenantId: string) {
  const response = await fetch(
    `https://api.turso.tech/v1/organizations/${orgSlug}/databases`,
    {
      method:  "POST",
      headers: {
        Authorization: `Bearer ${process.env.TURSO_PLATFORM_TOKEN}`,
        "Content-Type": "application/json",
      },
      body: JSON.stringify({ name: `tenant-${tenantId}`, group: "default" }),
    }
  )
  const { database } = await response.json()
  // Store database.hostname in your tenants table
  return `libsql://${database.hostname}`
}

Schema Migrations for JSON Columns

Bottom line: SQLite/libSQL migrations for JSON columns are just ALTER TABLE ... ADD COLUMN metadata TEXT. There is no special JSON column type. Expression indexes on JSON paths must be added via raw SQL since Drizzle Kit does not generate them automatically.

Run npx drizzle-kit generate to create migration files from your Drizzle schema, and npx drizzle-kit migrate to apply them. Drizzle stores migration state in a __drizzle_migrations table — query it to check which migrations have run. When adding a metadata column to an existing table, add a DEFAULT '' clause so existing rows get a valid JSON object rather than NULL. After adding the column, populate existing rows with meaningful data using json_object() to build JSON inline from existing column values. For expression indexes on JSON paths — which give up to 10x query speed improvement on large tables — write a raw SQL migration file and apply it with client.execute() or the Turso shell. Drizzle Kit 0.21+ supports SQLite/libSQL targets via dialect: 'turso' in drizzle.config.ts.

// drizzle.config.ts
import { defineConfig } from "drizzle-kit"

export default defineConfig({
  schema:    "./src/schema.ts",
  out:       "./drizzle",
  dialect:   "turso",
  dbCredentials: {
    url:       process.env.TURSO_DATABASE_URL!,
    authToken: process.env.TURSO_AUTH_TOKEN!,
  },
})

// --- Generated migration: drizzle/0001_add_metadata.sql ---
// ALTER TABLE products ADD COLUMN metadata TEXT DEFAULT '{}';

// --- Drizzle Kit generates this for a new TEXT column ---
// If you need DEFAULT '{}', add it manually in the migration file
// before running drizzle-kit migrate

-- Populate existing rows from existing columns
UPDATE products
SET metadata = json_object(
  'plan',      'free',
  'createdAt', created_at,
  'active',    1
)
WHERE metadata = '{}' OR metadata IS NULL;

-- Expression index on a JSON path (write as a raw migration)
-- drizzle-kit does not generate these automatically
-- File: drizzle/0002_json_index.sql
CREATE INDEX IF NOT EXISTS idx_products_plan
  ON products (json_extract(metadata, '$.plan'));

CREATE INDEX IF NOT EXISTS idx_products_active
  ON products (json_extract(metadata, '$.active'));

-- Check which migrations have run
SELECT * FROM __drizzle_migrations ORDER BY created_at DESC;

// Apply a raw SQL migration programmatically (e.g. for expression indexes)
import { createClient } from "@libsql/client"

const client = createClient({
  url:       process.env.TURSO_DATABASE_URL!,
  authToken: process.env.TURSO_AUTH_TOKEN!,
})

await client.batch(
  [
    {
      sql: "CREATE INDEX IF NOT EXISTS idx_products_plan ON products (json_extract(metadata, '$.plan'))",
      args: [],
    },
    {
      sql: "CREATE INDEX IF NOT EXISTS idx_products_active ON products (json_extract(metadata, '$.active'))",
      args: [],
    },
  ],
  "write"
)

console.log("Expression indexes created")

FAQ