Public
Script
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);
}
Val Town is a social website to write and deploy JavaScript.
Build APIs and schedule functions from your browser.
So cool! Can't wait to get this to work! In theory I could even expose it to the users of dateme.directory as an interface. I'm getting a weird sqlite parse error that goes away when I run the SQL directly:
The other issue is that it doesn't really understand my enums because they're just SQLite strings. It should probably also select distinct across all the tables to have a better understanding on how to filter for stuff, ie men or women, or locations.