Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Building AI agents to query your databases

blog.dust.tt

Post reply on HN
Print view View original

31–40 of 56 posts

Re: Building AI agents to query your databases

#31

Post by zurfer »

gergely wrote: post #20

How do you solve the following problems? 1. Whenever you run a natural language question the result will be the same for the same question? 2. How do you teach an agent/LLM for the datamodel of the application that stores the data in the databases?

1. By having one obvious documented way of answering the questions or accepting that humans would also come to different results.

2. Investing in good data models and documentating the edge cases. Also learning from explicit feedback is powerful.

This helps our customers at getdot.ai get highly reliable results.

The models also got a lot better at making sense of weird and complex data models in the last months.

Re: Building AI agents to query your databases

#32

Post by dimitri-vs »

breadwinner wrote: post #29
gcanyon wrote: post #19

My general method for things like this is to: 1. Get a .dot file of the database. Many tools will export this. 2. Open the .dot in a tool I built for the purpose. 3. Select the tables I'm interested in, and export a subset of the .dot file representing just those tables and relationships. 4. Hand that subset .dot file to the LLM and say, "given this schema, write a query -- here's what I want: " That gets the job don…

Something like this is what I do also. Is there another way? How are others doing it?

Export DDL of one or more relevant tables, run query to get a random sample of 5 records for each table. Really quick to gather all this and enough context to handle most query writing tasks with some guidance.

Re: Building AI agents to query your databases

#33

Post by rishabhparikh »

jt_b wrote: post #28
totalhack wrote: post #26

Earlier quoted context omitted.

You are doing a lot of the work a semantic layer would do for you. I wonder if you would have better luck having the LLM talk to a semantic layer instead of directly to the database.

Can you talk more about what an implementation of such a semantic layer would look like?

Not OP, but a naive guess is it would mean that you'd have your schema defined in an ORM (for example, Prisma). The advantage here is that the LLM gets context on both the schema and how the schema is used throughout the application.

Re: Building AI agents to query your databases

#34

Post by pbronez »

mritchie712 wrote: post #18
bob1029 wrote: post #5

> This abstraction shields users from the complexity of the underlying systems and allows us to add new data sources without changing the user experience. Cursed mission. These sorts of things do work amazingly well for toy problem domains. But, once you get into more complex business involving 4-way+ joins, things go sideways fast. I think it might be possible to have a human in the loop during the SQL authoring pha…

Using a semantic layer is the cleanest way to have a human in the loop. A human can validate and create all important metrics (e.g. what does "monthly active users" really mean) then an LLM can use that metric definition whenever asked for MAU. With a semantic layer, you get the added benefit of writing queries in JSON instead of raw SQL. LLM's are much more consistent at writing a small JSON vs. hundreds of lines of…

How well is that working for you?

We use a pattern where we ETL things into tables that model the upstream source closely, then use SQL Views to tighten up the model and integrate across data sources where needed. Keeping this all inside one DB allows us to use tools that understand the schema for autocomplete, etc.

I expect the developer experience would be significantly worse if we started writing views in YAML instead of SQL… but you’ve found the opposite?

Re: Building AI agents to query your databases

#35

Post by zerop »

mritchie712 wrote: post #18
bob1029 wrote: post #5

> This abstraction shields users from the complexity of the underlying systems and allows us to add new data sources without changing the user experience. Cursed mission. These sorts of things do work amazingly well for toy problem domains. But, once you get into more complex business involving 4-way+ joins, things go sideways fast. I think it might be possible to have a human in the loop during the SQL authoring pha…

Using a semantic layer is the cleanest way to have a human in the loop. A human can validate and create all important metrics (e.g. what does "monthly active users" really mean) then an LLM can use that metric definition whenever asked for MAU. With a semantic layer, you get the added benefit of writing queries in JSON instead of raw SQL. LLM's are much more consistent at writing a small JSON vs. hundreds of lines of…

Currently exploring cube for a "natural language to SQL" solution.

My schema is - 90+ Tables, 2500+ Columns, well documented

From your experience, does Cube look a fit? My use cases will definitely have JOINS.

Re: Building AI agents to query your databases

#36

Post by ilaksh »

On the off chance anyone is interested, I just built a Supabase plugin for MindRoot: https://github.com/runvnc/mr_supabase

MindRoot: https://github.com/runvnc/mindroot

Demo of agent using it to update a database after doing research: https://www.youtube.com/watch?v=nXwwSj0KSXI

If you give it the actual database password it will use a Postgres client to get the actual information_schema and put it in the system message. It uses the normal Supabase library for everything else.

Re: Building AI agents to query your databases

#37

Post by sitkack »

dimitri-vs wrote: post #32
breadwinner wrote: post #29

Earlier quoted context omitted.

Something like this is what I do also. Is there another way? How are others doing it?

Export DDL of one or more relevant tables, run query to get a random sample of 5 records for each table. Really quick to gather all this and enough context to handle most query writing tasks with some guidance.

Schema + samples. Same thing a skilled person would use.

Re: Building AI agents to query your databases

#38

Post by Gerardo1 »

LaGrange wrote: post #16

I mean this is generally repulsive, but please I beg of you, run this exclusively against a read only replica. I mean, you should have one for exploratory queries _anyway_, but nobody ever does that. "Validating the query to ensure it's safe and well-formed" all I can say to that is "ROFL. LMAO."

Yep. MCP is a project run by Anthropic (https://github.com/modelcontextprotocol) that "enables seamless integration between LLM applications and external data sources and tools."

They host a repo with lots of what they call "reference" implementations, including this one for postgres which naively takes a query from a request and shoves it at your database with no validation, escaping, anything: https://github.com/modelcontextprotocol/servers/blob/7d6cdb6...

There's an issue calling this out, and it's been labeled a bug, but still.

When you go to their documentation, the only example of actually building a server is this section where they just...use an LLM to build it. Here's their prompt:

> Build an MCP server that: - Connects to my company's PostgreSQL database - Exposes table schemas as resources - Provides tools for running read-only SQL queries - Includes prompts for common data analysis tasks

So, I think there's a fairly high chance that all of the "reference implementations" they're hosting are AI generated with basically no security considerations or code reviews made.

Re: Building AI agents to query your databases

#39

Post by totalhack »

jt_b wrote: post #28
totalhack wrote: post #26

Earlier quoted context omitted.

You are doing a lot of the work a semantic layer would do for you. I wonder if you would have better luck having the LLM talk to a semantic layer instead of directly to the database.

Can you talk more about what an implementation of such a semantic layer would look like?

There are a number of semantic layer tools out there these days. Each has their own unique approach, but essentially it's a meta layer on top of your database that can be used to do things like form queries or provide a consolidated API to your data (which may be in multiple databases).

Some comments on this thread mention popular semantic layer tools like cube.dev. I also made an open source one that I use regularly, though it's currently in I-hope-to-put-more-time-into-this-someday mode. Been busy with an acquisition this year.

https://github.com/totalhack/zillion

Re: Building AI agents to query your databases

#40

Post by Der_Einzige »

gcanyon wrote: post #19

My general method for things like this is to: 1. Get a .dot file of the database. Many tools will export this. 2. Open the .dot in a tool I built for the purpose. 3. Select the tables I'm interested in, and export a subset of the .dot file representing just those tables and relationships. 4. Hand that subset .dot file to the LLM and say, "given this schema, write a query -- here's what I want: " That gets the job don…

Combining this with structured/constrained generation with grammars/pydantic can supercharge this btw
Post reply on HN