In this example, we will use the Text to SQL API to query transactions records stored in an SQL database on Dzero using prompts.
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.
SELECT sql,name FROM sqlite_schema WHERE type='table' AND sql NOT NULL
SELECT CONCAT('SHOW CREATE TABLE ', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'your_database_name';
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;
const fullSchema = schemas.map((s) => s.sql).join("\n\n");
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();
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);
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);
}
};