Flutter Tutorial - SQL Database Storage Using Sqlite & Sqflite CRUD | Android & iOS

with sq flight you can save your structured data locally on your device with an sql database storage by default if you close your flutter app then the state of your flutter app is not persisted and therefore we need to have a database storage to also persist our data locally on our storage and therefore we will use sq flight with which you can write sql statements and it is also supported on android and ios and as i'm recording this it is not supported on flutter web if you are new here subscribe to my channel and make sure to watch this video till the end let's get started by looking at when to use a database and when not first of all if you want to store a large amount of data then it is always faster and more efficient to use a database otherwise if you have not that much data then it is sufficient to use the key value storage such as shared preferences and i will put a link in the description about shared preferences and secondly it is important to know which type of database you should use so first of all you can use an sql database which has then a schema and therefore we use your sqflight otherwise if you don't want to have a schema then simply take no sql databases such as hive or more now that we know when to use a database and which different types of database exists we can now start to implement our crude operations create reach update and delete for our database storage therefore we want to follow four simple steps first of all we want to add our sqflight dependency to include sql support secondly we need to open our database thirdly we need to create our database table and lastly we can then perform all of our crude operations so let's get started with the first step go to your pop spec jumble file and here under your dependencies you need to add this sqf light dependency and we will also make use of these other two dependencies later after this we create a new file and here we create a class notes database because we want to persist some notes inside of this class we create a private constructor and then we create a global field which is our instance and this is basically calling this constructor then we create a new field for our database and this comes directly from this sqflight package and now we can continue with the second step to open our database because before you can reach and write your data to the database we need to open a connection and therefore i create here this new database and inside of it we want to return our database in case it already exists however if it not exists then we need to initialize our database and therefore i create here a new file nodesdb and this is later where our database is stored and now we want to also return our database so that we can later make use of it and lastly we need to initialize our database and therefore we simply add here a new method where we get then our file pass and then we simply want to store our database in our file storage system so in my case i use here this get databases pass and like you can see on android it is then going to this location and on ios it takes then this document directory if you want to store your database in a different location then you also can use here this pass provider and then you can use here for example the application documents directory and then you put it simply here inside instead let's continue out of our database path and our file which we have defined here we create then a new path object and now we simply want to open our database and then we put here this path inside secondly we need to supply here a version and by default initially you have your version of one and lastly you also need to define here your database schema and therefore you have here this create database method and inside of it we need to define then our schema but before we do this we also want to create a new method to close our database and therefore we simply access first of all our database which we have created before and then we simply call here this method close and with this we have completed the second step to open our database and to also close our database and now we want to continue with the third step to also create our database table and this is what we do here inside but before we do this we also want to create a new model and therefore i have created here a class where we have a node and here inside i have put then all the fields which we later want to store in our database storage to store later our node we also need to create a table and therefore i create here a table notes so this is the name of our table which we want to create later and we also want to create then here all of these fields and therefore we create a new class node fields and here inside we define then all of our field names which are then later our column names within our database and by default in sql database we have always here this underscore before our id and our other fields we can simply name like we want to have them and now that we have defined all of our column names of our table we can go back to our file and here inside of our create database method we want to call the database execute method and here inside we can then put our create table statement and here we want to access and this table nodes field which we have created before so we want to create a table notes and then we need to define the structure of our table and this is what you put here inside of the parenthesis and here inside you need to define then all of your columns and therefore you define first of all the column name and this is then here our column name for example and secondly after it you need to define your type and therefore we create here a new field for our id type and in sql you need to write this year to create an identifier and therefore i simply put here this id type after it and now we also can define the other fields which we want to save in our database so for example this is important flag which we have here inside and which is also here inside of our node object and again next to defining the name you also need to define the type and therefore we create here a boolean type and in sql you write it like this so we write here boolean and we simply use then this boolean type and place it here after this so again to emphasize it here we use here this boolean type because within our node we have here also this boolean field next we want to create here this integer field number and therefore we also want to create another column number so let's go here back and then we define the number column and we also need to define again the type so here we define an integer and this is what you do in sql so simply put here your integer type after your column name let's go back to our model and here we also want to create for these remaining three fields also some columns and therefore we go here again inside and then we create here all of them and i want to create for each of them a text type and i also define here then the text type which is here the sql statement and with this we have also completed the third step to create our database table or our schema and this method create database is only executed in case your file which is here this node's db is not existing in your file system so if it is already existing then this is not executed again two more things to notice here is that if you later want to update here your schema and also want to include maybe other fields or other data tables then you always can do it here inside of this open database method and here you have this on upgrade and this means every time if you increment here your version number by one for a new version of your data table then it is also going here inside of this on upgrade method and the second thing to notice is that you also can create your of course multiple data tables so simply duplicate here this line and then create your your second data table if you like all right now let's continue with the last step to also perform our crude operations create read update and delete therefore we create here a new method which is called create and in our case we put then here the node inside which is then one node with for example a title a description and also here this id and now we want to persist this node within our sqlflight database therefore we first of all get here a reference to our database and then we simply call here on our databases insert method and then you need to define here inside first of all the table where you want to put it so we want to put it here inside of this table which we have defined therefore i also put here this table node's name inside and secondly you need to define here the data which you want to put inside of your database and we want to put our node inside and therefore we also need to convert it to a map to do this you simply go to your node class and here inside we create that a method to jason and we simply need now to implement here all of these fields and convert it to a json object and how you do this is by simply defining here a map of key values so here we have the key which is our name and therefore we access here every time the note fields and secondly we put then the corresponding value inside and this is what we do here with every field for example with the title which is here of type string we simply put here then the value inside also if we have your integer type then you simply can put here for example this number inside and you simply pass you then the value there are some special cases for example this boolean and the datetime object which you need to convert before you can put it inside so let's start here with our boolean type which is here this is important field and therefore you need to convert here your value so if it is important then you need to convert it to a one and if it's not important then you need to convert it to a zero and this is what we do because this is what our sql database understands and lastly we also want to convert our daytime object to json therefore i define here first of all this time field and then i put here the created time inside and now we also need to convert this daytime object so you need to write here to either string and then it is converting it to a string object and now if we go back here to our data class file you see that this error is gone because we have now implemented this to json method which is then converting our node object to a json object after that we have inserted our node object to our database we get then an id back which was generated for us and this is then a unique id which we also want to pass then to our node object again so therefore we create here a copy method and inside of it we simply copy then our current node object and only modify here the id therefore you need to go again to your node class and here inside we create then this copy message and here you put then all of your fields inside so we start here for example with this field we simply put it here inside and we also create then here a node object and then you pass here this syntax inside and lastly you also need to paste here all of these other fields here at the top inside and secondly you also create them for each of these fields these statements and this method copy is basically creating then a copy of our current node object and we simply pass in here all the values inside which we want to modify so in our case we only modify here this id and an important thing to notice is that this id is always generated by the database however if you like you can also pass here your own id inside therefore you simply go here to this note object and here i have also created here this nullable integer id and this means if you have initially an id then you pass it here inside and then it will generate this id in the database so make always sure that this is unique in the whole database otherwise if you don't pass your id inside then the database will generate it for you and simply return here this new id for you sometimes you want to create your own sql statement and therefore you also have here this method raw insert and then you can pass here your individual sql statement inside and here for example i define then the columns and values and therefore i also create here first of all all the column names which i access here over our note fields and secondly i also access here then the values which i access here right now over our json object and this whole code is basically doing then the same thing as if you only write here this one statement next to creating a note we also want to read a note and this is always what you need to do with this id which we have before generated and if you pass down here this id inside then we can get from our database the node object again and therefore we start here by defining our database and then you have here this method query like before you put first of all the table inside which you want to create so we want to query here this table notes next you define all the columns which you want to retrieve from your table and we put here simply then this note fields class inside values and this is a field which we want to create right now therefore i simply create here these values list and i pass in here all of our strings our columns inside so simply make sure that you pass here all these fields inside of this list and these fields are basically representing all of our fields of our model object and with this values we now make sure that we read here all of our columns from our node object and secondly you need to define which node object you want to reach so we go here over our id and then we simply pass here a question mark inside so you also can put here your id directly inside however this is not secure because it doesn't prevent sql injection attacks and therefore simply make sure that you use here this where arcs and for each of your question symbols you put here then your id for example inside and if you have multiple then you can also put your other values inside and this will then take you another question mark of your where statement and if you use here this syntax then it is more secure because it prevents sql injection attacks our query method returns then here a list of json maps and the js map is then representing one specific item one specific node in our case and we simply get a list of nodes back and now we simply need to convert our maps again to our node object and therefore we simply check here first of all if our request was successful and it was returning some value so if it is not empty then we can convert here our map again to our json object and because we read here only one node therefore we can simply take here the first item of our list and then we want to convert this json object again to a note and therefore we want to create here this from json method within our node class therefore we create here inside this method from json and now we want to convert this json object again to a node object and how you can do this is by simply creating here a node and then from your json you go over all of your fields so in this case the id because we want to get here this id back and then you also convert it here to the type so in our case our id has here this type integer and therefore we simply convert it back to this type and we also do the same thing then for our other fields for example this number is of type integer or also our title and description are always of type string and make sure that you always put here the right note field inside so if you have your description then you also put description inside like before we have again two special cases so first of all we have here this boolean field and secondly we also have here this created time which we need to convert bit differently so let's start here with our created time object and therefore we need to convert here our json object again to a daytime object therefore i simply define here this create a time and then we get here first of all our json object and convert it to a string and secondly we want to convert the string then to a daytime object because here within our class we have it as a daytime object and therefore we need to convert it back to this daytime object and this is what you can do with this daytime path and here inside you need to put then your string inside so we simply put here our json string inside and lastly we also want to convert here our boolean field and like you saw before if it was through our boolean field then we have put here a one inside and if it was false then we have put a zero inside and now with an hour from json method we do the same thing as before only in reverse order so we simply check here if our boolean field is of one and if this is one then it is true and if it is not one then it is false and with this we have learned now how we can convert all of our basic fields so for example we can convert right now a daytime object a string object an integer and also boolean field so let's go back to our database class and here we want to continue our read node method so here we right now have no complication anymore because we have implemented this from json method and secondly we want to implement the other case if our maps is empty and if this is the case then he couldn't find our object which we have specified here over our id and then we simply want to throw here an exception that we couldn't find the id alternatively you also can return here a null value and then you need to change here this at the top to a nullable type and with this we have learned how we can read here a single note however sometimes you also want to read multiple notes and therefore we want to create a new method read all notes and this returns then a list of notes and here inside we first of all get then our database instance and then we query again our database and this time we simply create our database table without asking here for a specific object because we want to retrieve here simply all of our objects and like before this query method is then returning again a list of our json objects and we need to convert these json objects again to our node object and therefore i simply map over our list and then we convert each of these json objects again to our node object and therefore we make use of this from json method which we have created before and of course within your query you always can specify here then all the things which you know already from sql so for example you have your where order by limit having group by distinct and so on so in our case we want to implement here this order by and then we simply want to order here our fields our data by this column time and we want to have it in an ascending order sometimes you also want to create here your own query statements with this sql statement and therefore you can use the again this raw query and then you simply paste here your sql statement inside and this is basically then doing the same thing as we are currently doing here with this statement next we also want to update our nodes so we create a new method where we pass in the node object inside and the important thing is that our node object has cnid because with this id we can then access our node object within our database therefore let's get first of all our database reference and here we call then this update method and like before first of all you need to define here the table which you want to update and secondly you need to pass here your node object inside and we need to pass here again a json object inside therefore we convert our node object to a json object and lastly you need to define which node object you want to update and therefore we define here where and then we simply define here over our id which node we want to update and again like before we use here this arguments to pass then our id again here inside and with this we make sure that we only update here our object which has here this node id and like before if you want to use an sql statement in set then you can always use here this raw and here for updates you need to use in this raw update and inside of it you can then pass here your sql statement inside and lastly we also want to delete our object again from our database and therefore we get here this identifier and over this identifier we can delete send our node object therefore we simply call here this method db delete and then we define here again the table from which we want to delete our object and lastly we define which object we want to delete and therefore we define here the where and we simply pass and here our id inside which we want to delete and at the very end we also want to make use of all the methods like create read update and also delete therefore we want to have a quick look at the implementation of this notes app where we can simply display some notes and we also can create here some nodes therefore we type here title and also description and then you can click here on save to create your node in your database we also can then update our node so we simply click here on update and then we can change our node and then if we click again on the save button then our note is updated in our database and of course if you click here on the top right delete then we also want to delete this note therefore let's get started here with our first page which is displaying all of our notes therefore i have created here a notes page and here inside of our state we have then a list of all of our notes and here i create the method refresh notes and here inside we read then all of our notes from our database and we simply pass in our notes inside of our field in our state and lastly you need to display your notes and therefore i have created here a staggered grid view and then i simply display here all of these notes with some note cards and with this all of the notes which you have within your database should be then displayed here within your ui and next you also can then click here on one specific note to see then the detail view of this note so here you see for example the title and the description and therefore i have simply wrapped here around our note card a gesture detector and if we click then on this note then we simply call here our new route so we create here a new node detail page where we pass then our node id inside and over this node id we can then later read this specific node on this other screen therefore let's go to this node detail page and here you see that we get here then this node id with which we can then reference our id so here we simply call in our init state refresh node and then you can simply call here this read node method from our database and then you simply pass here this id object inside and lastly you get here then this node object back which you simply put then inside of your state for example and lastly you need to display then this node so i simply go here to the build method and here you see that i display then here first of all the title of our node also right now here the created datetime object and also for example the description next to displaying our data we also have here at the top two buttons first of all delete and also edit so let's start here with the delete button here we simply call then our notes database delete method and then we simply pass here our note id inside which is then identifying our node object which we are currently displaying here on our screen and with this we delete our object and then we simply go to the previous page in our navigation stack and secondly you can click here on this edit button at the top and then we simply go here and navigate to a new page where you can then simply edit your note therefore let's also look here at the implementation of our edit button here we simply navigate to a new page and inside of it we pass in our note object or you can also pass the note id and inside of this page we can then for example add or simply edit a note and therefore i have created here for example a message add or update node which is always called on the screen if you later click here on the save button and if you click on this button then you basically check here if our note is already existing or not and if it is not existing then we simply want to create a new node otherwise we want to update our node and therefore i have also created here two specific methods and here we basically update then first of all our node object internally here and then we call here this update message on our notes database class and secondly you also could call here this add node method to create a new node in your database and therefore you first of all define here your node with all of your title description and so on and then you simply call here this create method and pass here your note object inside so this was a really quick overview of how you can implement all of these methods create read and update and also delete if you want to learn how you can build such a notes app then i will put a link in the description about a to-do app where you learn how you can build a to-do app from scratch and this works pretty similar to what we are doing here right now and secondly you also can check out the whole source code of my application of this notes app here on the right side with the first link in the description and with the second link you can get access to my flutter courses where i teach you how you can become a better and more efficient developer hello everyone thank you so much for watching this video please make sure to give it a thumbs up and subscribe to my channel here to get the latest news about flutter and see you soon bye

Channel: HeyFlutter․com

Views: 192,098

Keywords: android, flutter, 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, ios, mobile, open database flutter, sqflite, sqflite crud, sqflite flutter, sql database, sql database engine, sqlite, sqlite flutter, sqlite flutter tutorial

Id: UpKrhZ0Hppk

Length: 27min 20sec (1640 seconds)

Published: Thu Apr 15 2021