Readme

SQLite QueryWriter

The QueryWriter class is a utility for generating and executing SQL queries using natural language and OpenAI. It provides a simplified interface for interacting with your Val Town SQLite database and generating SQL queries based on user inputs.

This val is inspired by prisma-gpt. PRs welcome! See Todos below for some ideas I have.

Usage

  1. Import the QueryWriter class into your script:
import { QueryWriter } from "https://esm.town/v/nbbaier/sqliteWriter";
  1. Create an instance of QueryWriter, providing the desired table and an optional model:
const writer = new QueryWriter({ table: "my_table", model: "gpt-4-1106-preview" });
  1. Call the writeQuery() method to generate an SQL query based on a user input string:
const userInput = "Show me all the customers with more than $1000 in purchases."; const query = await writer.writeQuery(userInput);
  1. Alternatively, use the gptQuery() method to both generate and execute the SQL query:
const userInput = "Show me all the customers with more than $1000 in purchases."; const result = await writer.gptQuery(userInput);
  1. Handle the generated query or query result according to your application's needs.

API

new QueryWriter(args: { table: string; model?: string }): QueryWriter

Creates a new instance of the QueryWriter class.

  • table: The name of the database table to operate on.
  • model (optional): The model to use for generating SQL queries. Defaults to "gpt-3.5-turbo".
  • apiKey (optional): An OpenAI API key. Defaults to Deno.env.get("OPENAI_API_KEY").

writeQuery(str: string): Promise<string>

Generates an SQL query based on the provided user input string.

  • str: The user input string describing the desired query.

Returns a Promise that resolves to the generated SQL query.

gptQuery(str: string): Promise<any>

Generates and executes an SQL query based on the provided user input string.

  • str: The user input string describing the desired query.

Returns a Promise that resolves to the result of executing the generated SQL query.

Todos

  • Handle multiple tables for more complex use cases
  • Edit prompt to allow for more than just SELECT queries
  • Allow a user to add to the system prompt maybe?
  • Expand usage beyond just Turso SQLite to integrate with other databases
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
import { sqlite } from "https://esm.town/v/std/sqlite";
import OpenAI from "npm:openai";
export class QueryWriter {
table: string;
model: string;
openai: OpenAI;
constructor({ table, model = "gpt-3.5-turbo" }: { table: string; model?: string }) {
this.table = table;
this.model = model;
this.openai = new OpenAI({
apiKey: Deno.env.get("OPENAI_API_KEY"), // This is the default and can be omitted
});
}
private async getSchema() {
const tableCols = (await sqlite.execute(`PRAGMA table_info(${this.table})`)).rows.map(column => {
return `${column[1]} ${column[2]}`;
}).join(", ");
return `${this.table}(${tableCols})`;
}
private async executeQuery(query: string) {
try {
return await sqlite.execute(query);
} catch (error) {
// Handle the error appropriately
throw new Error("Error executing query: " + error.message);
}
}
private createPrompt(schema: string, str: string) {
return `
You are an AI assistant that returns raw SQL queries using natural language.
You only output raw SQLite queries. Never return anything other than raw SQLite.
Always begin the query with SELECT. You will be given the following schema:
${schema}
Take the below query and return raw SQLite:
${str}
`;
}
async writeQuery(str: string) {
const schema = await this.getSchema();
const prompt = this.createPrompt(schema, str);
try {
const response = await this.openai.chat.completions.create({
messages: [{ role: "system", content: prompt }],
model: this.model,
});
if (!response.choices || response.choices.length === 0) {
throw new Error("No response from OpenAI");
}
const query = response.choices[0].message?.content;
if (!query) {
throw new Error("No SQL returned from OpenAI. Try again.");
}
return query;
} catch (error) {
throw new Error("Error generating query: " + error.message);
}
}
async gptQuery(str: string) {
const schema = await this.getSchema();
const prompt = this.createPrompt(schema, str);
try {
const response = await this.openai.chat.completions.create({
messages: [{ role: "system", content: prompt }],
model: this.model,
});
if (!response.choices || response.choices.length === 0) {
throw new Error("No response from OpenAI");
}
const query = response.choices[0].message?.content;
if (!query) {
throw new Error("No SQL returned from OpenAI. Try again.");
}
return this.executeQuery(query);
} catch (error) {
throw new Error("Error generating and executing query: " + error.message);
}
}
}
Val Town is a social website to write and deploy JavaScript.
Build APIs and schedule functions from your browser.
v50
January 21, 2024