SQLite: Local Database | Swift 4, Xcode 9

hello everybody my name is Kylie aka kilo loco from kilo loco comm and today we're going to be going over SQLite local databases and well sequel lighter C SQL Lite or whatever you want to call it that's what we're going to be going over I'm going to be using Xcode 9 swift 4 I'm going to be using this framework called SQLite dot Swift on github and then the the skill level I set to intermediate only because I'm trying to make these videos a little bit shorter so the UI is going to be already done for you or well it started going to be done when we head over to the project and all you really need to know to move forward and follow this tutorial is now how UI alert controllers work you don't need them for SQLite but it's just how I implemented them throughout the tutorial so yeah I'd like to give a shout out to Akshay Ambedkar hopefully I've said your name ok or at least close and I they are the person they are the ones that want it to see SQLite along with a couple other people but yeah they were the first one to mention it so let's get into it alright so let's close this up I'm going to head over to the project and as you're going to notice this is all we got going for us right now so we just have 5 buttons if you want to go ahead and take a look at the storyboard it's just 5 buttons you know keeping it real nice and simple nothing's pinned as you can see they are hooked up everything's hooked up so we have the create table this one's hooked up create tapped right insert table this is the part where I said that you're going to probably need to know how UI alert controller works because all we're going to do is when we insert a user it's going to be done through the alert controller so we're just going to type in a name like hello or like Bob and then we're going to type in an email like Bob at aol.com and then we'll submit it so right now as you can see all it's doing is just printing those names nothing's happening nothing's being persisted same thing with list users you know connected updated update users and insert users pretty much the same exact code right pretty much the same exact code just changing things up a little bit so this is also connect I'll let you see that we're going to be putting in a user ID like five and then we'll say what we're going to update their email to like sale was Bob so now it's going to be Billy at a I am calm you guys probably won't ever know what a Ola I am are for the youngsters and then we'll have delete which is also going to be all alert and we're going to type in a number once again like five and then we'll delete that user so I hope that makes sense I hope it's not too complicated but that cuts down on like at least 15 minutes of unnecessary stuff so let's get into where we actually start you know getting SQL our x-cubed light into our project so we're going to do is head over to your browser head over to github and you're just going to type in SQLite dot Swift should be the first one make sure you check if you aren't sure which one it is it's one with a bunch of stars go ahead and click on that now this one is currently you know ready for Swift three so if you want to do Swift for like me and there's no changes in the actual code that they to implement it but if you want to use Swift for and you're using Xcode 9 if Xcode 9 beta is still going then what you're going to want to do is you're going to want to go down and go into these manual instructions which is what we're about to do right now so what it says it's pretty much we have to go over to our branches we have to go to the swift for branch mm-hmm and then what we do is we download this bad boy so download that zip do do doop do to download it alright so I'm going to just pull this over here somebody just got shot and I'm just joking I don't know what that was it's still kind of close to 4th of July so that's probably what it was alright so what we want to do clear that out now that we have the actual thing we're going to want to open it up and you're going to go to the SQLite Xcode project and it gives you a description on how to do everything or like a picture shows you how to do everything right here BAM pretty simple pretty straightforward so let's go back to our Xcode and since it's all open and ready what we'll do is we'll just go ahead and click drag and we're going to drop it over here I'll just put it underneath the products and here it is all right so that's not it we also have to go over to your actual build of your project file right you're going to scroll down all the way to the bottom where it says linked frameworks and bomb libraries click the little plus you're going to hit the platform that you want it for make sure it's under workspace we're going to be working with iOS today so I'm going to use the iOS SQLite go ahead and add that now we go up to embedded binaries we're going to do a plus on that and what we want to do is we want to make sure we get the SQLite framework the iOS framework and make sure this is under products so go ahead and click that and you add and you'll notice that you have two right here one right there and that's all you need to do to get it into your project so let's go over to the view controller and what we'll do is we'll import SQLite and what we want to do is we want to build just to see if this works because if it doesn't come be kind of sad that doesn't mean anything anything just yet we have to wait for it to actually build see what Xcode decides to do with it and see if that works see how it's building and that that error went away all right so it looks like we have successful successfully got into our project this will probably go away now what we can do is we can start implementing it now the way that we want to start implementing it is we actually have to create ups we have to create a location for you to store all this information we have to create a location for you to store your data basing your tables so what we're going to do is we're going to use file manager and we're going to use that so that we can choose a location inside the app on the user's device that the database can be stored to so let's go ahead and get that going all right so we'll do let document directory equal file manager dot default dot docx er no dot a URL and you're going to want to choose let's see this one so we're going to want to do for file manager search path directory in and then appropriate for and create so go ahead and click on this one it will autocomplete this one we're going to just do dot document directory in dot user domain mask appropriate for we're just going to do nil we don't need to specify any special URLs or anything like that and then create true just in case there isn't a file which there isn't it'll create that file for us so if I'm correct this one should throw so what we're going to do is we're going to wrap it in a do try catch block whatever you call those things and we'll just print the error if there are any and what we'll do is we'll command option and then square bracket left you know drop it in there and now we can try it right so now we have a document directory now we need an actual crew actually pre eight we need to create a file URL for the for this for our databases pretty much so let's go ahead and do that by doing let file URL you I like to do like this camel case everything and we'll do document directory human directory dot let's see let's see we want to append components right and we're just going to call this we're going to do database for users so this will be our users table so we'll call it users and then dot add all right now append I'm sorry append with extension and the extension I'm going to do sqlite3 and the only reason I'm using SQLite three is because that's what it suggests in the end in Swift RSQ light dot Swift documentation they specify the connection to sqlite3 file so that's imma just follow their conventions you can go you can feel free and experiment with a different you know different extensions but this is what I'm going to use because that's what the documentation says alright so now what we want to do is we have to do just like they did in the documentation we want to try to make a connection and create a database so let database equal try connection and we'll we'll actually initialize this connection with a path and we'll use our file URL path so file URL dot path and let me let me close out this left window because I don't think we're going to be going back and forth very much and it gives you guys a better view of the screen in my personal opinion all right so now we have this database as long as everything went well we have this database so what we're going to do is we want to have access to this database throughout the code so let's go ahead and make a global property we'll call it database and this will be of type connection and now we can set this database to our global database right there we go all right so now what we're going to so all we did right here was we create a file and we're creating it with this file name and this extension and that's going to be stored locally on the user's device now whenever now we're creating with the connection we're trading in a database that's going to be saving everything to that file so that's why we have the connection right here to the database and then we were just storing this database right here so that we can access it in all of these different functions so right now there's not anything too much that we can really test so let's keep going we're going to go into the create table and for the create table let me just pull up my notes okay so for the create table what we have to do is we're going to grab our database and we're going to all this all this stuff in SQLite swift is all like wrapped in throes so everything that you do is going to be like a do catch so just keep that in mind so what we're going to do is we have to create our we have to specify how we're going to create our table so we'll do let create table this is going to be kind of like a like in faction and this is going to equal self dot users oh well hold on we don't have a user's property yet okay so that's another thing that we need to go over real quick so we have a database but right now we don't have any of our columns right so we need to actually have like the properties of our columns and we have to write the we have to type those out so let's go ahead and make those right now so we have actually these will all be less I believe so we're going to have a user's table in our database right so we'll call it user's table and this is going to be equal to guess what yeah table and the way that you initialize table is with a name so users write pretty simple pretty straightforward next we want to specify what properties that we will we actually want to have in our table like what columns we want to have in our table so obviously we need an ID right so we'll do that and this is expression of type int and then we're going to initialize wait wait how did I do this let me double check how I did it because the formatting is a little bit weird and and then we want to say ID okay yeah so what we're doing is uh the columns for SQLite are specified through expressions and with an expression you have to state what kind or what type the expression is so since our ID for each of the rows is going to be a number we're going to do it through an integer if you're going to have like thousands upon thousands maybe do like a 64 but that's mainly up to you for this example we're just going to use an integer and we're going to do the other we're going to do the same thing with other properties so what I wanted to do if you notice is I want to have a name and an email and that's what's going to make up a user's row is it's going to be their name their email and their user ID so let's go ahead and add those name is equal to an expression and this is going to be of type string and one little note here is that you can actually make your expressions optional so you can just put in like optional string you could do it like that so that you know maybe you're not always getting a string maybe it's optional value or something right and the expression column title is going to be name right and we'll do the same thing with email expression also going to be a type string and we're going to call this email okay so now we have our table and we have the columns that are in that table so let's go ahead and create our table so let's let's do let's create our task that we're going to be running so let let create table equal cells users table dot we're going to actually do create and we want to do this one create with a block and this is the table builder so let's go ahead and do enter and we'll do enter one more time to complete this closure and we'll just call this table right so now we have to actually add those properties into our table so what we want to do is we want to do table dot column and then we're going to initial we're going to do table column and then we're going to enter in whatever expression or value that we wanted to put in so we're just going to go through each of those each of each of our properties up here call I want to make sure okay it's not Auto completing so we'll just do this and what we're going to do is we're going to say self dot user art we're going to do self dot ID right because we have the ID of the user and then we want to make sure that this is unique so we have to specify that we want it to be unique and that it's going to be our primary key or well well the primary key is a unique key so primary key and we'll just say true is it upper teeth like that now that's lower case there should be lower case so this is pretty much what's going to number our column our number our rows we have to add in the other the other two as well so dot column and we're going to just say self dot name and then for the email that a column what we can do is just to switch it up a little bit so we'll specify self dot email right just like how it is what name but what we can do is we can specify that it is unique you don't have to do this but you can it has the option to do it so you can specify that it is unique and you can say that it's true right because the name might not be unique this should work yeah the name might not be unique right there might be two kyle's but there should never be two users with the same email because then that's probably the same user right so that's that's one extra thing there's a lot of different ways that you can go about customizing your column you get limits and all the all these other things that you normally would do with your with your databases but you know we're not going to get too deep into that I just wanted to kind of show you some of the things that are possible all right let's continue so now like I said everything's wrapped in like dude do try blocks so we'll catch and we'll print error alright so what we're going to try to do is we're going to try to create this table so all we have to do is we have to do our database run and then we say whatever tests that we want to run so let's try that try self dot database run and we're going to say create table and if that's successful now we can just print out created table now this won't really show too much when I run it right here but if it gets to the created table point then we know that we were able to successfully run this create table task through our database and that we were able to create the table now we won't really be able to see anything until we actually start listing or actually inserting users into this into our table so create a table so we have created the table now if we try to create it again you'll get an error and it will notify you that there already is a user's table um you know in this file now if you wanted to change files which you could do up here if you wanted to change files and you kid you know create a whole nother table maybe you want to have like a dev environment and then a production environment and you can do things like that but yeah if if the table already exists it's not going to let you make another one so this piece for creating the table is pretty much already done that's all you really need to do to create a table alright so for the part where we actually want to insert a user now this part is just as easy all you have to do is uh once again create a task and you just run that task so let's go ahead and do that so let insert insert user equal once again self dot users table dot insert and we're going to actually we're going to actually specify the I don't see the one that I want I think it's just values where you're going to insert the values and the way that we're going to do is we have to take each of these that are non optional and set them so let's go ahead and do that so since I is the primary key it will Auto increment and we don't need to insert that but for the other ones we do have to so we'll do name and email now the way that we do that is also keep in mind that these are expressions these aren't actual values so like if I were to set if I was trying to set the name - Kyle setting self name equal the Kyle that doesn't work like that because it's an expression not a string and you'll see that right here so the way that we do that is we have to specify self dot name and then they have their own operator that returns it's like return but to the left and you just put in whatever the value that you want to pass so name and the name that that this is is actual string which I'm grabbing it from right here so if you look at this alert controller all I'm doing is adding two text fields grabbing the the name I'm saying is whatever the first text fields which will be this one the name whatever the text is it is in there is going to be the name and we're going to pass it in right here now we just do the same thing for the email self email remember this is an expression expression of type string so it only accepts strings and then we're going to put that we're going to put the value of email in there so now when we insert user that these these expressions in the table will be populated with these values name and email so now all we have to really do is just run that task so once again do catch print the error if there is any whoops and we want to try we want to do self dot database dot run and we're going to run an insert it doesn't matter it even if I were to select delete since these are since this run this run method is overloaded it doesn't really matter which one we select but go ahead and select query just you know because that's the right one query insert that is and we'll will run this task insert user insert user now if that was successful we'll say inserted user alright but right now we're not actually we're not actually seen what what's in the database so we're inserting something but we can't even see what the database looks like so let's go ahead and do our list users first before we move any further just so that we can see everything that we put in so once again just go ahead and we're going to kind of follow the same process except we don't need to add a task we just need to prepare our database to show us this information so let's just wrap this in a do block once again catch and print that error all right so now we have to we have to prepare our we have to prepare our database so we have to get the database we have to get all the the rows out of the database right so what we're going to do is we're going to put all those rows into like an array it's going to end up being a sequence actually it's going to be a sequence of users right so let's go ahead and grab that real quick so let users equal and we're going to do self dot database dot prepare and now this is like a query we're going to pass in we're preparing the database and we're grabbing the users table so we're going to pass in the users table so self dot users table I don't really know why I'm using self so much it's just there's so many closures just randomly spread so that's probably why Oh make sure you mark this with try so now if I were to print the users we're not going to print the users but just so you can see any sequence which means that it's a type and then it's just a bunch of rows okay so now what we want to do is we want to go into a for for each loop just so that we can print out each user in that in that sequence so for user in users since it's a sequence we can loop through it we'll just print user on a print wrap everything on a string we'll say user ID is and we pass in our expression so user I know we'll pass in user right this user but we have to we have to access the property through our expressions so you can subscript this user by doing it like this and we'll just say self dot user or self dot ID we're going to get their names and we're going to do we're going to access it the same way so user subscript self dot name and then we're going to get the email once again same thing user subscript self dot email okay so now if we're successful in preparing our database and grabbing those users what we'll do is we'll go through this for each loop and it will print out each of our users that we've inserted into the database so first we have the inserts um and then we'll actually be able to see see what we have in there so let's go ahead and do that I hope everything is making sense so far so once again if you try to create the table you cannot create it it already exists if we insert a user remember my UI set up to take a name and an email so name will be Kyle email will be Kyle at Kela local comm and we're going to submit that all right inserted the user okay so so far so good right I'm going to clear out all this all this junk in here and then next we're just going to list the users so so far we only have this one user let's go ahead and add in a couple more users so we'll say James and we'll say ah Jimmy boy at gmail.com and then we'll say Stacy and we'll say Stacy super sexy and she knows it so sexy Stacy at hotmail.com alright so now let's go ahead and clear this out as you can see we were able to prop it successfully insert all those and as you can see all of our information is being stored so that's really good we're setting up our database now well I mean you're pretty much good from here unless you need to actually update the user or delete and I'm just going to go through those real quick it's pretty much the same process but if this is all you really needed then you're good to go but for those of those of you that want to know how to update things then let's get into that too let's get into that too alright so same thing update is kind of like the same thing that we did over here for insert so we just want to create our the the task that we're going to run so let's do let update user right and just like we did up here we're going to access our user table and we're going to run update instead of that so user self dot user table dot update now one thing that we can't do one thing that we have to do before we do this is we have to actually specify which user we're going to be updating and what value we're going to be updating our yeah what value we're going to be updating now we're going to be updating the email but before we do that we want to actually get the user now in here I have the user ID and of course this is going to be a string now remember that our user ideas of type integer so this user ID string won't be too much help for us right here so what I'm going to do is instead of user ID string I'm going to actually create that into a integer so in user ID I'm going to just put it in between the guard statement so that it's going to automatically unwrap I forgot to do my let's so let me do that user ID is equal to integer convert it into an integer user ID string and now we have a user ID that we can access but that's not all that we need to do oh don't forget the comma that's not all that we need to do we actually need to go to the database and we actually have to make sure that we get the right user because we need to we need to have access to that user so that we can specify we want that specific user to be updated so let's go ahead and do that we'll do in order to do this what we can do there's a there's a filter all actually I don't I don't think I did this right let me comment this out for right now so let's say we want to get the user so let user equals self dot users table dot filter and notice that there's a predicate in here so what we want to do is we want this one and we're going to pass in a predicate so we want to just say self Oh self dot ID because we want to grab them by the ID is equal to our user ID and then if that's true then return that user back to us and you'll notice that it's the user from the table all right so now that we have that user I actually kind of did this a little bit wrong something I think I'm going to just delete this out because the update I forgot was a little bit different now we want to just go straight into our do catch block and once again we'll just you know print our error our air barrier there error alrighty cool and to update what we want to do is we want to do our database self dot database dot run and then you know I could do it like this actually actually let's keep the same format I think I can run it like that so let me do let update user equal the same way that we did it up here and we'll just say update right so we'll do we want to grab this but we have this specified user right so we're going to use that user user dot update and the values that we want to update right now we're only going to just do the email so we follow the same convened convention with the arrows pointing to the left the returned arrows with the left and we'll just say self dot email remember that's the expression and we're passing in the email this e-mail value into that column all right so now what we can do is we can actually run this just like any other task update user and o2 market which fry so now we should be able to update any user that we see from the table so let's go ahead and run that and see if we can update an email for one of these people okay so what I'm going to do is first I'm going to list the users that we have just so that I could see who I want to update and you know what James you know went through puberty and he's feeling pretty sexy so let's update James okay so the user ID that we're going to pass and we're going to say that's the user ID is going to be two so let's go ahead and pass in that to the email that we want to change it to is sexy James at super sexy calm don't go to that website I don't know what's on it so we want to update it we were able to update it successfully and we're going to list the users and look at that sexy Jane and sexy Stacy BAM look at that now you're able to update so last but not least is deleting and the leading is probably like the easiest thing to do so you want to definitely make sure that that you're a little bit careful yeah so what we're going to do is we're going to do the same thing as right here where we grab a specified user by their ID and we're going to delete them so let's go ahead and do that and make sure that you convert it into an integer as well because remember that our user ID is an integer so let user ID equal integer of user ID string there we go so now we have a integer of a user ID and we're just going to do the same thing as before as up here all right so we'll do let user equal self dot database or non self dot user table on that dot filter and same thing self dot ID is equal to user ID and then for the delete action it's really short but we'll follow the same conventions that we've been following so let delete user equal user dot delete simple as that now all we have to do is go back and we can do delete so let's just self dot database dot run and we're going to do delete user and we got to make sure that we print this error and we also have to make sure we mark this with try all right let's go ahead and run that everything should be good but on SQ light is very simple to work with it's a real database as opposed to as opposed to core data whereas core data is just a object graph this is an actual database so let's go ahead and delete you know what since James is right in the middle and I'm not really digging that James can see today I don't like him anymore let's go ahead and pass in that user ID that we want to delete once again it's going to be - I'm going to do submit now we're going to do list users alright so now we only have one in three now if I do an insert and this is um you know Cody and Cody's email is code smell dot @ gmail.com we're going to submit that we're going to list it notice that it does continue it doesn't refill that to position it's just like a normal database it continues moving forward until truncated so yeah that's the basics of SQLite I hope that was easy to understand and yeah if you have any questions feel free to reach out to me there's a lot of different things that you do with SQLite Swift it's a great wrapper around the you know the SQLite a/c library and I feel like it makes it extremely easy you would definitely want to use this in a project where the you know the programmer slash user has control over all the data as opposed to you know just creating object relational Maps or graphs where that's where core data shines but this is more we're just storing just straight-up information that's where SQLite shines so I hope you liked this tutorial make sure you give it a thumbs up and if you have any suggestions for future tutorials feel free to leave them in the comments I do check those make sure you check out all the links in the description as well alright guys once again my name is Kyle Lee aka kilo loco from kilo loco calm and as always code passionately

Channel: Kilo Loco

Views: 77,275

Keywords: swift 4 save data, swift 4 persistence, swift 4 core data, ios core data, swift saving to the device, swift database, swift sqlite, swift 4 sqlite, swift 4 local databse, how to use sqlite swift 4, sqlite.swift, grdb sqlite swift 4, how to make a local databse swift 4, how to save to database swift 4, swift 4 sqlite framework, ios sqlite, ios sqlite.swift, swift 4 database create, swift 4 database read, swift 4 database update, swift 4 database delete, swift 4 crud

Id: c4wLS9py1rU

Length: 43min 36sec (2616 seconds)

Published: Sun Jul 16 2017