Connect RUST and SQLITE #rust #programming #coding #database #rustlang #sqlite

hey guys in today's video we're going to be discussing how to connect rust with SQL light now there are many instances in life where you don't want to build a full-blown production app and you don't want to let's say use a database like Cassandra or mongod or postgress and you want to build something quick you want to prototype you're just testing out an idea testing out new framework something like that just want to build something very quick uh in those situations sqlite is perfect because it's not as Scrappy or as basic as just using a vector to store data right and it's not as uh elaborate as something like a postgress or mongod DV so if you want something quick and dirty SQ light is your friend and you I use it quite a lot whenever I'm testing things out and um this is why today's video is about SQL light uh so we're going to be using sqlx uh as a crate uh right so we'll talk a little bit more about it and this implements something called as Rus light which basically means you don't really have to install R uh SQL light on your PC so uh now that's a big blessing because imagine able to you you're able to use a database without installing anything right so let's say you're on you're sshing sshing sorry sshing into a remote server uh on AWS and you don't want to install anything there uh quickly just check you're building something with rust you quickly want to you know add rql SQL light check something out that's it right you can be very quick and dirty with this so uh before before I start the coding uh of this uh for this project what I want to tell you is that this video today's video is going to be part of the 50 rust project series now if you're new to this channel you probably don't about don't know about this uh this is a series where I've organized the projects in the increasing level of difficulty today's video is a beginner level video there's no uh you don't require a lot of knowledge of rust to to uh complete today's video so this is going to be towards the beginning because all the easy projects are in the beginning the difficult projects are in the end so this is the series that I'm building make sure you check out more projects in this series all right so uh and also feel free to share with your friends because you know there's so much awesome free content on this channel why wouldn't why wouldn't you share it with your friends right anyways uh this is also a something I want to talk about which is uh this is the Discord Community for my YouTube channel you might want to check it out the link is in my uh YouTube so when you go to my YouTube uh profile you'll see the link to this we discuss a lot of stuff about AI about I Shar a lot of Articles research papers and and that kind of stuff and then uh if you're a goang developer and in 2024 you want to uh upscale yourself this is the perfect project I've built this course it's there also the link is also there on my YouTube so might want to check it out anyways now that we have all of that out of the way let's get started so in my terminal what I want to say is Cargo new rust SQL light and YT for YouTube and I will now CD into it and open it up in my code editor okay so this is my SRC and I get a cargo. tomal file here now in the cargo. tomal file is where I have to now add my dependencies so the first dependency that I want is sqlx so I'll go ahead and add it and I'm going to be using async standard and you'll see very soon why we'll talk about it in a minute and because we working with async we'll also need Futures okay now async uh and Futures in Rust Works in a very similar way to how it works in JavaScript uh if you don't know about it don't worry I'll anyways cover line by line This is a beginner level project in beginner level projects I cover line by line and in advanced projects I uh skip a lot of details because I expect that you know rust okay so here the way we start with it is we say use standard result result now result is to handle the result of the operations that may fail the second one that we have is sqlx which is what the whole video today is all about and we get SQL light which is SQL light query result and SQL light and then SQL light pool migrate migrate datab base okay so these are all imported from sqlx and we we need this crate obviously work to work with the SQL light database and now what we'll do is we will um create two functions one is the async function create schema and our main function so now because I'm using I I have async STD and my uh dependencies so I can mark the uh the main function as the entry point so I'll say async STD main all right um the create schema function is going to be responsible for creating database tables and enabling foreign key constraints so everything will happen here and from our main function we'll just call this function okay now the create schema function is going to take a DB URL as the input so let's do that let's actually uh assume that we'll be accepting we'll be getting the DB URL and what you returning back is the result so you see here we we had uh the SQL query result and that's the one that we'll be getting back from here so SQL light query result and I want access to error also because I might want to return an error so so now it's clear that we we imported this for a reason right we have used it now we've also imported this for a reason so what we want to do is we want to create a pool so let's say a database pool with the help of SQL light pool and with the help of the pool is when we is what we connect with the DB URL okay so we know that we'll be connected to the database like this just this line helps us to connect with the database and we'll do a we'll create a query and then obviously we'll run that query but before that what we'll do is we'll actually create our DB URL which is SQL light SQL light. DV and what will happen is in the root directory of this folder itself uh of this project itself you'll see that SQL light. EB uh file will be created that will be our database file and to view that file we'll use a website to actually view that file so here what we're seeing is we'll say SQL light if the database exists by passing the DB URL so we're checking if this particular database this one which is now in the DV URL if it exists so if it doesn't exist we will create it so we'll say SQL light create database right so when we use an exclamation mark we basically are saying that if the database doesn't exist then we then we have to create it if it does exist then we'll uh call the create schema function with the dvore URL and here uh again we'll have to pass dvore URL do do unwrap okay if everything goes well you'll say database created successfully otherwise we'll print out the error okay so everything is extremely clear up until now we'll just quickly go over what we've done we've imported result we've imported sqlx uh result from the standard library and sqlx we have we have imported all of these things from the sqlx uh Library uh sqlite query result because we'll get a specific result for the SQL query result and that's why we we have this and then we also got error from sqlx and then using the SQL light pool we've created a connection to our database and then we create a DL uh variable and this is basically going to create a SQL light. file here in the root of the project itself we checking if the database exists if it doesn't exist we will create the database with the TB URL and if it exists then we'll just um create call the create schema function that we have here okay and then if everything went well we'll say database State successfully if not we'll say um error so we'll now create a variable called instances use SQL light pool and connect with the DB URL and we'll have a query insert into now I will create a database a table sorry and I will insert into that some values okay so so this query I'm going to be um executing here in my main so let's say let's let's say the table is settings okay and settings has a field called description let's let's think of that uh for now let's just assume that for now and I'll insert into that some values and then I will run this query and get some result so the way to run this query is sqlx query and you pass the query here ENT query bind testing do execute sorry that execute instances do arate and finally you'll close off the instance and you'll print out the result right so we've assumed that there is a table in my database my database is called SQL light. and I'm assuming there's a table already uh which is called settings and it has a field called description and I'm adding some values to it and this query that I've created here I'm going to be running that query with the help of sqlx and I'm going to uh get the the result back after running this query with the instance that I had instance I created here with the help of site pool and I'll get the result back here in this variable called result and that's the VAR variable that I'm printing out here in results right and whenever I create these these instances I have to close those instances uh before the end of the program right the same thing we do with uh goang as well so uh what we're supposed to do here create schema is you're supposed to create the table settings and that's how in the main function we'll actually add some values to the table settings so not only table but I'll create uh two table uh not only settings I'll create two tables here one is settings one is called project and I'll say let query is equal to prma forign Keys equal to one and then I'll write a standard SQL query which is create table if not exists and the table name is settings and it has all these fields which is settings _ ID it has description it has created on now mind you this is the same description that we're talking about out here okay then we have created on then we have updated on and we have [Applause] done this is my integer also primary key and it will be not null like it has to has have some value right this will be text and this will be not null so it has to have some kind of description this is day time and this will be default and [Applause] loal time and updated on is also date time default and same uh I'll just copy and paste this and done is julan this will be not null default and zero the other query is create table if not exists table name is Project this has multiple things and finally after I've created this query I'll put a semicolon and at the end also a semicolon this is this is a semicolon for the query inside the query the query that will go to sqlite so that also needs a semicolon this is the semicolon for rust and the result I will get if I use sqlx and query pass the query here that execute m% pool do po do close wait return [Applause] result so here you have uh in the projects you have project _ ID integer so the the reason is like so the thing is we just need that settings table here right but we could change it and have projects here uh change that around what I'm doing is here I'm showing you that this is how you can write a query inside rust uh an SQL light query inside rust which can actually have multiple um SQL queries is also so it's just one query in Rust but it has actually multiple queries inside uh sqlite so you can do that so I'll just quickly show you this and then we'll just try to run the program Auto increment product [Music] name now if you have I'm quite old school so I uh know how SQL works but in case you have difficulty writing SQL there are many AI models now which to text to SQL so you just go to chat gbd or any other model of your choice just uh ask it to create um just give it the text it'll create the SQL for you and feel free to change it here you don't have to follow the exact same one that I'm using and here I'll just exactly copy this comma here and then I have updated on which is exactly the same thing so I'll just copy and paste this here from this table and I have an image directory and an out directory and Status settings few things I'll just copy and paste them for my query that I already have I'll just quickly format okay now the foreign key here as you can see is settings ID which is settings ID here also as well okay so we giving a reference of the settings table this is the table settings and settings ID there as well okay so there these two both both the tables are connected now we can go ahead and run the program and I'm sure there will be errors because of the query uh the spacing and all can lead to some errors uh sometimes so don't don't worry about that what we'll do is we'll um we'll fix them together if there's any error so let's go and to Cargo run and then what we'll do is we'll go through the code again together once this works yeah so as you can see we have some errors and we expecting those not a problem not at all scared of of Errors because this is this is what we do right this is our job but anyways before I fix errors with you I'll just quickly go over the code okay now um the thing is I I never uh edit out these errors right so you might think oh you know why do you have so many errors in such a small amount of code that's because even after 10 or more years of engineering or uh experience like Hands-On coding experience I still get a lot of errors and uh what I always tell people is that you have to be absolutely relaxed and no matter how how much experience you have you will always be humbled you'll always uh you know see that you you make a lot of mistakes and um even small mistakes like syntax mistakes and all those and you can get around those mistakes very easily if you have like the vs code plugins uh there's this very nice vs code plugin that I use but uh just to build memory like let's say if you're very new as an as a rust engineer you you want to build memory you just want to be absolute what you don't want to learn is um just the language you also want to learn the debugging how do you debug and that's how you learn the syntax really well that's how you build muscle memory right so this is a different approach uh that I follow otherwise on other YouTube videos you might see that the the videos are very manicured right they just just explain the code where I try to build things with you with you right uh uh on on YouTube you'll just see people will just show you the code and that's it uh they'll just out all the errors very quickly and the video will look very perfect but I try not to do that because I want you to be very very comfortable getting these errors now before we do we solve all those errors let's just quickly go through the code so uh we have our main function main function we created a DB URL a a variable where we have uh mentioned that this is the URL for our DB and we check if the database doesn't exist if it doesn't exist we create the database uh if it exists then we um yeah we create the datab database and then we call the function which is create schema function and then we uh just yeah we just wait for it and then we um yeah and then we if uh everything goes well we'll just say database created successfully otherwise we'll just panic and get out from the function uh and print the error and then we have our uh we created our instances for the SP pool so we connect to the database URL and we create our own query to insert something into a table uh in the field descriptions and then we get a result by executing that query using that instance we've closed the instance uh printed out the result now in the create schema function that we have called here what you do is you pass in the DB URL expecting a SQL light query result and uh create a pool to connect to the database URL create a nice query uh this can be anything and then you just run the query close the pool return the result okay perfect now because you wanted to handle this particular type of result so you had to get this from sqlite and you had to get the pool from SQ as well now uh now the errors that we have let's go through them so we have one SRC main RS line number two okay and what what does the eror say it says um expected end but found keyword async okay you this shouldn't have been there and saying that you basically expected a semicolon right so now that that issue is gone now and uh the thing is you can be completely stupid when you write rust code because the thing is it tells you this is where you need to add semicolon right so I didn't have to figure it like I didn't have to use my brain I didn't have to figure anything out uh rust is telling me everything right and I I don't know if there are more languages that do this but at least with rust that's the that's the benefit you get you don't have to be expert developer with rust you'll get everything in the at compile time okay so here async function create schema blah blah blah blah unexpected token and I think this is happening because of the issue on the previous line but what I'll do is I'll just quickly check the uh the code anyway yeah looks okay to me uh so I'm going to skip it for now thinking that it was being generated from here itself uh line 35 okay line 35 all right yeah obviously it's saying that there is a semicolon missing and as you know there is a semicolon missing and it's telling you add the semicolon here okay and on line 36 it's saying unexpected token and but I but I still think it's the same issue like this which is being uh created because of the line previous to that and then on line 45 also it's expecting something yeah it's expecting a semicolon and again it'll tell you add the semicolon here exactly right and then on the next line again you see the unexpected token see the same error which you've seen uh two times before on line 36 yeah here it's saying if uh SQL light database exists okay so some of the errors look a bit weird like for example it says main function not found whereas we definitely have a main function uh maybe it's to do with the brackets or or something else but whatever it is the the best way forward is to always after you've fixed some of the issues it's always to run cargo new again sorry what I meant was cargo run again just to be sure the actual issues and uh okay uh car run thread panicked error return from database near is backlace environment V display backl okay so at line 40 it's saying it panicked now let's check out what's happening at line 40 so one thing to note here it says return from database near is is Right syntax error and if you go here here if you if you go through the code uh the problem is with the query because instead of if I have written is right can you see so so rust is aware rust knows that the problem is not with the rust code right everything is fine problem is with the MySQL code and that's why you get a different type of an error not not your usual rust error right so this is what you learn with experience the more experience you again this this is how you learn to distinguish between these errors and now hopefully when we run cargo R it should run so it says okay no such table exists it's not a problem so this SQL light. DB was created uh when I ran the project when when I ran the code earlier here when uh there was an error so it created SQL light. DB incorrectly for me but now when I run it it will say done okay everything is fine so we created we we inserted one last row okay you can write again the same result you'll get now how do you how do you VI view this database right that's another question uh because you you can't just you know um rely on vs code to read this database the contents of the database so there are tools that help you uh view the data okay so there's this tool for example called SQL light viewer and what what it needs is is a uh SQL light file from you so what you're going to do is you're going to take this and copy it here paste it here okay oh it didn't work didn't work I think I'll have to I'll have to like uh wait let me see what I can do here explore because I'm inside the WSL which is inside my uh windows so I'll just copy and paste it here maybe yeah so in the project there's nothing in settings there are two rows it says and there's uh a row called this testing two rows called testing okay so if you notice your code here you were inserting testing right the value testing into description all right so that is it that's how you work with sqlx SQL light with uh rust I hope you found this video very useful and The Logical next step after this right The Logical next step is uh this video that we had I created a video which was uh with SQL light yeah this one so this is the next logical step so you will watch this video here's how to add SQL light to an existing dust project and uh then you'll actually be able to use it in a project also so what happens sometimes is that okay now you know how to connect rust with SQL light you know how to work with that really well but then how do you add it to an existing project so this is where I show it how to add it to an existing project okay so that's a logical Next Step make sure you watch that video all right thank you so much and and it is it's best if you watch all of these videos in this series but uh make sure you watch that specific video okay thank you so much for watching and in the next upcoming videos I'll be showing you how to work with uh mongodb with postgress with my SQL that's what I've planned for for now let's see uh if I'm able to create all these videos or um yeah that's the plan all right thank you so much for watching guys and I'll see you in the next video

Channel: Akhil Sharma

Views: 1,684

Keywords: rust, rust programming language, learn rust, rust tutorial, rust project

Id: 8EBsOZPGZn8

Length: 31min 48sec (1908 seconds)

Published: Fri Feb 09 2024