Building an SQLite Ionic App with Capacitor

[Music] hey simonides what's up welcome back to a new tutorial i'm kinda pumped because this is actually the first video in our new home if the sound isn't yet perfect i'm sorry i'm still working on it but i hope it's okay for now so today we're going to work on an sql application using a capacitor plugin we will implement um actually importing an existing database from a json file that i hosted somewhere into our application and then we will work on the data and see how it's possible to actually sync that data now to get started just create a blank new ionic application type angular and capacitor of course and then install the sqlite plugin from the capacitor community also um for our application we need a new service to keep track of all the database interaction and another page so we can have a little detailed page within our list finally since we're using capacitor um we need to run this application on a device so i will also bring in my live reload and you should do the same run the first ionic build and then add the native platforms that you want to test for the um plugin itself we just need to do one thing we need to go to our android main activity java which is like the thing we usually have to do for all uh packages and then we add the import and within the init we add the capacitor sqlite class that's just the standard basically for like every capacitor plug-in since we're going to why is it always jumping to that file uh since we're going to load our database from a remote host we also need the http client within our app module so simply add it to the array of imports like i already did and then we need a slightly small change to our app routing we generated a details page so right now we have a home page in the details page we will just make the home page the default page for the empty path and for the details page we want to also pass an id with the url so we can later easily retrieve on the details page the information for one of our products that's so far everything for the setup now what i did is i created a database json file because the sqlite plugin actually allows to import a whole set of table schemas values from a json file actually i'm not sure if it works with sql statement as well but since you usually have an api then it should be possible for you to create this kind of json as an export for your application if you just want to set it up in the beginning with data or otherwise if you just need it locally put that database json file right into our onig application perhaps in the assets folder and load it from there but i've hosted it right here on digital ocean spaces actually never used that before but looked quite nice to me um just to quickly go over this we got the database name version so you could later actually sync and update your data encrypted false or you can set this to true mode full simply means when the data is imported it will replace the whole database on your device and then we got the definition for the tables we got a windows table and the products table simply um just some fake information uh some fake companies and fake products that have the vendor id as a foreign key so we can have a little relation between them so that's the data we are going to import um copy that url you can use it as well or look up the post linked below the video uh once we got all of that in place we can heat over to our database service i think and get started in there so i'm completely in the wrong post for looking stuff up um so what we need to do is first of all import um a little object from the sqlite plugin since we need it later and especially the capacitor sqlite from the capacitor plugins that's really what we need those two keys will be used to access the capacitor storage since you can store like the database name that you retrieved on the json or you can get really like tricky here um but that's just a basic stuff that we need then we also might need a ready state for our database you know with cordover it was actually a bit more challenging um because we had to wait for the platform ready and then open the database but still right now we need to or we might need to load the data first from the server and import it and during all of that we don't want to either show the application or not make any loading requests so keeping track of the ready state with the behavior subject should be totally fine and we will also keep track of the database name just in case we might need it so for the constructor uh we need our http client and the angular alert controller we need the alert controller because for android we actually need to make a specific check i'm going to import a bit of code every now and then since we really need to cover a lot in this video and i want to make don't want to make it 45 minutes so for our init functionality we need to check if we're currently running on the android platform and the reason is that on android we need to call request permissions now if that statement looks kind of strange to you i totally agree the problem is that on capacitor sqlite there's actually no request permissions um i don't know why they forgot to add the typings for this so the only way to call that function was to um yeah add this little line as any and then we can use it like in any object um if we don't have permissions which would get in the catch block we could show an alert you might have to handle this differently depending on if your application needs this but usually if you're working on an app like this i guess your application really needs this perhaps you could just shut down the application or show a reload or retry button anything like this but once we got the permission or in the other case meaning ios we can continue with the setup database function i already added a few steps in here um but no code in there so far so um for the setup database usually you only want to call this once now this can be um different for you for example whenever your app starts you want to reload the latest data from the server and then either completely with the mode full or with the mode partial update your own database or the sqlite database in your application i'll just assume that we only want to do this once and afterwards not again so if the setup is not yet done um and we need to access the value because um that's actually how capacitor storage returns our value then we want to download the database in the other case it means we have gone through the setup before so after downloading the database we will set this setup key in that case we simply need to retrieve the database name since we needed to open our database and that's why we had these keys for our capacitor storage so in capacitor storage we just store the name of the database in the end which we can see comes in right here from our json file and we will store it and then we can use it whenever we open the application again to just open our local database so once again you could become be more tricky here implement your own sync logic on every startup load data set the mode to partial update your local sqlite or well you would have to check for um if you had a connection before if you change something locally so it's really not that easy but i wanted to give you like the starting point for this now the download functionality is interesting so within our download database which will happen um the first time we open our application and this doesn't even fit my screen anymore we will download my file simple http get request and i marked this as a jsonq lite object so this is how it looks database version encrypted mode and tables and if you check here database version encrypted mode tables it exactly fits that interface so that's why i did it now once we get back the data uh we need to do two things um first of all we we we're not going to directly import it in our application what we're going to do is not that again um we're going to check if the json is actually valid and therefore uh we can use from the capacitor sqlite plugin the is json valid um the problem is that this expects just a string and if we transform it like here to the object it won't work so we can simply call json stringify in the beginning then we got a plain json string and that's what we can pass to our is json valid function if the json string is valid so is valid and once again we need to use i think result in that case we can finally import the data into our sqlite database um there are actually three elements or three steps that we will take because we also need to store the database name did we somewhere set the database name and i think we forgot about that so let's set our database name to the json export database nice typing so that's the first part second part import the whole json string to our capacitor sqlite database and finally set our db setup key which just means don't load all this stuff and do all of this again in the end now as a little addition um i added this little block to the original tutorial um you could perhaps pass if you if it's an update to an existing database or not and then if it's not an update so this would happen the first time it would create a sync data a data table and afterwards if you didn't update you could set the sync date to the database so that helps to manage your local perhaps offline sqlite database on the device and check for all the updates that happened offline on the device and once you do another sync from the server and load all the data from the server you could set the sync date so you always know which changes were made in the offline application now the most important part of this is calling now our database ready set and call next true so that means now our database is able enabled we import all the data we opened it so everything is fine at this point they're still working on our house before we continue with the other functionalities in here let's quickly move over to the app component because i think that's a good place to perform a bit of logic and the first kind of bit is actually display loading the idea here was that in the beginning i don't want to show the application actually here's the application running on my device i don't want to show the application in the beginning but a loading because when you run the first application or the first time or all the time you make this http request and you set up your local database so during that time we show this loading and we call our database service init function at that point now we could actually make this function return something and set up database return something but since we got our database ready state it's actually easier to use database servers database ready subscribe and this now means uh you can log in the beginning this will immediately return false but once is ready is true uh we will also get that result in here since we subscribe to the behavior subject in that case we can finally dismiss the loading and call what ionic is usually calling so like status bar style default and this dot splash screen height actually i think the splash screen will be hidden anyway at an earlier point of time but let's keep it like this so where's my application right here we can't see anything but we can still debug it there's an article an old article i had on the devtectic blog called how to build an ionic fear for with escalate database queries and debug it and especially the debugging section might be interesting for you so you can now debug this for both android and ios if you've built an android application and deployed it through android studio you can use this command make sure that you use the name of your database in here um actually let me quickly look it up yeah so the plugin adds a little addition that what i um that's what i used to get the database so the name of your application databases and then the our database was named productdb remember right here name productdb and the plugin adds sqlite.db at the end so make sure whatever the name of your database is you also add sqlitedb if you want to debug it in here for xcode it's actually a bit more challenging um so this will give you the export will give you a database file that you can then open with your local tool of your choice for xcode uh you need to go to devices and simulators from here you can find your application and you can download the container you can store it somewhere and once you got that container you can show the package contents too many windows in here please leave this is the package content documents and there's our little database once again so that's really the way to debug these things uh let's see browse data actually i got a bit of other stuff in here but we can see all of this and we also got the sync table from the plugin so that's how you can see it actually works and edit your stuff you can also check the native logs within xcode or android studio um if you mess up this file and i did for about like three or four hours it's not going to work you get some errors on the console not very helpful uh most of the time perhaps because i'm really not that good with sql and setting up this stuff but in the end um it's important that this first part runs through so let's do a bit within our home page now we're going to inject the database service database service at this point we really know we get the data in the application and we can retrieve it i really recommend to make sure that this first step works before you do all of the rest in this tutorial of course we will have an array of products um we will also do a little export um just for showing um that functionality and how that could be synced back to your server and then we will also add new products so let's give this already a dummy title like this um actually we now need to move into the database service so we can get our product list maybe we should just do the whole thing um usually there's only one thing you need to do you need to create the sql statement like um yeah i'm really bad at sql select star from products um that's usually all and then you run a return capacitor sqlite um actually i'm not sure what's the real difference is between query execute run i found they all kind of work for me the syntax is now adding your statement in here and it also expects values which we don't really need for our select now the problem is that the home page actually loads pretty fast in the background while our loading might still take place and the database is not even ready yet therefore we need to first of all check our database ready state and pipe the result and add a little switch map and check is ready um this might be a bit overkill at this point but i feel like it's more secure doing it like this so if the database is not yet ready we gonna return off and then the syntax used by the query return value so that returns uh sqlite values and if you inspect it it looks basically like values like this um cannot find name off um why why why you know find um behavior subject let's import from and off so off um just creates another observable of a static value or something else and from which we're going to use now creates an observable from a promise because at this point we want to have the benefits of the behavior subject observable but this one returns a promise and that's always kind of tricky so what we do in the case the database is ready is we will basically do the same but we're not going to return the promise we're going to transform the promise into an observable to keep our nice chain and then the return value of that whole function is observable with values array um actually it looks still looks or kept sqlite values well it's basically the same result type now um we're fine i think we're fine i hope so um let's actually let's test this i fear it's not going to work uh within the home page uh let's do another function to load products um look my products and we can't really test all of this on the browser i think there's no browser implementation for that capacitor plug-in yet i think it would actually be possible with a little fallback so not sure why it's not working um but we can take a look at our luck here so um it's a bit hidden you need to dig into this and find my products and we get some values back that looks good that means we get values back from the sqlite database so i think i didn't mess up anything um let's quickly do the remaining functions in here because most of them aren't really that complicated so to get a product by id which you might need on the details page when you want to retrieve it are using the nice angular router way once again create a statement again i'm not really in sql expert so i just did my best to make this kind of work select star from products left join vendors um in order to get the vendor information on vendors id equals products vendor id where product id equals our id that was passed to the function actually i think that was the most complicated to get a database export you can call the export to json it's basically the opposite of importing json and by passing in the mode either partial or full full means you would get the whole database partial means you would get all the changes since the last sync date and that's why all the columns all the columns have a last modified date so there we go and there we go i'm not sure if that's kind of standard for sql databases anyway just want to mention it now to add a dummy product um i just wanted to give you one field to play around and the rest will be random values for the price and the render and then once again the statement insert into products blah blah blah and the values like this there's actually also a different syntax where you could use like values in here and then pass the values here but i actually don't find real value in this i kind of like to do it with the string literal and using this syntax i'm not sure what's your preferred way but i kind of like it now to delete the product delete from products nothing really special and once again execute query had the values in here there's also run i'm not sure what's the difference but maybe if you're an sql expert you will know it and finally deleting just for testing i wanted to add this functionality reset the keys delete the database by name and then we're fine so that is our whole database service you can of course also find all the code linked uh in the tutorial uh below this video and since we are i guess at already 22 minutes we should hurry a tiny bit up so since all the functions in our home page are basically calling our database servers let's just add them and go through them create export we can link this to a button and then pass either full or partial in here and just display our database export to add a new product we will use the value from new product and load the product again um i think there's not really any other way if you if you would specify the id for a product or something that you add up front uh you could also locally edit to an array but since right now um add dummy product will not use an id i'd just let it generate by the table uh we should simply reload the product so we get the real ids delete product well deletes the product and then i just filters our collection to remove that element so no full reload and delete database well that's that's kinda easy right okay uh so far so great we still don't see a thing in our application after 20 minutes that's a new record simon but we're very close to it we just need an item and if i hit save now we see something in our application plows now the really interesting stuff is our list of items i use these sliding items so we can have a little uh delete button right here of course iterate your products that we got from the database and for the router link we use the link product slash id just like we specified in the beginning in our app routing and then i just used a few of the values actually for the currency pipe of angular i also use the currency from the database that was kind of like my my star moment when i created this tutorial now delete product nothing really interesting and then let's also add below the list a few buttons to create a full or a partial export to delete the database and to display the export so now we can finally see a lot in our application we see all products we can add products and we're so happy i can delete products i can create a partial json export [Music] which doesn't look too right to me at this time but um we can add let's give it a try again usually this product should now show up in here it might be that there's a problem with the sink date or that within our dummy product um i actually i think i know what's going on because i'm not setting the last modified date in here but i updated this right here so i will just delete the database and hope that the application is going to reload if i make a very important change um yeah so i should just uninstall and redo this yeah i kind of messed up in here i can't do anything let's let's do this all right that was a little problem so i changed the string if you're using it and you will anyway get the right one i've now set the last modified to a default value it didn't work for five times and then it suddenly worked so now i can finally see my data create an export no changes add a product create another export and then the new product appears in the partial export um the full json export i'm actually not now not sure why the full export is empty at this point i'm actually not sure why the full json export isn't working it definitely did work for me in the past so i'm not sure if it's about the database setup but i can find it quite nicely within xcode and the package contents i'm really not sure yet but it definitely used to work now before we cross the 30 minutes let's quickly also implement our details page because i wanted to do like the the full um well like the full the full stuff right um add all missing imports and on the details page we can now easily retrieve the id through our route we can use a product in here set it to null and then retrieve it from our database service using exactly the function we defined before and going to the details page and let's just do this in one so back button so we can go back to home and if we got the product display the name and the company info which is now populate actually for mongodb it's called populate which is now joined into our result i feel like i'm back a lot of years when i used to uh work with sql so we can dive into that page and see the actual information about the vendor because left join magic and well it's just sql anyway so i really hope you enjoyed this kinda long uh tutorial on using sqlite within your ionic application with a capacitor plugin there is the possibility to have a sync between your server and your application then continue working offline then getting a partial export of all the changes and later syncing those changes back to the server or loading new data from the server in a partial mode when you start the application again really there are different a lot of different use cases that you might have with this application i just hope that this gave you the right information the starting point to create your next application in a nice kind of way with an sqlite database if you enjoyed this video please hit the like button and stay subscribed so you get notified about all the new tutorials quick wins and other app development and web development videos on this channel if you want to learn more about ionic with in-depth courses a community of like-minded developers so you can learn and build your apps faster you should definitely check out the ioniq academy which is my code school to help you with everything ionic with a huge library of courses material and a supportive slack channel so we can get your app out i hope you enjoyed this video i will see you inside the next video have a great day and happy coding silent

Channel: Simon Grimm

Views: 9,657

Keywords: ionic framework, learn ionic, angular, ionic angular, ionic guide, cross platform, hybrid app, ionic for beginners, ionic course, ionic, cordova, javascript, ionic 5, learn ionic 5, ionic 5 for beginners, angular 9, ionic 5 tutorial, ionic 5 angular, ionic 5 course, ionic academy, ionic tutorial

Id: 2kTT3k8ztL8

Length: 29min 11sec (1751 seconds)

Published: Tue Nov 17 2020