Rust + SQLite: Complete Tutorial (Schema, CRUD, JSON & Async)

Today we're going to talk about Rust and SQL light. We're going to work on these six examples where we're going to learn the basics, then play a little bit with JSON and JSON B. And then finally, we will be looking at concurrency. And SQLite is one of my go-to SQL embedded database. And the reason why is because it's very embeddable friendly, then is relatively light and fast. And last but not least is very robust and mature as well as very well-maintained. Okay, enough talking. Let's start coding. So first what you do is you go to your cargo and we're going to add our rusculite. So we can use resculite or sequel light. And personally I like a lot resusculite. It's very focused and it's very well done. Make sure you use the latest one. And then that is important. If you do a library do not add bundle because usually you add that when you create a binary that use the SQL light and that will compile the SQL light as part of our binary. So now that we have that, we're following our XP pattern over there. And so we're going to go to our C 01 simple and we're going to do a cargo watch example. The alias C01 simple. And we're in business. Hello world. So far so good. Okay. So the first thing that we're going to do is we're going to create our connection. And for now we're going to use the memory. And that is simpler when you learn because you can recreate a database every time you run. So that is a nice way. Otherwise to open a file it's as simple as doing that. So my DB and usually the extension will be a DB3 that will work as well. So if we do that we're going to import that and that will create the database. So now we have a connection over there and as we can see is because we have our cargo watch is actually created the database over there. So we're going to go back to our in-memory because it's more flexible as we're going to recreate the schema all the time. We press save. that will do the thing and we're going to go there and we're going to remove this file. Okay. So now we have our connection to our SQL light which could be to a file or in this case in memory. Now what we're going to do is we're going to create our schema. So we're going to use the connection and we're going to say execute. So that is when we want to execute a command without getting a result back and we're going to do the create tables the person that is the names of the columns and then we have the types. So far so good. And now there's a little things here that I will recommend to always put in SQLite is a strict mode. And the strict mode here make sure that you always have the same type in the column. So it will follow these types. And we're going to see that later. If we don't put strict then we can have mixed types on the same column which is definitely not something we want. Then the next step is going to be to do an insert. And so here we are doing an insert in the person table which is what we had and we just insert the name which is going to be Jen and the year of birth which is going to be 2000. So we're going to press save and it should compile and no error but again we don't print anything. So now we're going to do the select and for the select we're going to do that which is we select the person ID the name and the year of birth from person for year of birth greater than a name variable SQL variable kind of we do a prepare statement so that is usually I'll always do that regardless and then we do a statement so that will return a statement everything has a lifetime of the connection and then we do the query and that is a way that in SQL light in resuscite we are passing name parameters. So it's kind of an array of topples and then in this case we say this one here which is going to be that one and then the 1900 press save is going to compile but it's not going to print anything. So now we're going to do the print. So we're going to do a while on the row next. So that is returning an iterator of rows and every time we have a row over there which is again all the lifetime is uh to the connection and we're going to add a row to give the second one which is a get one which is going to be that guy and that will give us a name and then just for demo here we're going to see that row implement debug as well. So that's nice. So we're going to press save and now we should see that the name gem and then we have all of the row here with the types. So that is pretty nice. Now let's see what happen if we are trying for example for Jen to put a number but rather to have a number as a number we're going to have it as a string. So still 2000 let's put 2020 for example and we're going to press save and then it works. Boom. So the string doesn't mean that the value has to be of the type of the matching type. It can do some coaling. But what is not possible now is we cannot have something like that. So that wouldn't work and that kaboom. Now if we were going there and remove the strict press save now that will work but now year of birth is text and that would be terrible in an application because now you will have some rows where the column will be numbers and then when there's a bug somewhere it will be text. So that is definitely not what we want. So typically and there might be exception to that but you do want to add the strict which is not super strict it's not posgress strict because again if you put a string here it will do some coaling but at least it makes sure that there's only one type all the time and that is the type integral so that is very nice now one thing I've done as well and this is because for me because since I work a lot with SQLite sometime I want to introspect what day is in SQLite so I've done a crate which is that one and we're going to put it in a dev dependency because it's not for production but is basically pretty SQL light that will allow us now here to have a print table. So again it's not for production but it's super useful. We're going to give the connection and we're going to say print person. Then that's it. Make sure we import it. Press save and then boom we have a nice print of the tables. So usually I use that and you can do print rows and print everything. So it's pretty nice and it will allow us to debug a little bit better. So that is what we're going to use from now on. We're going to keep this in this file and have that as well. Okay. So now what we're going to do is clear this one up and we're going to create a new file which is going to be our C02 join and that is going to be our base file for now. So still our open memory. Now what we could do is repeat this create schema again and again. But the reason why I like the XP kind of pattern with the examples and making that a lib is that we can actually go to our lib. I'm going to follow my pattern and say this modules and we're going to say pub mode db muts and this is going to be a single file for now. It can grow into a multiple file later. I'm going to do that type alias. So that will allow the other modules will be able to use that the result of the crate if it was a rare one. Now it's not it's kind of a little hack here but at least this guy won't know about it. So this way I can use a create result and then make the create result better over time. So that is a little best practice that I have. Now what we're going to do is have a function like that which is going to be create schema and then our connection. And because we have a join here and we want to show the join. We need two tables. So here's what we're going to create. First one is we're going to create a table or if it's not exist and that will have the ID and the name again that is just to allow us to do the join. Now what we're going to do as well is and that obviously that wouldn't work in production but this is just for experimental. We're going to delete the content such as every time we run it we delete it and we start from scratch. Eventually we might have something here that say you know delete true false or whatever but that would be good enough for now and then after we create our person table which is this one which now has the org ID over there. So that will allow us to have the relationship and then we are also deleting the user over there. So that is a little bit the strategy right now. We're making sure we import that and that should work nicely. So now that we have that we can come back to our join over there and we're going to create our schema. Press save. Making sure that we import that. And then we're going to do a CWE. C is our two join. And that should print nothing, but it shouldn't complain. So far so good. So now what we're going to do is we're going to go down and what we want is to insert our data. And so in this case, what we're going to do now is we're going to do an insert into or the name the value. And we want to get back the ID. And this is where we can use returning ID. Make sure that you do not use the old way in SQLite to get the last ID set because that will create a lot of issues if you have concurrencies and so on. So now since a while back SQLite has implemented the returning ID which is the right way. So in this case what we do is we prepare the statement we say returning ID and so now we know that when we're going to do a query row this is a special call we're going to give our name. So that is going to be uh what we put there. That's the way to do it. And that this query row if we go to the definition it takes a function over there. Yes. So the function f which has a row. So like that if there's only one row back we can process it into whatever we want and that is what we do. We do a row get. So that will be row and then we do a row get and then we get that and that will get the first one which is going to be the ID. So that is very important here to not use the old way of getting ids. So that this way we have the row ID of this one and then we're just going to create some persons or people and that will be Jen, Mike, Paul and Pierre that are all going to be born in 2000. And the way here just to show a little bit how John works is I'm doing a modulo two here. It could be anything. It doesn't matter. And then we either have the org ID equal to that org ID or to none. So this way here we are just enumerating across these guys. And then we are setting it the or ID here. So again when we created the database if we go back there we can actually close this files probably. We see that org ID we didn't say was not new. So it's okay to be new. So this is why in this case I just wanted to make sure we show the example of when we set new to it. Now we save that should work fine. And then finally we are going to do our select join. So for clity I put the join like that. So we have the select here. We do the person and then we do the org name as org name and then we do a person and then we just do a inner join and a work clause. So far so good. And then we are going to execute our query. So we're going to prepare the statement execute it and that will return the rows which will be an iterator. And the thing which is important to note here is that these rows every time we do that not everything is loaded in memory. So is as we create that it will do whatever is smart to fetch whatever I don't know what is the buffer but if you have a query that has a million kind of things you don't download the million things. So this is why it's important to understand that the rows is connected to the connection over there because it keep fetching as we iterate. And then this is where we have our cool print rows of our pretty SQL light. And we're going to go the rows. Again, this is not for production, but it's super nice in this case. Press save. And then boom, we have those two because the other one we're new. So in this case, we ask a specific or ID. And so everything is working nicely. Okay, so that will conclude our join. That is pretty cool. Now let's go and create our third example. And that will be our C3 values for the dynamic values. So, we're going to close those ones. Don't need them. We're going to clean that. And we're going to do a cargo watch execute three values. So far so good. No print. There's nothing. So, we create our connection memory. We create our schema with our DB utils. Create schema. And now we're going to see our users. So, we're going to have Jane, Mike, and Paul and Pierre back. Everybody has no org ID. So I could have omitted it there as well but I just put it there anyway. And then we have the 2000. In this case we don't need the or okay and then what we can do is we can actually do a print table. We're going to give our connection and then table will be person. This way we have our table here which is Jen Mike volunteer. Now what we want is we want to have kind of a dynamic update query. So we want to have an update but we want it to be dynamic meaning that the values won't be fixed like that because right now we have been working with those but what if wanted something a little bit more flexible where we build the query dynamically in a way. So for example let's say that we have a list of value for one user yeah one row of column name and the value and the value is going to be of the rusculite value. So this is what we have. We want to update a user with the org ID 1 2 3. So obviously it wouldn't work. But right now the F key is not there. So that would be fine. And then we want to update the name as well with new name 111. That is just an example. It's kind of a silly one. But that is to show the kind of data that we have in input. And obviously you can have different structure but this is just to show. So we're going to first extract the columns name and the values because that is the way that SQL work. SQL work with when you update or insert event you split the two. So we have a vector of string and a vector of value and then we are taking our name value list could have been a hashmap it doesn't matter into it and unzip that is what it does the unzip it split it into two guys. Then once we have that we want to build our columns for our update because we want to have a SQL update. So for that what we're going to do is we're going to do shadow. We are going to shadow the column. That's fine. And that will be one string. And we're going to iterate through each column. And then we're going to format it. We're going to put it under code equal question mark. And then the colon column name. And then we join everything with semicolon. So this is by hand. Sometime you might use a SQL builder for it or whatever, but I'm just showing here some examples. And then we're going to build our SQL. So our SQL will be something like that where we're going to say let SQL. And then we have that. So we're going to have update person. And now that these guys are well formatted SQL friendly, we're going to do the set column like that. And then here's a trick for the values. What we want is we want to have a vect of den to SQL. So the way it works is we know that we have values over there. So we're going to iterate in our values. So that will be a reference of value. But then we want it as a den of SQL. That is a way that the later the execute is going to expect. So this way we're collecting into a vector of din objects. So that is what we have. Now we're going to build the wear clause and this will be basically our let SQL. So we're going to shadow it. Format our SQL of before which is the beginning space where id equal question mark. That will give us that. So then after we have our let person id and that will be a value integer of one. So here we are cheating a little bit because we know that you know when we create is always start of 1 2 3. So we're taking the one otherwise we could have done the returning ID on top. And now the cool thing is because we have the values over there which is kind of dynamic. We're going to do values push and then we do the reference of person ID and the typing magic is going to work for us. We need to make sure we import this guy and that would work. Yeah. So here there's some magic. If we were going to pass the things like that that would have complained because he would have said I'm expecting this guy. You are giving me this guy. So we have to do that to do a vector of this. But in this case he knows what to expect and so he does the right coaling. So that's good. So now we're going to execute it with these two command where we do the connection execute. So when we execute we don't get the row back we get the number of rows that we have updated. Yeah similar to posgress and so on. So I'm giving the SQL and I'm giving the values here and I need to do that because I need to der and ref and all these kind of things and then I'm just print the number of row updated which would be one. Now press save and boom what we have is we have our user one with org ID 1 2 3 and the name new name 1 one. That is pretty cool. So this is kind of a long way in a way, but that is a beginning of a SQL builder if you wanted to build one by hand. Now you can use query or this kind of SQL builder if you want. I'm not a big fan of OM. So usually I like to have a smaller layer kind of a query builder way, but feel free to work the way it works for you. So that was pretty cool. And that shows us how to do the dynamic value query. So now our fourth example is going to be with JSON. So, first we're going to go into our crates into our cargo. I'm going to add set JSON. We're going to stop this one for now. And then we're going to create our C04 JSON. So, we're starting from scratch where we have our connection memory and we create our schema. So far so good. Now, if we look at our DB utils, we're going to see that for person, we have a data T and a data B for text and blob. So text will be for our JSON the normal way, the old way in a way and we will use data_b the blob for the JSON B. So for this one we're going to use data T. So that is going to be our goal. So we're going to do our CWE C04 JSON should compile nicely. Print nothing. So far so good. And now we're going to do the first one. And we are going to insert two user which is going to be Jen and Mike. But now we're going to add this number here which is going to be our zip code and it's going to make more sense later. And that will be the ids of these two users. So this way we're going to collect them the right way. And then we're going to have our for loop where we're going to iterate over that data set. And now I'm going to create the statement which is going to be that which is going to be insert person. And we are going to have the name, the year of birth, and the data t, which is going to be the string version of that. And we're going to see later. And here we have the same technique as before where we have this returning ID. So I go back there. So now I can do something like that where I'm going to do the query row. I'm putting the name. I'm going to say 2000. And I have the data JSON, which is this one. And I'm saying to string. So I want the string version of it. And then I get back the ID. And I'm going to do id push person ID. So like this we keep track of everything. So that should work pretty well. And actually if we do a print table, we're going to give our connection and person. Make sure we import it. Press save. And that will give us like that. So we can see that it's just a string at this point. There's some function that we could call over there that will kind of validate that is a JSON and so on. But so far since we have the JSON type over there, we're pretty safe on on this way. So now what we want is we actually want to use a JSON API of SQLite to update add or update properties within the JSON. So that is the goal of the JSON support in a way. It's not only to store string in it because otherwise we don't need JSON support. So for that first we are going to get the person one ID and then we're going to make this query. So we're updating person and we're setting the data. And here is a trick is SQLite has a whole set of JSON function a little bit like posgress and then we can say I want a JSON set. So that would set a property and so we give the column name. So it would be data t is the same one and then that is the language which is the same actually as posgress I think which is a dollar sign and then the dot way of getting into your property and then I put the value over there and then the homeowner I put a value over there and so in this case I'm going to put the person one ID which is this one yeah and so I can do also question mark with the index of where it is and it start at one yes lua and sequence are very old things and they like to start index at one. And then we have this reference number which is going to be two. So that would be the address code over there. So everybody would be at um this zip code. It doesn't really matter. And then we take true and we put it as a string. But here is we do not want the home owner as a string true. We want it as the boolean JSON true. That is a string. But we are wrapping it with a JSON function to make sure that we get a JSON value true. And now if we press save, we should see gen with a zip code of 94222 and homeowner true. That works pretty nicely. So when you look at the SQL light documentation, there's quite a bit of function here with JSON set and array and all these kind of things. So it's pretty complete and it's very well done. Now, one things where JSON is very powerful as well is to select. So, here's how it works. So, let's say we're going to comment this one out actually. And now we're going to do this one. So, now what we want is we want to select all person that are in a home owner true. So, the way it works is this way. We select whatever we want to select. We make sure that we select the type here where we have our JSON and then we can do a wear clause. And here's a trick is we do a JSON extract. And they have another kind of notation as well which is kind of compatible with posgress but right now we're going to use a function way. So we can do a JSON extract then again data t and then we're giving the path and then here we're going to give a name argument. So that would be a prepare statement again and the way that we're going to do it is we're going to do the prepare statement query. We're going to say that the home owner is going to be the reference of true. So here's the trick is when you use JSON extract the type over there that has to be the type of SQL light which is what is happening when we're doing that that is a rust type and the restite is changing it into a SQL light type that's why it works now if we press save should work often we get that we get only gen because only gen that is the things that we updated has a hormonal true that is pretty cool now the thing to understand is when we're working with text as JSON So those are the JSON extract and so on. What is happening is SQLite get all of the text out pass it look at the value and then match it and so on. And the same thing for update and everything JSON set it will get the value out set the value and get everything back in with the JSON B is much more granular. So there's some big value of using JSON B over the JSON text. And now obviously we can have more complex kind of select. So for example here we're going to say people that do not own a home. So we're going to select everything for example from person and where either the homeowner is new so hasn't been defined or the homeowner is zero and again here the zero looks weird but I wanted to defer the things in SQLite there's no type boolean so when rescuite change true to a SQL light type is actually change true to an integer type which is either zero or one when you have a true type now here in this case I wanted to show how we can hardcode the value over there. Yeah, because now we know that it's false. So we can hardcode that for whatever reason. And so in this case we can say zero. So obviously here if we would put true I mean this doesn't even exist. It will return everything. Yeah, because even the one that doesn't have the value. So what we want is to have the zero here. That will do that. And if we do a one, it will return probably every everybody. That is the way it works. So now we have that. Okay. So now that will conclude the text JSON and now we can go into JSON B. So I copy everything there and it's going to be very similar. So this way we can scan it through very fast. So first we create the database exactly as before and remember we have the underscore T and underscoreB. So now we're going to use obviously the underscoreb. So we have our data and we're going to have our JSON. So that is a sad JSON. So the value here is a S value is not a SQL light value. And then we're going to prepare our statement where we have our name and things. And this time we do a data B. Now in our SQL the query row we are going to give the string but then in the SQL we are going to make sure that we are wrapping it into a JSON B. So this way the SQL line can take this text and do it magic to pass it into a binary format. So that is where the magic happen. And then we push the ID exactly as we did before. So we're going to stop this one actually and do a CWE5 JSON B. And so everything is working nicely. And so for the top here, it's exactly the same except that now we have this JSON B. Now when we're going to make the query, here's the thing. The way it's going to work is actually very much the same except that now rather to put a JSON set, you put a JSON B set. Now the magic here is because the way they store it the JSON in their binary format which is obviously super smart when we do a set is not going to pass the whole beast. So they are going to be relatively optimized and they will be able to go directly into those nodes and that is a big value of JSON B. And again when we do the JSON B set here this is the same thing. We have to make sure that if we're using a string in this case and we wrap it into a JSON something like that. So that will become a JSON type which is what we want inside the JSON function. And then if we go down we can see that now we're going to use another notation to do the same thing that we did before for the select. So in this example I'm showing different ways of doing it. And I'm using that notation which is this guy. I think we can put a space over there. And if we press save hopefully everything works and that mean that this is a colon and this is a way to address. So result to have the function that we had on top which is that we have I still have it there just b extract now we can do as well like that. Now this conflict with my best practice of debug print that is a me problem in a way but this is pretty nice and I think is the same as posgress. So that's pretty nice. That's the way to address still the same. And then there's this kind of difference between when you are using two greater than and one. So when you are using two greater than the types on the right is a database type. So that would be the SQLite type. So for a boolean for example that should be an integer because in SQLite there's no boolean and so that would be the integer zero or one for example. But if you use this guy then the right side needs to be adjacent one. So that would be thing. So that's why this works. Now the JSON B extract like the JSON extract they work with a SQL light primitive. So here we do not give JSON something we give the SQL light and for boolean the SQL light of a boolean is integer in SQLite. Okay. So that is our JSON B very similar to JSON but has a lot of performance benefits. So usually that will be my go-to. Okay. So now what we're going to do is we're going to do our sixth chapter and that will be our C06 as sync and that is how to use SQLite on the connection in Tokyo sync or threading is the same thing. It's basically what is send and sync and so on. So for that we're going to use Tokyo sync but again we could have done the same thing with multi- threading. It would have been the same characteristic in many ways. So, we're going to put that we go back to this file. We're going to make it a sync with Tokyo main. And for that example, we're going to actually save it to a file just for fun. And that will be the underscore. So, like this in my getting ignore, I don't save it by default. The underscore is ignored. And then the DB3. So, press save. We're going to do a cargo watch execute C06 as sync and that should create our underscore my DB DB3. There were leftover file over there. Okay. So now that we have that we're going to have our list of names that we're going to insert and at the bottom we're going to have our final print such as we print the table here person and then over there we're going to do a for name in names and then we're going to do another loop. So it's going to be a little bit silly here, but we're going to do a for i in 1 until 10 9 I guess. And that will be the let name. We're going to format it and we're going to take the name dash i. So like that it will give us a unique name all the time for every run. And then what we want is to do the insert. But we don't want to do the insert within this task because the whole point is to see how to use the connection in a multi-threaded or multitask to see what is sun and what is sync and so on. So what we want is we're going to do a let we don't really care about the result and we're going to have a Tokyo task and here we're not going to do the blocking or whatever. We're just going to do a spawn and we're going to do a nice sync move. I guess that now we could do a nice enclosure but this one would be good for now. And here's a trick. The trick is we cannot use that connection there. And that is for good because SQLite support multi-threaded access but the connection doesn't support multi-threaded. There's a way in SQL light to compile it for it, but it's not recommended and I don't think it's a good way and luckily RPI is very good and they don't compile it this way. But you can create a connection per task and eventually you will use a DB pool or something like that like deadpool and so on. But right now we're going to do it by hand to understand how it works and the limitations. And then we might have another video of how to use database pool and so on. But for now we're going to do a let con and we're going to have that again and then this one air to string that will make it a little bit more flexible for this example. We're obviously going to put the await and then here we're just going to do that. So we're going to execute insert person and with our value here our name that we have there and the value there. Very simple. Press save. Everything should compile nicely. And then we can see that we have all of that which is a nine * two. So two users and then we created clones kind of things. And then in our create database we are resetting all the time the table. So that's why it's stay at 18. If we were going to go there and we were going to say I don't want to delete and press save 36 54 and so on. So that's the way it works. Everything works very nicely. So, so we have two choice either we use a database pool connection which is completely fine into our thread and so the database pool connection will do his magic here to do these kind of things or and is I think is valid as well is we have the connection with another DB kind of things that we manage and it's behind mutx so that might be good as well or if we know that we don't have that many threads and everything is completely fine to create one per thread or per back task. But the point here is that we couldn't do that. This is not a thing. If we were using connection which is this one which is used over there but even if we were not used over there press save obviously we couldn't move it. So then the thing is say well perhaps we could do something like that where we do a let con or clone and then we do a con clone but that is not a thing. Yeah. So that is not that doesn't work. So you which makes sense. Yeah, because you do want to do these kind of things. The pattern is pretty well. That is the way it works. And now we go back down. Make sure to reenable this and then everything is work nicely. So in a future video we're going to see how we can use database pool and make our own wrapper with mutx manually and that really depend of the use case. And that would be it for this video. Hopefully you liked it. Like and subscribes helps a lot. And by the way, I created something pretty cool in R is fully open source. So you have the binaries or you can compile it your way and it does allow you to create agent and to use the procoder to have a very efficient way to do production coding with AI and you can go there and there's quite a bit of videos over there to show how to do production coding with the IPAC and AI. Until next one, happy cutting.

Learn how to use SQLite in your Rust Programming binary or library in just 30 minutes. This full walkthrough covers schema definition, CRUD operations, JSON/JSONB support, and async concurrency using the rusqlite library. GitHub repo: https://github.com/jeremychone-channel/rust-xp-sqlite - 00:00 Intro - 00:26 Simple CRUD - 06:12 Join - 11:46 Dynamic Values - 17:19 JSON - 24:34 JSONB - 28:07 ASYNC GitHub repo: https://github.com/jeremychone-channel/rust-xp-sqlite NOTE: For `cargo watch ..`, do `cargo install cargo-watch`. I sometimes use bacon, but I still like the simplicity of cargo watch. Join this channel for early access to production coding videos and live streams: https://www.youtube.com/channel/UCiT_r1GD7JSftnbViKHcOtQ/join == Jeremy Chone: - Join this channel - https://www.youtube.com/channel/UCiT_r1GD7JSftnbViKHcOtQ/join - Member Only Videos - https://www.youtube.com/playlist?list=UUMOiT_r1GD7JSftnbViKHcOtQ - Rust Production Coding with AI https://www.youtube.com/watch?v=zL1BzPVM8-Y&list=PL7r-PXl6ZPcB2zN0XHsYIDaD5yW8I40AE (using `pro@coder` and `pro@rust10x`) - Check out AIPACK https://aipack.ai (Built with Rust) with the - Video about AIPACK and Production Coding: https://news.aipack.ai/archive - Twitter - https://twitter.com/jeremychone - Discord general-rust - https://discord.gg/W2besKCzjx - Discord genai & aipack - https://discord.gg/6KkpQBKGFS - AIPACK - https://aipack.ai (and then 'aip install jc@coder') - Rust10x - https://rust10x.com - Rust resources for production coding. - Patreon - https://patreon.com/jeremychone - Any help is a big help (for Rust educational content) Rust10x - Coding Resources for Production Coding in Rust. ➜ https://rust10x.com === Other notes: - ScreenBrush for the green lines. (Gromit seems to be the equivalent on Linux) - Sketchapp for some graphics. - Davinci Resolve and Fusion video editing. - VSCode with Google Material icon themes (with some customization)