​
Overview

In this example, we will use the Text to SQL API to query transactions records stored in an SQL database on Dzero using prompts.

​
Initial Steps

  • Setup a JigsawStack account (if you don’t have an account already)
  • Get your JigsawStack API key from here.
  • [Optional] Obtain your Dzero token from here.

​
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:

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.

SELECT sql,name FROM sqlite_schema WHERE type='table' AND sql NOT NULL

​
MySQL

SELECT CONCAT('SHOW CREATE TABLE ', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'your_database_name';

​
PostgreSQL

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:

const fullSchema = schemas.map((s) => s.sql).join("\n\n");

// Then use the full schema on guide below.

​
Usage

sql.ts
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);
  }
};

Find more information on Text to SQL API here