Flutter SQFlite CRUD Tutorial | SQL Database Storage In Flutter using SQLite

in this video we'll take a look at how to work with the SQL light database within a flutter we're going to be using the sqf FL package to achieve this functionality and sqlite is a selfcontained high reliability embedded SQL engine that you can use to persist data on device for your flutter application to learn how to use the SQL light package we're going to be implementing a very basic to-do list application using flutter where all of the information is going to be persisted within the sqlite database our application will allow us to create to-dos read to-dos update to-dos and delete to-dos from our database so to get started the first thing that we're going to be taking a look at is the actual dependencies that will require to actually build our application and we're going to be only using two packages the first is going to be sqf light which is going to allow us to work with a SQL light database so let's copy this dependency come back to the pope.l file and paste that under the dependency section as a side not links to all of the resources that I mentioned within this video as well as a link to the source code can be found in the description below so feel free to take a look at it if you're confused at any point once this is done the other package that we're going to be using is called path and path is basically going to allow us to perform path manipulation which we're going to be using to basically combine together different file names and paths so that we can have a path to the actual place where we'd like to access our database or save our database too so path is really important for that so I'll copy the dependency for this then minimize the browser window come back to pope.l and paste this as another dependency now that this is done since I had the flutter extension installed it automatically detected the changes within the pubp spec. will fall and automatically ran flutter Pub G if it doesn't run for you automatically what you'll have to do is basically open up a new terminal window then navigate to the folder within which you have your root of your flutter project and then run the command flutter top get and this should pretty much do it for you and get the dependencies and inject them within your project now that this is done the next thing that we're going to be doing is just giving an application a quick test run to make sure that it's working as intended and that it builds appropriately and then I will resume the video Once the application is running on our simulator welcome back everybody so as you can see the application is now running on the simulator and it's presenting to us a blank screen because by default what I've done is created a stateful widget called homepage for which we have a build function that just returns a scaffold and on my main. file I've set the material apps home property to be this home pain widget and that's pretty much all I've done we're going to be implementing all of the functionality from scratch so the first thing that I'd like to do is basically create a class that is going to be responsible for managing our interactions with the database and also managing the connection with the database such as opening the database up and making sure that it's set up properly so to do that what I am going to be doing is within the lip folder I'll create a new folder and I'm going to call this Services then underneath of services I'm going to create a new file and I'll call this database undor serviceart once this is done I am then going to be creating a class within this actual file called database servic so the name of the file as so and then I'll open up the classes definition the first thing that I'd like to do is basically Implement some functionality within the class so I can ensure there only ever exists a single instance of the database service class within the life cycle of our application and that the user isn't able to spin up other instances of this class and the Singleton instance is basically going to be the one that's going to be responsible for then opening the database for us and allowing us to interact with the database so to do this the first thing that I'm going to be doing is basically on the database service class I'm going to be creating a static variable and I'm going to say static final and this is going to be of type database service and then I'm going to call this instance like so and then I'm going to set this equal to database service. instance we could use something like a service locator package to also achieve SE functionality such as get but this is the most barebones way of implementing a Singleton pattern within Dart so that there only ever exist a single instance of our database service once this is done the next thing that I'm going to be doing is basically saying that I'm going to create a Constructor for the database service class that is going to be private so I'm going to do database service doore Constructor like so and that's pretty much all we had to do and then here where I'm setting the instance to database service instance it actually needs to be doore Constructor like so and that's all we have to do so now that this is done the next thing that I'm going to be doing is basically creating an actual function that is basically going to set up our database when the actual database service gets created so for that what I'll do is the following I'll say that there's going to be a function that Returns the future and the future that it returns is going to be of type database this class is going to come to us from the sqlite package once this is done I'm going to say that this function is going to call the get database and it's going to take no parameters and then I'll mark it as async and open up the function body the first thing that we have to do within this actual function is to choose where is the place where our actual database is going to exist a handy function that's going to allow us to get the path for the actual database directory is as follows I'll create a variable called database door path and I'm going to set this equal to a weit and then we can do get database path like so and this is going to provide to us the actual directory where we can store our database as you can see it says on Android it is typically data/ database double for/ databases once this is done I'll construct the complete path for the database so this includes the path that we've currently gotten to the database directory as well as the name for our database so for that we can do final and then I can do database path and then I'm going to set this equal to join this is going to come from the path package so the join function as you can see allows us to join two given paths together to form a single path so the first part of our path is going to be our database directory path and then after that I'm going to append to that or join with that act path the name of our actual database so I'm going to call my database Master db. DB and that's pretty much all we have to do and do save once this is done the next thing that we can do is actually use the sqf light package to Now open up our database so to do that we are going to be doing the following I'll say that we're going to have a variable called the final database it's going to be equal to a weit and then be called to a function called open database as you can see open database expects ACC to path a path to our actual database and it returns to us a future database then what I'm going to do for the path here is just pass our database path and with this done if we just do command save this is not going to work the way we'd like it to work and the reason for that is because for now the database doesn't have a defined schema doesn't have any tables within it so even if we open it we can't use it in any sensible way so what we have to do is basically Define some kind of a parameter on our open database function that is going to allow us to basically Define what's going to happen when the database initially gets created and then we have call functions for other things as well such as on upgrade or downgrade and I'll explain to you what these are but just as a side note a good place to learn a lot about the actual packages capabilities and what the package can and cannot do is the actual pop. Dev page for the sqf light package link to which will be down in the description it'll show you all of the features for this package as well as how to use it efficiently how to perform raw SQL curies as well as how to use the built-in helper methods to maybe ease in performing these actual data retrieval and data management tasks at the bottom i' just like to quickly mention that since now we're going to be creating a table and I'll show you how to do that the actual supported data types that are supported by sqlite out of the box are the following which is integer real which is a do type for numbers so if you want to store for example decimal numbers then you should use real text envelope and this is pretty much all the data types that's SQL supports so when you're saving data to your database you'll have to ensure that any information that you save is from within these four data types bu is not supported so a workaround to that is to use integers and use zero as true and one as false and I'll show you how to do that as well so now with this done what we can do is minimize the window and come back so on the open database function you can see that I have this call back function called oncreate and what oncreate basically allows us to do is Define some actual logic that needs to run when the database gets initially created here we get to us past two things one is the actual database and then one is the version of the database and the version can be used to determine whether the database needs to be upgraded or downgraded if for example you publish a new version of our your application where you might have updated some of the tables or maybe added a completely new table so in that case you'll have to Define for example the on upgrade or downgrade callback functions but they are very Advanced for this tutorial so we'll skip those but we'll take a look at how to work with on create so what we basically want to do is basically have one table within our actual database which we are going to call tasks it's going to have a couple of columns within it which are going to then store information regarding our task such as what the idea of the task is what the actual task is and whether it's done or not so to actually create the table we're going to do db. execute and as you can see the execute function tells us that it executes an SQL cury with no return value then here what I'm going to be doing is passing a multi-line screen to a string that I'm going to break onto multiple lines so for that we are going to use three quotes and then three quotes at the end and in the middle we can actually add a multi-line string and what I'm going to be doing is as follows first thing I'm going to say that I'm going to create a table so create table and this is basically a SQL query that we're writing so here you're seeing SQL syntax and then what is the name for our actual table going to be what I'm going to do is actually create a property on my actual database service class which will actually keep a track of what the name of the table is and the reason for this is because we're going to be using this string at various different places it's better to add add that as a property to our database service so that we can avoid making mistakes and then I'm going to say that we are going to create a table and then the name of the table will be the value of this actual variable called underscore tas's table name and then I'll interpolate that within our string once this is done then what I'm going to be doing is basically defining what are the columns for this table going to be well I require three columns within this table the first is the ID column and that's going to be Auto incrementing and the primary key column for this actual table then we're going to have a column for the content which is going to be string or text I should say and then after that we're going to have a column for the actual status of our task whether it's complete or not so let's create them so to Define that we are going to have a column within this table we have to first Define the name of our column so I'll do the same as I've done before I'll basically create a property on my database servers class called undor tasks ID column name and set it to ID and then I'll copy in the other two ones as well which is the actual column name for the content of the task which is just content and the column name for the status which is just status with this done I can come back here and I can do the following I'm going to say that there's going to be a column which is going to be the ID column as this variable maps to ID and then once I've defined the name the actual data that's going to be stored within this column is going to be integer like so this is going to be the primary column and is going to be the primary key as well for this table so now we've done that and we've defined our column then I'm going to use a comma and move on to the next part now I'd like to create a column that's going to store the actual content for the task so it's the same process we're going to have underscore and then I'm going to do tasks content column name as you can see this actual column will store text and there's a certain condition on this and that is that it can't be null so when we add a new entry or a new to this table the content column can't be null and then finally I want to add the last column which is going to be the actual status for our task so that's going to be task status column name this is going to be an integer column and then I'm going to say that it also be not null and there we go that's pretty much all we had to do with this done we can do command save and that's all we had to do so now the next thing that I'd like to do is basically create an actual getter on the database service class that will give us access to our database well to do that I'll do the following after the Constructor has been defined I will say that I will have a function that returns a future database it's going to be a getter I'm going to say that it's going to be called database and then it'll be marked as async and I'll open it up with this done I will now come to my database service and on that I'm going to create another static variable called Static database and I'm going to say this is going to be optional and then I'm going to doore DP like so then within the getter I will come here and I'll say if theore DP is not null so if it's not null then we just return the database like so and if for some reason it is null then we have to firstly doore DB is equals to and then call our function await get database so that we can correctly open up our database and then take that return value that this function gives us and save that within undor DB and then finally we can do return DP like so and there we go that's pretty much all we had to do and then finally for the get database function I forgot to do one last thing which is that once we have opened our database we need to return the database because that's what the return type for our function is and with this done the actual setup process is now complete and now the cool thing about doing it this way is that if I want to access my database service anywhere else within my application for example the homepage all I'll have to do is the following which is to come to wherever I'd like to access the database and then do database service do instance and I have access to the actual database so now what I'm going to be doing is on theore homepage State I'll create a variable final I'm going to call that this is going to be database service and I'm going to call this underscore database service and set this equal to database service. instance and now within our homepage State using the underscore database service we aable we have access to our database servers so now let's restart our application and make sure that nothing's broken and then we can move on to the next part which is how to actually add information to our database now that we've actually created it so to do that we can come back to our database service and here we can create a function which is going to be responsible for allowing us to add tasks to our database so to do that I'm going to come to the very bottom of this class and say that we're going to have a function that doesn't return anything it's going to be called add task and then I'm going to say that the only parameter that it's going to take in is going to be a string which will be the content for the task that we need to add and then I'll mark this as async and open up the functions body firstly I need access to my database so for that I'll do final TB is equals to and then awade database and this is the getter that we're now using and once we have access to our database then I will do a wait DB Dot and on the actual database object we have a bunch of different functions that we can utilize to interact with it one of them is insert which is going to allow us to insert information within the database we can also use raw insert and what raw insert allows us to do is execute a raw SQL insert query but I like to use insert because it's a little bit more user friendly and easier for us to understand what's going on for the table here this refers to the actual table name so this is going to be our tasks table name and then the value here corresponds to a map which has key value pairs within it and the keys here need to correspond to the actual names of the column that we have defined when we are actually creating our table schema so if I remember correctly the two things that I need to provide are the actual content for the task and then the status for the task the column is for the ID is auto incremented so for that I will Define a map here and within this map I'm going to have two keys one is going to be our column for the content so I'm going to do task content column name and the value for that is going to be set to the content that gets passed to us and then the other is going to be the tasks status column name and then here the value will be zero because by default a task will not be complete so zero refers to false then we do save and that's all we had to do so now with this done we can come back to our homeor page. file and start utilizing the actual database service here but before we can do that we need to create the actual UI elements that are going to display aite dialogue within which we can add some information and then call the function within our database service to actually add data to our actual database so on the scaffold I am going to Define firstly a floating action button property and set that equal to a function called underscore add task button like so and then what I can do is within this class after the build function has been defined create a function returns a widget called underscore add task button and then within this I can say that I'm going to return a floating action button the onpressed call back for now is going to be empty and then the child for this is basically going to be an icon which is going to be icons. add so I'll just copy and paste that in and then do save so now we can see the button appearing once I click on it nothing happens so then within onpressed call back I'm going to call the show dialogue function there we go and then we pass it the context as well as the Builder that basically defines how the actual dialogue is built so for the Builder I'm basically going to say that we ignore the actual parameter that gets passed to us and then we return in alert dialogue like so and for this alert dialogue the last thing that I'm going to be doing is basically saying that the title is going to be a text widget that says add task then save come back click on it and there we go the dialogue is now appearing and if we click outside of it it disappears as well then on the actual alert dialogue I'll set the content property to be a column the column will have some children and the children here the first one is going to be a text field so let me add that and then the one after that is going to be a material button so let me do that as well there we go the onpressed call back for the material function is going to be an empty function for now and then we can do command save and let's see how it looks so looks great one thing I'm going to do is quickly adjust the styling for my column or the layout attributes I should say so the main exercise is going to be main exercise. Min and this should fix the actual sizing issue with our actual dialogue there we go it looks good now so for the text field what I'd like to do is basically first add some decoration property to it which is going to be of type box decaration and on this I'm basically going to say that the border is going to be outline input border like so and do save and then after this the next thing that I am going to be doing is saying that the hint text is going to say subscribe and that's all we had to do and it's giving me an error saying with cons Constructor keyword we can't use it so it needs to be input decoration let's do save dismiss the dialog see it again and there we go now we have this outline border around our actual text field then what I'd like to do is basically add a call back to my text field which is on change which basically provides us the value within the text field every time the user types something into it and then what I'd like to do is actually on my homeor page. state class I'd like to create a property string optional undor task and set that equal to null by default and then within the actual call back function for the text field I'd like to call set State and then say that underscore task is going to be equal to the value that the the user puts within the actual text field and that's all we have to do our text field has now been set up properly then within the material button I am going to do the following I'm going to set the color for it to be the primary color of our color scheme and then the child for this is going to be a text widget with the style of the color of the text being white and now it looks good and we can click on done and nothing happens for now so within the actual onpressed call back for the material button the first thing that I'd like to do is basically take our database service and I'm going to call add task on that and pass the underscore task to it and I'm going to make sure that I add the exclamation mark here then I'm also going to add an if statement to check if the task is equals to null so if it's null or if task dot is equal to an empty string then what I'm going to do is basically just return out of this function because we are not going to be adding an empty task to our actual database if that is not the case then we add the database if that is not the case then do the database we add our task then what I'm going to do is basically called set State and within that I'm going to doore task is equals to null and then after that I am going to dismiss the dialog by doing Navigator pop and pass it the context and there we go that's pretty much all we had to do so now with this done we're ready to test our application out I'll just restart to make sure that nothing's broken then click on the plus button there we go it appears and I can say eat food and then let's see if it works as intended done and it's giving me an error which is that on create must be null if no verion is specified so what we can do is quickly stop running our application come back to our database service then come to the point where it basically says the oncreate function and here for the open database function I can set the version property to be one to begin with and this is so that correct versioning can be performed for the database and that's pretty much all we had to do so now with this done let's do run once again and hopefully this time the error shouldn't appear and the application should work as intended so I'll resume the video Once the application is running on the simulator welcome back everybody so now that the application is running once again let's test the actual application out and see if the actual data gets added to our database So within the dialogue I can say subscribe to my channel and then do done and there we go it works as intended and the data has now been added within the tasks table so now the next thing that I'd like to do logically is once we've created some data let's read data from the database so for that what we can do is again come back to our database service and create a function on it which is going to allow us to retrieve these tasks from the task table within our database so for that I'm going to say that we're going to have a future list of type task that is going to be returned to us this actual task model is something that I've created by myself so what I'm going to do is come to my lip folder and create a new folder here called models and then within this I'm going to create a new file called task. do and you can access all of the source code with the link down in the description below and you can download it and what I'm going to do is basically copy this task class and paste it in it's basically a DOT class which includes some properties within it which basically resemble to what a task representation is within our database then what I can do is just basically close this come back to my database service and import this task model from do do/ model task. do and then I'm going to do the following which is to say that the actual name for the function is going to be get tasks not going to take in any parameters we'll mark it as async and open up the body I will firstly get access to our database so for that it's going to be the same as we've done before final DP is equals to weight database and then to get the data we can do the in which is final data is equals to and then await DB Dot and then we can do the following which is do cury and here what I'm going to be doing is basically passing in the table name which is going to beore tasks table name there we go with this done before we proceed any further let's do print data then we can open up our actual debug console by doing the following and then what I can do is come back to my homeor page. dfall and basically create create the mechanism here by which we can actually interact with this actual function and actually utilize the return that it gives us so to do that I am going to firstly come to the build function and on my build function I am going to say that I am going to have a body property which I'm going to set to a function called underscore tasks list like so and once this is done I can then come to the bottom of my actual class and here I'm going to do the following which is to say that I'm going to return a widget from this function it's going to be called undor task list and then open up the body within which for now I'm going to say that I'm going to return a future Builder which is a actual widget that allows us to consume a future and as you can see we get from the get tasks function a future return to us and then when the future resolves or when it's being resolved we can use the Builder to Define what actually gets returned for the actual widget so the future here is going to beore database service do m and get tasks like so and then the Builder here basically is going to be the following it's a function that takes in a context and a snapshot and for now I can say that we return a container just so that we can test things out let's do all shift F command save come back to our database service see that the get task function is giving us an actual error so we can do list optional for now let's restart our application and let's see what happens so as you can see within the actual console there gets this list printed out which currently contains one map within it which has the ID for task being one the content being subscribed to my channel and then the status being zero so now what I'd like to do is basically take this list of maps and convert that into a list of tasks so that's going to be fairly simple to do the thing that I'm going to be doing is removing this print statement and saying that I'm going to have a variable of type list task and then I'm going to do task is equals to data. map and then for every element that we get I transform that element into a task element and then here I can do e ID e status and then e content there we go and then I can take this iterable and make that into a list and the last thing that we have to do is basically say that the actual ID that we get it's not going to be object optional it's going to be as int for the status it'll be as int as well and then for the content that will be as string and with this done we can now test things out so alt shift F command save and basically say that I'm going to return the tasks there we go and then I can remove the list task optional from here just say that we return list task so with this done we can come back to our homecore page and then we can actually work on our future Builders Builder fun function to now actually show these tasks on the screen so to do that it's going to be fairly simple we're going to be utilizing a list view widget to actually display these tasks I'm going to say that we'll return a list View and since we do not know how many tasks we'll have and we'll have to programmatically build them we're going to use the list view. Builder Constructor the item Builder here is going to take in the context and the index for the actual list tile that it's building or List item I should say and then after this is done I'm going to define the item count here which is going to be how many items within the list we are going to be built which is going to be equal to our snapshot. data and then if it's not null then the length otherwise zero and then within the item Builder I can say that I will return a list tile and once this is done on the list T I'm going to set the title property to be a text widget and then what is the title going to be well this is going to be the content of our task so to get access to the task for this corresponding list tle I will do task task is equals to and then I can do snapshot. data and then add the specific index and then I can import the task model from do do/ model task. Dot and then here I can do task. content Al shift F command save and there we go now we can actually see the task appearing now let's test out whether when we actually add something does that also appear here so for that I can click on the button here and then I can say go to the gym and then do done and there we go as you can see go to the gym is being shown as well so everything's working as intended so now the next thing that I'd like to do is basically implement the functionality of updating information that's within our actual task table so that we can mark it to- do done or not so to do that I am going to do the following firstly on my list T I'm going to find a trailing which is which is going to be a checkbox widget and then for now I'm going to say that the value for it is going to be false and then when it gets changed we'll get a value passed to us but for now the call back function is going to be empty so now we can see these checkbox appearing then what I'll do is come back to my actual trailing widget and here I'm going to Define where we're going to get the value from so to get the value what I'm going to do is basically say that we're going to do task. status is equals to one so if it's equals to one then this will be true so the value will be true otherwise it will be false let's do save and both the tasks to begin with have a status of zero so they are not checked when we click on this check box this all changed function call back will be called with the actual status change that is going to happen so if it's true now the value will be false if it's false the value will be true so I need to update this for the corresponding task within the database so let's come to our database service and let's see how we we can do that so to do this what I'm going to do is come to my database service and say there is a function that doesn't return anything it's called update task status and it takes in an INT which is the ID of the task for which we'd like to update the status for as well as what the new status is going to be mark it as async I'll open up the body then we need to get access to our database so for that just copy this paste it in like so and now we have access to our database and then we're going to do the which is to await dp. update now we can use the raw SQL cues as well by using these actual functions that are raw and then whatever the action is but I like to use the update function because it makes it a bit more user friendly the name for the actual task table is going to beore task table name and then the value here is going to be the value that we'd like to update so here what I'd like to update is the status so I'm going to say that the map will only contain one key which is the status column soore task status column name the value for which is going to be the status that we pass but if I just leave everything like this it's going to go ahead and update the task status for all of the tasks that are within our task table and I don't want that I want this cury to be performed for a specific row on the table so for that what I can do is add the wear Clause so I'm going to do where the ID is equals to and then I need to basically add the ID that gets passed here within this actual string so I can use a question mark here and then I can do where arcs this is going to be a list of arguments that we pass within which I'm going to pass the ID and that's all we have to do to command save so the question mark that is here is going to be replaced with the value of the ID that we're passing here for where arcs so now with this done we've correctly defined how to update the status for a specific task within our table and then we have some filtering parameters here to only apply this actual update to these specific rows so now with this done I can come back to my home _ page. do come to the onchanged function call back and here I'm going to do the following which is that we're going to doore database service dot update the task status the ID of the task is going to be task. ID like so and then the status here is going to be the following which is if the value is equals to true then what we're going to is do one otherwise zero and there we go that's all we have to do and once this is done I will call set state to rerender the actual UI so at this time let's test it out so if I come back and click on this checkbox there we go now a task has been marked as done and if I restart my application you can see that the information is being persisted and now the task is being correctly marked as done I can do the click here and there we go now go to JY has been marked correctly I'll click on these again and they'll unmark them as you can see so everything is working as intended so the last thing that we'd like to do is basically Implement functionality so that we can delete information from our database table as well so for that what I'd like to do is basically Implement a call back function on the list tile called on long press which is basically a function that's going to get called when we long press on one of these list tiles and what I'd like to do within this is basically delete the specific task that I click on from our database so for that again I can come to my database service and here I can create a function that is going to allow me to delete a task so a function doesn't return anything it's called delete task it takes in an ID for the actual task that we would like to delete mark it as asnc open up its body then I like to get access to my database first I'll do final DB is equals to AWA database and then I can do await db. delete and then the table name is going to be tasks table name but if we just run it like this it's going to delete all of the rows that we have within this table I'd like to only perform the operation on specific rows specifically the row with the ID which I'm going to pass we can use the same thing that we done before when updating which is Define the where clause and say where ID is equals to question mark and then the where arcs is a list of arguments and then here I can do ID like so save come back to my homepage and then on the long press IC can doore database service. delete task and task. ID is the ID of the task that we'd like to delete and that's pretty much all we had to do and one last thing the ID will be int there we go so let's do command save come back to our homecore page and on once we've deleted a task call set State again to render the widget tree now let's restart our application make sure that nothing's broken and if I long press on one of them there we go they are deleted now I can add a new task hi for example done I can do subscribe and everything seems to be working the information is being persisted and if I delete something it works the way it's supposed to so with that that's pretty much it for today's tutorial I hope that you learned a thing or two about how to work with seite databases within flutter using the sqf light package as always if you have any question comments or concerns feel feel free to leave them down in the comments below and I'll try my best to answer them for you and as always stay happy stay healthy keep learning keep growing and I'll see you guys in the next video bye-bye

Channel: Hussain Mustafa

Views: 2,704

Keywords: crud operations, flutter database, flutter local database, flutter local db, flutter persistent data, flutter sqflite, flutter sqflite crud, flutter sqflite database, flutter sqflite tutorial, flutter sql, flutter sql database, flutter sql tutorial, flutter sqlite, local database, open database flutter, sqflite, sqflite crud, sqflite flutter, sql database, sql database engine, sql db, sqlite, sqlite flutter, sqlite flutter tutorial, flutter local storage, flutter

Id: pFctmsTDoa0

Length: 37min 6sec (2226 seconds)

Published: Wed May 01 2024