### Create a Minimal Studio Extension

Source: https://github.com/outerbase/studio/blob/develop/docs/README.md

This is a basic example of an extension. Implement your custom logic within the `init` method. Extensions are located in `/src/extensions` and attached in `/src/core/standard-extension.tsx`.

```typescript
export default class SampleExtension extends StudioExtension {
  extensionName = "sample-extension";

  init(studio: StudioExtensionContext): void {
    // this is where we extend studio functionality
  }
}
```

--------------------------------

### SQL NULLIF Function Examples

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/nullif.md

Demonstrates the behavior of the NULLIF function. It returns NULL when both arguments are identical, and the first argument otherwise.

```sql
select nullif(6, 6)
-> NULL
```

```sql
select nullif(7, 6)
-> 7
```

--------------------------------

### Implement Custom QueryableBaseDriver and Wrap with SqliteLikeBaseDriver

Source: https://context7.com/outerbase/studio/llms.txt

Example of creating a custom `QueryableBaseDriver` for an HTTP endpoint and then wrapping it with `SqliteLikeBaseDriver` to provide SQLite compatibility. This demonstrates how to integrate a new database backend.

```typescript
import { BaseDriver, DatabaseResultSet, DatabaseTableSchema } from "@/drivers/base-driver";

// Minimal QueryableBaseDriver that proxies to any HTTP endpoint
class MyCustomQueryable implements QueryableBaseDriver {
  async query(stmt: string): Promise<DatabaseResultSet> {
    const res = await fetch("/api/db", {
      method: "POST",
      body: JSON.stringify({ sql: stmt }),
    });
    return res.json();
  }
  async transaction(stmts: string[]): Promise<DatabaseResultSet[]> {
    return Promise.all(stmts.map((s) => this.query(s)));
  }
}

// Wrap with the full SQLite-compatible driver
import { SqliteLikeBaseDriver } from "@/drivers/sqlite-base-driver";
const driver = new SqliteLikeBaseDriver(new MyCustomQueryable());

// Fetch schema
const schemas = await driver.schemas();
// { main: [ { type: 'table', name: 'users', schemaName: 'main', tableSchema: {...} }, ... ] }

// Run a query
const result: DatabaseResultSet = await driver.query("SELECT * FROM users LIMIT 5");
console.log(result.rows);        // [{ id: 1, name: 'Alice' }, ...]
console.log(result.stat);        // { rowsAffected: 0, rowsRead: 5, queryDurationMs: 12 }

// Select with pagination, ordering, and WHERE
const { data, schema } = await driver.selectTable("main", "users", {
  limit: 20,
  offset: 0,
  orderBy: [{ columnName: "name", by: "ASC" }],
  whereRaw: "age > 18",
});
```

--------------------------------

### SQL SIGN Function Examples

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/sign.md

Demonstrates the behavior of the SIGN function with negative, zero, and positive numeric inputs. Returns -1, 0, or 1 respectively. NULL or non-numeric inputs return NULL.

```sql
select sign(-5);
-- Result: -1
```

```sql
select sign(0);
-- Result: 0
```

```sql
select sign(5);
-- Result: 1
```

--------------------------------

### Manage Dialog State and Completion in a Page Component

Source: https://github.com/outerbase/studio/blob/develop/src/app/storybook/create-dialog/page.mdx

This example demonstrates a traditional approach to managing dialogs using local state for `open`, `color`, and handling completion via `onComplete`. This pattern has shortcomings in reusability and state management compared to `createDialog`.

```tsx
function SomePage() {
  const [open, setOpen] = useState(false);
  const [color, setColor] = useState();

  return (
    <div>
      <TestingDialog
        color={color}
        open={open}
        onClose={() => setOpen(false)}
        onComplete={(result) => {
          // do something here
        }}
      />

      {/* imagine hunderd of lines of code here */}

      <Button
        onClick={() => {
          setOpen(true);
          setColor("text-red-500");
        }}>
        Show Dialog
      </Button>
    </div>
  );
}
```

--------------------------------

### SQL ROUND Function Examples

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/round.md

Demonstrates rounding a number to the nearest integer and to two decimal places. The second argument defaults to 0 if not provided.

```sql
select round(1.6);
-> 2
```

```sql
select round(1.665, 2);
-> 1.67
```

--------------------------------

### Correct Component Usage Example

Source: https://github.com/outerbase/studio/blob/develop/src/app/storybook/page.mdx

Use standard composite components like FormGroup and LabelInput for consistent and maintainable UI. This pattern simplifies development and ensures adherence to design standards.

```tsx
<FormGroup>
  <LabelInput
    placeholder="Enter your username"
    error="Your username does not exist"
  />
  <LabelInput placeholder="Enter your password" />
</FormGroup>
```

--------------------------------

### Incorrect Component Usage Example

Source: https://github.com/outerbase/studio/blob/develop/src/app/storybook/page.mdx

Avoid this pattern of styling individual elements. It leads to inconsistency and excessive customization.

```tsx
<div className="flex flex-col gap-2">
  <div className="flex flex-col gap-2">
    <Label>Username</Label>
    <Input placeholder="Enter your username" />
    <div className="text-red-500">Your username does not exist</div>
  </div>

  <div className="flex flex-col gap-2">
    <Label>Password</Label>
    <Input type="password" placeholder="Enter your password" />
  </div>
</div>
```

--------------------------------

### Embed LibSQL Studio with Custom Name

Source: https://github.com/outerbase/studio/blob/develop/src/app/(public)/docs/embed-iframe-client/page.mdx

Customize the embedded LibSQL Studio by adding URL parameters to the src attribute. This example sets a custom name for the database.

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

--------------------------------

### SQL ABS Function Examples

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/abs.md

Demonstrates the usage of the ABS function with positive, negative, and string inputs. Note that non-numeric inputs like strings or blobs result in 0.

```sql
SELECT abs(-5);
-- Expected output: 5
```

```sql
SELECT abs("-3");
-- Expected output: 3
```

```sql
SELECT abs("libsql");
-- Expected output: 0
```

--------------------------------

### Get Active MySQL Database

Source: https://context7.com/outerbase/studio/llms.txt

Retrieve the name of the currently active database schema.

```typescript
// Get the active database
const currentDb = await driver.getCurrentSchema(); // "myapp_db"
```

--------------------------------

### Using REPLACE to Substitute Substrings

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/replace.md

This example demonstrates how to use the REPLACE function to replace all occurrences of a specific substring within a string. It takes the original string, the substring to find, and the substring to replace it with as arguments.

```sql
select replace('hello world', 'world', 'planet')
```

--------------------------------

### Get Blob Length with length()

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/length.md

Use length() to find the byte count of a blob. Returns NULL if the input is NULL.

```sql
select length(x'ff00ee');
-> 3
```

--------------------------------

### Get String Length with length()

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/length.md

Use length() to find the character count of a string. Returns NULL if the input is NULL.

```sql
select length('hello');
-> 5
```

```sql
select length(NULL);
-> NULL
```

--------------------------------

### Extracting and Comparing Vectors with vector_extract

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/vector_extract.md

This SQL query demonstrates how to use the vector_extract function to get the string representation of an embedding and then use it to calculate the cosine distance between the embedding and a new vector.

```sql
SELECT title,
  vector_extract(embedding),
  vector_distance_cos(embedding, vector('[5,6,7]'))
FROM movies;
```

--------------------------------

### Show Reusable Dialog with Custom Parameters

Source: https://github.com/outerbase/studio/blob/develop/src/app/storybook/create-dialog/page.mdx

Use the `show` method on a dialog instance to display it. Pass custom parameters to configure the dialog and await its result asynchronously. The dialog can return a value upon completion.

```tsx
<Button
  onClick={async () => {
    // Flexible dialog that can be reused. It can accept custom parameters
    // and return a result asynchronously.
    const result = await testingDialog.show({ color: "text-red-500" });

    // ...
  }}>
  Show Dialog
</Button>
```

--------------------------------

### Create a Tab Extension with Parameters

Source: https://github.com/outerbase/studio/blob/develop/docs/window-tab.md

Shows how to create a tab extension that accepts and uses parameters. The key and generate functions can utilize these parameters to customize tab behavior and content.

```typescript
function SampleTabContent(
    { schema, table }:
    { schema: string, table: string }
) {
  return <div>This is tab for {table} of {schema}</div>;
}

export const sampleExtensionTab = createTabExtension<{
    schema: string,
    table: string,
}>({
  name: "sample-extension",
  key: ({ schema, table }) => `${schema}-${table}`,
  generate: ({ schema, table }) => ({
    title: "Sample Extension",
    component: <SampleTabContent schema={schema} table={table} />,
    icon: LucideCog,
  }),
});
```

--------------------------------

### Open a Tab Extension with Parameters

Source: https://github.com/outerbase/studio/blob/develop/docs/window-tab.md

Illustrates how to open a tab extension that requires parameters. The parameters are passed as an object to the open method.

```typescript
sampleExtensionTab.open({
  schema: "public",
  table: "users",
});
```

--------------------------------

### Initialize SqliteLikeBaseDriver

Source: https://context7.com/outerbase/studio/llms.txt

Instantiate the SqliteLikeBaseDriver with a queryable object and optional configuration for features like PRAGMA database_list and BigInt support.

```typescript
import { SqliteLikeBaseDriver } from "@/drivers/sqlite-base-driver";

// Use with any QueryableBaseDriver
const driver = new SqliteLikeBaseDriver(queryable, {
  supportPragmaList: true,   // enable PRAGMA database_list (Turso/LibSQL)
  supportBigInt: true,       // store BigInt values
});
```

--------------------------------

### Using printf() for Formatted Output

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/printf.md

Constructs a formatted string using a format specifier and a value. Ensure the format string correctly matches the type of the provided argument.

```sql
select printf('i am %d years old', 50);
-> 'i am 50 years old'
```

--------------------------------

### Initialize PostgresLikeDriver

Source: https://context7.com/outerbase/studio/llms.txt

Instantiate the PostgresLikeDriver with a PostgreSQL queryable object.

```typescript
import PostgresLikeDriver from "@/drivers/postgres/postgres-driver";

const driver = new PostgresLikeDriver(myPostgresQueryable);
```

--------------------------------

### format() Function

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/format.md

The FORMAT() function takes a format string as its first argument and constructs an output string using values from subsequent arguments, analogous to C's printf().

```APIDOC
## format(FORMAT, ...)

### Description
Constructs an output string using a format string and subsequent arguments, similar to C's printf().

### Parameters
#### Path Parameters
- **FORMAT** (string) - Required - The format string.
- **...** (any) - Required - Values to be formatted into the string.

### Request Example
```sql
select format('i am %d years old', 50);
```

### Response
#### Success Response (200)
- **result** (string) - The formatted string.

#### Response Example
```json
{
  "result": "i am 50 years old"
}
```
```

--------------------------------

### Open a Tab Extension

Source: https://github.com/outerbase/studio/blob/develop/docs/window-tab.md

Demonstrates how to open a previously defined tab extension. This is used to display the custom tab in the UI.

```typescript
sampleExtensionTab.open();
```

--------------------------------

### StudioExtensionContext API

Source: https://context7.com/outerbase/studio/llms.txt

This section describes how to create and register extensions for Outerbase Studio. Extensions can add custom sidebar panels and window tabs.

```APIDOC
## StudioExtension / StudioExtensionContext — plugin API

Extensions are ES classes that extend `StudioExtension` and receive a `StudioExtensionContext` in their `init()` method. They can register sidebar panels (with content or click handlers) and custom window tabs with typed parameters. Extensions live under `src/extensions/` and are registered in `src/core/standard-extension.tsx`.

### Registering Sidebar Panels

Use `studio.registerSidebar()` to add new panels or buttons to the sidebar.

- **`key`** (string): A unique identifier for the sidebar item.
- **`name`** (string): The display name for the sidebar item.
- **`icon`** (ReactNode): An icon to display next to the name.
- **`content`** (ReactNode, optional): The content to display in the panel if it's a panel.
- **`onClick`** (function, optional): A function to execute when the item is clicked, if it's a button.

### Creating Typed Window Tabs

Use `createTabExtension` to define new window tabs with typed parameters.

```typescript
import { LucideDatabase } from "lucide-react";
import { StudioExtension, StudioExtensionContext } from "@/core/extension-context";
import { createTabExtension } from "@/core/tab-extension";

// Define a typed window tab component
function TableStatsTab({ schema, table }: { schema: string; table: string }) {
  return <div>Stats for {schema}.{table}</div>;
}

// Create a tab extension
export const tableStatsTab = createTabExtension<{ schema: string; table: string }>({
  name: "table-stats",
  key: ({ schema, table }) => `stats-${schema}-${table}`,
  generate: ({ schema, table }) => ({
    title: `Stats: ${table}`,
    component: <TableStatsTab schema={schema} table={table} />,
    icon: LucideDatabase,
  }),
});

// Example Extension Class
export default class TableStatsExtension extends StudioExtension {
  extensionName = "table-stats-extension";

  init(studio: StudioExtensionContext): void {
    // Register a sidebar panel with content
    studio.registerSidebar({
      key: "table-stats-sidebar",
      name: "Table Stats",
      icon: <LucideDatabase />,
      content: <div>Select a table to see stats</div>,
    });

    // Register a sidebar button that opens the tab
    studio.registerSidebar({
      key: "table-stats-open",
      name: "Open Stats",
      icon: <LucideDatabase />,
      onClick: () => tableStatsTab.open({ schema: "main", table: "users" }),
    });
  }
}

// Open / close the tab from anywhere
tableStatsTab.open({ schema: "public", table: "orders" });
tableStatsTab.close({ schema: "public", table: "orders" });
```
```

--------------------------------

### List MySQL Schemas and Tables

Source: https://context7.com/outerbase/studio/llms.txt

Retrieve a list of all schemas (databases) and the tables within each schema. Includes basic table metadata.

```typescript
// List all schemas (databases) and their tables
const schemas = await driver.schemas();
// { myapp_db: [ { type: 'table', name: 'users', schemaName: 'myapp_db', tableSchema: {...} } ] }
```

--------------------------------

### Create Basic FTS5 Tables

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/fts5.md

Use CREATE VIRTUAL TABLE with the fts5 module to create full-text searchable tables. Specify columns for indexing.

```sql
CREATE VIRTUAL TABLE movie_fts USING fts5(title, summary);
```

```sql
CREATE VIRTUAL TABLE name_fts USING fts5(name, tokenize='trigram');
```

--------------------------------

### Using COALESCE with NULL and a Value

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/coalesce.md

Demonstrates the basic usage of COALESCE, returning the first non-NULL argument.

```sql
select coalesce(null, 50);
-> 50
```

--------------------------------

### Natural Language to SQL Conversion with ChatGPTDriver

Source: https://context7.com/outerbase/studio/llms.txt

Use `ChatGPTDriver` to convert natural language queries into SQL, leveraging the database schema as context. Supports conversational follow-ups by referencing previous message IDs.

```typescript
import { ChatGPTDriver } from "@/drivers/agent/chatgpt";

const agent = new ChatGPTDriver(driver, "sk-YOUR_OPENAI_API_KEY");

// First turn — pass the full schemas so the model has context
const schemas = await driver.schemas();
const answer1 = await agent.run(
  "Show me the top 5 customers by total order amount",
  undefined,   // no previous conversation
  { schema: schemas, selectedSchema: "main", selected: "" }
);
// Returns a SQL query string, e.g.:
// "SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id ORDER BY total DESC LIMIT 5"

// Follow-up turn — continue the conversation using the returned ID
const answer2 = await agent.run(
  "Now filter to only include orders placed in 2024",
  answer1,     // previous message ID for context
  { schema: schemas, selectedSchema: "main", selected: "" }
);
```

--------------------------------

### React Component for iFrame Client Integration

Source: https://github.com/outerbase/studio/blob/develop/src/app/(public)/docs/embed-iframe-client/page.mdx

This React component sets up an iFrame to embed the Turso Studio client and handles communication between the parent window and the iFrame. It listens for messages from the iFrame, executes SQL queries or transactions against a Turso database using the `@libsql/client/web` library, and posts the results back to the iFrame. Ensure the `url` and `authToken` are correctly configured for your Turso instance.

```tsx
"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),
  };
}

```

--------------------------------

### AI Agent Integration

Source: https://context7.com/outerbase/studio/llms.txt

Integrates AI agents for natural-language-to-SQL conversion. `AgentBaseDriver` is the abstract base, and `ChatGPTDriver` uses OpenAI's API.

```APIDOC
## AI Agent Integration

### AgentBaseDriver / ChatGPTDriver — natural-language-to-SQL

### Description
`AgentBaseDriver` is the abstract base for AI agents. `ChatGPTDriver` uses the OpenAI Chat Completions API (`gpt-4o-mini`) to convert natural language questions into SQL using the current database schema as context. The Cloudflare AI variant uses Workers AI instead.

### Usage
```typescript
import { ChatGPTDriver } from "@/drivers/agent/chatgpt";

const agent = new ChatGPTDriver(driver, "sk-YOUR_OPENAI_API_KEY");

// First turn — pass the full schemas so the model has context
const schemas = await driver.schemas();
const answer1 = await agent.run(
  "Show me the top 5 customers by total order amount",
  undefined,   // no previous conversation
  { schema: schemas, selectedSchema: "main", selected: "" }
);
// Returns a SQL query string, e.g.:
// "SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id ORDER BY total DESC LIMIT 5"

// Follow-up turn — continue the conversation using the returned ID
const answer2 = await agent.run(
  "Now filter to only include orders placed in 2024",
  answer1,     // previous message ID for context
  { schema: schemas, selectedSchema: "main", selected: "" }
);
```
```

--------------------------------

### Using FORMAT() with Integer

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/format.md

Constructs a string by inserting an integer value into a format string. Use this for basic string interpolation with numbers.

```sql
select format('i am %d years old', 50);
-> 'i am 50 years old'
```

--------------------------------

### Initialize MySQLLikeDriver

Source: https://context7.com/outerbase/studio/llms.txt

Instantiate the MySQLLikeDriver with a custom queryable object and the database name.

```typescript
import MySQLLikeDriver from "@/drivers/mysql/mysql-driver";
import { MyCustomMySQLQueryable } from "./my-mysql-queryable";

const driver = new MySQLLikeDriver(new MyCustomMySQLQueryable(), "myapp_db");
```

--------------------------------

### Embed LibSQL Studio iFrame

Source: https://github.com/outerbase/studio/blob/develop/src/app/(public)/docs/embed-iframe-client/page.mdx

Use this basic iFrame code to embed LibSQL Studio into your website. Ensure the src attribute points to the correct embed path.

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

--------------------------------

### Using vector_top_k for Similarity Search

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/vector_top_k.md

Use the vector_top_k function with an index name, a query vector, and the desired number of results (k) to find the most similar vectors. Join the results with your main table to retrieve associated data.

```sql
SELECT title, year
FROM vector_top_k('movies_idx', vector('[4,5,6]'), 3)
JOIN movies ON movies.rowid = id
```

--------------------------------

### Execute Sequential SQL Statements with Progress Tracking

Source: https://context7.com/outerbase/studio/llms.txt

Use `multipleQuery` to run an array of SQL statements sequentially. It tracks progress, stops on the first error, and returns all result sets with headers along with execution logs.

```typescript
import { multipleQuery } from "@/lib/sql/multiple-query";

const statements = [
  "CREATE TABLE IF NOT EXISTS logs (id INTEGER PRIMARY KEY, msg TEXT, ts INTEGER)",
  "INSERT INTO logs(msg, ts) VALUES('start', unixepoch())",
  "INSERT INTO logs(msg, ts) VALUES('end',   unixepoch())",
  "SELECT * FROM logs",
];

const { result, logs } = await multipleQuery(driver, statements, (progress) => {
  console.log(`${progress.progress}/${progress.total} complete`);
  if (progress.error) console.error("Stopped at:", progress.logs.at(-1)?.error);
});

// result => [ { sql: 'SELECT * FROM logs', order: 3, result: { rows: [...], headers: [...] } } ]
// logs[1] => { order: 1, sql: 'INSERT ...', start: 1710000000000, end: 1710000000002, stats: { rowsAffected: 1 } }
```

--------------------------------

### min() with Multiple Arguments

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/min.md

Use the multi-argument min() function to find the smallest value among several provided arguments. String comparisons are based on the collating function of the first argument.

```sql
select min(5, 6, 1);
-> 1
```

--------------------------------

### Define and Register a Typed Window Tab Extension

Source: https://context7.com/outerbase/studio/llms.txt

Defines a typed window tab component and registers it as a Studio extension. Use this to create custom, data-driven tabs within the Studio UI. The tab's key is generated dynamically based on its parameters.

```typescript
import { LucideDatabase } from "lucide-react";
import { StudioExtension, StudioExtensionContext } from "@/core/extension-context";
import { createTabExtension } from "@/core/tab-extension";

// --- Define a typed window tab ---
function TableStatsTab({ schema, table }: { schema: string; table: string }) {
  return <div>Stats for {schema}.{table}</div>;
}

export const tableStatsTab = createTabExtension<{ schema: string; table: string }>({
  name: "table-stats",
  key: ({ schema, table }) => `stats-${schema}-${table}`,
  generate: ({ schema, table }) => ({
    title: `Stats: ${table}`,
    component: <TableStatsTab schema={schema} table={table} />,
    icon: LucideDatabase,
  }),
});

// --- Register the extension ---
export default class TableStatsExtension extends StudioExtension {
  extensionName = "table-stats-extension";

  init(studio: StudioExtensionContext): void {
    // Sidebar panel with content
    studio.registerSidebar({
      key: "table-stats-sidebar",
      name: "Table Stats",
      icon: <LucideDatabase />,
      content: <div>Select a table to see stats</div>,
    });

    // Sidebar button that opens the tab
    studio.registerSidebar({
      key: "table-stats-open",
      name: "Open Stats",
      icon: <LucideDatabase />,
      onClick: () => tableStatsTab.open({ schema: "main", table: "users" }),
    });
  }
}

// Open / close the tab from anywhere
tableStatsTab.open({ schema: "public", table: "orders" });
tableStatsTab.close({ schema: "public", table: "orders" });

```

--------------------------------

### printf(FORMAT, ...)

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/printf.md

The printf() function constructs a string based on a format string and provided arguments. It supports various format specifiers for different data types.

```APIDOC
## printf(FORMAT, ...)

### Description
The printf() function, similar to C's printf(), uses a format string (first argument) to construct the output with values from subsequent arguments.

### Parameters
#### Path Parameters
- **FORMAT** (string) - Required - The format string that specifies how subsequent arguments should be formatted.
- **...** (any) - Required - The arguments to be formatted according to the FORMAT string.

### Request Example
```sql
select printf('i am %d years old', 50);
```

### Response
#### Success Response (200)
- **result** (string) - The formatted string.

#### Response Example
```
'i am 50 years old'
```
```

--------------------------------

### Create PostgreSQL View

Source: https://context7.com/outerbase/studio/llms.txt

Generate SQL to create a new view in the specified schema with a given name and select statement.

```typescript
// Create a view
const createViewSql = driver.createView({
  schemaName: "public",
  name: "active_users",
  statement: "SELECT * FROM users WHERE active = TRUE",
});
// "CREATE VIEW \"public\".\"active_users\" AS SELECT * FROM users WHERE active = TRUE"
```

--------------------------------

### EmbedQueryable

Source: https://context7.com/outerbase/studio/llms.txt

Implements QueryableBaseDriver using window.postMessage for parent frame communication or Electron IPC for desktop apps.

```APIDOC
## EmbedQueryable

### Description
Implements `QueryableBaseDriver` using `window.postMessage` to proxy SQL queries to a parent frame, or through the Electron IPC bridge (`window.outerbaseIpc`) when running as a desktop app. This lets any web page embed Studio as an iframe and serve its own database.

### Usage
```typescript
import { EmbedQueryable } from "@/drivers/iframe-driver";
import { SqliteLikeBaseDriver } from "@/drivers/sqlite-base-driver";

// Inside the embedded Studio iframe
const embed = new EmbedQueryable();
embed.listen(); // registers window.message listener

const driver = new SqliteLikeBaseDriver(embed);

// The parent page handles the message:
window.addEventListener("message", async (e) => {
  if (e.data.type === "query") {
    const result = await myDb.query(e.data.statement);
    e.source.postMessage({ type: "query", id: e.data.id, data: result }, "*");
  }
  if (e.data.type === "transaction") {
    const results = await myDb.transaction(e.data.statements);
    e.source.postMessage({ type: "transaction", id: e.data.id, data: results }, "*");
  }
});
```
```

--------------------------------

### Using ifnull() to return a non-NULL value

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/ifnull.md

Demonstrates how ifnull() returns the second argument when the first is NULL.

```sql
select ifnull(null, 5);
-> 5
```

--------------------------------

### Create a Basic Tab Extension

Source: https://github.com/outerbase/studio/blob/develop/docs/window-tab.md

Defines a simple tab extension with a unique key and a component. The key ensures uniqueness, and the generate function provides the tab's title, component, and icon.

```typescript
function SampleTabContent() {
  return <div>This is tab</div>;
}

export const sampleExtensionTab = createTabExtension({
  name: "sample-extension",
  key: () => "sample-extension-type",
  generate: () => ({
    title: "Sample Extension",
    component: <SampleTabContent />,
    icon: LucideCog,
  }),
});
```

--------------------------------

### Build Parameterized SQL Queries

Source: https://context7.com/outerbase/studio/llms.txt

Use `selectFrom`, `insertInto`, `updateTable`, and `deleteFrom` for dialect-aware query building. These functions ensure correct identifier and value escaping for SQLite, MySQL, and PostgreSQL.

```typescript
import { selectFrom, insertInto, updateTable, deleteFrom } from "@/drivers/query-builder";

// Assuming `driver` is a SqliteLikeBaseDriver instance

const selectSql = selectFrom(driver, "main", "users",
  { role: "admin" },
  { limit: 10, offset: 0 }
);
// 'SELECT * FROM "main"."users" WHERE "role" = 'admin' LIMIT 10 OFFSET 0'
```

```typescript
const insertSql = insertInto(driver, "main", "users",
  { name: "Bob", email: "bob@example.com" },
  true,   // supportReturning
  true    // supportRowId
);
// 'INSERT INTO "main"."users" ("name", "email") VALUES('Bob', 'bob@example.com') RETURNING rowid, *'
```

```typescript
const updateSql = updateTable(driver, "main", "users",
  { name: "Robert" },
  { id: 1 },
  true, true
);
// 'UPDATE "main"."users" SET "name" = 'Robert' WHERE "id" = 1 RETURNING rowid, *'
```

```typescript
const deleteSql = deleteFrom(driver, "main", "users", { id: 1 });
// 'DELETE FROM "main"."users" WHERE "id" = 1'
```

--------------------------------

### Create ANN Index with libsql_vector_idx

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/libsql_vector_idx.md

Use the libsql_vector_idx expression in a CREATE INDEX statement to create an ANN index on a vector column. This is typically used for vector similarity search operations.

```sql
CREATE INDEX movies_idx ON movies (libsql_vector_idx(embedding));
```

--------------------------------

### Create FTS5 Table with External Content

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/fts5.md

Create an FTS5 virtual table that indexes content from an external table. Specify the content table and its row identifier column.

```sql
CREATE VIRTUAL TABLE student_fts USING fts5(
    name,
    tokenize='trigram',
    content='student',
    content_rowid='id'
);
```

--------------------------------

### Register Sidebar with Content

Source: https://github.com/outerbase/studio/blob/develop/docs/sidebar.md

Use this to register a new sidebar item that displays custom React content. Ensure the `content` prop is provided.

```tsx
function SampleSidebar() {
  return <div>Sidebar Content</div>;
}

export default class SampleExtension extends StudioExtension {
  extensionName = "sample-extension";

  init(studio: StudioExtensionContext): void {
    studio.registerSidebar({
      key: "sample-extension-sidebar",
      name: "Sample",
      icon: <LucideArrow />,
      content: <SampleSidebar />,
    });
  }
}
```

--------------------------------

### Construct String from Unicode Code Points using CHAR()

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/char.md

Use the CHAR() function to create a string by providing the Unicode code point values of the desired characters as arguments. This is useful for generating specific character sequences.

```sql
SELECT CHAR(65, 66, 67);
-> 'ABC'
```

--------------------------------

### SqljsDriver

Source: https://context7.com/outerbase/studio/llms.txt

Wraps the sql.js WebAssembly SQLite library for in-browser SQLite file editing. Tracks row modifications to prompt users to save.

```APIDOC
## SqljsDriver

### Description
Wraps the `sql.js` WebAssembly SQLite library, enabling fully in-browser SQLite file editing. It tracks whether rows have been modified so the UI can prompt the user to save.

### Usage
```typescript
import initSqlJs from "sql.js";
import SqljsDriver from "@/drivers/database/sqljs";

const SQL = await initSqlJs({ locateFile: (f) => `/sqljs/${f}` });

// Load from a File input
const arrayBuffer = await file.arrayBuffer();
const db = new SQL.Database(new Uint8Array(arrayBuffer));
const driver = new SqljsDriver(db);

// Query the local file
const result = await driver.query("SELECT * FROM sqlite_master");
console.log(result.rows);

// Check if unsaved changes exist
if (driver.hasChanged()) {
  const bytes = db.export(); // Uint8Array — save to file
  driver.resetChange();
}

// Reload with a new file without constructing a new driver
const newDb = new SQL.Database(new Uint8Array(newArrayBuffer));
driver.reload(newDb);
```
```

--------------------------------

### Basic LIKE Pattern Matching

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/like.md

Checks if a string matches a given pattern using the LIKE operator. Returns 1 for a match and 0 for no match.

```sql
select like('hel%', 'hello')
-> 1
```

```sql
select like('wor%', 'hello')
-> 0
```

--------------------------------

### sign(X)

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/sign.md

Returns -1, 0, or 1 if X is negative, zero, or positive, respectively. Returns NULL if X is NULL or not a number.

```APIDOC
## sign(X)

### Description
The sign(X) function returns -1, 0, or 1 if X is negative, zero, or positive, respectively. If X is NULL or not a number, it returns NULL.

### Parameters
#### Path Parameters
- **X** (any) - Required - The input value to determine the sign of.

### Request Example
```sql
select sign(-5);
select sign(0);
select sign(5);
```

### Response
#### Success Response (200)
- **result** (integer | NULL) - The sign of X (-1, 0, or 1), or NULL if X is NULL or not a number.

#### Response Example
```
-1
0
1
```
```

--------------------------------

### Embeddable Driver for iframes and Electron

Source: https://context7.com/outerbase/studio/llms.txt

EmbedQueryable facilitates communication between an embedded Studio instance and its parent frame or Electron host using window.postMessage or IPC. The embedded instance listens for queries.

```typescript
import { EmbedQueryable } from "@/drivers/iframe-driver";
import { SqliteLikeBaseDriver } from "@/drivers/sqlite-base-driver";

// Inside the embedded Studio iframe
const embed = new EmbedQueryable();
embed.listen(); // registers window.message listener

const driver = new SqliteLikeBaseDriver(embed);

// The parent page handles the message:
window.addEventListener("message", async (e) => {
  if (e.data.type === "query") {
    const result = await myDb.query(e.data.statement);
    e.source.postMessage({ type: "query", id: e.data.id, data: result }, "*");
  }
  if (e.data.type === "transaction") {
    const results = await myDb.transaction(e.data.statements);
    e.source.postMessage({ type: "transaction", id: e.data.id, data: results }, "*");
  }
});
```

--------------------------------

### Using GLOB for Pattern Matching in SQLite

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/glob.md

Demonstrates the basic usage of the GLOB operator to check if a string matches a Unix-style file glob pattern. The operator is case-sensitive.

```sql
select glob('*hello*', 'hello world');
-> 1
```

--------------------------------

### Retrieve MySQL Table Schema with Constraints

Source: https://context7.com/outerbase/studio/llms.txt

Fetch detailed schema information for a specific table, including primary keys, foreign keys, and other constraints.

```typescript
// Retrieve detailed schema with constraints and foreign keys
const schema = await driver.tableSchema("myapp_db", "users");
console.log(schema.constraints);
// [
//   { name: 'PRIMARY', primaryKey: true, primaryColumns: ['id'] },
//   { name: 'fk_role', foreignKey: { columns: ['role_id'], foreignTableName: 'roles', foreignColumns: ['id'] } }
// ]
```

--------------------------------

### Introspect PostgreSQL Schemas

Source: https://context7.com/outerbase/studio/llms.txt

Retrieve comprehensive schema information, including tables, columns, and constraints, from a PostgreSQL database.

```typescript
// Full schema introspection with constraint details
const schemas = await driver.schemas();
```

--------------------------------

### Concatenate Strings with Separator

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/concat_ws.md

Use CONCAT_WS to join strings, specifying a separator. NULL arguments are ignored.

```sql
select concat_ws(', ', 'hello', 'world')
-> 'hello world'
```

--------------------------------

### RqliteQueryable

Source: https://context7.com/outerbase/studio/llms.txt

Sends queries to rqlite's unified /db/request endpoint with optional Basic Auth. Converts rqlite's columnar response format to the standard DatabaseResultSet.

```APIDOC
## RqliteQueryable

### Description
Sends queries to rqlite's unified `/db/request` endpoint with optional Basic Auth. It converts rqlite's columnar response format (columns/types/values arrays) to the standard `DatabaseResultSet`.

### Usage
```typescript
import { RqliteQueryable } from "@/drivers/database/rqlite";
import { SqliteLikeBaseDriver } from "@/drivers/sqlite-base-driver";

const queryable = new RqliteQueryable(
  "http://localhost:4001",
  "admin",          // optional username
  "secret"          // optional password
);
const driver = new SqliteLikeBaseDriver(queryable);

const result = await driver.query("SELECT id, name FROM nodes WHERE active=1");
console.log(result.rows);  // [{ id: 1, name: 'node-a' }, ...]
console.log(result.stat.queryDurationMs); // timing from rqlite meta
```
```

--------------------------------

### multipleQuery

Source: https://context7.com/outerbase/studio/llms.txt

Executes an array of SQL statements sequentially, tracks progress, and returns result sets and execution logs. Stops on the first error encountered.

```APIDOC
## multipleQuery

### Description
Executes an array of SQL statements one at a time using any `BaseDriver`, tracks per-statement progress and timing, stops on first error, and reports analytics events. Returns all result sets that had headers (i.e., SELECT-like results) and a complete log of every statement's execution metadata.

### Usage
```typescript
import { multipleQuery } from "@/lib/sql/multiple-query";

const statements = [
  "CREATE TABLE IF NOT EXISTS logs (id INTEGER PRIMARY KEY, msg TEXT, ts INTEGER)",
  "INSERT INTO logs(msg, ts) VALUES('start', unixepoch())",
  "INSERT INTO logs(msg, ts) VALUES('end',   unixepoch())",
  "SELECT * FROM logs",
];

const { result, logs } = await multipleQuery(driver, statements, (progress) => {
  console.log(`${progress.progress}/${progress.total} complete`);
  if (progress.error) console.error("Stopped at:", progress.logs.at(-1)?.error);
});

// result => [ { sql: 'SELECT * FROM logs', order: 3, result: { rows: [...], headers: [...] } } ]
// logs[1] => { order: 1, sql: 'INSERT ...', start: 1710000000000, end: 1710000000002, stats: { rowsAffected: 1 } }
```
```

--------------------------------

### Register Sidebar with onClick Handler

Source: https://github.com/outerbase/studio/blob/develop/docs/sidebar.md

Use this to register a new sidebar item that performs an action when clicked, instead of displaying content. Provide an `onClick` function.

```tsx
export default class SampleExtension extends StudioExtension {
  extensionName = "sample-extension";

  init(studio: StudioExtensionContext): void {
    studio.registerSidebar({
      key: "sample-extension-sidebar",
      name: "Sample",
      icon: <LucideArrow />,
      onClick: () => {
        // do something
      },
    });
  }
}
```

--------------------------------

### min() as an Aggregate Function

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/min.md

When given a single argument, min() operates as an aggregate function to return the minimum value from a specified column across all rows.

```sql
select min(age) from users;
```

--------------------------------

### Close a Tab Extension

Source: https://github.com/outerbase/studio/blob/develop/docs/window-tab.md

Demonstrates how to close a specific tab instance using its parameters. This is useful for programmatically removing tabs.

```typescript
sampleExtensionTab.close({
  schema: "public",
  table: "users",
});
```

--------------------------------

### Define a Reusable Dialog with createDialog

Source: https://github.com/outerbase/studio/blob/develop/src/app/storybook/create-dialog/page.mdx

Define a reusable dialog component using `createDialog`. This function takes a render prop that receives `close` and any custom parameters. The `close` function can be used to pass a result back to the caller.

```tsx
const testingDialog = createDialog<{ color: string }, string>(
  ({ close, color }) => {
    const [name, setName] = useState("");

    return (
      <>
        <DialogHeader>
          <DialogTitle className={color}>Hello World</DialogTitle>
          <DialogDescription>Please enter your name</DialogDescription>
        </DialogHeader>

        <div>
          <Input
            value={name}
            onChange={(e) => setName(e.currentTarget.value)}
          />
        </div>

        <DialogFooter>
          <Button
            onClick={() => {
              close(name); // <=== Passing the value back
            }}>
            Done
          </Button>
        </DialogFooter>
      </>
    );
  }
);
```

--------------------------------

### rqlite Driver

Source: https://context7.com/outerbase/studio/llms.txt

RqliteQueryable connects to rqlite's /db/request endpoint, supporting optional Basic Auth. It converts rqlite's response format to a standard DatabaseResultSet. Useful for distributed SQLite.

```typescript
import { RqliteQueryable } from "@/drivers/database/rqlite";
import { SqliteLikeBaseDriver } from "@/drivers/sqlite-base-driver";

const queryable = new RqliteQueryable(
  "http://localhost:4001",
  "admin",          // optional username
  "secret"          // optional password
);
const driver = new SqliteLikeBaseDriver(queryable);

const result = await driver.query("SELECT id, name FROM nodes WHERE active=1");
console.log(result.rows);  // [{ id: 1, name: 'node-a' }, ...]
console.log(result.stat.queryDurationMs); // timing from rqlite meta
```

--------------------------------

### Concatenate Strings with CONCAT

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/concat.md

Use the CONCAT function to join multiple string literals. It handles NULL values by ignoring them in the concatenation process.

```sql
select concat('hello', ' ', 'world')
-> 'hello world'
```

--------------------------------

### Using octet_length with Text and Hexadecimal Values

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/octet_length.md

Demonstrates the octet_length function with a Unicode string, an ASCII string, and a hexadecimal BLOB. Note how the Unicode string 'វិសាល' results in 15 bytes due to its UTF-8 encoding.

```sql
select octet_length('វិសាល'); -> 15
```

```sql
select octet_length('visal'); -> 5
```

```sql
select octet_length(x'ff11ee');  -> 3
```

--------------------------------

### CONCAT_WS Function

Source: https://github.com/outerbase/studio/blob/develop/src/drivers/sqlite/functions/concat_ws.md

The CONCAT_WS function concatenates all non-null arguments beyond the first, using the first argument as a separator. If the first argument is NULL, it returns NULL. If all other arguments are NULL, it returns an empty string.

```APIDOC
## CONCAT_WS Function

### Description
Concatenates strings with a separator, skipping NULL values.

### Syntax
`concat_ws(SEP, X, ...)`

### Parameters
- **SEP**: The separator string to use between concatenated values.
- **X, ...**: The strings to concatenate. The first argument is the separator, and subsequent arguments are the values to join.

### Behavior
- If `SEP` is NULL, the result is NULL.
- If all arguments after `SEP` are NULL, an empty string is returned.
- NULL values among the arguments after `SEP` are skipped.

### Example
```sql
select concat_ws(', ', 'hello', 'world')
-- Result: 'hello world'

select concat_ws('-', 'a', NULL, 'b', 'c')
-- Result: 'a-b-c'

select concat_ws(NULL, 'a', 'b')
-- Result: NULL

select concat_ws(',', NULL, NULL)
-- Result: ''
```
```