> ## Documentation Index
> Fetch the complete documentation index at: https://jigsaw-13.mintlify.app/llms.txt
> Use this file to discover all available pages before exploring further.

# Text To SQL

> JigsawStack Text to SQL example.

## Overview

In this example, we will use the Text to SQL API to query transactions records stored in an SQL database on [Dzero](https://dzero.dev/dashboard) using prompts.

## Initial Steps

* Setup a JigsawStack account (if you don't have an account already)
* Get your JigsawStack API key from [here](https://jigsawstack.com/dashboard).
* \[Optional] Obtain your Dzero token from [here](https://dzero.dev/dashboard).

### SQL Schema

> A valid schema is passed to the `sql_schema` parameter as a string. The model uses this schema to generate an accurate SQL statement based on the given prompt. Below is an example of valid schema:

```sql theme={null}
CREATE TABLE transactions (
  id TEXT UNIQUE NOT NULL DEFAULT (
    lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))), 2) || '-' || substr('89ab', abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))), 2) || '-' || lower(hex(randomblob(6)))
  ),
  created_at DATETIME NOT NULL DEFAULT (datetime('now', 'subsec')),
  amount NUMERIC NOT NULL,
  user_id TEXT NOT NULL,
  PRIMARY KEY (id));
```

This schema was copied from the Dzero dashboard after the table was created.

## How to retrieve schema

#### SQLite

> Run this query to retrieve a schema for your SQLite database.

```SQL theme={null}
SELECT sql,name FROM sqlite_schema WHERE type='table' AND sql NOT NULL
```

#### MySQL

```SQL theme={null}
SELECT CONCAT('SHOW CREATE TABLE ', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'your_database_name';
```

#### PostgreSQL

```SQL theme={null}
SELECT
    'CREATE TABLE ' || table_name || ' (' ||
    string_agg(
        column_name || ' ' || data_type ||
        CASE
            WHEN character_maximum_length IS NOT NULL THEN '(' || character_maximum_length || ')'
            ELSE ''
        END ||
        CASE
            WHEN is_nullable = 'NO' THEN ' NOT NULL'
            ELSE ''
        END,
        ', '
    ) || ');' AS create_table_statement
FROM
    information_schema.columns
WHERE
    table_schema = 'your_database_name'
GROUP BY
    table_schema, table_name;
```

### After getting schema (optional)

> If you are performing the operation above in node.js, you can consider the following below:

```javascript theme={null}
const fullSchema = schemas.map((s) => s.sql).join("\n\n");

// Then use the full schema on guide below.
```

### Usage

```javascript sql.ts theme={null}
type TextToSQLParams = {
  prompt: string,
  sql_schema: string,
};

type TextToSQLResult = {
  success: boolean,
  sql: string,
};

type BodyParams = {
  prompt: string,
};

import { JigsawStack } from "jigsawstack";

const jigsawstack = JigsawStack({
  apiKey: "your-api-key",
});

export const POST = async (request: Request) => {
  try {
    const params: BodyParams = await request.json();

    // SQL schema
    const schema = `CREATE TABLE transactions (
  id TEXT UNIQUE NOT NULL DEFAULT (
    lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))), 2) || '-' || substr('89ab', abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))), 2) || '-' || lower(hex(randomblob(6)))
  ),
  created_at DATETIME NOT NULL DEFAULT (datetime('now', 'subsec')),
  amount NUMERIC NOT NULL,
  user_id TEXT NOT NULL,
  PRIMARY KEY (id))`;

    const payload: TextToSQLParams = {
      prompt: params.prompt,
      sql_schema: schema,
    };

    const data: TextToSQLResult = await jigsawstack.text_to_sql(payload);

    // Makes DB call with translated query
    // Instead of using Dzero, you can use any SQL DB service of your choice.

    const dbResponse = await fetch("https://db.dzero.dev", {
      method: "POST",
      headers: {
        token: "<your-dzero-token>",
        "content-type": "application/json",
      },
      body: JSON.stringify({
        sql: data.sql,
      }),
    });
    const resp = await dbResponse.json();

    return Response.json(
      {
        results: resp.results,
      },
      {
        status: 200,
      }
    );
  } catch (error) {
    handleError(error);
  }
};
```

<Note>
  Find more information on Text to SQL API
  [here](/docs/api-reference/ai/text-to-sql)
</Note>