Examples
Text To SQL
JigsawStack Text to SQL example.
Introduction
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
Was this page helpful?