Easiest Database Setup in Next.js 14 with Turso & Drizzle

I have a react app that generates code using GPT based on prompts so I could type in something like a profile page and it will generate all the code I need for that component and I set up all of this open AI code in a separate video which you can check out if you want to but in this video I'm going to focus on setting up a database so that I can actually persist this chat data and for the database I'm going to use turo which is the fastest easiest cheapest database in the world it is a SQL light database that you can run at the edge and it works really well I have a couple of apps in production using this and my opinion is that you should always start with a relational database and only switch to no SQL when you have a really good reason to so we'll be using turo for the database and for the omm of course we're going to be using drizzle so we're going to start by actually setting this up using drizzle and since this is turo we'll be following all of the SQL light steps actually they have a turo step right here here so I am going to start by installing these things let's use bun and I'll just paste that into terminal and then we can copy this boilerplate code and in my next app I like to create a new database folder inside of source not inside of app I'm going to call this database and inside of this I'm going to put an index.ts file so that if I import database I just get everything out of this file uh I don't need to run a query right now what I do want to do is export DB so we're going to use drizzle to create a client and then export this DB thing which we can use to run all the insert and select statements that we're going to need for this app and then this does depend on a database URL and a database orth token so this is going to come from our environment variables and orth the next step is we need to actually set up the schema for the tables I am going to go to column types and SQL light and here are all the the examples you really need to set up your schema before I do that I'm actually going to create a schema directory this where we're going to put all the tables uh and I know I'm going to need a chat and a messages table actually I'm going to keep these both plural because I like to pluralize my tables so my chats table I'm just going to use comments for now is going to have an ID name and maybe like a created Tim stamp messages are going to have an ID a chat ID the content of the actual message created at timestamp again so this is starting to look like a standard chat app every single time I start a new chat I just create one of these and then all the messages are related to chats with a foreign key so each chat can have many messages every message belongs to one chat but since this is a chat GPT message we're also going to need the roll which can be user system or assistant and since chats is the simpler table let's set that up first so I kind of just want to start with an example I guess I'll go with this uh that's fine and usually I probably want an auto incrementing integer to be the ID for a table but in this case where I have a chat so if I come over here I'm creating a chat and I want to list all the chats here if I select a chat I want the ID of the chat to appear in the URL and I potentially want those to be shared variable so I'm going to set this to be text and then I'm going to create some sort of unguessable unique ID just for my chats table and then I'll rename this to chats and this to chats this is my JavaScript variable and this is the name of the table in the database this is the property in JavaScript this is the property name in the database so anything in the strings you see here will be what the database sees and then I'll just keep going here so name is going to be text and then created at actually going to make this text to created at but I need some helpers here so let's go back to drizzle uh I want current timestamp okay so I can just copy these so we're going to have text timestamp default current timestamp and then I need SQL which I can import from drizzle omm and I'm not really using integer here and then for all three of these I want to make sure they're not null this top one yeah is is going to be the primary key and then I think chat is looking pretty good I just need to make sure I export this so I can see it from the other parts of my application so this is the schema for my chats table now I'm going to copy this for my messages schema ID here I'm actually going to use the integer because I'm not going to need to share a message going to rename chats to messages here for some reason it imported the postgress version of this that's weird no I just need integer from SQL light this I'm going to replace with content then I have two more let's see I have chat ID which is going to be the foreign key so that's good right now but I do need to have it reference the other table and then we'll have roll which is also going to be text but I want to make sure that it's limited to only one of these so let's go check for that let's check for enum so right here is how I can add an enum value in SQL light that looks good so roll is going to have that enum too many brackets this is going to be either user or assistant since I'm not going to store system messages in the database and then since this is a foreign key I need to make sure that it's referencing the other table let's do a search foreign keys so we can say references the other table ID so if this were user ID I would look like this but it's not it's for the chat table so let's import chat chats from there we go perfect so this column references the primary key of the other table so I have my schema set up here in my application I just need to create a database that I can apply this to and then I can start inserting and selecting data so I'm going to go back to turo and we're going to check out the docs here to see how we can get set up so maybe let's go to tutorial and we'll do this CLI walk through so I have already installed turo on my laptop so we'll go to step two I've already signed up for turo so I'm going to go to step three and I'm just going to run turo DB create and create a new database for this project test app demo turns out I'm not logged in so let's quickly log in and now let's try creating the database so it set me up a new database now let's follow along with the steps steps let's show the database it probably picked my closest location to put this in I should name my database correctly test app demo so I have a new database that's the URL that's the ID and the location is Boston and SE all right I've set up my account to have two locations with replicas okay that doesn't matter it just set up a database and they're accessible over the internet all right so let's go to the next one make queries I don't care about that I don't care about replicating I need the one with the keys so how do I generate Keys the cell setup guide let's see no no no no where do I set up the keys keys here we go okay turo DB tokens create e none let's paste that in this should be my database name whatever I named it test app demo here is the key to the database here's the database ID and E none means that this won't EXP expire so I can use this as long as I like these are the details that I would put into versel I can put them in locally too so I'm going to copy this key here and open up my environment variables and I'll just paste that in and that is the orth token so set that equal there and then we need the database URL so this is the URL for this specific database I'm going to make sure I copy this correctly database URL so this is the information I need to access the database over the public internet like I said this is what I'll put on Vel or whatever server I'm using uh but if I'm just doing local development I can use this database but there's a really nice feature of turo because it's a SQL like database where I can just set up a local database really really easily uh and access it over Local Host 80 so let's do this right now and then later on I could use these details so to do this and get this working using the turo CLI I can run turo Dev DB file dev. DB this last part doesn't really matter it's just whatever name I want to give to the local file for the database so it now sets up a local file it's hosting that at Local Host 880 I should be able to see yet there's my database files make sure you don't commit those but I can now really quickly get set up with my database using this local version so now I'm going to go back back to the drizzle docs and go to drizzle kit because this is the thing we're going to use to actually apply the schema so I need to install this into my app and then where's the quick start there we go that's what I need so over here in the top level of the project I'm going to need a drizzle. [Music] config.sys and then we need to add driver is going to be turo we need DB credentials Perfect all right so this is the object we need a URL is going to be process. mv. database URL perfect and the or token it's going to be awesome the or token okay so this is going to pull in the environment variables it's going to be able to use all of the schema files all the files in the schema directory perfect uh and apply a database push or migration to the database and if you're not using bun you'll need to bring in the environment variables somehow maybe using M but since I'm using bun here I should be able to run bun X drizzle kit push SQL light and this should push my schem of changes to the database it did not for some reason it might be I'm using Local Host instead of 127.0.0.1 that is a solution into a lot of problems okay let's try that again nope still failing um I'm a I put s in there all right fine yeah obviously an SSL issue ah okay cool all right so I push those updates to my local sqlite database perfect okay so now now I should actually be able to interact with the database so I'm going to close all of these windows and we're going to talk about the workflow of this application a bit a user can input a message here and hit send and this should create a new message trigger the open AI GPT response but it should also save that message to the chat that it's a part of so if it's already part of an existing chat just use that as a foreign key but if no chat already exists then I need to create a new chat as well so essentially before sending a message or creating a message I do need a chat entity to exist in the database so in here I have this handle submit and it does a whole bunch of things but right here it is sending off a request to the back end to start streaming in that llm response so before I get here I really want to make sure I have a chat ID that I can use that I can link to my message entities so the way I'm going to go about doing this is I'll keep this in state so I'm going to create a new state variable called chat ID set chat ID and let's just make that a string so it can default as an empty string so if I open up this page if I like refresh then I'm not part of a chat which means this is an empty string so then when I handle submit the first time I hit enter I'm going to check to see if a chat ID already exists and if it doesn't then we'll have to create a new entity in the database create a new chat in the database and get the ID and store it in state and I'll delete these comments from the last video so right here if there is no chat ID I need to create a new one and the easiest way to do that in a nextjs application would be to create a server action so I'm going to create just an actions. TS file here and we're going to export an async function that is going to create a chat um and this needs to be used server so in here I'm going to import D from my at SL DB so this will just import the index file which Imports my DB object here and this is how I interact with the database using drizzle so then in here to create a new chat I can just say db. insert then I need the chat table so let's import chats from schema SL chats so we're going to insert into chats values and then which values are required here so we need an ID a name and then the creat that has a default value so we're going to need ID ID and name and I'm not really sure what to do about name now so I might leave that blank an ID needs to be a unique ID that I can share in the URL and can't be guessed so I'm going to use the same technique I used in my S3 video and in my utils file I have this function that is Generate random string that uses the node crypto library or the edge runtime crypto library to create an unguessable unique string so we'll go back into actions and I'm just going to import this generate random string const ID equals Generate random string we'll make that 16 bytes and I'll throw that in here and I'm just going to use the ID for the name now I could update this later but this is good enough so this will create a new chat so result is going to await insertion if I want to get this and actually I don't need to get the result I can just wait for that to be done uh and then out of this I'm going to return an object that has the ID in it because the client is going to need the ID so essentially if I call this function we'll create a brand new chat we'll return the ID to the client component that can then store it in state and I would most likely also want to do some like user or or something if I had that set up just to make sure the user was logged in and attach this to that user profile uh but this is going to work just fine for now back over in my chat content I would usually import the action straight into here but I've been having issues with that so instead I'm going to import create chat function from actions and I'm going to pass that from this server component the page component into this child component which is just called chat content so we'll say create chat equals create chat so then in my chat content child component that does all of the form actions and everything in the client uh I need to accept that so we're going to create new thing here create chat is going to be a method that doesn't take any values but instead returns a string object that has ID which is a string there we go okay so that's the type create chat returns so then down here if I'm not already creating messages within a chat we're going to create a brand new chat so cons chat equals a wait create chat and then we'll store that ID in state perfect just like that but since setting state is asynchronous I should probably store this in a variable so I'll just call current chat ID equals chat ID if there is no chat ID then we're going to grab that we're going to create a new one and then set it to update the state but now we have a reference to it in this variable that we can use down here so when I am sending a request to the server to basically send these messages and stream down the llm data I'm going to also attach into that chat ID so chat ID is current chat ID do that for both versions that I send up to the server so so then in my route over here let's see API message route I am getting content but I should also now have a chat ID so I should probably do a check if there is no chat ID then return new response D is required perfect and again I'd probably have some user or stuff check if the user is logged in make sure that chat belongs to them when I get around to adding user I would add these things but this is good so make sure I have a chat ID and then I also want to go to the database and make sure that chat ID actually exists in the database so I'll do a similar thing here I'll import the database from DB import chats from there we go so now I'll do a quick db. select select star from chats where this is not the right syntax I need where EQ chats. ID is chat ID and this EQ comes from drizzle directly so import EQ which is equals from drizzle omm oh that's it cool and then since this is SQL light I also need to say get at the end and I'll say con chat equals a wait and then probably do another check just make sure that the chat actually exists is not found so at this point the chat would definitely exist and I have the message content that the User submitted in the form there but I don't yet have the response from the llm from GPT so I want to wait until that response has come back before I add those to the database so using the open AI stream from the AI Library let's actually just look at the docs here open AI stream AI for cell let's go in here hopefully this is the docs you know I'm actually going to do a search for database save to database after completion open ey perfect okay this is exactly what I want so I'm going to copy all of that cuz this open AI stream function here accepts the response from open AI but also gives a whole bunch of callback functions so I don't actually want to do it this way I am going to wait for complete completion from open Ai and then save all of that in the database okay so right here the content is what the user has sent the completion is what GPT has sent and I need to store these to as separate messages into the database so let's go db. insert messages and I'm going to need to import messages up here import messages from messages and then the value so so what goes into a message I need chat ID actually I'm going to change this I want this to be camel case chat ID roll content these are the two or three required things that I need to add in so chat ID is going to be the chat ID we already got that roll for the first one is going to be user content is going to be the actual content all right that was easy for the user then we'll make this an array so we can pass in multiple objects and the second object is going to be similar but this is going to be assistant and let's see format for me too many bracket not enough brackets I need that I do need that one what there we go Okay cool so we're going to insert those two things in the database and Link them to the chat they're a part of let's await that and we're not really going to do anything else I guess we should really have some error handling of some sort like at the very least it would probably be nice to console error this so we can see it in the logs so we're streaming the response down we're not really going to see any of this in the response we get in the client cuz that's just for the llm data but once that's completed before we completely disconnect from this endpoint we're going to store those things into the database let's just go through it before we run it make sure it's not insane so when we handle submit if there's no chat ID create a new chat then use that ID that comes back from the server action to send these messages back to the server create an LM response and store those in the database so that means that if I submit again after that chat ID should be stored in state so we won't create multiple chats we'll just create multiple messages for the same chat in this case until I refresh the page so now I'm going to go back to the application hopefully it's working and actually before I do that I'm going to do one more thing I'm going to bun X drizzle kit Studio which will hopefully open up drizzle Studio perfect okay so I have chats and I have messages and there are zero entities in each of those tables so go back over here uh I'm going to say write a function in typescript so hopefully this just created a new chat and then created two messages within that chat my message to instruct it to create the function and the response from GPT creating the function so if I refresh here I can see there's one chat and two m messages the single chat just has that unique ID and the messages should have my instruction write a function in TS and then this type script function perfect okay so let's try that again let's say write a function in Rust okay wrote a new function so what I should see here is four messages all with the same ID and over here still only one chat even though there's four messages perfect okay so if I go back here and refresh it will lose that state so now if I say write a function in Swift then this should create a brand new chat two new messages okay just double check that two chats six messages perfect okay so they're all being stored in the database that's really good so now it would make sense to actually retrieve each chat put it in this bar over here and if I click on that I would hopefully be able to see the last message from that chat so I'm going to go back over here into my code and I'm going to go to the initial page which loads in that client component for the chat content with the form but I also have this chat list with a placeholder here which is just this stuff over here and this is a server component so that I can bring in the stuff from the database and just load it into the sidebar here so I'm going to import DB from DB and I just need a list of the chats so import chats from schema SL chat and then yeah when this loads I'm going to say say const chat equals a wait DB select uh I guess we could just select the ID in this case I have a naming Collision here I actually like naming my collections simply so instead of chats I'm going to Alias this as chats table that's just a personal preference so I'm selecting from the chats table chats is what I get back from the database chats table is the table that exists there so we're going to select just the chats table ID from chats table and we're going to get all of them so then I should be able to Loop over these so chats. map chat and I'm going to use a link for this to easily be able to link to the chat page from next link perfect so put that in here key is chat ID I'm actually going to change in a moment I want to make sure that I put class name truncate here so it doesn't grow wildly long if I had a chat name actually I would want to use this the chat name wouldn't I right here I don't have any good names I'm just using the ID as the name but at some point maybe I'll change that and then I'd want it to display the nice name so I guess I should actually import the name from chats table too okay so we get the ID and the name it is nice using a type safe orm like drizzle because I would have just put that in and not caught the fact that I completely missed that if it wasn't checking my types like it knows that this is going to be an array of objects that contain ID and name only that is a super nice feature I like that there is type safety at the layer that is closest to my database for this hrf part I'm not going to navigate to a new page cuz that makes refreshing a little bit more difficult instead what I'm going to do is make this a search pram so we're just going to say chat equals and have that chat ID there but right now if I go back to the page yeah I can see there are those chat IDs and if I were to click on one it should give me chat equals the ID up there so that's really awesome this is working okay I'm going to go back into my app and go go to page and since this is kind of a long running task this chat list stuff it could take a while to connect to the database I am going to create a Skeleton version of this so export function chat list skeleton and I'll return actually let's use the app for this create a Skeleton version of this for loading using Tailwind let's see if it can do that okay decent amount of codee but let's try it out I'm down so return that Skeleton version then back in my page component I'm grabbing the chat list I'm also going to grab the chat list skeleton and we're going to use suspense here so we'll suspend fullback is the skeleton wrap that up okay and then just to test this out let's make this take a while so await new promise resolve should make this take 10 seconds okay so let's refresh this page that I mean it's not exactly right but whatever it's good enough okay so I'm going to take out this fake weit go back to page and you can see here that I'm already expecting the chat prams I actually called it chat ID here instead I'm going to change that to chat so if a chat is passed in here chat equals whatever the ID is I'm going to be able to get it in the page component so if there is a chat ID I want to load that chat immediately and pass it into my child component where it can display that since this child is a client component I will need to wrap this up so that I can do the database call in the server wrapper that I'm going to create here so I've got chat content uh I'm going to create a new one called uh I guess chat content wrapper the only reason this exists is so that I can suspend on this so in here we're going to take out this and the import so in the wrapper we're going to import that export default async function chat content wrapper return I guess I'll need to grab the create chat from here and this is going to accept the chat ID as a string perfect and then it will do the loading back in this component if there is a chat ID so I'll say if there's a chat ID then we are going to load okay yes we will suspend suspense fullback sure I'm going to change that fullback in a moment we'll put in the chat content wrapper and pass in the chat ID perfect okay close suspense cool if there's no chat ID then we're just going as normal so if there's nothing to load from the database just load that page immediately but if we are loading those chats from the database we're going to wrap this in suspense and we're going to grab all the chats that exist so in the wrapper here I need all that database stuff so import DB from DB import chat import and actually I've just just realized I don't need the chats because all I want to do here is if a chat exists I just want to get the most recent message that was sent from GPT and just display that block of code here since this app is only really meant to display code blocks returned from GPT so this is going to be a simple query where I can say const message equals wait DB do select star from messages table where EQ I need that again from drizzle where the messages table. chat ID is the PIN chat ID we are going to order by created at descending so that we can get the most recent one and this is going to be using the disk function and I also want the end function in here because I don't just want it for this chat I also want to make sure sure that the messages table. roll is the assistant because that's the important message that I'm going to want to display and one more bracket and then since this is SQL light we use. getet and now I need to pass this message to my client component and the client component is using this assistant response to display everything that GPT returns uh as this code block and in this markdown component so really what I need to do is be able to pass in in the text so that it can be loaded the first time this page loads so I think what I'm going to do here is say initial assistant response and that can be an optional string that I will default to an empty string and then I'll put that in here there we go so if there is an initial assistant response from the server then that will just get loaded in and everything else should work as normal so if I go back here and I load a chat it's not doing anything let's see if there's anything in the server nothing oh I wrote this as prams but this should be search prams so now oh no I messed up everything this is prams not search prams because I actually set this up to work with the slash so I'm a bit of an idiot there let's go back to the chat list this should have always been a slash because everything after the slash will be the chat ID and then back in page this is chat ID and the same here okay so I had it set up correctly the first time okay so now if I click on one of these it will go SL there we go all right so this is now at least going to the correct page but it's not loading the content so that's a little weird let's just make sure this is coming in chat ID so if we go let's refresh go to the server yeah there's the chat ID so if there is a chat ID perfect it goes here it passes in the chat ID I didn't pass in any message here okay so the this is going to be initial assistant response is the message. content okay that's the thing that comes back from gbt okay so I think hopefully all right sick okay so we're loading this one perfect it loads the most recent message if I click on this one loads the most recent okay this is working exactly perfect okay that is awesome and then if there isn't a chat response so if I delete that and hit enter write a function okay so it does that gives me a function and what it should do it created a new chat here so what I really want is to see the new chat show up on the side here if I refresh the page I can see that appear over here but I want it to be a media I want to be able to see that so what I'm going to do in my chat list where I'm getting all those chats I'm going to use unsafe cash as cach let's see am I going to get this right from next slash it's not unsafe it's unstable unstable cache as cache and we can take a look at the next docs to see how this works so we can bring in unstable cache allows us to cach the result of expensive operations like database queries the really cool thing about this function though is that we can pass in tags an array of tags that can be used to control cash invalidation so basically when we make a query it will cache that query but when we want to invalidate the cache and rerun that query we can say invalidate the cache using a specific tag so let's go back and create a new function this is going to be get chats I guess yeah that's fine this is going to be equals a sync now here I'm going to put in the function that we were calling before then the second parameter you can see here is the key parts so this needs to be a globally unique value and then we're also going to pass in tags in the options so this unique value get chats for chat list maybe is good actually don't know what naming is good and then tags is going to be I'm going to use the same thing so now I can invalidate this query using the tag and then I can get the chats by calling a wait get chats okay there we go so now in my server action over here after I have inserted a chat into the database I am going to import revalidate tag from next cache perfect and I can revalidate the get chats database call here revalidating here will cause the chat list to rerun this database call to select the chats when it's been invalidated so let's try this again uh write a function in C++ and immediately you see that it adds the new chat to the bar over here and we get that output over here and then if I write uh another thing convert this to c we don't get a new chat because this exists for the current chat but notice that this comment is here because it doesn't know that we already have messages in this chat although I'm just adding to this chat in the database I keep adding new messages GPT doesn't know about these previous messages so to get this feature working I have to go back into my route here and when I come in when I make that post request I'm first making sure that a chat exists then I'm sending off that request to open aai and then I'm storing new messages into the database but now before sending anything off to open aai we should query the database for all the messages so database select star from messages where the messages equals the chat ID and then we'll get all of those so const all uh DB messages equals wait that and this should be an array yeah of all of those awesome and let's actually order this by created at in ascending order and then if we actually only select the RO and the content should be all we need so then we can pass it in here we can just spread that so now we have the initial system messages that we sent to GPT then all the messages from the database and then the message that has just come in in the post request uh and then we save the new messages at the end so the next time we submit a message we're sending all of them together to GPT so now let's start this over from scratch let's say write a function in C++ then convert that to python convert that to JavaScript sick so now it's actually able to keep track of all that context and what I found for this specific application is you don't even need to load in all of the database messages I found that if you keep track of the user messages the ones that I'm sending up to GPT and you just keep track of the very last thing that the assistant responded with you still get the same results so instead of having to load in a ton of information every single time you really just need the little prompts from the user and then the last big response from GPT but you can play around with this as much as you want and see what works best for your application and because this is now all stored in a database you using this unique unguessable URL we can share that link with other people and they'll be able to see the exact same thing and the next thing to add to this would be user authentication so if I'm the currently logged in user that created this I'd be able to add to this and generate more chats but if I share this with someone that isn't logged in I probably wouldn't show this Bottom bar I wouldn't show this sidebar they would just be able to see the thing that I shared with them so that is it for this video but in my next video I will implement user or on this application and get all of those features working so make sure you subscribe and don't miss that video did I get everything that was like an hour just for the database what the

Channel: Sam Meech-Ward

Views: 7,835

Keywords: auth js, bun js, drizzle, drizzle orm, fydeos, git fetch vs git pull, javascript, next 14, next js, next.js, turso, turso db

Id: 4ZhtoOFKFP8

Length: 38min 8sec (2288 seconds)

Published: Fri Dec 01 2023