Ionic 6, capacitor and local database (SQLite?)

Ionic Framework
1

Hello,

I’m trying to find the best solution with the latest version of Ionic and Capacitor.
My main target is Android.
I have large amount of pictures (thousands) and I want to give the user the ability to add them to favourites, to mark them as read and to hide (ban) them.
I want also to give them the ability to search them by tags (there are also thsoudands of them) ansd save sets of tags.
I want to give them the ability to do it locally, on the device, without having to query a distant database to save those informations.

I think that I will have to do some SQL queries, so I’m not sure that ionic-storage will be enough for this practice?
I’ve found a lot of tutorials, but they are all outdated, using Ionic 5, Cordova, etc.
Do you know if there is a simple example repository with a orking up-to-date example of Ionic 6 + database working ith Capacitor?

2

I don’t really think you should use Ionic Storage, Capacitor Storage or Browser’s Local Storage for this kind of persistent data. They’re awesome but only to store small sets of data in key-value format.
You should use Cordova SQLite plugin if you only want to do that locally. If, in some point, you intend to sync the data with a remote database maybe use Firebase, which already has support to offline mode without too much effort on the Dev part

1 Like
3

For Cordova SQLite, I did not manage to make it work with Ionic 6, I always get error messages when I try to follow (old) tutorials or readme.md.
I will try to give it another shot with the simple example shown in the link you gave me.
Thank you, I will also maybe give a try at Firebase.

4

When I try to test the given example, I always have the following message:

common.js:357 Native: tried accessing the SQLite plugin but Cordova is not available. Make sure to include cordova.js or run in a device/simulator

core.mjs:6484 ERROR Error: Uncaught (in promise): TypeError: Cannot read properties of undefined (reading ‘then’)
TypeError: Cannot read properties of undefined (reading ‘then’)

I’ve typed the following commands:

ionic start ionic-sqlite-app blank --type=angular
cd ionic-sqlite-app/
npm install cordova-sqlite-storage
npm install @awesome-cordova-plugins/sqlite
ionic cap sync
ionic serve

or maybe I’m not supposed to open it in the browser?
Also, I’ve created the data.db in src/assets/ but maybe it’s not the right path?

home.page.ts

import { Component } from '@angular/core';
import { SQLite, SQLiteObject } from '@awesome-cordova-plugins/sqlite/ngx';

@Component({
  selector: 'app-home',
  templateUrl: 'home.page.html',
  styleUrls: ['home.page.scss'],
})
export class HomePage {
  private db: SQLiteObject;

  constructor(
    public sqlite: SQLite
  ) {
    this.query();
  }

  query(): any {
    this.sqlite.create({
      name: 'data.db',
      location: 'default'
    })
      .then((db: SQLiteObject) => {


        db.executeSql('create table danceMoves(name VARCHAR(32))', [])
          .then(() => console.log('Executed SQL'))
          .catch(e => console.log(e));


      })
      .catch(e => console.log(e));
  }

app.module.ts

import { NgModule } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { RouteReuseStrategy } from '@angular/router';

import { IonicModule, IonicRouteStrategy } from '@ionic/angular';

import { AppComponent } from './app.component';
import { AppRoutingModule } from './app-routing.module';
import { SQLite } from '@awesome-cordova-plugins/sqlite/ngx';

@NgModule({
  declarations: [AppComponent],
  entryComponents: [],
  imports: [BrowserModule, IonicModule.forRoot(), AppRoutingModule],
  providers: [SQLite, { provide: RouteReuseStrategy, useClass: IonicRouteStrategy }],
  bootstrap: [AppComponent],
})
export class AppModule {}
5

You cannot use sqlite plugin on browser. If you want to use browser with database, just use websql. You can detect if you are on browser or on device and you can use either websql or sqlite methods which are very similar.

1 Like
6

Ok, I see.
I had the bad habit to create a web app to easily debug in Chrome console, then to convert it to Android app after.
I will search how to debug in VSCode or Android studio with an emulator.
Thank you.

7

There is a capacitor command to “serve” the app. So you can connect via USB and, since the app run in a chrome WebView, you can debug it use chrome inspect. I’m AFK now so can’t test, but try this one:
ionic capacitor run android --livereload --external

Also take a look at This Link

1 Like
8

Thanks a lot! That works perfectly with remote debugging, also with the emulator.
Now I can finally work! Thanks to both of you for your time!

1 Like
9

hello?
Since there are many examples of using the previous ionic 3,
I want to use typeorm sql.js in browser even with ionic 6. However, when I create the example source and try to connect, I get an error saying that the database object cannot be found. Can you give me some solution why this is?

10

Using a SQL database is difficult because the transactions only work locally and replication is complex. That is why most people choose NoSQL for client side storage.
If you require SQL, the way to go is SQLite because it can be bundled inside of your application and you are not able to spawn a regular database server (like MongoDB or Postgres) on android devices.
Firebase can be used but it requires to use the firebase backend which creates a vendor lock in. Also it is not fully offline capable because it requires authentication at least at the application start.
If you can go the NoSQL route, you should check out RxDB which comes handy with the support for replication and observability. It is also able to store your images as binary data besides your documents.