Prompt to Dashboard in One AI Tool Call

I talked a little bit about my work on TanStack AI and Code Mode before, but in this video, I wanna show you how Code Mode connects to SQL databases and does it really well. Let me just give you a quick demo. On the left-hand side, I've got Drizzle Studio, and as we can see, we've got some e-commerce type data. Here are my customers, and you can look at their purchases. This is a really cool interface. Really love that. Over here is an AI chat powered by TanStack AI with Code Mode that connects to that database. Let's just try it out. I'm just gonna click on this daily revenue trend, and that's gonna create a report showing revenue trends the last two months. the first thing it does is create this new report over here. That's kinda interesting all on its own. Instead of giving the AI tools like execute SQL, it instead just writes TypeScript and then uses injected functions like query table that gets our data. After that, it can then just use standard TypeScript to just chow through the results of that and build out all of the math, which is great because LLMs are terrible at natively doing math, but they're very good at creating TypeScript that does math. Once all that math is done, then it can go and use other injected tools like, report text, report grid, report card, report metrics, so on and so forth, to format all of the data that it just created. So it goes and it fires off all these things, and it creates this chart dynamically. It also gets to decide using a return statement what the output of that execute TypeScript is, and that's what goes back into the LLM chat. You can see it down here in execution complete. Then with that data, we get back also a nice markdown text summary of what it put over into our report. Let me go and show you how this actually works in the code. Let's get right into it. Let's talk about the database first. For the database, I'm going to be using Netlify's new Netlify database. I do work for Netlify now. This is a little bit of a promotion for Netlify database, But that being said, it's a great database wrapper, and it works both locally and also easily deploys out to production. It even does branch deploys, and that's just super cool on its own. But let me walk you through how to get this set up because, of course, all this code is available to you for free in GitHub in a link in the description right down below. Let's start fresh. To do that, I'm gonna remove the .netlify and Netlify directories. The .netlify directory really is transient, but the Netlify directory has migrations in it, so you probably wanna go and check those in if you're really gonna do this. go and take a look over at our package.json. We can see that we got our Netlify database at 1.0. We've also got a bunch of different database commands over here. So what I'm gonna do is in one terminal, I'm going to run my dev environment, and that's gonna start up the dev environment plus the local database simulator. And then in the second terminal, I'm going to generate my migrations. You can see that here with Drizzle kit generate. It's built out the migrations. Those are over in Now I'm going to apply those. So what does that look like? Well, over in apply, I'm gonna use "netlify database migrations apply". That's gonna apply the migrations that I just created to the database. That's applied that one migration. And now that I've got the schema, I need some data, right? Next thing I'm gonna do is seed that data. So I'm gonna do db:seed. So it inserted a bunch of customers and products. Now I can do something really cool and do db:studio. That's gonna bring up Drizzle Kit Studio. And from there, I get that cool DB preview that we saw before. I mean, that's literally as easy as it gets. And let's take a look over here at the configuration of all this. This Drizzle config is really easy. We're just bringing in defineConfig over here from Drizzle Kit. And then we're setting up our Drizzle, which is a ORM layer to say PostgreSQL is our dialect, and then we give it a schema. So that schema is located over in DB. That schema defines the posts and the customers using that Drizzle ORM layer to give us portability across different databases. And then in our seed, we set up our drizzle with our connection, and then we just insert all of our data, and it's really that easy. So now that we know how the Netlify DB is set up, how do we actually set this up in Code Mode so we can connect to that database? The secret sauce here is over in tools, under database tools in particular, we've got a standard TanStack AI tool definition for queryTableTool. But you might be saying to yourself, "Wait, wait a second, hold up. You're defining it as a standard AI tool here, but really it's not an AI tool. You're giving it to this Code Mode system." Well, that's the cool thing about Code Mode, is it actually works with exactly the same tools as the standard LLM stuff. It just injects those tools into the VM that runs the JavaScript. That's so cool because you can actually just reuse those tools. If you wanna use it in Code Mode, you can just bring it in there. If you wanna use it in standard LLM mode, you bring it in there. Just need to write that tool once, and it works in both places. Execute query of course takes a bunch of parameters. What table do you want? What columns do you want from it? The where conditions. And in this case, we're actually just writing the SQL raw. We just construct some select statements and then execute those against the database pool So how do we actually set this up with Code Mode? Well, if I go over here to routes and then into reporting, we've got our API report. This is the chat endpoint that the UI talks to. And the really important function there is called getCode ModeTools. We create an isolate driver. That's the VM that's actually going to run the JavaScript that is created by compiling the TypeScript from the LLM, and then we create the Code Mode tool using createCodeMode. It returns the tool as well as an additional system prompt. The tool is what we send off to the LLM, and then the system prompt has all of the additional information about all of the injected tools that we've added into the Code Mode system. In this case, that's those database tools as well as those UI tools. Let's go take a quick look at the reporting system, and I'll show you how the UI side of this works. If I look over here in source, components, reports, primitives, data, we can see that we have things like chart and data table and all that, progress, sparkline, a whole bunch of different utilities. We've also got stuff for layout, grid, VBox, et cetera. We don't really see it using all that much, but it can use all of these layout primitives to go and build out a UI dynamically. And the way that it does that is it uses this node renderer NodeRenderer takes an array of nodes that are managed by the application for a specific report, and it just renders those nodes. It has a lookup from all of the nodes types in the array to the React components that it actually uses to render those. You can see it importing them all here. Then it connects the different node types to those different component types. It just iterates through that array of nodes and builds out the UI dynamically. That's how in this case we're doing a generative UI using LLMs, which in turn are using Code Mode. To be honest, I could go for days drilling down into the nuts and bolts of how all this works, but it would take a long, long time. I just recommend if you're really into this kind of thing, if you're into generative UIs, if you have a database that you wanna just be able to connect to and do some chatting around and get some reports from dynamically, this might be a cool tool for you to try out. If you wanna know more, I put a link to the Netlify Database docs as well as the Code Mode docs on TanStack AI in the description right down below. If you have any questions or comments, be sure to put that in the comment section right down below. And in the meantime, if you like this video, hit that like button. If you really like the video, hit the subscribe button and click on that bell, and you'll be notified the next time a new Blue Collar Coder comes out.