Javascript example of using OpenAI to do natural language queries on an ethereum dataset in postgresql populated by moralis

gistfile1.txt

/*

I wanted to comment briefly on this LLM/SQL binding because (thinking about it) there are some deep ramifications for other projects. Basically - it's hugely empowering to think of LLMs as a bridge between natural language and DSLs (domain specific grammars).

As we know, everybody is talking about an "AI revolution" but it isn't always clear precisely or exactly how AI benefits organizations. We all appreciate that it can summarize, or write copy or so on, but that is more evolutionary rather than transformational.

What I now appreciate better (in toying around with this example code below) is that we as programmers have already built armatures for manipulating the world. And all we need are bridges to them. And that by itself is fairly transformational for human labor.

We've invented a ton of specialized, formal, actionable grammars that can translate what is effectively prose into actual physical actions. SQL is one example of a formal actionable grammar, but so is gcode (for driving 3d printers), and MIDI (for driving musical instruments). ChatGPT happens to already have been exposed to these grammars, so it isn't hard to target them as outputs, but you have an esoteric domain that you can describe in a formal actionable way, then you can prompt that target grammar to ChatGPT to have it act as a translation bridge. We see this discussed in various forums, and it's a useful way to think about leveraging AI systems: https://community.openai.com/t/specifying-formal-grammar-for-a-domain-specific-language/148996 .

Philosophically, a naive approach to having an AI driven database might be to try have the AI somehow inhale the entire knowledge of the system. A GIS focused AI for example might be trained against a place name dataset. But that isn't necessary - any more than it is necessary to have a human memorize all the places in the world. GIS specialists simply are specialists in knowing how to form the queries - the steps along the way - to successfully derive meaning and value. And this is about the right way to think of these new LLMS.

Natural language bridges constitute a new UX. Would you rather hunt and peck through a 2d user interface with buttons and menus and sub-options, or would you rather simply talk to a person like entity that can resolve the request for you?


*/


const { writeFileSync } = require('fs');

///////////////////////////////////////////////////////////////////////////////////////////////////////////
// Moralis
///////////////////////////////////////////////////////////////////////////////////////////////////////////

const Moralis = require("moralis").default;
const { EvmChain } = require("@moralisweb3/common-evm-utils");

////////////////////////////////////////////////////////////////////////////////////////////////////////////
// psql
////////////////////////////////////////////////////////////////////////////////////////////////////////////

/*
import postgres from 'postgres'
const sql = postgres('postgres://username:password@host:port/database', {
	host: keys.db.host,
	port: 5432,
	user: keys.db.user,
	password: keys.db.password,
	database: keys.db.database
})
*/

// open a connection to our test database (this is a dockerized psql db mounted on my external ip)

const { Pool, Client } = require('pg')

const client = new Client({
	host: keys.db.host,
	port: 5432,
	user: keys.db.user,
	password: keys.db.password,
	database: keys.db.database
})

////////////////////////////////////////////////////////////////////////////////////////////////////////////
// populate db helper - run once
////////////////////////////////////////////////////////////////////////////////////////////////////////////

let blob = null

//let blob = require('./data.json');

async function populate_database() {

	// get test transactions (for now they are in the file above)

	console.log("... fetching transactions from moralis")

	if(false && blob == null) {
		await Moralis.start({ apiKey: keys.moralis })
		const address = "0xd8da6bf26964af9d7eed9e03e53415d37aa96045"
		const chain = EvmChain.ETHEREUM;
		const response = await Moralis.EvmApi.transaction.getWalletTransactions({
			address,
			chain,
		})
		blob = response.toJSON()
		writeFileSync("./data.json", JSON.stringify(blob, null, 2),'utf8')
	}

	// make table if needed

	console.log("... building table if not exists")

    await client.connect()

	let results = await client.query(`
		CREATE TABLE IF NOT EXISTS "transactions" (
			"id" SERIAL,
			"block" BIGINT NOT NULL,
			"index" BIGINT NOT NULL,
			"address" VARCHAR(100) NOT NULL,
			"value" BIGINT NOT NULL,
			"date" TIMESTAMP NOT NULL,
			PRIMARY KEY ("id")
		);`)

	console.log("results")

	// walk transactions and write to database if novel

	console.log("... copying transactions if new")

	for(let i = 0; i < blob.result.length;i++) {
		const t = blob.result[i]

		t.date = new Date(t.block_timestamp)/1000.0
		t.date_string = new Date(t.block_timestamp).toDateString()

		console.log("......transaction:",
			t.block_number,
			t.transaction_index,
			t.from_address,
			t.value,
			t.date_string,
			)

		let exists = await client.query(`SELECT * FROM transactions WHERE block=${t.block_number}`)
		if(exists.rowCount > 0) continue

		let insert = `insert into transactions (block, index, address, value, date) values(${t.block_number}, ${t.transaction_index}, '${t.from_address}', ${t.value}, to_timestamp(${t.date}))`
		let results = await client.query(insert)

	}

    await client.end();
}

/*

The above populates a database with entries like so

 id |  block   | index |                  address                   |        value        |        date         
----+----------+-------+--------------------------------------------+---------------------+---------------------
  1 | 18423030 |   164 | 0x9261ac56d49e89524ade99329ca5141a4e0048e1 |       1000000000000 | 2023-10-24 22:15:35
  2 | 18411602 |   193 | 0xc650c509d9e4a542e46ecde38a986588f8eb5636 |                   0 | 2023-10-23 07:50:59
  3 | 18409919 |   242 | 0xbf3395f721ced84cd4c630b6c98e18574e551dfb |                   0 | 2023-10-23 02:11:11
  4 | 18409245 |   103 | 0x7a0bc51cd9a47d98081731a49e7a1b66db2d8648 |        446000000000 | 2023-10-22 23:55:59
  5 | 18409226 |   105 | 0xd8da6bf26964af9d7eed9e03e53415d37aa96045 |                   0 | 2023-10-22 23:52:11
  6 | 18408723 |   100 | 0xa1d4f57cd759cbf0fd3be688165adbfc1a63b61a |        526000000000 | 2023-10-22 22:10:23
  7 | 18401377 |   169 | 0x1d96d54d1c074a07d91152f64fd0c00435665605 |        749000000000 | 2023-10-21 21:30:35
  8 | 18400862 |   150 | 0x186dc22283d5a879988fd2e1ced9ce15f185cf75 |        869000000000 | 2023-10-21 19:46:59
  9 | 18400746 |   152 | 0xd1ec8ce6af04db8131166157104e6e377caba5c4 |        552000000000 | 2023-10-21 19:23:47
 10 | 18399528 |    67 | 0x5ef8ec09314113e625ca7c9163292d663009d10e |                   0 | 2023-10-21 15:18:59
 11 | 18397717 |   183 | 0x8f70dd7c779b6df0d4e1ae591445315e5623677e |          1000000000 | 2023-10-21 09:13:11
 12 | 18394825 |   188 | 0x69d37cdaaa045c1a56a52006e61c0d0afa526151 |        611000000000 | 2023-10-20 23:30:11
 13 | 18394225 |    36 | 0xd8201168a94bad07f98af2ab3cabf21006fb7f25 |       1000000000000 | 2023-10-20 21:29:47
 14 | 18391383 |   185 | 0x9dfa6c7e88ea0559cf36cf17280a371b18539d61 |        762000000000 | 2023-10-20 11:57:35
 15 | 18386383 |   127 | 0x5a787b6e7e97bc79ea33f5f5e1f94253a8e371a8 |         36000000000 | 2023-10-19 19:11:35
 16 | 18383890 |   109 | 0xa4bc0d0ff80f865ad63552b7715d2a7e789be332 |         89000000000 | 2023-10-19 10:50:11
 17 | 18383263 |   113 | 0x6710be701d5377335b7a296cd15f2642b73de7c3 |        429000000000 | 2023-10-19 08:43:11
 18 | 18379965 |   131 | 0x88875f56e7e13668a8645aa449b56c57f29fccff |       1100000000000 | 2023-10-18 21:37:11
 19 | 18379770 |   146 | 0x4731618507abe463f4fcaacd5783d4b5127dd085 |        123000000000 | 2023-10-18 20:58:11
 20 | 18379360 |    51 | 0x4a9ee3a0ab1a83be8c8533eab838f4406ef9ec2d |                   0 | 2023-10-18 19:35:59
 21 | 18377288 |   125 | 0xc0efb08170a534eb3038da7530bebe3b3c5aac31 |        722000000000 | 2023-10-18 12:38:23
 22 | 18377028 |   156 | 0xf29cf3dd04acb2357f961a86456b9def70add9b4 |        616000000000 | 2023-10-18 11:46:11
 23 | 18376525 |   160 | 0x42ddcdd2264dac0e352c2400976240a969dae543 |        947000000000 | 2023-10-18 10:03:47
 24 | 18376037 |    24 | 0xc227c3c151d96c7e20f956c2147225fb85caa74c |   15880539891680138 | 2023-10-18 08:25:23
 25 | 18374909 |    52 | 0xac23ddb2188b809513eb456ac936f73aacea446d |     857154448684095 | 2023-10-18 04:38:23
 26 | 18372037 |    61 | 0xc227c3c151d96c7e20f956c2147225fb85caa74c |  188814448182508400 | 2023-10-17 18:59:47
 27 | 18366798 |   171 | 0x07bbc35d796a84f24b0d95b0571ac9a3fc38e109 |                   0 | 2023-10-17 01:24:23
 28 | 18364500 |    59 | 0x3e4f5818cbc67a29dd1863dde194ad2d9f417125 |       1000000000000 | 2023-10-16 17:42:23
 29 | 18363137 |    68 | 0xb0477a50cafe4ce6d3f8f64a81cde4f42963755a |      50000000000000 | 2023-10-16 13:08:47
 30 | 18361698 |   454 | 0x0931b3ee5ffebe506b91c0d35f4f59d0d089a38f |                   0 | 2023-10-16 08:19:35
 31 | 18361304 |   475 | 0xcfe99e4daddcf2c3d0666e7b23ffa0766b1a4c84 |      10000000000000 | 2023-10-16 07:00:11
 32 | 18360990 |   103 | 0xc39a7b3d35c1e60d50a778077d8f28a1fbda8d8d |                   0 | 2023-10-16 05:57:11
 33 | 18359932 |   126 | 0xd8da6bf26964af9d7eed9e03e53415d37aa96045 |                   0 | 2023-10-16 02:24:47
 34 | 18359453 |    86 | 0xb9845e5622b9009dd1697fec65682a971e14725b |                   0 | 2023-10-16 00:48:35
 35 | 18359300 |    41 | 0xe026f123b1b31fa12308bf322dfdb7daf2366c57 |                   0 | 2023-10-16 00:17:47
 36 | 18358666 |    42 | 0x3438c826d8701c293377d5a9550281ebf7212060 |    2570103994856000 | 2023-10-15 22:09:47
 37 | 18354805 |    86 | 0x2c64a1d5d602e7fb6d21da6211dcecc6e17a0649 |      10000000000000 | 2023-10-15 09:12:35
 38 | 18354547 |    59 | 0xc3837039e30732013bed735be0ae828df6d5983a |       1000000000000 | 2023-10-15 08:20:23
 39 | 18352913 |    77 | 0x0e73aa88bbe0c794ed91b76047ce6104086d008c |      44994809527332 | 2023-10-15 02:52:11
 40 | 18351208 |    41 | 0xe09b50361421580eb60626891eca8da4529d4ee2 |                   0 | 2023-10-14 21:09:11
 41 | 18350873 |    99 | 0xa788e0586409fa4eaf986e24604003adaa3dcc9d |      10000000000000 | 2023-10-14 20:01:59
 42 | 18347775 |   118 | 0xeba4da9dde892fc6a997577540f39f6dfcf20739 |                   0 | 2023-10-14 09:37:35
 43 | 18347117 |   208 | 0xba45b69ff42345075fe4e2b4d6efa62b87261a09 |                   0 | 2023-10-14 07:24:47

 */

////////////////////////////////////////////////////////////////////////////////////////////////////////////
// prompt openai
////////////////////////////////////////////////////////////////////////////////////////////////////////////

const OpenAI = require("openai").default;

const system_prompt = `
Given the following SQL table, your job is to write queries given a user’s request.

CREATE TABLE IF NOT EXISTS "transactions" (
	block int,
	index int,
	address varchar(32),
	value int,
	date datetime,
	PRIMARY KEY (block)
);
`

const openai = new OpenAI({
	organization: keys.openai.org,
	apiKey: keys.openai.key
})

async function llm_to_sql(user_prompt) {

	const response = await openai.chat.completions.create({
		messages: [
			{ role: 'system', content: system_prompt },
			{ role: 'user', content: user_prompt }
		],
		model: 'gpt-3.5-turbo', // gpt-4
		// temperature: 0,
		// max_tokens: 1024,
	})

	/*

	here we get :

	[
		{
			index: 0,
			message: {
				role: 'assistant',
				content: 'SELECT SUM(value) AS total_value\n' +
				'FROM transactions\n' +
				"WHERE date = '2023-10-14';"
			},
			finish_reason: 'stop'
		}
	]

	*/


	const sql = response.choices[0].message.content.replace(/(\r\n|\n|\r)/gm, " ");

	return sql
}


////////////////////////////////////////////////////////////////////////////////////////////////////////////
// prompt openai
////////////////////////////////////////////////////////////////////////////////////////////////////////////

async function main() {

	// run this as needed to populate the db from moralis
	// populate_database()

	// get a prompt
	const user_prompt = `Write a SQL query which sums all values as total_value for all transactions on 2023-10-14.`

	// pass it to the llm 
	let sql = await llm_to_sql(user_prompt)
	console.log(sql)

	// pass the resultant sql query to postgres

    await client.connect()

	let result = await client.query(sql)

	/*
	Result {
	  command: 'SELECT',
	  rowCount: 1,
	  oid: null,
	  rows: [ { total_value: '10006401000000' } ],
	  fields: [
	    Field {
	      name: 'sum',
	      tableID: 0,
	      columnID: 0,
	      dataTypeID: 1700,
	      dataTypeSize: -1,
	      dataTypeModifier: -1,
	      format: 'text'
	    }
	  ],
	*/

	// this isn't exactly what I want
	// SELECT SUM(value) FROM transactions WHERE date = '2023-10-14';

	// this is what i want
	// SELECT SUM(value) as total_value FROM transactions WHERE date = '2023-10-14';

    await client.end();

	console.log(result.rows[0])


}

main()