sqlite is my favorite database (beginner - intermediate) anthony explains #500

hello and welcome to another video in this one we're gonna be talking about sqlite which is I guess my favorite database engine I'm going to talk you through what it is how it works and why you may or may not want to use it because it's not it's not universally applicable but it is pretty nice for a lot of situations I'm also going to show you the basics of getting started with it as well as some quick little tips for the command line uh but anyway let's jump into it okay so what is sqlite sqlite is a very small database engine uh you can basically build it into a single C file it doesn't actually show up that way in the source but they build what's called an amalgamation where they Jam all the C files together so it's really easy for you to include it in your application uh it is a flat file store so it stores all of its data in a single file and it's really easy to embed it and put it into applications I guess I should say it could also store it into an in-memory database for instance if you do sqlite colon memory colon I think it is uh cycle light three right this will be a temporary in-memory database rather than storing it to disk which might be useful if you're doing like I don't know uh like a full text search which is one of the features that an extension of sqlite has you might just create the in-memory database run a full text search on it and then blow away that in memory database or I don't know maybe use this for tests or something um but it works just like any other uh database engine create table I don't know what uh with some value in it uh insert into what values one select star front wet you know it's it's a database engine it has tables you can do you know inserts you can do joins you can do most of the things that you would normally do in a database now some of the types are a little bit wonky I seem to remember a lot of it devolves to Strings and so it's not the most time safe database but it's pretty darn good for most things that you would need a database for so this was in an in-memory database let me do this with an actual database file so I can show you what that looks like create a table what a insert into what values one select start from when okay we have it we have a database here and you'll see that it's created this file here and if we use the file utility it will tell us this is a sqlite 3.x database last written blah blah a bunch of other stuff uh but basically it stores all of its data in this file here you can run commands on it directly using the sqlite uh command line so if you do select star from what directly here you can see we can get values in and out of it and it's it's basically a database engine now there are a few things that I sort of didn't show here that I want to call out explicitly uh because there's a few commands that are really helpful for working with this interactive utility here uh the most important one and probably the one that I run the most often is the schema command this will show you all of the tables and Views and other stuff that's in your table or in your um in your database uh if you want to show headers when you're selecting stuff from things so like you can see here we did select start from wet it only showed A1 here there's a DOT headers on Goose like star from what now you'll see that we show the column name here in our output this can be really useful when debugging but it's disabled by default if you miss any of the commands or don't remember them there's a whole bunch of them here I don't use most of these but you can see them by doing Dot help all of sqlite special commands start with a DOT um there's even directory changing there's all sorts of stuff that you can do with this uh little binary here the other thing is quitting I quit sqlite by doing control d uh uh but you can also quit it by typing dot quit that is also another command there does it also dot exit oh dot exit also works um so those are those are kind of the three ways to quit on of sqlite uh so now I want to talk about what sqlite is good for the first and foremost at least for me is for prototyping things I find sqlite a really good lightweight uh utility to spin up a database like you don't even have to spin up anything because it's it's literally just a flat file uh but you can have a database that's really easy to set up uh really easy to test out an idea maybe you'd want to build a small website and I want to see what a database would look like behind it it's really good for prototyping it's also really good for read-only storage uh we'll talk more about that later but sqlite actually doesn't do that well with multiple writers and it's really good for embedded situations so imagine like I don't know well I don't have my phone on me but imagine like a mobile application where you need to store some data directly on the phone maybe you want to store it in a relational store maybe you need to do joins or some other fancy stuff and rather than inventing your own serialization format for that why not use a database that's really good at these things and really good at joining and selecting that data later and sqlite is really easy to put into a mobile app such that you can select data out there now I want to talk about what it's not good for and the the big one is multiple writers uh sqlites the way it handles concurrency for writing is it locks the entire database so and it doesn't block it doesn't wait for anything it just errors if you try and do multiple rights so for instance if I do I don't know seek 110 x args run five processes uh replace out of string sqlite3 database.db insert into what values whatever our value is here so this will try and insert the values one to ten but it'll do five processes at once and I think some of these should fail yeah so you can see here error and prepare database is locked and if we then uh it's like start from wet we can see that only one of the values successfully got inserted so um yeah basically sqlite locks the database and it's not good in a in a multiple writer situation uh the other thing that seagullit is not great for is distributed systems now there's a little asterisk here because there are some Forks or uh special uses of sqlite and distributed systems where people have you know built a uh protocol such that you know you can have sharded sqlite or you're going to have multiple replicas of sqlite they've basically turned it into a real database engine but with the very simplistic implementation underneath it I've also seen some distributed systems that use SQL Lite as their uh as they're eventually consistent data so basically they write to a central place and then they replicate the entire database file to all of their hosts this can be really useful if you have very few rights but you you know are mostly reading so like say you build an index set of data and you can pop that on a machine and query it really quickly uh the other thing that single light doesn't do all that well for although they do claim that you can make a sqlite database that's 281 terabytes um having a machine that has 281 terabytes of space is maybe another question uh but I find that it's not that great for extremely large databases because it must be in one file and it must be on one machine so this can be a bit of a bit of a problem there uh but lastly I want to talk about why I like sql8 and first and foremost is that it's widely available so you know there's this sqlite command line utility which you're probably not going to use if you're actually developing against this but most programming languages have good bindings for this so for instance python has a sqlite library built in uh so you can directly connect to a flat file store and play around with that directly in Python Ruby has one too probably no libraries there's probably you know most programming languages will have this readily available the other thing I like about sqlite is it's dead simple stupid it has very few features out of the box and so it is a very very basic database engine this makes it really good for teaching database engines but it also you know it limits you a lot so you try and make things as simple as possible rather than leaning on more complicated features that you might get out of like postgres or something else I actually have used sqlite in a few places and I want to show off a few places that it gets used in the python Community because it's so convenient the first of those is coverage and this isn't my stuff but uh coverage is a utility which I've done a video on there's no way I've got to remember to put that in the video description but if I do maybe it will be there um but coverage is implemented in sqlite they store well the current implementation previous versions were not and future versions may not be as well um but coverage stores all of its data inside of a little sqlite database this is really convenient because it makes it really easy to query this and poke around at the um at the actual coverage data or if you need to do special stuff to it I know it's a database didn't type complete for whatever reason uh but if we you know look at schema here you can see that there's information for tracers arcs Etc the comments are even in here uh it's pretty accessible and really easy to understand like how how coverage is built from this and you can manipulate this data one thing that oops the command Line's a little bit fiddly with control C it doesn't quite readline properly here but that's a whole different thing another thing that I have written that uses sqlite is pre-commit uh pretty much you know this is implementation detail so don't super depend on this but uh pigment stores all of its uh hooks as you know built environments inside of this cache directory and you'll notice there's a little db.db file here this is used to keep track of where the actual repositories live on disk as well as where your configurations live on disk it used to be Sim links but it didn't work so well for Windows and so this was a convenient way to represent the same structure also it turns out that if your shebang is too long then things don't work either and so this prevented really really deep deep Long paths so it was another nice little win there um and this is actually used so that you can do frequent GC which will garbage collect any of these any of these repos that are unused so it knows all of the configurations you have on disk and can remove any of the ones that aren't reference another cool thing that I've built with sqlite is uh my twitch chat bot uh it stores some amount of data based on actions that happen in chat and we store these here so for instance there's a there's a way for you to donate bits that forces me to use Vim we have a full text search for my YouTube videos there's a message of the day which people can set there's today where I keep staying about uh what I'm working on and you know keeping these around forever seems kind of cool you can do select star from day and see exactly what I was working on on whatever day so you can see if we scroll back all the way back to the uh you know in 2019 November I was working on GitHub actions for pre-commit way back when um but you know this is kind of a cool little use of of sqlite as well um but anyway that's that's sqlite that's a little intro to it why I like it why you might use it or not hopefully you found this useful if there are additional things you would like me to explain leave a comment below or reach out to me on the various platforms but thank you all for watching and I will see you in the next one

Channel: anthonywritescode

Views: 54,744

Keywords:

Id: jH39c5-y6kg

Length: 12min 25sec (745 seconds)

Published: Fri Nov 25 2022