Document
Integration

Embedding Our Client via iFrame

LibSQL Studio is a powerful SQLite client that you can now embed in your website using our iFrame API. Key advantages of using our iframe include:

  • No need to expose any sensitive credentials to our client
  • Ability to embed and rebrand it on your website
  • Instant access to LibSQL Studio updates

Embed LibSQL Studio

You can embed LibSQL Studio as the following

<iframe src="https://libsqlstudio.com/embed/sqlite"></iframe>

There are several parameters you can control. For example:

<iframe src="https://libsqlstudio.com/embed/sqlite?name=MyCoolDatabase"></iframe>

Example

Here is a sample code demonstrating how to integrate using React and connect to Turso:

"use client";
import { createClient, ResultSet } from "@libsql/client/web";
import { useEffect, useMemo, useRef } from "react";

export default function ExamplePage() {
  const iframeRef = useRef<HTMLIFrameElement>(null);
  const client = useMemo(() => {
    return createClient({
      url: "libsql://xxx",
      authToken: "xxx",
    });
  }, []);

  useEffect(() => {
    const contentWindow = iframeRef.current?.contentWindow;

    if (contentWindow) {
      const handler = (e: MessageEvent<ClientRequest>) => {
        if (e.data.type === "query" && e.data.statement) {
          client
            .execute(e.data.statement)
            .then((r) => {
              contentWindow.postMessage(
                {
                  type: e.data.type,
                  id: e.data.id,
                  data: transformRawResult(r),
                },
                "*"
              );
            })
            .catch((err) => {
              contentWindow.postMessage(
                {
                  type: e.data.type,
                  id: e.data.id,
                  error: (err as Error).message,
                },
                "*"
              );
            });
        } else if (e.data.type === "transaction" && e.data.statements) {
          client
            .batch(e.data.statements, "write")
            .then((r) => {
              contentWindow.postMessage(
                {
                  type: e.data.type,
                  id: e.data.id,
                  data: r.map(transformRawResult),
                },
                "*"
              );
            })
            .catch((err) => {
              contentWindow.postMessage(
                {
                  type: e.data.type,
                  id: e.data.id,
                  error: (err as Error).message,
                },
                "*"
              );
            });
        }
      };

      window.addEventListener("message", handler);
      return () => window.removeEventListener("message", handler);
    }
  }, [iframeRef]);

  return (
    <iframe
      className="border-0 h-screen w-screen"
      ref={iframeRef}
      src="https://libsqlstudio.com/embed/sqlite"
    />
  );
}

interface ClientRequest {
  type: "query" | "transaction";
  id: number;
  statement?: string;
  statements?: string[];
}

interface ResultHeader {
  name: string;
  displayName: string;
  originalType: string | null;
  type: ColumnType;
}

interface Result {
  rows: Record<string, unknown>[];
  headers: ResultHeader[];
  stat: {
    rowsAffected: number;
    rowsRead: number | null;
    rowsWritten: number | null;
    queryDurationMs: number | null;
  };
  lastInsertRowid?: number;
}

enum ColumnType {
  TEXT = 1,
  INTEGER = 2,
  REAL = 3,
  BLOB = 4,
}

function convertSqliteType(type: string | undefined): ColumnType {
  // https://www.sqlite.org/datatype3.html
  if (type === undefined) return ColumnType.BLOB;

  type = type.toUpperCase();

  if (type.includes("CHAR")) return ColumnType.TEXT;
  if (type.includes("TEXT")) return ColumnType.TEXT;
  if (type.includes("CLOB")) return ColumnType.TEXT;
  if (type.includes("STRING")) return ColumnType.TEXT;

  if (type.includes("INT")) return ColumnType.INTEGER;

  if (type.includes("BLOB")) return ColumnType.BLOB;

  if (
    type.includes("REAL") ||
    type.includes("DOUBLE") ||
    type.includes("FLOAT")
  )
    return ColumnType.REAL;

  return ColumnType.TEXT;
}

function transformRawResult(raw: ResultSet): Result {
  const headerSet = new Set();

  const headers: ResultHeader[] = raw.columns.map((colName, colIdx) => {
    const colType = raw.columnTypes[colIdx];
    let renameColName = colName;

    for (let i = 0; i < 20; i++) {
      if (!headerSet.has(renameColName)) break;
      renameColName = `__${colName}_${i}`;
    }

    headerSet.add(renameColName);

    return {
      name: renameColName,
      displayName: colName,
      originalType: colType,
      type: convertSqliteType(colType),
    };
  });

  const rows = raw.rows.map((r) =>
    headers.reduce((a, b, idx) => {
      a[b.name] = r[idx];
      return a;
    }, {} as Record<string, unknown>)
  );

  return {
    rows,
    stat: {
      rowsAffected: raw.rowsAffected,
      rowsRead: null,
      rowsWritten: null,
      queryDurationMs: 0,
    },
    headers,
    lastInsertRowid:
      raw.lastInsertRowid === undefined
        ? undefined
        : Number(raw.lastInsertRowid),
  };
}