SvelteKit 1.0 with SQLite Tutorial

hello and welcome to this quick and small tutorial where I want to show how we can use the weld kit and combine it with sqlite to make powerful small self-contained database applications so I used this sample database called Chinook and it has basically tracks albums and artists at least that's what I used in this app and you can download it from here and um we build a small swedkit app with it so the result is this here so we have a base page where we have some tracks and we can we can actually search here and um we also got a link to an album so we will be redirected to a small Details page and we can also update information here so this album name is now changed so if you're interested in finding out how it's done please check this tutorial out and have fun okay our first step is to create a new felt kit project I've opened the swellkit documentation you can visit yourself on kidsvelty.dev and under creating project we get this command here npm creates filter at latest my app and this actually creates a new spell kit project so let's do it I'm here in a folder where I want it to be paste pasted command and I'm changing the name to sveld sveld Kit sqlite music app and it's gonna ask ask some questions for example um which template we're going to use the first one is a demo app with a few pages already created I'm not going to use that instead I'm going to use a skeleton project because we want to add our own roads and we start with yeah the minimum or the minimal setup so I'm going to press enter on this one and I'm going to use typescript and I'm going to use Eastland and prettier for code lending and code formatting we don't need tests for this demo so I'm going to choose no for playwright and also know for v-test so we created our project what we're going to do now is we can open this with Visual Studio code so svelt kit sqlite music app and we get a new window and the first thing we're gonna do here is we gonna do an npm install and that's gonna take some time so I'll be back when it's done okay it's installed now and um to see if it works we can just enter the npm run devs to start or swell kit server in Dev mode and it's going to give us this URL with this part I already opened it and yeah we can see we are on a sample starting page okay great so let's move on with um setting up the database connection to our sqlite file um first off we need to actually place the database file in here so I'm going to create a folder called Data and I'm gonna copy this Chinook file into this data directory and um with this file we can then query the data and to access this file we actually need to install some sqlite Handler for node.js and I'm going to use um better Escalade 3. there are other ones you can try out other ones but I like to use this one and notice that I switched to yarn because I had a problem with my Mac I'll make that installed some different versions for ES build if you have the same problem you may want to check out this solution here but notice I'm gonna solve the yarn now and you can basically do the same with npm so yarn edge better as collect three install that and we also need the types of this package add types slash better escalate 3. great now we are set up so we can start integrating the database so let's again start the dev server yandef or npm run Dev whatever you're using and create it starting so um we need to set up our database connection we do that install kit not anywhere here in the routes folder where we Define our application or our front end we have a dedicated lib folder we can use so let's create it lib and in there we can do all the shared stuff that's not front ends related or you can also put some components in here basically anything that's not directly sitting in a route so um let's go ahead here we can add a subfolder server so stuff in here is only accessible for the server um the back end so not the front end that's what we want and we can also create a DB folder in here in there I'm going to create an index file index.ts where we then can go ahead and start using or setting up the database so going to import database from Battery SQL 3 great and I'm going to initiate a database with new database I'm gonna put in the path so um path is slash data slash chin Chinook dot DB and we can also a log everything to the console so we can see if it works this should be turned off in production but I'm just using it for now another thing is we can so we hard coded this path here but if we want to later um deploy to production maybe we don't want to put it in the same path in here maybe in different path we can do that simply by making it configurable in an EnV file so I'm going to create a DOT end file and in here I'm gonna um put a variable with DB path and we can change that then in the deployment in the production environment so instead of hard coding it also here we need to change it and for that soil there's also a nice way to import EnV variables so I'm gonna import DB path great thing it's also typed so we can import it but it doesn't actually um bring the cnv stuff I don't know if it's needed but I used it all the time so let's add this in the part before it and then we're gonna use that variable in the new database part okay great so we have already set up our database connection thing is we need to query some data actually so um to do that we can I'm gonna open the database overlay so this is a beekeeper Studio it's a free tool where I can handle a lot of databases I recommend you to check it out and we're gonna on the first page on the main page we're going to show some tracks we have so let's first start with Select Staff from tracks and we get everything we know everything that the table holds here so um we want to show artist info album info for each track so let's uh actually write a better query so um I'm going to start with the fields we only want so track ID um I also always Alias The Columns so we can it's like in the format we want to use in JavaScript so camel case is this I guess so um in JavaScript you can just handle it easier or like we used to and next one name is Trick name create and um I think we also want to join the albums so it has the album ID under the table album so we can join albums a um on T dot album ID equals a DOT album ID and then we can also put the a album ID a room ID um and also the name title as platform title so I'm gonna add a few more um columns to the statement and fast forward this so see you soon so the query is done now we get more info and uh yeah track album title artist name and genre and I think what we also want to do is um limit how many rows we get because we don't um yeah I think there are thousands of tracks in this this database and it may grow so you don't want to load every track for each user so let's Maybe limit it and only show the first 450 and um so yeah the user doesn't need to load too much data great so we have our query now and I'm going to copy it and we can actually do a function in our database files to query this data export function get initial tracks I call this function and and there I'm gonna at first off put the SQL statement so this is our query and with that we can make a statement DB prepare SQL to get the rows we can do statement dot all and which one draws great so one thing is that we currently are not really using typescript so we return anything so let's add a type for it so I'm going to create a types file for the database and I already created it I'm just going to paste it so we export type track and here are all the fields all the columns specified with that type and in this file we can then say as track need to import that type and this returns this trick and another thing is maybe we want to make this limit a configurable so let's actually add a variable to it so let's say dollar limit and pass it as an argument limit and default it to 50. and then we can actually in this statement that all section pass the variable so it will get replaced in the actual query so yeah our function to query the data is now set so we now want to actually show this data on this page so let's go ahead and uh in this is the main file in the routes the main road page that's filter this is a markup this filter markup for this file and we want to show the data in here so what we want to do is to load the data on the server and just output it to the page for that we're going to create a new file here and it's page Dot server.ts and in This Server file um it the stuff gets executed on the server to enrich the page with some data so in this we have a load function load and um in there um we actually have to say satisfied satisfies page load page server load um so uh knows that this function needs to run before um the page is loaded actually and we can just get the tracks data tracks is equals to get initial oh get initial tracks thank you imported and we then return our data cracks so that's all we needed to do here we just said to spell it okay to render this page we need to load data on the server that's why it's called server TS can also quickly look at the documentation so um here's the section loading data and uses also explained here you can also create a page.ears in the page server.js where you can decide okay this this first one loads on the client if you fetch in public API you don't need to do this on server but because our database connection is from the server or on the server we need to actually do it on the server the page loading so check out the docs if you want to know more um and let's go back so now we've actually loaded the data we need to implement it into the page so for that we're going to add a script here script language TS and [Music] um we can then import a type from sweat that's called page data and it's cool that um it's very automatically types what we export here so we can import that and we get the data from this data variable so let's make a small pre And Then There are a code and we could display all the data but we actually here we return this object we actually only want the tracks in here so we can say dot tracks and it actually you can see here it knows its type so let's go back to the to the file to the page and it's already reloaded and we can see we see the Json of our query data exactly 50 rows so this works fine great so let's make this page look a little bit more beautiful and actually have some layout that's not Json and for that I'm gonna because I don't want to spend too much time on it I'm going to use Bulma Bulma is a small CSS framework and um it's very opinionated so it has default styles for almost everything and helps make um everything makes uses a lot simpler so to do that I'm quickly just gonna input or add a style sheet from a CDN so here cdnjs deliver and I'm just going to Google polymer the file and if we read it out we can already see it looks a little bit different here and so let's change the content here first off this is the tracks page so H1 tracks and let's delete this section here and make the heading actually look big so classes is size one that's one of the classes Bulma has to make text big and let's also get rid of this code stuff and add an actual table so in former we can then add the class table to style it and we go ahead make a table header make a table row and we gonna display these for columns track out this Island genre great and for the data we add a tea body and now we're gonna Loop through all the tracks we imported and you can see already typescript does work co-pilot Auto generated some code for me but it got the names mixed up so we can actually fix it with our typing so we first want the track name then we want the artist name then we want the album name no album title and John is right so if I refresh the page we actually get a nice table here and um yeah it's it's maybe a little bit stretched to the left we want some padding so let's wrap this in a div um yes and in the div add class it's PX4 so it's a little there's a little space to the left okay this looks good enough for me and the next step is to create a page for the albums so if we have a link here we can click on the album and maybe get a small overview on this album so let's jump back into the code and see um what we can do so on this component here we have our table and here's our album type of title so let's actually create an edge area so we can link to the album and the link will be let's say we add a sub route album and then we actually add an ID so let's make it dynamic string template so slash and then we're gonna oops pass the track dot album ID as parameter so we will just link to a noun a route endpoint so if we go back to it yeah we will get an error because we actually haven't defined this route and it sends back a default 404 page and to define the route we create just a new folder in this routes folder and we call it first album so that's this part but this is still not enough we have another slash and another sub route here so this is dynamic because the album ID can be anything so we can do it with the squared brackets and we're gonna give it a name called in this case album ID and here what we can do here is page.s world and just return any album for now so let's go back and we can see here we have any album page and no matter what album we click we get to this page so yeah that's good enough for now but we actually want to show the real data of the io so first off we need to again create some queries and so for the first one we want to get the album data for our album ID and for that I'm going to create a new function export function get album by ID and we're going to pass an album ID of the type number number okay so we again need an SQL statement I prepared something for this so I'm gonna create a select the album ID title and artist information from eye bombs join the artists table and we're gonna do a where on the album ID and again we can create a statement like above to be prepared SQL and we get the Raw not statement at all but statement.get and we pass the album ID so it gets replaced in the query and we don't get any injection um vulnerabilities so return row as album we haven't created that type yet so let's go back to our types and here is our album type so still need to import it and voila I also add another function um we also want to show the tracks for the album for that I also prepared something so we select the check ID track name track milliseconds um again from the album and we order by track ID again another type is needed so let's add that to Bible track and gonna import it here so we now have two functions get ion by D and get album checks and we can go back to our album route and create again a page Dot server.ts this is the same procedure as before we export a load function load equals and it satisfies page server load um what did I do wrong um I think I need to put this also in break it yes great um so we first need to know which I want to fetch so we need the album ID from the path from the URL path so we get that by adding a parameter to this function we get that from sweater called Power arms and we get our album ID JS by getting params.albom ID so because we named it this way it is in our spread brackets um we get it also typed and if you look at the type we see that it's actually a string that makes sense because URLs don't know any types it's just characters um and so we need to do a pass end here so we get it as an integer or number in typescript so um we get our album data I using our function we created get album by ID and we get our tracks with get eyeball tracks and we're gonna return both so great again we need to actually show this data so same procedure as before we create our little script tag here import our page data type and Export the data let so we get the data in this variable also again greatly typed and to show the header we can do now is we can go ahead and make data dot album dot album title and let's add the class so it's actually a nice big header go back and we have big ones checked little pill so we now allowed the correct data can go do a little bit more so let's again add a div with the padding to the sides so let's do this and maybe add a little paragraph that shows the artist so we say bye artist name and again add a small table that shows the tracks soon here also get from in each Loop this iterator so I can show the track number and for the track milliseconds I floor them after divided by a thousand to get seconds so let's see how it looks and we get this beautiful page and I won't buy AC DC and we get a track list okay great so what happens if we now do something here that doesn't exist so we get a internal server error that's pretty bad so what happens here um we actually um go back to the server file so what we do here is we get the album ID we pass the end what I did here is I entered just text so after the power after parsing this as an integer we get nothing because it has no numbers in the text and we still pass that to the um to the function that queries database and we can see here because we Trace our database log um that we query with where album ID is null and of course that doesn't return anything and um that's bad so what we can do to mitigate this error is um we can make a small check here if not album ID then we can actually throw a swelled error so it's an old class so it's lowercase so we import error from swelter and we put our HTTP code 404 if I'm not found and also the text I will not found so we now get this page we get in four four status code and some info um okay so what happens if we put an album here that is doesn't exist in the database but is still a number we get again an internal server error that's also not what we want so again we query the database and we actually use the correct number but database doesn't find anything so let's maybe just check here if there is no album returned from this again for error album not found great so um what we can do additionally is we can customize how this error page looks so we can do this also per route base so I can go ahead in my folder and create an plus error dot spelled page and in there we can first import this from apps to us we get some page info in there and then we can do our own markup so I'm going to create a H1 oopsie whoopsie and put in their page.status that's the number the status number and Page error message we can then show what message we have put in there so basically these two infos and if we go through a page we see now okay it's our custom page with this oopsie whoopsie header so we can Define um specific error Pages 12's page okay so let's go back to an album that actually exists and okay the next thing we're going to do is find out how we can maybe update our database table and change the name of our album so to do this we start off by again going to our database file and in there adding a new function that actually does the update so um again I prepared something create this function update album title and I'm gonna pass album ID and the title the new one we have this simple SQL statement that updates the column and we just run it and yeah to call it we must go to our page and just add a little section that actually executes the statement or that allows us to execute it so we're gonna add a little section here so a small H2 update album name and in there we're gonna put a form so this one gonna say it's gonna post use post as a method you could also use get what I like to use form I like to use post and in there we're gonna go put an input and um kind of give it the class input so it's dialed gonna give it a tab text names album title we default the value from our data so from the same we put up here in the title so we put the div the album title in there already and let's also give it a Max width [Music] um marks birth of 50 characters yeah and we also need a button so class this button and this primary and type is submits we want to submit the form and we call it update so let's take a quick look and yeah this looks great and works so svelder helps us doing this in a good way so we can actually give this action a specific name so we can then handle this action I'm going to give it a form action name start off with a question and then a slash because we we want to stay in the same path so it's just a query parameter and the URL not just a subpath and we call it update album title so when we submit this form we're gonna get the form data so this album title but we still also want the ID so we which now which one we can update so I'm gonna add a input with type hidden for the album ID we don't see it on the page but it gets also submitted so we can just get these two values the title and the ID and with that we can introduce a SQL statement we created so let's take again a look and yeah from this point nothing looks changed so now we have to add this form action here to our server TS and for this we gonna create a new or export a new variable it's called const actions and it gets a type actions from swellkit and in here we just put our name we defined for the button and we get a request in there and this function then executes or update so let's get the data we submit in the form we can wait request dot form data this is of the type form data and to actually get the information from there we can um use the get function and from it we also call to string but this is actually a we are we actually want this again pass as an integer so I just go it again so if this is not empty so we say I will ID string and reposit otherwise we put it to null and the album title is a string so we can just go do the tostring part data get album title and then to string then we can do a quick check so if not album ID and and title so we need both data um to actually do our update statement neither can be empty or at least we don't want to allow the user to update an album to an empty name so we can say boom ID or I won't title missing and in the end if we pass through that section here we can call our database function so again we created this form with the method post we put our two inputs in there the two values we need for our statement we put a button in here type is submit and we add this form action attribute with the question mark slash and then the name of the um of the um H we want to call so what I could also do is um duplicate it and make a delete action delete album and then in here put another method here delete album but for this example that's enough so I'm going to remove it again and let's see if it works so I go back to our page we are on the album for the rock we set it to you and I'm just gonna add ABC on the end I press update and you can see it's already reloaded quickly and if we go back we actually load the main page you can see here the name is updated and we can change it back again if we look at the code um on the on the logs from the database we actually see here update album set title where I'm ID equals one and to finish off this tutorial I want to show you how we can make a search for this tracks page that listens to changes and um yeah basically if when you type it filters the data shown here and so before that we need to create this filter sveld kit API so let's do this first up so we want to filter the tracks so let's make a quick database function for it so search track and add search term string um as a type as a parameter and for that we need to add a where Clause so where T name like a search term but that's not enough we also also want to lowercase both both the terms and um we want to search anywhere in the text so we had present signs so it says basically allow anything after or before um the search term yeah and so this is our function and we need to still pass the search term so let's quickly add a search Inbox here and for that we need to go to the basic page so this attracts section here and for that corner add some margin below and here I'm gonna work small input search so we get that little X to uh to delete our phrase place order is search and classes input so it's dialed also had some margin below that Style let's do Max with of 80 characters and let's create a value that stores it um search term and let's say value equals search and now let's have a look at the result okay now we need to create the API that actually handles the request where we want to fetch or searched um search tracks from the database so we for this we can create a svelte API and that's basically a server function that's not sitting inside a page that's sitting alone so for that we're going to create a subfolder in our routes called API in there create another folder called search track that's our API endpoint here we don't make a DOT page.7 yes but just a DOT server.ps because again there's no page in this endpoint it's just the single endpoint and yeah what we do here is um we fetch the data so to do that we're going to export s and this must be named like the HTTP function we want to provide so I think our use case get suits us because we want to basically just query or get the tracks behind the search term so let's do that and we get our URL in here and reinvent this you say here satisfies request Handler so um I think it's it's the best to to pass the search term as a UL parameter so we can um uh get from the UIL search programs get search term um so uh yeah we basically get from the search parameters the named one called search term and again we can stringify this um want to return tracks so we type them of our database type track and initiated empty array so when we found find nothing we want to return an empty array instead of an error because we can show that there are no results um so if there isn't a search term passed for example if the search is cleared after um after the search was done we could want to get the initial tracks that's the same that are currently loaded on initial page load and otherwise if we have a search term we can then load or function did I name it search tricks let's find out you know how did I name it search track I did I did export it all right so let's check yeah I think it's called it should be called be called search checks because it returns an array foreign so now we get our tracks and maybe if this doesn't return anything because it doesn't find anything let's default it with an empty array and we can then return and call this vertical Json function and just path the tracks so now we have an API endpoint we can call it with Slash search check and then um pass the URL parameter with search term I guess we should also rename this search tracks would make sense I guess so let's go back to our main page and what we can do here now we gonna add a little um event handler basically on key up so right after we type we can make a handle search function so this handle search function it see if an event search event and [Music] we basically want to get to the Target of this event and we type it as a input element so we have value from it and we can then set our search term to target.value because we type the fast so we don't want to actually trigger millions of requests here while we type we're gonna de-balance it a little bit so let's create a small timeout here so timer let's node.js timeout first if we enter this function we're going to clear the timeout so if there's still one running we cancel it and reset a new one new uh set timeout and reconna put this these two rows in there and let's say we debounce in let's say 300 milliseconds so now we need to make our actual request to the API so let's fetch tracks make a function for it and we can that then just use fetch from the browser it's a we can basically do a rest request here so let's go to slash API then we go to search tricks and we pass our search term as a parameter and then we want to yeah pipe that results to the Json function and then we get the data we can call the log it first so we now need to call this function so let's head back we have our search here and let's open the Daft worlds console and let's put child and here you can see nothing you can see still doesn't work we don't get any child results why is that and do we actually pass um the search term here with password child so let's head back to our get function search term maybe we can lock it search term search term again child uh systems undefined why is that why is it I missed an arrow I guess thanks co-pilot I guess it was you so let's find out if this does not work we actually get an error it's not better search strikes is not a function I actually didn't save this file okay let's try again child yeah this works payload preview we get only 20 one results and I think every there there's always a child in there so that's good this indeed works so we still need to update the table here and to do that we need to actually uh go to the page and So currently we go directly on data.trex but let's make a new variable contracts and [Music] on page load we said let tracks equals data or tracks but here where we currently lock it which instead go tracks equals to data so here we we just get a flat array so we don't need to do data.tracks so we update the variable where the each Loop is based upon so again we go to tracks enter child and we update here this table oops that's not what I wanted and I can cancel this all right it actually doesn't uh the x button doesn't doesn't um this is not a key up so that should be handled as well but for now this this search works great so I guess this is the end of the tutorial I hope you learned enough to build your own sweat kit apps if you have any feedback any suggestions please let me know in the comments and thanks for for viewing this have a nice day

Channel: hartenfellerdev

Views: 8,415

Keywords: svelte, sveltekit, sqlite, webdev, web, sveltejs

Id: iO4VUbQ6ua4

Length: 53min 21sec (3201 seconds)

Published: Fri Jan 06 2023