Readme
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.
- Import the QueryWriter class into your script:
- Create an instance of QueryWriter, providing the desired table and an optional model:
- Call the
writeQuery()method to generate an SQL query based on a user input string:
Create valconst userInput = "Show me all the customers with more than $1000 in purchases.";
const query = await writer.writeQuery(userInput);
- Alternatively, use the
gptQuery()method to both generate and execute the SQL query:
Create valconst userInput = "Show me all the customers with more than $1000 in purchases.";
const result = await writer.gptQuery(userInput);
- Handle the generated query or query result according to your application's needs.
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 toDeno.env.get("OPENAI_API_KEY").
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.
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.
- Handle multiple tables for more complex use cases
- Edit prompt to allow for more than just
SELECTqueries - 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
97
98
99
100
import { type WriterOptions } from "https://esm.town/v/nbbaier/WriterOptions";
import { sqlite } from "https://esm.town/v/std/sqlite";
import OpenAI from "npm:openai";
interface QueryWriterOptons extends WriterOptions {
table: string;
}
export class QueryWriter {
table: string;
model: string;
apiKey: string;
openai: OpenAI;
constructor(options: QueryWriterOptons) {
const { table, model, ...openaiOptions } = options;
this.table = table;
this.model = model;
// this.apiKey = openaiOptions.apiKey ? openaiOptions.apiKey : Deno.env.get("OPENAI_API_KEY");
this.openai = new OpenAI(openaiOptions);
}
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);
}
👆 This is a val. Vals are TypeScript snippets of code, written in the browser and run on our servers. Create scheduled functions, email yourself, and persist small pieces of data — all from the browser.