How to Build an Ionic 4 App with SQLite Database & Queries (And Debug It!)

In this tutorial we add a SQLite database to our Ionic 4 app and see how to write simple SQL queries.
🔥 Learn to build mobile apps with Ionic in my Ionic Academy: ionicacademy.com/
💎 Get the Built with Ionic Book: builtwithionic...
#############################
Want to read instead of watch? Here's the full Ionic tutorial: devdactic.com/...
Want more tutorials? Here you go! devdactic.com/
Just getting started? Take my 7 Day Ionic Crash Course: ionicacademy.c...
#############################
You can also find me...
on / simongrimm_
on / schlimmson
on / devdactic
#############################

[@MaxDiegardo]

thanks simon. everything is superb and understood easily. everything in this can be used in capacitor right? confused with cordova vs capacitor ...

[@galaxies_dev]

Yeah you can also use Cordova plugins with Capacitor if you want to, but there's also a Capacitor SQLite plugin you could use instead: github.com/capacitor-community/sqlite

[@akashpandey2287]

Awesome tutorial, please help me how to open the local DB in android......I didn't get that from that doc you opened at the last of the video.

[@laxusmorilla1223]

Thanks for another great video Sir, but i got problems in developers.page.html.. i got an identifier 'name' is not defined.'{}' does not contain such a member..Please Advise. Thank you so much..

[@Roman-zo4yp]

in your .ts file developer = { name: '', ... } and in addDeveloper this.developer.name; instead of this.developer = {};

[@Imaginativeone_DF]

Got any tutorials on the topic of working with json within SQLite?

[@galaxies_dev]

Hmm actually don't think so, and not sure if that's a good idea - you could instead store JSON with Ionic Storage!

[@ArifHussain-od2fw]

Hi this is really a great tutorial . I have a question regarding synchronizing this local database with mysql and spring boot at the backend. Can you please provide tutorial for that ? or any link to it .

[@JohnPaulcm]

Hi Simon. Thanks for this wonderful tutorial. I just want to know how Can I use prepulated .db file in ionic. My .db file contains tables and data

[@firzahardyn]

Hi simon, you don't have tutorial configuration ionic 4 with MySQL?

[@jorgefranciscourramerino4295]

does anyone have any tutorial to do this in Ionic 4-5? regards

[@thembeladaphula4835]

Sir, do you by chance use a server here? If not can you do a video of where you are able to do all of this using firebase and a server to fetch and return data to the database.

[@leonardosouzaconradodesant6213]

Thank you!!

[@galaxies_dev]

You're welcome Leonardo!

[@minigeek]

Nice tut. Q: will this db everytime created and loaded with scripts on platform.ready? Seems like you added no check for one time load

[@galaxies_dev]

It's been quite some time.. But I guess you could easily change the SQL and create the table only of it doesn't exist yet!

[@minigeek]

@@galaxies_dev thnks for replying.this is good option instead of local storage

[@DavidFrassi]

Hi Simon, what can i use for sql db, on native apps but also on pwa running on browser?

[@GerardoCignarellaGodoy]

Hi Simon, thanks for the video, I need your help to create and query a database located in a folder at the root of internal memory, is this possible? How can I do it ?, I appreciate any help you give me, thanks again

[@deliotablang823]

well explain thank you.

[@galaxies_dev]

Glad it was helpful!

[@cristiana.9265]

Great video , thanks!

[@galaxies_dev]

You are welcome!

[@hafizmustafa9305]

Hi Simon. Thanks for the tutorials. I've been following your tutorial since i started learning ionic. I'm having an issue while following this tutorial which is in the developers.html there are some errors. It said that "Identifier 'name' is not defined. '__object' does not contain such a member" for each ngModel. I don't know why but i hope you can tell me the solution. Thanks!

[@galaxies_dev]

Where does that error come up? It looks like you might be accessing the object in the wrong way!

[@MS-tn4zq]

@@galaxies_dev I have the same errors. I copied the code 1 to 1 but it still doesn't work

[@kowalski0414]

Hi Simon, will this work with electronjs ?

[@arielcastilho5602]

did you manage to solve?

[@kowalski0414]

@@arielcastilho5602 no haven't. I did make it run queries but the data are not saved permanently. Do you have solution?

[@arielcastilho5602]

@@kowalski0414 Not yet, but I'm trying here.

[@fariskp5709]

hi simon, its nice tutorial what about the storage capacity. Is any limitation for ionic storage

[@musthagmohamed8175]

Awesome tutorial. Thank you..

[@alfredocarreon4449]

hello, we recently upgrade our ionic 3 app to version 4. how im having issue on initiating the app's sqlite database. it keeps throwing "cannot read property of undefined (reading 'executeSql'). the return value of this.sqlite.create() also is undefined. how can we fix it? thanks in advance!

[@sokhchamroeunkol9827]

Hi Simon, based on your Tutorial, does the db get created every time the application is ready?

[@galaxies_dev]

If you don't check if you already created it then yes, it would be created every time!

[@sokhchamroeunkol9827]

@@galaxies_dev Oh I got it. Thank you.

[@sokhchamroeunkol9827]

Hi Simon, if we want to use a JSON file of the two tables "developer" and "product" instead of the sql file, what should the JSON file look like ? I have been using JSON with one table, and it works, but with multiple table it does not. It's probably because of incorrect syntax when I included another table.

[@galaxies_dev]

This is how your JSOn should look like: github.com/dpa99c/cordova-sqlite-porter#example-usage-2

[@idricekaverlin760]

Really good. Just to no, am having an issue on android device. After restart all the data in my splite is wipe away and splitePorter return and error .

[@idricekaverlin760]

Any help Simon ?

[@galaxies_dev]

Sounds strange, the SQLite data wouldn't simple disappear. Are you perhaps inserting the same data on next app start again? The initial logic should only run once for the insert!

[@idricekaverlin760]

@@galaxies_dev SQLITE wasn't the problem here. The problem was my INSERT function used for adding dummy data so that i can test my app. After removing the insert of the dummy data everything was good.

[@_heybboying]

Hi Simon, Great tutorial as always... could you please explore this package and make tutorial for us? npm ionic-native-sync

[@Mfr12s]

Hi Simon, Your videos are awesome. I was wondering why don't you use capacitor instead of cordova ?

[@galaxies_dev]

Because Capacitor is still in Beta and I don't want to create a bunch of videos that will be outdated soon. Once capacitor is finally released I will definitely use it :)

[@Mfr12s]

@@galaxies_dev fair enough. Thank for the reply. Keep on posting your videos on ionic 4.

[@cebecoii2018]

Hi Simon, Thanks for the tutorials...Can I ask a favor?can you make a tutorial on how to deploy it as web application?Please...Thank you so much..

[@galaxies_dev]

You can check out a tutorial on this using Firebase hosting for example here: kzbin.info/www/bejne/Z2i2apaBiNOZra8

[@somenathdas8989]

Thanks for the video. I have a different issue. I am trying yo make a web view application using Ionic. But it gives error ERR_CLEARTEXT_NOT_PERMITTED. Can u please provide any suggestion/solution regarding this.

[@galaxies_dev]

I think that's usually an error you get when you don't use HTTPS for your API calls!

[@somenathdas8989]

@@galaxies_dev Thank you Sir

[@agung12348]

hello, i want to make my db loaded from API, when the updated time (stored in table) is not match with updated time (from api), i need to sync it, but, i run it for the first time i have to load it all without checking it with the updated time. how can i perform that? is it possible anyway? (i have around 3k-4k row data)

[@rezwansaki]

Which is the best and fast for database app, sqlite or json?

[@galaxies_dev]

JSON isn't really a database, so within your app you should use SQLite or otherwise get the data from a server that is connected to a database.

[@rezwansaki]

@@galaxies_dev Thanks. When is JSON used?

[@dineshbabuperumal4026]

Hi Simon, The data is not persistant on the database on Hardware device.. I deleted one of three entries. When I open the app, there are still 3 entries. It works fine in emulator but on hardware device, the persistance is not working... pls help..

[@rulight]

yes this happens to me as well.. did you find a workaround?

[@tradewithtony]

Hi @Simon Grimm, this is nice tutorial you're doing excellent work. Could you please share a video on how you could work with SQLITE and MYSQL in such a way whenever there is no internet data will be pushed to SQLITE and whenever internet is back it synchronize to the data on the server. Thanks in advance

[@galaxies_dev]

That's a really complex topic, but I'll think about it!

[@tradewithtony]

@@galaxies_dev Hi Simon, Thanks for your quick reply. I was able to get in touch with your video "Ionic Offline" it's really helpful. Again you're doing excellent job, greetings from Tanzania, Dar es salaam

[@mownitirzah6174]

@@galaxies_dev Hi... I am also looking for the same. It would be helpful if you do so. Thanks in advance.

[@shyam5631]

Sir, i have followed your written tutorial and the app is not working. it shows the pages but when i try to add new developer it does nothing. It just stays on that page.

[@galaxies_dev]

Try to add some logs and see if there are any errors!

[@marcoingo9726]

Hi Simon, Great tutorial! I've a question about data persistance. What happens to the sql lite stored data if the User updates a new release of the mobile app (iOS/Android)? Data will be lost? If yes, there is a way to prevent losing data while updating the app? Thanks in advance! :-)

[@galaxies_dev]

The underlying database of the app won't be affected by an app update, only when you want to change it!

[@marcoingo9726]

@@galaxies_dev It sounds great! thank you very much! :-)

[@jonathanlopezlondono3343]

Hi Simon, i have a question... You are using one file (database.service.ts) to implement 2 CRUDS but.... What about Seeding several tables (12 tables) , how can I use the same file to implement 12 CRUDS? Is that a good idea? Thanks for your answer!!

[@galaxies_dev]

If you want more structure then perhaps create services for each of your tables! But perhaps also have one service to manage the initial import, since having the initial import in each of the files will be a bit more tricky (to combine all the different promises/observables).

[@exsitewebsolution6803]

Hi Simon, can this be implemented in a Ionic PWA without cordova

[@galaxies_dev]

Hmm I don't think so. We are using the Cordova plugin to access the SQLite database, and you don't have that plugin or the db available inside a PWA!

[@arturfil]

How to store mp3 from and endpoint and reproduce it from a media player? I saw all the other tutorials related with these subjects but I haven't had any luck.

[@galaxies_dev]

You need to write it to a file on your local fileSystem, then keep a reference of it e.g. in the Ionic Storage or SQLite db and then later call it with that URL!

[@carlosgp1696]

Thanks for this tutorial man you got one more subscriber

[@JDKamauIII]

I mean... who can say no to that subscription pitch?

[@impactsongs]

This will work if test from web? like "ionic serve" or just on a mobile phone?

[@galaxies_dev]

SQLite is only available on a device, and you can't use Cordova plugins in your browser anyway. So only on a device!

[@impactsongs]

@@galaxies_devGrimm thank you for you fast response Simon, I have other doubts, I think your code is creating a new database every time is built, how can I make this to check if a database already exists and just create if don't exist?

[@carloliwanag5532]

Thanks Simon for this video. Great work! Can I also request for a tutorial on capacitor and sqlite? thanks!

[@oko2708]

It's not any different using capacitor, since it supports using cordova plugins.

[@muhammadhamdani1693]

how to import data from restapi to sqlite? because data from rest api is large.

[@itsPAN1K]

Can I do the same app for web too with mysql?

[@galaxies_dev]

On the web you don't have the underlying SQLite database that you have when the app runs on a real device.

[@vajidali9764]

Hello Sir can we get Image on our device because paste Image url not a good things

[@parikshitsarkar5117]

I am currently using an ubuntu computer, any suggestions on how I can simulate the app on an ios device? Because I cannot test the app on the web browser since we used cordova plugins. Any help would be appreciated. Thanks.

[@galaxies_dev]

You need a Mac to build apps for an iOS device, otherwise you can only build them in the cloud with a service like Ionic AppFlow!

[@skverskk]

Well done

[@carlosalbertobarrientossan9416]

por que no pones los codigos fuentes para descargar el ejemplo

[@mizutsunemt3375]

yo digo lo mismo , vi todos los comentarios y dicen que hay fallas , lo malo es que no hay mucha información en internet de sqlite y ionic 4 solo de ionic 3 .

[@jassersaanoun9493]

Simon pls a tuto on geolocation and geocoding.

[@galaxies_dev]

What exactly with Geolocation would you like to see?

[@jassersaanoun9493]

@@galaxies_dev basic things like detecting current address (street, city..) and also autocomplete (i tried, geolocation works, but i couldnt do reverse geocoding. i think there's a problem with ionic 4).

[@skverskk]

@@galaxies_dev Google Maps platform which returns driving distances, directions and driving times from point a to point b.

[@luiszamoraveliz2862]

database Service are created everytime I open the app or just on the first installation, picture this escenario, for example I make a http get to a api and the database is created with that information, but next time that i dont have internet what will happen ?

[@galaxies_dev]

You need to add a check and see if you got a connection or when you already populated your storage, you could keep that information as a flag in local storage as well!

[@MrBledi]

thank you very much you're like a bible man even better because what you say makes sense

[@galaxies_dev]

Haha thank you so much!

[@edgareduardodelgadotovar553]

Hi simon, im testing it on a androd device wit the ionic cordova run android command and it save data but if i close and then open the app database is empty.

[@galaxies_dev]

Perhaps because you always replace the database in the beginning? Have you checked the device DB file after you have added data like shown in the video?

[@sahilparmar9764]

@@galaxies_dev i have the same issue can you plz reply for the same issue how can we manage in browser or electron build. its always been empty when reload

[@ebeliejinfren5198]

Thanks alot, Good Tutorial, please create a tutorial for skeleton resolver for image and texts

[@galaxies_dev]

What exactly do you mean, the skeleton views of Ionic when loading data or something else?

[@jenyarmusic4006]

@@galaxies_dev yes, but as resolver (or service) and for image when image loaded completely (ex: using listener) thanks

[@mizutsunemt3375]

se ve funcional en el video Simon , pero la cuestión es que al tratar de emular tus pasos me temo que truena la app, no entiendo mucho ingles y no hay mucha referencia de mysqlite en internet y más con la ultima version , esta muy padre tu video pero no me ha ayudado . Saludos.

[@MS-tn4zq]

Nice Video. I have a problem with my developers.page.html. For example ..... Here the skills are red. I have the same with developers.name and img. I don't get any data when I start the app on my iphone. I hope you can and want to help me with that. Thank you ;)

[@vencendor666]

Thanks, for this videos. You are greatest! :)

[@galaxies_dev]

Thanks a lot Maxim!

[@beastencarlat7351]

I have tried to runt your code it shows 'TypeError: Cannot read property 'then' of undefined' in database.services.ts file in constructor and it also shows 'identifier name is not defined, skills is not defined in developers.page.html. Please help me to fix these issue. I have tried a lot.

[@lifasibiya4810]

I been having the same 'TypeError..' error problem for over two weeks now

[@dockies]

I'm having the same problem. Have you found the solution?

[@lifasibiya4810]

@Doc in my case, I upgraded node and the npm packages then I reinstalled ionic-native/sqlite and cordova-sqlite-storage. If that all fails, try running the app on a different device

[@AbdElrahmanMahmoudPro]

Good

[@mctribals]

Gracias cada vídeo esta muy bien explicado, Thank's..

[@ricko13]

2:54 everything is red, I don't care hahahahahahha that was so funny xD

[@oko2708]

For some reason the .then() part of my sqlite.create() is never executed. Any idea what could cause this?

[@raghavs6554]

Even I face the same issue. Have you solved that?? Please let me know

[@oko2708]

@@raghavs6554 It was because I was reading config from a .env file. Those values had not been read yet while executing the create function. Make sure you read the env file before you call the create function.

[@raghavs6554]

@@oko2708 OK thanks

[@mdmarufurrahman3547]

I am facing same problem. it says cannot read property 'then'. what can i do. can anybody help me ?

[@gustavofunchal9209]

I followed the project step by step but this error always happens: "Could not find plugin 'proposal-numeric-separator'" ... I don't know what else to do :/

[@galaxies_dev]

This is an Angular 8 issue - simply upgrade to Angular 9!

[@gustavofunchal9209]

@@galaxies_dev Thank you very much, you saved my life haha

[@PSnaki]

how connect to SQLite in ionic6 but in vue?

[@galaxies_dev]

You can use the Capacitor plugin!

[@robertoluisrodriguezherrer8892]

tienes buenos tutoriales pero deberias incrementarle tus propios subtitulos en español

[@mooodddy1]

plz which the best for realtime application use sokect.io+mongoDB OR use database Realtime LIKE Firebase ?

[@galaxies_dev]

Both are great ways, Firebase will be a bit easier in the beginning but if you need more control, your own solution with Socket is a better choice.

[@marv-ws1rs]

Hi Simon, nice Coding but: what an unsatisfactory approach for the viewer, if you program 3 hours and look at the very end if it works, but then have to debug 3 hours again. If you prefer to go step by step, then the viewer can check for himself whether everything works and understand the functions of the code snippets better in context. By step by step I mean that after a certain programming time you can check if the code is error-free and also show what use the last lines of code have for the program.

[@galaxies_dev]

Hey Marvin, yes I try to show the result more often in new tutorials, you are right that it's not a great experience otherwise!

[@abhishekvaze4758]

when I close the app and also kill it from background and open it again, then there is no data in the database !!! any solution to this ???

[@galaxies_dev]

Did you extract the database at that point? Are you overwriting it or deleting it at some point? Because the apps SQLite database wouldn't be removed by closing the app!

[@abhishekvaze4758]

@@galaxies_dev I was able to solve the issue. There was logical error in my implementation. Thank you for your reply though ! It means a lot ! 😊

[@sudinmondal456]

could you please tell me sqlite database store location on real device(android) i saw Android/data/project_name/file but file has 0

[@galaxies_dev]

If you don't find a file on the device, the database was likely never created :/

[@sudinmondal456]

but it work perfectly when i am going to offline mode then data fetched from sqlite database.

[@musthagmohamed8175]

Thank you so much for the wonderful tutorial. I have been learning a lot of beneficial content from you, Simon. I will definitely join your academy next year, as I am not financially stable at the moment, due to covid. 2:56 Adding data to DB using seed.sql file takes a lot of time. I have 30,000 records and it takes 3 minutes to update on the first run of the app. After the first run, their app works perfectly. How do I speed up the process of INSERTING records process? Hoping for a reply and thank you in advance.

[@galaxies_dev]

I actually don't know if this process simply takes time if you have such a huge DB :/ Perhaps let the user know that this first setup takes a few minutes if it's really necessary?

[@neivabmxcom]

please share the code. thanks for the video

[@galaxies_dev]

The tutorial is always linked in the description :)

[@neivabmxcom]

​@@galaxies_dev I see thanks nice work

[@johngeronimo8821]

10:00

[@markanthonyguiquing1515]

man can you help me? do you have facebook or other social media account

[@axrorabduganiyevich6667]

I think this problem is here : constructor(private plt: Platform, private sqlitePorter: SQLitePorter, private sqlite: SQLite, private http: HttpClient) { this.plt.ready().then(() => { this.sqlite.create({ name: 'developers.db', location: 'default' }).then((db: SQLiteObject) => { this.database = db; this.seedDatabase(); }); }); } what is wrong here?

[@galaxies_dev]

You need to run the app on a device, Cordova plugins won't work inside the browser.

[@axrorabduganiyevich6667]

@@galaxies_dev I run the app on android device but The result is the same :(

[@ropeadventures8314]

Thank you for all your tutorials. Unfortunately, i think you talk too fast and a lot. Thank you anyway.

[@tinofourie7963]

I am new to Ionic and your series of tutorials. I must admit that I have absolutely no idea what you are talking about. Furthermore, why would anyone use a mobile app to create a database and populate it with fixed values ?? There are proper DB Tools for it like PHP MyAdmin and MySQL Workbench. If I may suggest somethings while you are on your journey to 100k subs, PLEASE give a basic overview of what you are going to do using pictures, diagrams, flowcharts, whatever. Then explain what files / libraries will be needed and why they are needed and how they will be used to achieve the end goal. PLEASE, don't present yourself as a guru and me being an idiot. I am here to learn, so teach me by not belittling me with running at the speed of light through code, assuming I know more than you about the topic by using shorthand language and not commenting your code properly in order for me to follow along. Sadly you failed to interest me as a potential subscriber. Maybe by looking at how Derek Banas and MM TUTS present their tutorials you could gain more viewers and subscribers, while we (the viewer) gain more knowledge. Also, I am not interested in learning Ionic coding from the start. I have always been an individual who learns a new language by starting at the need I have - the rest will come as I work towards my end goal.

[@galaxies_dev]

Feel free to use any other material, some people find value in my content, and some don't and that's fine for me! Thanks for your recommendations of other channels, I'll take a look at them and see what can be improved. BTW: I never said I'm a guru, I just try to pass on what I learned about Ionic in my videos and hope it helps people :)

[@mindarentillosa1146]

(method) Promise.then(onfulfilled?: (value: SQLiteObject) => void | PromiseLike, onrejected?: (reason: any) => PromiseLike): Promise core.js:9110 ERROR Error: Uncaught (in promise): TypeError: Cannot read property 'then' of undefined TypeError: Cannot read property 'then' of undefined

[@aram5642]

Does the code have to look so imperative and verbose? Why not return Observables from the service calls in the first place? or at least use async/await. 'Then' causes callback hell. 15:38 - subscribe within subscribe... This is an antipattern Just avoid using subscribe as much as possible and your code will get much more declarative. this.data$ = this.db.getDatabaseState() .pipe( filter(ready => !!ready), // or simply filter(Boolean) switchMap(() => forkJoin( this.db.getProducts(), this.db.getDevs(), )), map(([ products, devs]) => /* return joined and mapped */) ) and in the template: {{ data$ | async }}

[@galaxies_dev]

You are right with your code, but a lot of people are confused by heavy usage of rxjs operators and will be very lost with this code. But still, your code is definitely a lot cleaner.

[@aram5642]

​@@galaxies_dev Personally, I disagree with this approach of allegedly not "confusing" people. People who reach out to Firebase, being a reactive database, should have already become familiar with RxJS basics and not be afraid of operators. Secondly, if there is anything confusing in the code, it is mixing imperative approach with reactive one. The simpler the code looks, the more encouraging it is to learn about the entire solution.

[@akashpandey2287]

Awesome tutorial, please help me how to open the local DB in android......I didn't get that from that doc you opened at the last of the video.

[@galaxies_dev]

This is the command: adb -d shell "run-as io.ionic.starter cat databases/data.db" > ~/androiddb.db