Using SqlStorage Instead Of SQLite In An Ionic 2 App

When it comes to Ionic 2 there are many ways that you can store your data.  For example you could use HTML5 local storage, Mozilla’s localForage library, or Ionic’s SQLite extension that is part of Ionic Native.  With these options available, I get a lot of requests for information on Ionic’s less advertised SqlStorage option.

We’re going to take a look at using SqlStorage in an Android and iOS application rather than the SQLite alternative.

Before getting too deep into this we should probably figure out the differences between SQLite in Ionic Native and SqlStorage which is part of the Ionic platform.  Per the Ionic 2 documentation, SqlStorage will use the Apache Cordova SQLite plugin, if available, otherwise it will fall back to WebSQL.  This is different from Ionic Native SQLite which will only use the Apache Cordova SQLite plugin.  I previously wrote how to use SQLite with Ionic Native.

To show SqlStorage in action, we’re going to create a fresh Ionic 2 project.  From the Command Prompt (Windows) or Terminal (Mac and Linux), execute the following:

There are a few things to note in the above set of commands.  First you’ll note the --v2 tag.  This means we are creating an Ionic 2 project which requires the Ionic 2 CLI.  We are adding both the iOS and Android platforms, but if you’re not using a Mac, you won’t be able to build for iOS.

With the project created, we have an optional next step.  If you wish to use the Apache Cordova SQLite plugin, which you definitely should, then you need to install it.  This can be done by executing the following:

Remember, if you don’t install the plugin, you’ll be using WebSQL which has a few limitations in comparison to the native alternative.

We’re going to edit two different files in our Ionic 2 project.  Starting with the TypeScript logic, open the project’s app/pages/home/home.ts file and include the following code:

There is a lot happening in the above TypeScript file, so it is probably best that we break it down.

To make this project possible we need to import both the SqlStorage and Storage components that are part of the Ionic 2 platform.  Inside the HomePage class we define both a public and private variable.  The personList array is public because it will be accessed via the UI.

In the above constructor method we initialize our storage object and create a new database table if it doesn’t already exist.  We are also initializing our personList to a blank array.

After initializing all our components we find ourself in the onPageLoaded method.  This method is triggered after the constructor method fires.  This method is responsible loading our data.  Now it makes sense to look ahead to the refresh method that we’re trying to use.

The refresh method will make use of a SELECT query that will get all person data.  Every row in the results will be pushed into our array of objects which will eventually be presented on the screen.

Finally this brings us to the add method.

In the above method we are inserting data into the database.  Of course this example is very simple so we’re not inserting dynamic data.  Every insert will only add my name to the database.  After the insert is complete, the data will be pushed into the array of objects for displaying on the screen.

This brings us to the UI of our simple application.  Open the project’s app/pages/home/home.html file and include the following markup:

In our HTML file we essentially have two buttons in the header.  One button will refresh the list and the other will add items to the list.  The core content of this HTML file is a loop of our array of objects.

Conclusion

You just saw how to use SqlStorage instead of SQLite with Ionic Native.  While I haven’t really figured out why you should use one over the other when both interface with the same Apache Cordova SQLite plugin, at least you have options.

If you want to take this to the next level, you should try to create a shared service from the SqlStorage code.  This can help reduce a lot of duplicate code.  If you need help getting started with this, check out a similar tutorial I wrote on the subject.

Nic Raboy

Nic Raboy is an advocate of modern web and mobile development technologies. He has experience in Java, JavaScript, Golang and a variety of frameworks such as Angular, NativeScript, and Apache Cordova. Nic writes about his development experiences related to making web and mobile development easier to understand.

  • Amr Ayoub

    Great tutorial , but it keep add the same names ,how can i add different data , and is there any way i can edit it after that?
    Thanks

    • Create a form and bind the fields with [(ngModel)] tags. Replace [“Nic”, “Raboy”] with the form fields.

  • k3x

    Hi Nic, thanks for the post.
    Do you know how to use SQLite with a pre populated database?
    The Ionic forum and documentation on this is soo poor i don’t know why.
    I’ve been trying to use a pre populated database for a week and no luck. This database has 20K rows(will have more in the future) and all i got on foruns is to populate it at the beggining with code, and i’m speechless when i see this response.
    In Ionic 1 i used your tutorial on the matter an it worked flawlessly but now with ionic 2 i can’t figure it out.

    • I’ll give it a look and create a tutorial for it.

      Best,

      • k3x

        Thanks Nic.

      • k3x

        Hi Nic,
        I figured it out.
        To use a pre populated database on ionic 2 use the cordova-sqlite-ext plugin.
        I don’t know why but the cordova-sqlite-storage does not work with pre-populated databases even if you have the option to say to use an existing database.

        • Maybe submit a ticket to Ionic Framework? If people don’t tell them, they likely will never know.

          • k3x

            Already talking with the creator but it’s supposed to work like this. The storage plugin does not support pre populated databases atm. But the other plugin may dissapear in favor of another approaches like using a database dump or JSON File to create the database.
            As seen here: https://github.com/litehelpers/Cordova-sqlite-storage/issues/529

          • Thanks for sharing 🙂

  • Gopinath Kaliappan

    Hi Nic How to add pre populated database into ionic2

  • Paulo Botelho

    This is strange upon following your tutorial it complains that it cant find the SqlStorage and Storage:
    Error: Module /home/raz/Work/MyWorld/node_modules/ionic-angular/index.js does not export Storage (imported by /home/raz/Work/MyWorld/src/pages/home/home.ts)

    Maybe the recent versions changed something? and yes i did do ionic plugin add cordova-sqlite-storage

  • Flavio Passa

    For a Sqlite pre populated database for Ionic2 Framework working 100% you should have a look at this: https://codecanyon.net/item/quizionic2-ionic2-app-template-w-sqlite-admob-inapppurchase/18182426
    This app template works great with latest version of Ionic 2 Framework

  • Vivek Gautam

    few days ago your code was working for me. but not it is showing “type error. unable to “Uncaught TypeError: Cannot read property ‘openDatabase’ “

  • Roland Bryce

    Pls how can I create multiple tables?

  • Basant Pandey

    I did the same step with ionic 2 but some how I got the error in console. Please check my screen shot. https://uploads.disquscdn.com/images/70444ae453b1ff9933dea5e49f76bcffe29ff03c8711d43de190b6c4359c4947.png

    • Well it says you didn’t install the plugin

      • Basant Pandey

        I have installed all the package. As I mentioned I used the above mentioned blog. I used the latest version of Ionic 2 and used the Cordova-sqllite-storage.

        • Makakura Ngân

          I think it required you run on device or emulator

          • Basant Pandey

            Thanks