Flutter Sqflite | Easiest Tutorial | Flutter Tutorial for beginners

welcome back to the channel dog ring developer today's topic is one of the most demanded topic many of you have requested in the comment section to upload a video for the SQ flight flocking so here I am with the tutorial and in this tutorial we'll learn all the basics about the script light plugin and how we can integrate local database into our application so I was searching through the internet and I saw many tutorials none of the tutorials were actually satisfactory never using very complex structures so I'll be following a very simple structure so that it is easy for you to understand once you get familiar with the structure you can move forward with the complex queries right so for the predict websites as you can see in my pops record I am Alive used to plug ins one is sqf light and the other is path provider so good go to a pub spec 20 ml and add these two plugins into your flutter project now after that nothing else I've created a scaffold and a gap bar with the title SQLite example that's it so let's move into the coding part and we have to create a database helper class first now why are we using the database helper what is the use of path provider plug-in and we'll discuss all about that while we code so let's start integrating the local database into our application [Music] first things first we have to first create our database helper class why are we creating our database helper class because we can encapsulate all the functions into that class and later on we can refer to that class to call order crud operations for that database right so go to your lip folder and we'll create a new pack new dot file and I'll name it as database helper and this file will create a class I named it as database helpful right the first thing is to make this class a singleton class so why am I making it a singleton class because for the whole life cycle of our flutter' application we want only one instance of the database so that's why we are creating it a singleton class now there are many methods to create a singleton class the easiest method that I find is by creating a private constructor out of it so how to create a private constructor will write database helper dot private constructor which keyword will you use to make anything static and constant that is a final keyword that is the value cannot be changed so I'll write static final that is once it is initiated nothing else it will not be changed so for the whole application life side cycle will be having only one instance of this database helper class after that we'll try it database helper instance status instance of type database simple will be equal to database helper dot private constructor this way we have created an instance of this database helper see whenever you are near whenever you need to create an instance of any class you call it colleges constructor normally what we do is we write like database helper DB equals to new database right wait a minute right that's how we create object now in this case we are creating a constructor and the constructor is static final as well as private right so we have to initialize the database that is you need to add some function that will be initializing the database now we need to understand some terminologies before initializing the database we need to have some sort of table we need to have some sort of database name we need to have some sort of database version so let's quickly create variables for that because we might be using those again and again so it's better to create a variable and then use the variable rather than hard-coding the name again and hang it right so above this I'll create a static final database name and now you can give any name you want so in my case I'll be giving it my database dot DB don't forget to our this dot DB because this is the extension of the database this helps the plugin this helps the application to decide that this is the database that we are talking about you can name any file so dot DB ensures that you are talking about the database right now next thing is the database version so it will be DB version for the DB version I'll be giving it a value of one now see that I am NOT giving any kind of type to it because it will automatically take the type that is given first to it right and after that let's initialize our database so how to initialize the database one thing to note here is that the return type that the type of the database is database that is we have a database class inside this SQLite dot dot package database database let's make it a private one so that's it a static database I done why am i creating everything static here because I want these values to be accessed using that class thing so understand this that initially the database will be null that is our database will be null as the application is run first time in any device or is installed first time in any device so for that let's create a gator it will be of type future database right alligator and right database and it will be a sink why I am writing the synchronous keyboard because it might take some time to get the database here right now it is clear that this function will be returning as the database this thing should be clear in your mind does that this function will return our database that is this value right so it should be returning the database that we have just created all initiated right so will be returning database nothing to note here what is the value of underscore database here this is null that is this database is yet to be initiated so we have to put some conditions here so I will try it if this database is not null it's not null that is if it is not null then only return this database otherwise otherwise what we're gonna do is we'll try it database we have to initialize it right we have to create our new database into a local directory so how we're gonna do it will write database equals to await any database now this initiate at a basis function that is yet to be created and after that we'll be returning the database let's review this function once again if the database is not null then only return this value otherwise initiate the database and then return the return value from this function now you have to create this function ID write in a share database assign andthey the error is gone but we need to write some logic inside this right so how we do it will write directory now once I write directory you can see that this file is getting imported at i/o so for input and output we use these library and directory directory equals to a weight get application document documents directly now what does this function do now this function is from the path provider package you can see that these packages are automatically imported now these for these files are used to get the documents directly that is you might have seen the in your pc's also that you have the documents folder you have the Downloads folder so those are the directories that are by default given to the system so in similarly in our mobile systems also we have our default documents directory where all the applications local data is stored as well as we have the download directory so you can play with the directories that you want I'll be using the documents directory here now let's create our path variable so it will be string path equals to now you may be able to guess the path what will be the path it will be the documents directory plus the file name so we'll be using the joint function on this joint function we have to import the path package so let's quickly import the path package from the path file and there you go you can see that there is now on now what this joint function will do is that it will join the file name with the path that we get from this directory right so the path will be directory dot path comma the DB name now the whole path becomes whatever the documents directory is plus the file name right now next thing is to open the database the open database function inside in those SQLite plugin will automatically initialize the database for you as well as it will open the database for you so how we do it we write a wait open database now why am I using a wait again and again because all these functions need some time to be executed right so a wait open database to the path that is way to open the database we have given the path this path go to you the documents directory and the database name and open the database there after giving the path we need to give some database version why do we give versions so that we keep track of which version to use the complexity of the application increases we need to maintain some versions for the database for simplicity for learning purpose we have created only one version right so we'll give the version so it will have the parameter as version so inside this I'll give it a DT version now after that it has some parameter known as and create that is what to do once the database is created done when the database is created as soon as the database is created you want to make a table into it done whoo hope you are able to understand this what is the use of database without any tables sure so you need to have some kind of initial table on to it on on create method call I'm gonna create another function now this on create method is yet to be created don't worry we'll create it right now and we'll create the oncreate function so it will be again of future wait a minute this on could hear it now you can see that we still get there why because that will be given some kind of parameters and we have not given any parameter oncreate method will return two parameters as you can see from here that first one will be the data base and second is the integer that is the version value so we'll try it data base DB comma and why that makes sense now this database will be returned from this open database command as you can see that we are we are yet to return the data base from here so we'll try to return statement here understand the flow here we ne we are getting the data base now if the database is null we are if the database is not null we'll be returning the database as it is if the database is null then what we are going to do is we will be creating a new database calling the initial database function and in this initiate database function we are creating add new documents directory we are joining the path and then we are opening the database and this open database function in town returns the database that is created and that database we are returning I might be very fast you can slow up the video speed and understand the stuff once the database is created we need to make a new table so how to do this you can see that we have got that database artists created so well write DB dot query now inside this query we need to pass the query that will create the database right so I'll write three single codes and once again three single quotes why am I using three single codes now this way I can write any strength in multiple lines so it will treat all the string gasps so it will treat all the lines as a single string right so let's quickly start write the command so the command is create table now should our now comes the table name so what is a table name so let's create a new table name here so I'll write static final table name and it will be maybe my table let's create a table name with a my table so I want to use this value here so how to interpolate strings I'll write dollar sign and inside this I'll write table yeah then create table table name and now you have to specify the column name that is what will be the heading the titles of all the rows so in our case I'll be using only ID and name right so let's quickly create the column name here so it will be static final column ID and start the final column name now these are the fields that I am giving to the table that is the titles that will be given to the table so ID would be like ID and for the column name I'm you I'll be using name so it will create a table with two columns one is with the name of underscore ID and the other will be with the name of name done now create table table name that is create table my table and now we will write the columns so it will be dollar sign column ID and what do you want the column ID to be you always want the ID to be a primary key so I'll write integer it will be of integer type friendly okay now next thing will be the column name now this column name will be our text value and it should never be null and well that said okay once once again you have to give some parentheses to it now this is the complete command right so let me just arrange it so that there you go so create table the table name and inside the parentheses you have to give the column name and the data type for the columns then now this will create our own table inside our application so we will have the table with the name my table and the columns would be like these right now as you can see that we have initialized our data base and on created we have created the our denie table done now next is the insert delete update and the query command right so how to get the data that is inside the table how to insert the data inside this table how to update any preceding values and how to delete any values so let's see now let's understand all the four functions that we have inside our SQLite plugin we have the insert we have the update command we have the delete as well as we have to query command now what does query do query will return a list of map that means our database table values will be kind of like this ID and the ID respective ID and then the name respective name so this will be the structure of the values that we'll be getting in return of the table so the table values will be returned in this format so the table structure will be kind of this right so whenever we are gonna insert anything so whenever we are gonna insert any values to the table it must be passed as a tie of map whenever we are gonna retrieve something from the table we're gonna get inform of this in case of deleting will be only passing the parameters so in our case it will be the ID to be deleted don't worry I'll show you one by one so let's quickly create the insert function so I'll write future end insert now you might be asking that why am I using the integer value here because whenever you will be creating a new value to the database you will not be providing this ID now this ID needs to be primary and now this ID will be auto-generated incremented by one each time you will be inserting anything now this way it ensures that all the values are unique done now as a parameter to this insert command we'll be passing a mapped map of type string and dynamic rope understand this as I told you earlier in this video that the data that we'll be inserting should be in the form of map so we are creating this map now this insert function will create now this insert function will take up a map as a parameter and will perform the insert functions on the database how to call the insert function of the database we need to get the database first so we'll try it database TB equals to a way it instance dot database from the present instance get the database now once this command is run instance dot database this Gator will be called and once this data Gator is called we'll create our own database because at present our database is null so it will initiate the database and database will be created after that we'll be calling the insert command from this DB dot insert table and the values so what will be the values it will be the row that we are passing from the calling so it will be Drew and what will the table it will be our table and that's why I created a new variable for the table name because we might be using it again and again so I'll write table name there you go we have inserted the some values to it now how to get the primary key we have to return it from it now once this insert command a successfully executed it will be returning the primary key or we can say that the unique ID automatically generated that's all about the insert one now let's go and quickly write the what is the rental command the query command right so I'd write query all you need not to pass anything to this query all function because we need to simply get all the rows from the database right now what will be the return type of this function it will be a list of maps right so in our case suppose we have multiple rows in our table so all the rows will be in the form of maps so it will be returning a list and the list will be of type map the map in turn is of type string comma dynamic if you are able to understand this and of course the acing keyboard needs to be there after that we'll copy this command from here and paste it as it is because we will be getting the present database instance and from there we'll return the result that we get from the query commands who tried T be dot query and it will normally take the table name so in our case it is the table if that said it will query all the rows that are present in our table then our next function is our update function so we'll try it future update a synchronous keyword of course and and copy this line once again because we all see that's why we created this class as a singleton class because we are using the same instance again and again right so once we update now how will gone updated for updating anything you will be giving some kind of data to it so we need to pass the ID that is to which ID you need to update the command as well as the value the updated value so this will again take up a map as a parameter and the map will be of type string comma dynamic this will be Rho now the role you need to be updated you will be passing it here okay now we need to done now let's see how are we gonna update it so we'll try it DB dot update now this will again return the integer value that is the key of the data to be updated right so it will be table name right values will be true now now this will update the specific row but there has to be some sort of parameter as to which row as to which column IDs has to be updated so for that we have some parameter inside it that is where where column ID equals to question mark why am I putting a question mark that is I'll be passing the ID here inside the sphere arguments see you can always give multiple arguments to a where clause that is where ID is this and name is this so I'll write so for that I will write like where column ID is this and column name is the test list will follow the order of these question marks that is first thing that you enter in this list and the second thing that went in that this list will be automatically replaced by these question marks right so first question mark will be replaced by one and the second question will be replaced by Sahib here done so in our case we don't want to change any column name are we not we need not to check to the column name parameter so inside this I'm gonna pass the ID now where is this ID we have not yet created ID now tell me one thing once you return or once you pass any data to it suppose you are passing this to be updated how are you getting how will be how will you be getting the ID yes from this value right so we'll write so we'll try it int ID equals to row column ID I hope you are able to understand this once we like creating the UI part you will all you will understand the you will understand it even better so that's for the update command that is we'll be fetching the ID of the route that you have passed suppose you are passing ID 12 our name Sahib that is presently the name is mark suppose presently the name is Mark now you want the name to be changed to sipes we will write that for the ID 12 name type so we'll write you will pass the updated value so what element so what I'm gonna do is from this map that we have passed that is from this row I will fetch the ID so ID will be passed from the column ID parameter and will update that from there column ID is this that is the ID update the values done now after this update command this update command is also a future so we have to write the wait here by obeyed because update must still with take some time and we'll be returning now once this update command is successfully executed it will return the ID of the updated command that is suppose the ID is 12 so it will return 12 that is the record with ID 12 is being updated done now next is our delete command now this will again be a in synchronous function and this will also return a future of integer type now for the delete command you will just gonna pass the ID that is just delete the record which is having this ID right so we'll try it first of all we'll get the database instance and then after that we'll try it return that is we'll be returning the deleted ID so I'll write D be dot delete the table name so it will be the table name comma what do we have we're so inside this where it will be same as the update command that does column ID equals to question mark and what will be replaced to the question marks it's inside the whale arguments the ID that we just passed done that's all for the database helper class was quite simple I hope you are able to understand all the concept that we have discussed here now it's the time to move to the UI part I will tell you how we are gonna pass the values to these functions so that the respective commands are executed right so let's move on to the UE part this was all about the database helper part or inside this UI now we put it on do it for the body I'm just gonna pass a column inside this column as a children I'm gonna pass a flat button with on pressed as some sort of function and the child will be text in copy it and paste it four times say when you can see that we have four function four buttons here so first one will be insert second will be query third will be update and fourth will be delete so we have all the four buttons so what I'm going to do is I'm going to wrap this column into a center so that everything is in center there you go for the color part let me just give it a color so that they are actually visible there you can see that I've created the whole buttons and given it some color so you were able to identify which button is for what purpose for the main experiment I'll just posit it's enter so we have all the four functions so we have all the four buttons first button we'll do the insert operation second we'll do the query operation third one we'll do the update operation and the last one will do the delete operation right now now tell me how we're gonna do the insert operation I'll not be creating any different different functions for it I will be writing the stuff here only you can also create another functions if you want more than enough modularity right so an insert command will be so it's quite simple right we have created our database helper class inside this database class we just gonna create we're just gonna call this insert function and pass the row that we need now what are the different column that we have the above we only have the ID and the name now tell me one thing do we really need to pass this ID the answer is no this ID will be auto-generated as it is the primary key so right right database helper dot instance dot insert right this room you have to pass the map here so what will be the column name you can see that the column name here is name you can either write the name directly or you can also write the variable right so you can use the variable here so we'll try it database helper dot column name right and the value that you want to be inserted so in first case I'll be writing Sahib right that's a dull so after this once this insert function is executed what we're gonna get in return go to insert function and see you will be getting an integer value so let's just cast the integer value so I'll write int I equals to a wait you know to add asynchronous keyword here oh wait I let's print this I so I'll write so I'll write the insert an ID is and then just simply write the ID here the inserted ID is the ID that we get in return from there it's like done so once this function is executed we'll get the inserted ID so let's check it right so I'm just in order you start this application now let's restart this application and see if this method is working or not if we just press this button let's see what happens oh we get some sort of exception so let's see where is the problem no such table my table so let's go to our database helper class and okay so here you need to use the execute function I instead I accidentally use the query function that's my mistake so you will write the execute function here if you start this application once again so we have fixed their you started our application and if I press on this you can see that the inserted ID is 13 I have already pressed this button three times so you might have kind of you can see that two or three records already present there so next thing is let's just get the details right so how to get all the details so inside this on pressed what if you're going to do it's the same thing now what will be the return type of our query function it will be a list of maps so I'll write list map strength dynamic a why I'm typecasting all this you may be familiar to it it and if you are new to it you can see that I've created a new list list of map and map in turn will have the keys are string and the value dynamic the suspicious right so query rows and after that I'm gonna write a weight database helper dot instance dot query all that's are done and after that we need to print it so I'll write print query rows done so suppress on query you can see that we have three records and the result is printed as a list then next let's move on to the update command so it would be just same so it will return an integer value and it will be like updated ID so in this case we need to pass the ID that needs to be updated and the updated value so we'll write a weight you two are the key word here sorry for that oh wait it's the same thing database helper this way we are getting the instance and from that instance we are calling the red command inside this update command we need to pass the updated values understand this we need to pass the updated value so dried database helper dot column ID which column ID to be updated not equal to EE cool column so I want to update this name that is the ID name with ID 12 so forth 12 I'm gonna write the name should be mark right that's it done now we'll just print the updated ID right save it and if I just press this update button you can see that that one is getting printed now what is this one sorry I told you that it will return the operated ID no it will return the number of rows that are affected so there was only one row which was with the ID 12 so it will returned one right so if I just query and you can see that now the second row that is the second value is having name as mark so the so the records are successfully updated now next thing is the delete command now inside this delayed it's a it is the same thing right so it will be nth rows affected now this will also give the Rose effector and it will be write a wait I always forget to add the sink keyword here so wait database helper dot instance dot the lead and this delete will take the ID so I want to delete the last record that is the ID which is having 13 as an ID right so I'll write 13 only after that we just print the DOS affected now this rows affected should also be a one right so save it and FL on it you can see that one is getting printed off a query you can see that we have only two records so I guess I'm gonna wrap it for this video hope you learn something from this video hope you are clear with all the concepts of database and please write in the comments how you feel about this video and if I am actually able to clear all your doubts I tried to be I try to make it as simple as possible because all that was covered with all all the internet was populated with all kind of complex stuff so I try to make it as simple as possible for any kind of doubts you can always write in the comments you can always connect me through Facebook LinkedIn Instagram however you want it's in the descriptions all the links are in the descriptions hope you liked the video thank you so much for watching this video and stay safe stay at home and please like the video and subscribe the channel if you haven't done already share the video with your friends thank you so much goodbye [Music] developers [Music] you

Channel: The Growing Developer

Views: 41,767

Keywords: flutter, flutter sqflite, flutter tutorial for beginners, flutter sqlite, sqflite, flutter sql, flutter sqflite tutorial, flutter database, flutter sqlite tutorial, flutter app, sqlite, flutter sqflite crud, flutter sqflite example, flutter sqflite database, google flutter, flutter database sqlite, flutter dart, flutter local database, flutter cross platform, flutter tutorials, flutter database tutorial, the growing developer, flutter tutorial

Id: 8bV9ixYNAL4

Length: 40min 42sec (2442 seconds)

Published: Wed Apr 22 2020

Reddit Comments

[u/corner-case]

I've just started tinkering with flutter, and I'm surprised how there's not a strong consensus on which DB to use.