AI Assistant on Supabase - Query Multiple Tables, Insert, Update, Delete and Analyze Data

in one of our previous videos we looked at how we can use Bill ship's open AI assistant to easily chat with the data stored in a super based hosted table but now what if we want to be able to chat with multiple tables and what if we want to give our assistant the ability to perform other database operations how cool would it be if we could just tell our assistant to insert a new record for us in natural language or likewise to update an existing record well luckily for us creating a powerful database assistant like like this is Made Easy thanks to Bill ship it's easy to talk so let's see a working demo here we have a bill ship workflow and it's using Bill ship's open AI assistant node and here we're giving our assistant the ability to use two tools our assistant will be able to use the fetch table schema tool to fetch the data structure of any table we query and our second tool SQL query will come in handy once our assistant has generated a SQL query relative to The Prompt we send and the table schema it was able to fetch now let's put our assistant to work we'll go to test and we'll send a prompt please add a new user for me John Doe and His email is John example.com we'll test the workflow and after a few seconds our assistant has notified us that it has successfully inserted our new user let's verify this here we have our super base table users and at the bottom we can see that John an example.com has been inserted just how amazing is that now it goes without saying that the opportun unities for this are endless and guess what we're going to show you how to build this in just four easy steps in Bill ship you'll find a number of templates available to help you get started creating your own assistant our first step will be to clone the database query assistant template we've included some sample prompts that you can use to test out the assistant but before we can get to that we need to set up our template for step two let's start configuring the open AI assistant node the first thing we'll enter here is our API key we'll go to secrets and we'll select open AI if you don't already have your open AI API key saved in Bill ship then you can just go to the open aai console and get it from the API key section and while we're still in the open a console the next thing we need to do is get an assistant ID so we'll go to the assistance page and we already have created an assistant that we want to use but if you don't have one then you can easily create one the only thing you need to do here is enter a name for your assistant and select the gbt model you want to use you don't have to enable any tools here because we're going to be doing this in Bill ship so we'll go to our assistant and we'll copy the assistant ID we'll go back to Bill ship and we'll paste that value here and as we're setting up our assistant it's important for us to put enough thought into the system prompt we give the assistant we need to make sure we clearly instruct our assistant to use the tools we give it let's look at the instru instructions we're using here we're telling the assistant that it has all the knowledge about all the various tables inside of my postris database and we will ask various questions regarding the tables inside of this database and we wanted to construct valid SQL statements the important part here is that we're telling our assistant that we will be giving it two tools to use and it must follow the guidelines of each tool the first guideline is that it must use the fetch table schema node to find out the structure of the table being queried and the second guideline is that it should use the SQL query node to execute the query it generates and then return the results the last bit of instruction we are giving our assistant is for our own safety so for any volatile operations such as delete we want the assistant to ask for confirmation first so depending on your use case and requirements this is where you will customize how you want your assistant to operate based on the tools you give it user prompt will be our message to the assistant if we look at the value being passed here it's using a prompt field that we're extracting from the request body for this demo we're going to be skipping over tread ID but if you're looking to maintain conversation history then you should pass one of your tread ID here we're also not going to be using none of the built-in tools for this demo because we're going to be supplying our own tools step three let's focus on the tools and actions we're giving our assistant the great thing about using Bill ship's open aai Assistant node is that you can use any of the bill ship pre-built nodes as tools for your assistant what does that mean you ask well as you can see right now we're using two tools but these are actually nodes from the bill ship library if we scroll down to the postgress group we'll be able to see these nodes here so that means that if you want you can use any of the nodes that bill ship provides as tools for your own assistant in this demo we're just working with postgress but if you want you can work with mySQL as well mongod DB or maybe you want to create an assistant for managing data inside of Google Sheets then you can do that as well and don't forget if you're trying to use a tool in the form of a node that isn't already available in the bill ship library then you can just generate your own with AI with Bill ship there's no limitation now we need to start configuring our tools we need to provide our database credential so that our assistant will be able to communicate with the tables we query our database is hosted on super base but it's important to mention that these two nodes are platform agnostic so if you have your postgress database hosted somewhere else then it should still work as long as you're providing the correct credentials to get our database host we'll head back to super base and we'll go to Project settings from Project settings we'll go to database and here's the database host so we'll copy this we'll head back to Bill ship we'll paste this value here next we need to get our database name we'll copy it go back to Bill ship and paste then we need to get our user so we'll just copy the user head back to Bill ship and paste the user lastly we already have our database password stored as a secret so we'll just go to secrets and select it now All That Remains to be filled is table name but notice how the star icon next to this table name input is highlighted what this means is that this input will be autofilled by the assistant this brings us to another interesting feature of using Bill ship's open AI assistant so based on our user prompt the assistant will be able to intelligently autofill this with the table name we're querying if we click this icon to autofill the way this work is that we need to provide good descriptions about the input for example here the input name is table name and for the input description we're letting the assistant know that this is supposed to take the name of the table for which to fetch the schema we're also telling it that it should try and follow well-known and accepted conventions for table name for example pluralized table names and once we give our assistant description about our input then it will be able to autofill this for us and why is this useful well because we want to be able to operate with multiple tables and not just hardcode this to users for example so having the assistant autofill this value will allow us to operate with any tables inside of our database after our assistant fetches the schema of the table being queried the next thing it will do is try and generate an SQL query relative to The Prompt we're passing and once it has generated this SQL query it will move on to the SQL query node to execute the query now we just need to configure this second tool the same way we did with the first we'll go back to our previous node and we'll copy the database host and we'll just paste it here next we'll copy the database name and paste it as well the same for username and then for the password we'll just select it from our secrets our final put here which is the SQL query we want to execute will also be autofilled by the assistant the power we're unlocking by giving our assistant these two tools to use is that our assistant will be able to operate on any tables in our database with any given operation we want to perform so that means that we can read write update and even delete from any tables we want the final node in our workflow is to Simply return the response from our assistant and just like that we've arrived at step four where we can deploy our workflow and start testing our assistant first we'll ship our workflow great our workflow is now shipped and now we can start testing our assistant against some of these sample prompts in the beginning of the video we already saw how we could insert a new user so let's try this prompt give me five latest users the star will go to test switch to the body Tab and for the pr prompt will paste this value and now for the moment of truth let's test the workflow great so after a few seconds we get back our result here are the five latest users and immediately we can see the latest user is John Doe which is correct because as you can recall this is the user we inserted at the beginning of the video to further understand how this is working let's inspect the logs of the assistant we'll view logs when we send our prompt the first thing that the assistant will do is execute of fetch table schema node SL tool if we try and inspect the previous logs we can see that the assistant will autofill the table name input with users and then if we continue down we can see that the next thing our assistant will do after fetching the table schema is execute our SQL query node and if we dive in and further inspect the logs then we'll be able to see the SQL query that the assistant generated for US based on the prompt we send so remember in our prompt we said give me five latest users and if we look here the query that was generated is Select everything from users order by created at in descending order and limit to only five so that was pretty accurate now let's try some more examples let's go back to test inbody and let's update our prompt and this time let's perform an update operation so we'll say update the user John do set his bio to loves eating too much now let's test the assistant again and finally our assistant has notified us that the bio for jando has been updated to loves eating too much let's verify this in super base let's look for John Doe and we can see that his bio has indeed been updated to loves eating too much perfect but now we've come to the conclusion that we no longer want John do to be part of our user base so we'll delete his account we'll update our prompt and we'll say delete John do let's test the workflow and would you look at that our assistant is following the instructions we gave it that if we want to perform a volatile operation such as delete then it needs to ask us for confirmation this is a very important detail to keep in mind because you don't want to give your assistant too much control over your database and you definitely don't want to be Reckless with the data you have stored and this is good because maybe we've had a change of heart and we no longer want to delete John do now let's try one more example and this time we're going to be quering from a different table in our superbase database we have a contact table with just two entries so in Bill we go back to update our prompt and for the prompt will say give me only the name of my contacts Let's test nice we get back LS RJ and John Doe as always we can verify LS RJ and John Doe is correct this showcases the ability that our assistant is capable of interacting with multiple tables and this is all made possible because we're using the fetch table schema node as a tool for our assistant as always if you're curious you can always inspect the logs to view what SQL the assistant generates for you so in this example we can see that the generated SQL query is Select name from contacts which is dead on because we only wanted to select the name field from our contacts table and that's how by using Bill ship you can build your very own database management assistant one which you can use to operate on your database in natural human language and in the process reducing the need for you having to write SQL queries and that's as far as we'll go today but while you're still here don't forget to subscribe to our YouTube channel because we have a lot of AI based content coming soon and you're not going to want to miss out on any of them and remember we love hearing your feedback and ideas so please feel free to share them with us in the comments that's all we have for now and we look forward to seeing you in the next video until then happy Bill shipping

Channel: BuildShip

Views: 1,436

Keywords: API builder, no-code backend, low-code backend, automation builder, AI powered workflow builder, visual backend, backend builder, no code google cloud, low code google cloud, no code AI workflows, AI workflows, AI workflow, AI workflow builder, nocode, lowcode

Id: uM5YP43SikU

Length: 13min 5sec (785 seconds)

Published: Tue Jun 18 2024