Use SQLite In Ionic 2 Instead Of Local Storage

Ionic 2 is becoming all the rage right now because of it using Angular 2.  With the introduction of Angular 2, comes many differences in the language and framework itself.  One of the most critical parts of any mobile application is its ability to save data and have it persisted when the application is launched at a later date.  I demonstrated in Ionic Framework 1 how to use SQLite as a storage solution, so I figured it would be a good idea to demonstrate the same using Ionic Framework 2.

Let’s see why it might be a good idea to use SQLite in an Ionic 2 application rather than HTML5 local storage.

In case you’re unfamiliar with local storage, it is key-value storage within your application.  This makes it difficult to query for data when you have a lot of it.  Not only is there query difficulties, but there are limitations.

Here is an article on Stack Overflow that lists some of the storage limits for local storage.  In short, local storage has a limitation of 10MB of data.

These two reasons are what makes SQLite a better choice if only comparing against local storage.

Now let’s look at getting SQLite up and running in our Ionic 2 Android and iOS application.  From a Command Prompt (Windows) or Terminal (Mac and Linux), run the following:

It is important to note that if you’re not using a Mac, you cannot add and build for the iOS platform.  It is also important that you’re using the Ionic CLI that offers version two of the framework.

We’re not done yet.  We also need to add the Apache Cordova SQLite plugin.  It is the same plugin we used in the Ionic Framework 1 tutorial and it can be added via the following:

With the project created and the plugin added, open your project’s app/app.ts file and change it to the following code:

We’ve included SQLite as documented in the version two official documentation and created a new table as soon as the Apache Cordova plugin is ready.  All interactions with SQLite are handled through Ionic Native.  This particular table has three columns, an id, a firstname, and a lastname.  Nothing fancy here.

Now we want to change the code in the actual page of our application.  Open your project’s app/pages/home/home.ts file and change the code to the following:

There is a lot happening in the above TypeScript code.  First off we’re including the SQLite dependency from Ionic Native.  Now in the constructor method we are opening the database again, but this time we call the refresh function that will do a simple SELECT query against our database and table.  During this SELECT, the array we’re storing data in is emptied and repopulated with the results.

We also have an add function in this HomePage class.  It is responsible for inserting static content into our database table.  To keep it simple we are just inserting my name.

To make things graphically pleasing, let’s head over to the project’s app/pages/home/home.html file and change the markup to the following:

Our screen will have two buttons in the navigation bar.  One button will refresh our list content and the other will add to it.  Each item in our list will be from the people array created in the app/pages/home/home.ts file.  We’ll be printing the firstname and lastname of every item.

Conclusion

As demonstrated in my Ionic Framework 1 SQLite tutorial, both are using the same Apache Cordova plugin.  The differences between the two being one uses Angular 2 in Ionic 2 and the other using ngCordova with Ionic Framework 1.  Using SQLite is a better choice than using the key-value local storage offered by HTML5 and JavaScript.

What I showed isn’t the sexiest way to use the database.  It is actually better to create a provider for your database.  To see how to created a shared provider, visit this article I wrote on the subject.

A video version of this article can be seen below.

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.

  • Mark Tucker

    Why do you need the this.platform.ready() check in the add() and refresh() methods?

    • Native plugins cannot be used unless the app is in its ready state. It is an Apache Cordova thing. One would hope the app is ready by the time it leaves the constructor, but that isn’t always the case.

      Yes, in this scenario this.storage would be null, but this works too.

      My tip would be to just wrap all plugin calls in the platform.ready(). It is a silly thing to stress about if you run into problems because your app wasn’t ready.

      Best,

      • Karim Mesallum

        this.platform.ready() was the only tip I learned from this tutorial and it was quite helpful.

        In the constructor, my app was checking for the saved values in the database before opening it, and checking for the platform readiness got it fixed.

        Thanks!

  • Kim Wong

    Got error after add :
    this.storage = new Storage(SqlStorage);

    “ionic-framework”: “2.0.0-alpha.46”,
    ionic version: 2.0.0-beta.15

    EXCEPTION: TypeError: Illegal constructorBrowserDomAdapter.logError @ app.bundle.js:33736BrowserDomAdapter.logGroup @ app.bundle.js:33746ExceptionHandler.call @ app.bundle.js:7880(anonymous function) @ app.bundle.js:16862NgZone._notifyOnError @ app.bundle.js:17503collection_1.StringMapWrapper.merge.onError @ app.bundle.js:17398run @ app.bundle.js:1186(anonymous function) @ app.bundle.js:17416zoneBoundFn @ app.bundle.js:1156lib$es6$promise$$internal$$tryCatch @ app.bundle.js:2556lib$es6$promise$$internal$$invokeCallback @ app.bundle.js:2568lib$es6$promise$$internal$$publish @ app.bundle.js:2539(anonymous function) @ app.bundle.js:1288run @ app.bundle.js:1183zoneBoundFn @ app.bundle.js:1156lib$es6$promise$asap$$flush @ app.bundle.js:2350

    app.bundle.js:33736 STACKTRACE:BrowserDomAdapter.logError @ app.bundle.js:33736ExceptionHandler.call @ app.bundle.js:7882(anonymous function) @ app.bundle.js:16862NgZone._notifyOnError @ app.bundle.js:17503collection_1.StringMapWrapper.merge.onError @ app.bundle.js:17398run @ app.bundle.js:1186(anonymous function) @ app.bundle.js:17416zoneBoundFn @ app.bundle.js:1156lib$es6$promise$$internal$$tryCatch @ app.bundle.js:2556lib$es6$promise$$internal$$invokeCallback @ app.bundle.js:2568lib$es6$promise$$internal$$publish @ app.bundle.js:2539(anonymous function) @ app.bundle.js:1288run @ app.bundle.js:1183zoneBoundFn @ app.bundle.js:1156lib$es6$promise$asap$$flush @ app.bundle.js:2350

    app.bundle.js:33736 TypeError: Illegal constructor

    • I see 192.168.0.177:8100 which leads me to believe you’re trying to use Ionic Serve or Ionic View. You need to compile and install your app to a device or simulator. Using one of these other services is not good enough.

      Please confirm how you’re testing.

      Regards,

  • Pedro

    Nic, Can’t I use sql plugin in Ionic Serve? Is there any way that I can simulate in server mode?

    • You should not use ionic serve in apps that use native plugins. My advice is test your apps how they were meant to be tested, by compiling and installing them.

  • diegoponciano

    How would you handle shipping a pre-filled database?

    I read your post on doing it with Ionic 1, but I’m new to Ionic 2/Angular 2.

    And, I’m thinking if I should just convert it to json files and be able to read them using “ionic serve”.

    • Anything with ionic serve is the wrong way to do things. I suggest you stop now before you corner yourself with a mess of problems.

      In terms of pre-filled databases, I was waiting on that tutorial for Ionic 2. I want it to be beta first because the current SQLite plugin that Ionic 2 uses supports pre-filled databases, the commands are just missing from the wrapper. If Ionic decides to add them, I’d rather show how to use the current. If they don’t add it by beta I will use a different method.

      Best,

    • Jonathyn Tellez

      Hi Nic,

      Re: pre-filled dbs. Any movement from Ionic 2 team on exposing those commands? I’m able to pass in {name:’path to my db’} as second arg to Storage. But keeps using new copy of db instead. Maybe something about the paths, hard to tell.

      Love the blog!

      • The Ionic guys told me they are re-doing the SQLite and storage stuff. They’ve even stopped advertising it on their website. The joys of working with something alpha.

        I’d say sit tight for this.

        • brutka

          Hi,
          do you have an idea – when will you have info on prefilled db?

  • Paca-vaca

    Could you please help me? I’m getting this error:

    EXCEPTION: Error: constructor(options={}) not implemented for this storage engine

    when i’m trying to create storage = new Storage(SqlStorage);

    • Android or iOS? JavaScript or TypeScript? Please paste your full logs as seen in the Terminal or Command Prompt.

  • Dipak Mahapurkar

    “Error: constructor(options={}) not implemented for this storage engine

    Thank you!!

    • What platform? What do your full logs say? What version of the Ionic CLI are you using?

      I need more information to help you.

      • Dipak Mahapurkar

        i am using android platform and my ionic info is:

        Cordova CLI: 5.4.1
        Gulp version: CLI version 3.9.0
        Gulp local:
        Ionic Version: 2.0.0-alpha.51
        Ionic CLI Version: 2.0.0-beta.17
        Ionic App Lib Version: 2.0.0-beta.8
        OS:
        Node Version: v5.3.0

        and i also follow your steps but doesn’t solve it.

        • I just tried it again and my info is as follows:

          Cordova CLI: 6.0.0
          Gulp version: CLI version 3.9.0
          Gulp local:
          Ionic Version: 2.0.0-alpha.53
          Ionic CLI Version: 2.0.0-beta.17
          Ionic App Lib Version: 2.0.0-beta.8
          ios-deploy version: Not installed
          ios-sim version: 5.0.6
          OS: Mac OS X El Capitan
          Node Version: v0.12.7
          Xcode version: Xcode 7.2 Build version 7C68

          It worked fine for me.

          If I had to guess, I think you’re using a version of Node that is unsupported. I could be wrong, but I think Ionic currently supports Node 4.x and below.

          Try changing your Node and updating all your components to match my info.

          Regards,

          • Dipak Mahapurkar

            Hello nic, Thanks fro your replay, The new beta-version of ionic v2 is released, I am trying to make app with sqlite database, but i will get me an error “No provider for Storage” how i solve that issue with my app,
            my ionic info is:

            Cordova CLI: 6.0.0
            Gulp version: CLI version 3.8.11
            Gulp local:
            Ionic Version: 2.0.0-beta.1
            Ionic CLI Version: 2.0.0-beta.17
            Ionic App Lib Version: 2.0.0-beta.8
            ios-deploy version: 1.8.4
            ios-sim version: 3.1.1
            OS: Mac OS X El Capitan
            Node Version: v4.1.1
            Xcode version: Xcode 6.4 Build version 6E35b

            Please give me some demo example.
            Thanks for help.

          • This tutorial is now outdated because Ionic 2 keeps changing on a weekly basis due to its beta status. When Ionic 2 becomes release candidate I will fix any problems.

            Until then I cannot keep up with their weekly changes.

          • Dipak Mahapurkar

            Ok, got it Thanks you.

  • Ray T

    Is there any way to enable foreign key support? I can’t seem to get it working. Thanks.

  • Fábio Burkard

    Very nice tutorial!

    Just curious: is the “this.platform.ready().then(() => {” really needed inside @Pages?

    I understand that inside @App it has to wait for the platform to be ready, but once the app bootstraps (and the pages are loaded as they are imported), is there any risk if I remove this snippet?

    Just subscribed; love your blog!

    • The @App is asynchronous just like the module.run in AngularJS 1.x. The platform.ready is also asynchronous so @App is not really waiting for anything. You risk loading your page before it has finished.

      Now I could be wrong on the application lifecycle, but if it’s anything like other asynchronous development I’ve done or Angular 1, you’ll still need to be sure everything is ready regardless of what page you’re on.

      If you find something that says otherwise, please share.

      Best,

      • I was wondering this when looking over the code. It seems like Nic is correct, according to the documentation:

        http://ionicframework.com/docs/v2/api/platform/Platform/#ready

        The code example is within an @Page and it says “Returns a promise when the platform is ready and native functionality can be called”, so you need to wrap it with this whenever you want to make use of anything native (which the SQLite cordova plugin is).

  • Steve

    For the life of me I can’t figure out how to use placeholders in the add function.
    I have a form to enter names, I would like to add these via a prepared statement.

    Any help would be appreciated

    Thanks Steve

    • Awesome! Thanks for sharing your solution 🙂

  • Amyou Teddy

    Nice Tutorial NIC
    I looking forward to see the tutorial about pre-filled SQLiteDB on ionic2 from you
    https://www.thepolyglotdeveloper.com/2015/01/deploy-ionic-framework-app-pre-filled-sqlite-db/

    I have problem on sqlitedbpath.

    I have ionic1 code and i’m trying to update to ionic2 & angularjs2

  • Dipak Mahapurkar

    Hello @nicraboy:disqus,
    Are you make tutorial for Internal service in ionic v2 for data passing or any other way for data passing in ionic v2 ?

  • Michael Gikaru

    Great tutorial, helped save so much time.

  • alby111

    Hi Nic, Thanks for your tuts, mails,

    I have an app with a SQLite DB, when I install the application
    and I click a link that goes to a search form, I can access to the
    form to search the SQLite data loaded, everything works well. If
    I add a word to the search form and I can see the list off the
    that word that I put in the search form field.
    But when I close the app and open it back again, and click on the
    form link I can´t access to the the form to search the data that
    is loaded.

    I wonder if the problem is that the second time I open the app
    when I click the link to search I can´t find the DB or I need
    another code to work along with the link, to find the form an
    load the data from the DB ?

    I begin loading the data in www/js/app.js to the DB located in
    www/contract.db
    The view is in wwwtemplatesapplication-storagesqLitehtml
    contract-list.html

    The controller is in wwwtemplatesapplication-storagesqLite
    jscontroller.js also the services in services.js
    In the controller.js I make the call to obtein the data
    $scope.getContractList();

    I use Versions
    Ionic 1.7.14
    Cordova 5.3.3
    Node.js 4.4.

    and the following plugins
    Plugins
    “cordovaPlugins”:
    [email protected],
    [email protected],
    [email protected],
    [email protected],
    [email protected],
    [email protected],
    [email protected],
    [email protected],
    [email protected],
    [email protected],
    [email protected],
    [email protected],
    [email protected],
    “cordova-plugin-dbcopy”,
    https://github.com/devgeeks/Canvas2ImagePlugin.git”,
    “id”: “org.devgeeks.Canvas2ImagePlugin”
    https://github.com/wymsee/cordova-imagePicker.git”,
    “id”: “cordova-plugin-image-picker”
    https://github.com/litehelpers/Cordova-sqlite-
    storage.git#0.7.14”,
    “id”: “cordova-sqlite-storage”

    Thanks for your support

  • Piccaza

    Hi Nic,

    Nice blog, very helpful.

    I have few doubt, regarding providing a name for storage and creating tables in it. Following is the code I tried. It doesn’t throw any error and data is populated when reopen the application, seems to be INSERT query not worked.

    this.storage = new Storage(SqlStorage,{name:’alpha_1′});

    var c = ‘CREATE TABLE IF NOT EXISTS alpha_1.details(id TEXT PRIMARY KEY,name TEXT,description TEXT)’;
    this.storage.query(c);

    var i = ‘INSERT INTO fobs_alpha_1.fobs(id,name,description) VALUES(‘+uuid+’,’+fob.name+’,’+fob.description+’)’;
    this.storage.query(i);

    Is this correct ? Or is it not the way to do it? Please help out.

    Many many thanks.

    • Seems fine. How are you checking your logs to be sure there are no errors?

      • Piccaza

        Hi Nic,
        I was looking at logs via running ionic app by command “ionic serve -lc”.
        When I checked the storage.query response data, found the issue that “storage_name.table_name” table is not exist. So instead of “storage_name.table_name” when I used “table_name” seems to working fine.
        Thanks.

  • Andrew

    I am running into problems modifying an existing table. Adding further columns etc..
    Do you or anyone know how to modify a table while keeping the data?
    Optionally, I could create a second table but I am also having problems Deleting an existing table.
    I have tried:
    this.storage.query(“DELETE sometable”).then((data) => {

    Perhaps I need to address this from the DB somehow..?

    However I can empty the table with:
    this.storage.query(“DELETE FROM sometable”).then((data) => {

    Any leads appreciated.

    • Using the DELETE command will certainly wipe your table. When you want to add columns to an already existing table you should use ALTER.

      • Andrew

        I will try ALTER but using DELETE or DROP is not working.
        The following produces the Error. I also tried without the return ‘data’

        this.storage.query(‘DROP TABLE IF EXISTS alerts)’).then((data) => {
        console.log(“ALERTS TABLE DELETED -> ” + JSON.stringify(data.res));
        }, (error) => {
        console.log(“alerts DELETE ERROR -> ” + JSON.stringify(error.err));
        });

        Thanks for the response.

        • What is the error?

          You don’t need the IF EXISTS in your statement. The command will succeed regardless if the table exists.

          If you’re new to SQL, you probably want to find a quickstart guide or tutorial like this one:

          http://www.tutorialspoint.com/sqlite/sqlite_quick_guide.htm

          Best,

        • John Edvard

          I have the same problem as you do. I am not able to delete my table even if I write: this.storage.query(“DELETE FROM myTable”);

  • Dhaval Shah

    Hi Nic,

    Have been trying to follow your guide to build a data service layer.
    While table is being created, but seems that the DB is not open and all the DB commands seems to be queue
    Have listed the details on SO as well (http://stackoverflow.com/questions/36717853/cordova-sqllite-plugin-with-ionic-2-giving-new-transaction-is-waiting-for-ope)
    I have been getting log message like “new transaction is waiting for open operation”

    Any help or pointer is appreciated

    Regards
    Dhaval Shah

  • Alexander Draxler

    Hi! Thx for the tutorial! I tried to redo it myself, but i get this strange behaviour. Maybe you know what to do…
    When i add an element and then hit the refresh button in the app, the view doesn’t get updated. Only at the second click on ‘refresh’.
    Or, for example, i add an input field to the page: i hit ‘add’ (console says it’s done) – i hit refresh (console.log at the end of the refresh method is being reached and written to the log) – i click into the input field -> the list is refreshed.
    … so it kind of needs a second ‘interaction’ with the site before the list REALLY refreshes…
    but why?!
    — btw, i’ve done it like you with JS, with TypeScript, with ionic serve, with the apk installed on my mobile device. everytime the same.

    • Alexander Draxler

      well NgZone was required to have the UI explicitly updated. Like in the “Creating a Real Time Chat Application with Ionic 2 and Socket.io” Tutorial.
      You maybe want to add that to this one…

  • Ashutosh Choudhary

    First of all thanks Nic for this awesome tutorial.
    I am getting a strange error while trying to fetch the records from the table.
    when i try print the records in my console
    data.res.rows.item[0].id prints nothing not even undefined, also the code stops at this particular line
    data.res.rows[0].id gives me the actual values which is 3
    This happens in chrome
    But if i use the same code and try to run my app in genymotion and use the debug tool to check the logs nothing work
    data.res.rows.legth was printing the correct value but data.res.rows.item[0].id, data.res.rows[0].id both arn’t working.

  • Emory Raphael Viana Freitas

    First of all, nice tutorial. I am using your example for my studies.

    I have a question:

    I tried to add the query using “WHERE” but it’s not working. Do you have any idea?

    My query is like “SELECT * FROM database WHERE id LIKE” + id_query

    this.data.query(query).then((data)

    Then nothing returns, have any idea?

    Thanks

    • My guess is a few things:

      You are not including a space after the LIKE term which is the same as saying LIKEid_query
      When using LIKE you should be using a wildcard % symbol, otherwise it is being used the same was as an equal sign

      I encourage you to read up on SQL.

      Best,

      • Emory Raphael Viana Freitas

        Thanks for reply,

        But I found the problem.

        The id_query should be in the params, then my final is:

        query: SELECT * FROM database WHERE id LIKE ?”

        this.data.query(query, [id_query]).then((data)…

        Anyway, thanks again.

  • Wambugu K. Martin

    it is a week now I have been following this tutorial and i haven’t been lucky to have my app working. Right now, when i run Ionic serve on the command line i get ”Unexpected token (9:24) while parsing file C:usersmkwionicfolderExampleProjectapppageshomehome.js

    kindly assist,

    • Don’t use ionic serve when using native plugins.

      This tutorial uses JavaScript. Can you confirm you are not trying to use TypeScript?

      • Wambugu K. Martin

        Hello Nic.
        Sorry, I have been MIA for a few days. If at all i have TypeScript it is not that I have downloaded it or installed it. What i have installed in my machine is Nodejs. In one of my project folders I can see node_modules.
        How do i check if I have any other Javascript framework other than JavaScript?

        • TypeScript will be TS files, not JS.

          • Wambugu K. Martin

            Now confirmed. I don’t have any .TS files in my project folders.

          • I’ll go back to my original statement then. Don’t use Ionic Serve.

            https://www.thepolyglotdeveloper.com/2016/02/why-you-should-not-use-ionic-serve-for-hybrid-apps/

            Try it building and installing, then report back.

            Best,

          • ES

            Hi, same problem here Unexpected token (8:24) while parsing file: /Users/base5/project/app/pages/home/home.js

            I didn’t use TS or ionic serve. I’ve just follow your steps, the only difference is that the “blank ionic project” now is a bit different compared to yours.

            Any suggestions?
            Thanks for you great tutorial

  • Periyasamy M

    What is The database path?how to view Database files or data?.
    I entered a data and save the data.how to i view my all data in database?

  • Perhaps I’m reading the code and understanding it all wrong. Here’s what I think:

    the function refresh() would be called after only when the platform is ready. Since the platform gets ready once, why is the refresh() function also wrapped within a platform.ready promise? Isn’t that redundant, or there’s something I’m missing.

    • Valid question!

      The refresh is wrapped in the constructor because the storage object must be ready first and it is dependent on the Cordova plugins being ready first. Since it is asynchronous we must keep refresh in there too. Otherwise refresh will happen before storage is set.

      There is also a platform ready in the refresh function itself because refresh can also be called via button press in this particular application. The ui which includes the button can be usable before the Cordova plugins are ready.

      In Ionic, all code that depends on Cordova plugins should be wrapped. We risk to many race conditions otherwise.

      Does this clear things up?

      • Yeah makes sense. It is a wise safeguard. Since the ui can call the refresh function, then I get it now.

        thanks for the explanation.

  • I have moved my sqlite queries (get, save, delete etc) into a service. (that’s a good idea?)

    I want to return the value of this.people so that I could use in my other pages. How would I?

    • Half of what you wrote confused me. Might be a language gap.

      Can you maybe ask one thing at a time?

      • Sorry I messed up with the syntax (why on earth doesn’t disqus support code formatting?) edit: just figured they do.

        Well, this was what I was trying to do, figured it out. Perhaps it might be helpful for those who wish to turn their sqlite queries into asyncs and harvest them wherever in their app.
        http://stackoverflow.com/a/37221208/1757321

        thanks for the article and help.

  • Steve S

    If you’re not using a Mac, you can’t add iOS as a platform? That’s actually not true. Sure you can. The only thing you can’t do without a Mac is build. True of both Ionic and plain-vanilla Apache Cordova (I’ve used both).

    • Where would it be useful to add the platform but not build it? I add that statement to deter people from trying. I don’t count build tools like PhoneGap Build or Ionic Deploy to be solutions as more often than not they are not compatible with the many third party plugins that exist.

      • Steve S

        I’m not saying that in most cases, it would be. It IS for me, but I admit I’m a fringe case. I have a Mac mini but the only development I do directly on it is native ObjC and Swift programming in Xcode. My main computer is a Windows laptop and I do most of my development on the laptop – web dev, Java and Cordova apps, and then I just build the stuff on the Mac that needs to be built on the Mac. I can see it possibly being useful in a team environment too.

        But you didn’t say “it’s pointless to do this”, you said “you can’t do this”. I was only correcting a (partially) incorrect statement. Of course, I just went and re-read and you did say “add AND BUILD”, so my correction wasn’t completely correct either.

        • Fair enough. I appreciate your 2 cents 🙂

  • Periyasamy M

    how to i view in my batabase and tables?where this file located in my projects.?

  • Роман Любимов

    What about multiple inserts? 🙂 In this case you should use SQLite from “ionic-native”

    • Ionic Native is nothing more than an Angular 2 wrapper for the base Apache Cordova plugins.

  • Throze

    Do I need to use the platform.ready erm…directives(?) if I’m using the SQL database as a data model?

    • All native plugins need to be wrapped in platform.ready(). If you handle this via a directive or service, fine, but every call must be wrapped in some fashion. This is because if the plugin is not ready and you try to use it, it will throw errors.

      Best,

      • Throze

        Alright, my implementation is made in a model and now I’m using platform.ready() and everything seems to be working fine.

        Except for one thing, which is actually a javascript thing: how can I return the result of refresh()?

        • It is asynchronous so you’d have to return a JavaScript promise.

          • Throze

            Could you explain how?

          • Something like this would probably do the trick:

  • George Chapman

    Hi, having a bit of a problem implementing… I’ve copied your (Type Script) tutorial word for word but am getting the following error:

    ReferenceError: sqlitePlugin is not defined

    Researched the error but can’t find any resolution, any ideas?

    Cheers

    • Are you trying to test in a browser? Per one of the Ionic team members on one of my other posts, SQLite in Ionic Native can not be tested from a browser.

      Best,

      • Erkan

        I am having same error with George. ReferenceError: sqlitePlugin is not defined. I see the cordova-sqllite-storage in plugins. And i am working on real android device. I wrote the exception in html and saw it. So exactly the same code with you. What am i missing?

        • Erkan

          I fixed, in home constructor, we have to cover code with platform.ready like when creating db.

          • CroModder

            I was struggling with these too, and just came here to post a solution.. wish I saw your comment earlier 🙂
            Maybe @nicraboy:disqus could update the article? It would definitely help new visitors, as this tutorial is first on google for “ionic2 + sqlite”

          • Should be good now. Thanks for pointing it out 🙂

    • Dustin

      Just ran into this myself. If you look around the docs, its a little easier than I expected. Rather than using the Sqlite plugin, you use the Ionic Storage service and declare it SQL storage, like this: this.db = new Storage(SqlStorage);

      Then ionic chooses between Sqlite and WebSql for you. If you open it in a browser, you get WebSql, on mobile, you’ll get Sqlite. But at least it looks like you don’t have to worry about all of that as they function equally.

      Caveat: I’m just trying this out myself right now.

      • I have an article on SqlStorage vs SQLite publishing on Wednesday. Inspired by you guys 🙂

  • Vicheanak Vannavy

    This doesn’t work on some Android devices. I had a look in the forum, they suggested to use androidLockWorkaround: 1. How can I add it in Ionic2? Thanks.

  • Varshil Shah

    hey Nic i used your code but i got error – Property ‘sqlitePlugin’ does not exist on type ‘Window’
    i also added plugin.
    any changes there?

    • What do your full logs say? Where are you testing? What versions of everything? I need a lot more information.

      • Varshil Shah

        i used sublime text editor.. this error get as syntax error in that editor

  • Anil Yadav

    Thanks for the providing information. It’s really help full http://goo.gl/TrIaEU

  • Billy Knox

    Thanks for the tutorial.
    I am new to mobile development and Ionic, etc, so I am having a little problem with using the sqlite database. I have gotten as far as making sure that the device is ready in my app.ts and there I also try to access my database.
    I know the database cannot be accessed via a browser without the proper coding in place, but when I load my app using the IOS emulator I get console output stating: Unable to open database.

    Code sample from my app.ts file:
    initializeApp() {
    this.platform.ready().then(() => {
    // Okay, so the platform is ready and our plugins are available.
    // Here you can do any higher level native things you might need.
    StatusBar.styleDefault();
    console.log(“Device is ready and willing to do your bidding!”);
    let db = new SQLite();
    db.openDatabase({
    name: “data.db”,
    location: “default”
    }).then(() => {
    db.executeSql(“CREATE TABLE IF NOT EXISTS handler (id INTEGER PRIMARY KEY AUTOINCREMENT, firstname TEXT, lastname TEXT)”, {}).then((data) => {
    console.log(“TABLE CREATED: “, data);
    }, (error) => {
    console.log(“Unable to excecute sql”, error);
    })
    }, (error) => {
    console.log(“Unable to open database”);
    });
    });
    }

    I would appreciate any advice you could give.
    If more code is required my GitHub repository is here:
    https://github.com/Billy5Knox/flashagility/tree/master/app

    • Try reinstalling the plugin. Sometimes Apache Cordova plugins get corrupted.

  • Abdul Munif

    Hi, I am following your tutorial and at the first time it doesn’t work in recent Ionic version. I am using Ionic 2.1.0-beta.3, Cordova 6.3.1, and cordova-sqlite-storage 1.4.7, and running on Android 6.0.0.
    Code in home.ts line 14-19 should be enclosed in deviceready event. If it is not enclosed inside deviceready event, the query won’t run. The error said: ERROR : TypeError: Cannot read property ‘executeSql’ of undefined. So, I modify the code like this.

    constructor(public navCtrl: NavController, private platform: Platform) {
    platform.ready().then(() => {
    this.database = new SQLite();
    this.database.openDatabase({ name: “data.db”, location: “default” }).then(() => {
    this.refresh();
    }, (error) => {
    console.log(“ERROR OPEN DATABASE: “, error);
    });
    });

    }

    It works like a charm :). I write here just in case if anybody have same problem with me. Thank you for your tutorial :).

    • Wesley Pimentel

      Wow this line on the Home.ts makes all the difference:
      platform.ready().then(() => {
      }); //in the end
      Thanks a lot man, salam!

  • sarde

    I’m trying to use SQLite in my project, but it’s returning the following error:

    “Unable to execute sql”, source: file:///android_asset/www/build/main.js”
    Here’s the implementation inside the constructor:

    this.database = new SQLite();
    this.database.openDatabase({
    name: “data.db”,
    location: “default”
    })
    .then(() => {
    this.database.executeSql(“create table if not exists people(id INTEGER PRIMARY KEY AUTOINCREMENT, firstname TEXT, lastname TEXT)”, [])
    .then(() => {
    console.log(“TABLE CREATED: “);
    },
    (error) => {
    console.error(“Unable to execute sql”, error);
    })
    },
    (error) => {
    console.error(“Unable to open database”, error);
    });

  • Lokesh Sahu

    Like Is not working, shows error. My code is

    let myarray = [];
    console.log(‘text ‘+this.QueryText);
    let sql = ‘SELECT firstname FROM category WHERE firstname LIKE %(?)%’;

    show following error:
    ERROR: {“message”:”sqlite3_prepare_v2 failure: near “%”: syntax error”,”code”:5}

    • “SELECT firstname FROM category WHERE firstname LIKE %(?)%” is not a valid SQL statement for SQLite. ‘%(?)%’ is not valid syntax. You might want something like this:

      “SELECT firstname FROM category WHERE firstname LIKE ‘%’ + ? + ‘%'”

      • Lokesh Sahu

        Thank you for the reply.

        I tried another way its working.
        let sql = ‘SELECT * FROM Recipients WHERE Recipients LIKE (?)’;
        this.newconn.executeSql(sql, [‘%’+this.QueryText.toLowerCase()+’%’]).then((data) => {});

  • Lokesh Sahu

    Like operator is not working. My code is

    let sql = ‘SELECT * FROM people WHERE firstname LIKE %(?)%’;
    console.log(‘sql ‘+sql);
    this.database.executeSql(sql, [this.QueryText]).then((data) => {

    ERROR: {“message”:”sqlite3_prepare_v2 failure: near “%”: syntax error”,”code”:5}

  • Vikram Lele

    Thanks for the tutorial – very helpful to get started.

    While working with ionic2 RC1, I ran into a problem with this sample, and found an interesting cause and effect.

    In MyApp class, the first line is

    rootPage: any = HomePage;

    This causes the HomePage to get created even before platform.ready() gets called (which actually indicates that all plugins are now available). As a result call to

    this.database.openDatabase

    in HomePage constructor fails (as the sqLite plugin is not yet available / accessible). An easy solution for this is defer assigning to rootPage in MyApp till platformReady is called.

    Chnage:

    export class MyApp {
    rootPage: any; //= HomePage;

    and add

    this.rootPage = HomePage;

    as the last line of platformReady callback.

  • AV

    I have a question, if I put a form in a modal, how do I update the information on the home page (Ionic 2)?

    Thanks

  • Vivek Gautam

    im getting one error in this line— let db = new SQLite();
    “Supplied parameter do not match any signature of call target”

    • Vivek Gautam

      i was using Visual Studio 2015 . which was not have up to date ionic2 libraries

  • Wesley Pimentel

    Nic you are amazing, thanks a lot

    Just some tweaks and voilà, I followed
    Abdul Munif tip.

  • Paul S

    hi nic thanks. great article saved me a lot of time. works fine on device. can u suggest me how to make it work on browser/mac/pc. we got lots of queries to implement in app. it would be better if we can debug (with ionic serve) without device/emulator. suggest if any other options too.. i worked on SQLStorage come to know that was deprecated. so any suggestion would be appreciated.

    • Native plugins cannot be tested from the browser. Browsers don’t understand native Android and iOS code.

      You could write some conditional logic to use WebSQL like SQLStorage did, but you probably shouldn’t.

      Just get into the habit that you’re building apps, not websites.

      Best,

  • David Palafox

    I keeping getting an error that the database cannot be opened. Any suggestions?

  • Vivek Gautam

    Thanks for this tutorial

  • Gpzim Pacheco

    Excelent

  • james

    Is it possible to syncronize ionicsql and mysql ?

    • Anything is possible with enough effort. Is it going to be easy? Probably not.

      • james

        Ok! Thanks Nic

  • Thibauld Nuyten

    Sigh, I’m just not allowed to learn ionic2 , am I? It literally took me a day (8 hours) to find this tutorial, as it’s the only one that even tries to approach what you need to do to actually get a running database set up, and still it’s not enough.

    I did exactly as this tutorial said. I’m running an ionic 2 project. Yet there is no app.ts in my entire project, hell, there isn’t even a (root) app folder. Which file are you referring to?

    Not just this, it won’t let me add android platform at all with “ionic platform add android”, it gives the error:

    cp: dest file no such file or directory: platforms/android/assets

    Any help greatly appreciated.

  • james

    it is working on a mobile device but not in firefox or chrome. whats up?

    • Native plugins do not work in the browser

      • james

        Thanks Nic, it is possible to create a background service in ionic 2? Thanks

      • james

        Hi. I am unable to drag ionic 2 side menu. How can I solve the problem? Thanks

  • james

    How can I put a foreign key using sqlite. Thanks

  • Michael Coyne

    I am assuming this creates a data.db file? If so where is this located as I can’t find it. Also how would this work with a pre-populated database?