Moor (Room for Flutter) #1 – Tables & Queries – Fluent SQLite Database

mobile apps and local databases go well together if you want to take the relational database route SQLite is the winner however the problem with the bare-bones SQL databases of any kind is writing queries and then integrating them with your other code Android solves this with the beloved room library on floor though you are stuck with the low level SQL flight package not anymore more is a library allowing you to work with the flutters SQLite database fluently and in pure Dart behind the scenes it uses the SQLite package that you're familiar with if you want to do the low-level stuff oh and if you are wondering more is just room spelled backwards whoa welcome to resell coder and in this first part of the more series on flutter you're going to learn how to work with tables and simple queries we're going to build a project which will be a task list where you have multiple tasks displayed in L list view and you can take those tasks and everything happens persistently you can also add new tasks new tasks add it here and you can also set a date I know the UI is not all that great but this is not a tutorial about good looking you is after all so tasks with date and here we go we have some tasks with date here and then we can delete it later in this series in the next parts you're gonna learn about advanced queries so for example sorting those tasks or even about joins foreign keys migrations and then about pagination so if you don't want to miss those parts definitely subscribe to this channel and also hit the bell button so that you will get really notified when those videos come out and be sure to check out the written tutorial from the link in the description where you can also find all the code written in this video and links to the library we're gonna start off in the top spec da yellow file because we want to import all of the libraries we're gonna use more flutter and also more generator because more works by source generation at the end of the day everything you write with more using the fluent syntax using the dart programming language has to get converted to SQL the good thing is that you do not need to write SQL yourself so let's import more flutter into dependencies and then also we want to have more generator together with the build runner inside the dev dependencies whatever is inside that dependencies doesn't get packaged together with the app once it's built finally for the devices this is all you need for flutter directly but because we are building an app we also need to add something for the UI so we're gonna use provider and then also flutter slidable provider is a simplification of the inherited widget and then we have flutter slidable here because we want to be able to slide a task in order to delete it once we save this pop spec diamo file we can go ahead and create our first table with more we're gonna do that inside a folder called data so let's create such a folder under Lib and also this folder will contain a file called more data base dart and we're going to put all of our code related to more over into this file just to keep it simple in this tutorial you can obviously split your files up however you wish as I've already briefly outlined more works by writing pure dart without the need to write SQL of course if you want to do something really exotic you can jump in and write SQL yourself but for the most part I would say 90% of the time you do not need to touch SQL at all tables are defined as classes which extend the table class and we want to name our table tasks so extends table and this table class which is extended comes from the more flatter library and also because more works by source generation we also need to provide a part directive or part statement this will be the file where the generated code will be contained and this files name will be also more database that genes for generated that dart for now it's going to show an error because this file doesn't yet exist but once it's generated this will error will go away let's now go over to tasks and let's ask what will the tasks need well we need to somehow identify each and every task so it's probably good idea to have an ID inside of it then every task needs to have a name or in other words a title for example learn more here then it can have a date and also whether or not it's completed so let's add these columns over to this table in other words let's add properties to the class because properties of a class are the same thing as columns of a table the idea will be an int but we cannot just write int it's actually an INT column it's going to be a getter every table column is a getter in the class so in column get now its name will be IV and we want it to be an integer so integer and also it's going to be Auto incremented and now we somehow need to tell more that yeah this is it that's all there is to the definition of this ID in column in order to do that we can either call call and then call it a day but because it's a call function we can also just delete this call and this accessor and same please add another parentheses this is the same thing it's just a shorter syntax for coal it's here because we want to turn this in column builder into an actual column here so let's just call the coal method we want this integer to be auto incrementing because we don't want to deal with the IDS ourselves and also Auto increment sets this to be the primary key automatically then we also want to have text column it's name will be name and this will be simply a text with a length so let's put a constraint on here the minimum length will be 1 and the maximum length let's say 50 let's also call it and if the length constraint is not fulfilled it's going to not accept the task which is being put into the database and also it's going to throw an exception which you can then later catch and show an error message to the user another thing that the task should have is date time but date times are not by default supported by SQLite but Moore is smart and it can convert these date times which are not natively supported by SQLite into integers which are basically UNIX seconds and it does that behind the scenes so that you would think that actually day times are supported even though they really aren't so let's input a date/time column get date your due date it's going to be a date time and now it's going to be also nullable and let's finalize it here and finally we want to have a boolean completed and also similar to date times boolean are not supported by default but behind the scenes more converts them to integers which have value of either 0 or 1 so we again do not need to worry about boolean so with more so bull column get completed and it's going to be a bull or actually a boolean so that it doesn't mess with the keyword of bull which is a keyword in dart and we want to have a default value for this completed column for every new row inside the column and defaults here can be specified with a constant and this constant is a class with a constructor which accepts some value and this value in our case will be false and also we mustn't forget to finalize this by using the parentheses or in other words the call function another cool thing about more is that it takes this tasks table and generates a dark class out of it and not just any kind of a dark class it's a fully blown data class which supports value equality simple deep copies and even converting to and from JSON by default the name of the generated data class will be simply in this case tasks without the S at the end if you want to change the name of the data class to be something different you can use an annotation so data class name and you can specify some name in this case we want to leave the data classes name to be a task so we are now going to use this annotation again if you wanna have a closer look at this code you can check it out from the link in the video description where there's also a written tutorial and all of the code is available there too you might also be wondering that it's called that Auto increment sets this column to be the primary key but what do I want to have some other primary keys altogether the good thing is that you can absolutely set your own primary keys by overriding primarykey property which has the following definition so it's going to be a set of column and it's a getter called primary key and we use the new dart set syntax and we want to set it to be for example ID and also name so now this table will have two primary keys ID and name in our case we do not want to have custom primary keys so we can just delete this overridden property before we can generate the code we need to define a database class and in more database classes are also just simple classes let's call it AB database and let's make it extend the not yet generated AB database class and generate the classes are prefixed with an underscore and then dollar sign and the name is the same as the non generated class so AB database every database class in more needs to be annotated with use more and it needs to have the tables specified and the tables that this class operates with will be tasks table and every database class needs to have a constructor which passes some things into the super constructor so the super constructor takes in a flatter query executor in database folder so basically we are telling where the database file should be located and it should be located relative to the database folder in a file called div dot SQL Lite it doesn't really matter which kind of a name you choose here and this is a name parameter not just some unnamed one with the name of path so let's specify it here and then also we're gonna tell to statements to be true this is good for debugging because it's going to print out the SQL which is running behind the scenes into the console but we have some kind of an error here and of course this error is here because this AB database class is not yet generated but it's going to be in just a bit so let's actually generate it now let's open up the terminal and let's run flutter Bob or packages Bob run build Runner and let's say watch so that it rebuilds each and every time we save this more database file and once it's built after a while we're going to see the generated file here but we still have one error inside AB database and that is that we need to override the schema version which will be just one for now because this is the first iteration so to say of the database and this should be bumped up whenever you change the schema so you update your tables or update columns within a table each and every time you do a change you should provide a new schema version and then also a migration and migrations will be covered later on in this series so definitely stick around and subscribe to this channel and also hit the bell button if you do not want to miss that part let's now take a look at the generated file it contains the data class named tasks and really it's a full-blown data class because it contains among other things copy with to string and also hash code generation and also the Equality operator is overridden in order to make it a value equality then it also contains the tasks table which does some interesting things it worked with generated in columns and if we take a look for example at this generator in column class you will be able to see that really it does use plain old SQL integer then it also sets primary key an auto increment here but the good thing is that you do not have to deal with this stuff because it just works without you needing to operate with SQL which is definitely a good thing let's now finally get to quartz quarries can also be written in the fluent syntax in pure dart but also you can write custom SQL queries if you do something really exotic in this part we are going to put the queries directly inside the AB database class but do not freak out because you can obviously put your queries inside data access objects are wise called dows and really separating your queries into multiple dials is a good practice because if you have multiple tables and you could all of your queries inside a single class it's gonna get really messy really fast but let's just for now put all of our queries inside the app data base class queries can return a future and this future will contain a list of tasks and task is the generated data class let's call it get all tasks and whenever it's called we want to select as usual in SQL and each and every table has a getter property inside the database class so let's say we want to select tasks table and from it we want to get everything but this only returns a future what if we want to observe the tasks and get a new value out of them whenever new tasks are added or something is just modified inside the tasks table well we do not have to operate purely with futures we can also have strings here so let's duplicate this and below by using shift alt and down arrow at least on Windows in vs code and we are going to change this future to be a stream and instead of get all tasks it's a good practice to say watch all tasks and then instead of get we're going to call watch and that's it now I have a stream which automatically emits new values whenever the underlying table changes then we want to have insert tasks which simply returns a pure future without anything inside of it so insert task and it's going to say into tasks table we want to insert a task but this task needs to come from somewhere so we obviously need to specify it inside the parameter list of this function so let's say task task needs to be passed into this function and also you can return an integer here if you want to because this insert returns the future of integer and this integer is simply the key of the newly added task but only if the table contains an auto increment column in our case it does contain an auto increment column but either way we are now really interested in this integer after all similar to insert you can also have update so let's copy it duplicated below let's say update task and instead of into task insert we are going to say update the tasks table and replace the task and because task contains an auto incrementing primary key the Moore library will know which task should be updated and it's not is going to update all of them with the same value obviously finally we want to have the elite task and the leaf in the task is really simple as everything with more we want to call delete and then again delete here we are selecting the table tasks and then we call another delete because we want to delete a particular task awesome so we have the table definition we have the app database class and then we also have all of the queries which we currently need in order to make this app so let's create the UI now we're going to start off in main dot dart and let's delete everything which is inside of here we are going to use the flutter awesome extension for vs code to create a new material app and instead of scaffold and Center we are going to delete it from here and say that this home will be a home page widget which we are going to create in just a bit and also we want to wrap this material app with a provider so wrap with a new widget provider let's also import provider here and provider needs a builder and inside this builder which accepts a built context we are going to create a new instance of app database and now this instance of our app database will be available throughout the whole widget tree of course since this is only a simple app we are now going to use any fancy state management solutions because for us simple stateful widgets will be enough but I really recommend that you in your own apps step up your state management game and use the block library because that's in my opinion the best state management solution for flour out there and if you want to learn how to use block you can check out a tutorial from the cart in the corner alright so with this done we can now create a UI folder and inside it we are going to have the home page file so home page dot dart and because this is not the tutorial about the UI I am really going to go fast through this UI part so we first need to add all of our import statements in here including the new task input widget which is the widget on the bottom here which is used to add new tasks and also it their date here and then we're going to have a stateful widget home page which has a scaffold and then its body is a column containing a task a list which is the list view and then on the bottom will be the new task input which I have just shown it to you so this built task list will be a stream builder but first we want to access the database using the providers of provider of AB database simple inherited widget syntax with the provider package which is really awesome and then we want to have a stream builder in here because we are watching all of those tasks and if you remember watching tasks returns a stream which then is updated every single time that something changes inside of tasks table which is definitely an awesome thing so as every string builder it has a builder which takes in an acing staff shot containing a list of tasks which is the data class generated by more package and here we just populate the list builder by building list items which contain as slidable so that we can delete those tasks by sliding and definitely check out this code from the link in the video description which is gonna take you to resew color comm because over there you can check it out in full detail at your own pace Here I am really just going fast through it because I don't want to really bother you with the UI code since this is not a UI focused tutorial but we have a slidable here with the icon slide for deleting and then we have a simple checkbox list style which accesses values on the item task which is the task for this particular list item and then whenever the checkbox list style is tapped we want to update the task with a new value so we can use the copy with method which is part of the data class and we can change only one field on this data class which will be completed and completed will be set to either true or false depending on what the value is passed into unchanged of this checkbox list style oh and also here I have realized that I've made a typo in more database file because this is not actually called due date in our task table definition but here's a typo it's called you Dada so let's change this to due date and now everything should work properly once we have this typo sorted out so now we can actually access the due date and display it in the subtitle so that's definitely a good thing now let's import homepage into main dot dart and now let's move on to implementing the new task input widget it's going to be located under widget folder and the file name is this one so new task input widget dot dart and again it's pretty standard flower stuff we want to have imports here for flower material and also for provider and it's gonna be a stateful widget because we are using only a simple state management with widgets and not any kind of a block or anything like that and because we have stateful widget here we need to keep all of our state directly inside the state class so we're gonna keep track of new task date this is the value which is from this calendar pop-up here the value will be stored inside this date time new task date and then we also have text editing controller because we want to be able to reset the value input inside this text field once we submit it we want to have it cleared that's why we need to have the text editing controller inside in its day we set the controller to be a new instance of text editing controller and then we have a build function which contains a row containing text field and then the date button the text field is really standard it has controllers set on it and then it has some nice decoration and once the text field is submitted we want to access the database using the provider then we instantiate a new task data classes object and set a name and you date on it all of the other fields of this data class and subsequently of the table will be set to their default values so because ID is auto incrementing it will simply Auto increment and this boolean completed will be set to false because it's a default value for this column back inside the new task input widget we then wanna call insert tasks on this database instance and also because due date is nullable inside the table definition we do not need to worry about whether or not this new task date is null if it wasn't knowable here as we have defined and then you tried to input some null value into an unknowable column you would have problems you will get an exception but because it's knowable we do not need to worry about that and then we're gonna reset values after submit but before that we would not build the date button which simply calls show date picker and it sets it up so that the first date is 2010 the last day is 2050 and it stores the return value from this date picker inside the new task date field this one date time and finally reset values after submit well call set state and set the new test date to be no so that new tasks will have new dates set on them and not the last date from the last edit task and it also clears the text editing controller so that it doesn't have any value inside of it like this it clears it all right once we save this we can run it inside the emulator and here we go and actually because we have set on the more data base to lock statements we can see that it locked the first statement create table if not exist the table name is tasks and so on and so on it defines all of its columns here you can see that due date is in fact an integer and it can be no because dates cannot be stored by default and then also completed is a boolean which is kind of interesting because boolean s-- should not be supported maybe I am mistaken but still they are not like pure boolean because still they have to check for the range of the completed column here because the completed column needs to be in range of 0 & 1 as an integer because 0 & 1 are the only values that a boolean can have right so still that's kind of funky with this boolean but it works so I mean that's gold and then also we have selected all tasks from the tasks table but let's now actually go into the app for real let's add a new task let's also change its date to be the 30th of June 2019 and let's say submit whoa we have some error here but it's only a flutter error what's up with that run flex overflowed by 46 pixels on the right well we're just going to pretend that something like this had never happened and what's important is that more works not that the UI is throwing us some errors left and right but more works we can see that it inserted into tasks a new task with name and due date set on it and the values for these two rows are new tasks and then the due date represented as UNIX seconds and we can complete this task we can add another task without a date so that it's going to be null actually and if it's no we have a logic inside the widget so that when it's no is gonna say no date and then we can because it's persisted and drive inside this DB SQLite file we can really just hit ctrl shift f5 to do hard restart but even though it's hard restart nothing will be lost the data is still persisted because we are using a database here so that's definitely the most awesome thing that's it for this first part in the next part you're going to learn about advanced queries and data access objects foreign Keys joins and migrations and also about pagination so definitely subscribe to this channel and also hit the bell button so that you will join the notification squad and be notified about every video that I upload because here on reso culture I am determined to provide you with the best app development resources and tutorials out there if this video helps you give it a like and also share it with other developers who might be also struggling with doing SQL Lite and flutter remember that you can learn from the written tutorial and also get all of the code written in this video from the link in the video description leave a comment if you have anything to say follow me on insta Facebook and Twitter and see you in the next video [Music]

Channel: Reso Coder

Views: 54,421

Keywords: resocoder, tutorial, programming, code, programming tutorial, flutter tutorial, flutter sqflite tutorial, moor_flutter, flutter moor, flutter sqflite, flutter sqlite crud, flutter sqlite tutorial, flutter database, flutter database tutorial, flutter local storage, flutter local database, flutter local db, flutter sqflite example, flutter sqflite crud, flutter room database, flutter floor

Id: zpWsedYMczM

Length: 33min 26sec (2006 seconds)

Published: Wed Jun 26 2019

Reddit Comments

[u/andreyeurope]

Sweet. But do you believe it is production ready?

[u/saleem1333]

It's a really awesome package.

[u/dragneelfps]

Gonna try it in my next flutter pet project.

Edit: Is it inspired from Exposed or similar? It looks like it.

[u/maylortaylor]

Does anyone know how you would link two classes together using Moor?

​

Say something like a POST class with a list of COMMENT(s) on the Post class.

[u/nickwu241]

Cool package!