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
Readme
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
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({
👆 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.
Comments
stevekrouse avatar

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:

Screenshot 2024-01-19 at 18.09.23@2x.png

stevekrouse avatar

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.

nbbaier avatar

Weird on the error when you do await writer.getQuery(userInput). Any idea of what's going on there? Something about the prompt?

Re the second issue - any idea about how to fix that, maybe also prompt related?