Step‐by‐Step Guide to Set Up SQLite for Browser (Development) and Device (Production) - rtlehr/mobile-app-start GitHub Wiki

Step 1: Install Dependencies

You need to install the @capacitor-community/sqlite plugin and set up a WebSQL-like solution for the browser via sql.js.

  1. Native (Capacitor SQLite)

    npm install @capacitor-community/sqlite

  2. Browser (sql.js)

    npm install sql.js @types/sql.js

  3. Update capacitor.config.ts

    // capacitor.config.ts
import { CapacitorConfig } from '@capacitor/cli';

const config: CapacitorConfig = {
  appId: 'com.example.app',
  appName: 'MyApp',
  webDir: 'www',
  plugins: {
    CapacitorSQLite: {
      iosDatabaseLocation: 'Library/CapacitorDatabase',
      iosIsEncryption: false,
      androidIsEncryption: false,
    },
  },
};

export default config;

  4. Sync Capacitor

    npx cap sync

If you get an error you may need to build the project

npm run build

Step 2: Set Up SQLite Service

Generate your Angular service and implement the cross-platform logic:

ng generate service services/sqlite
// src/app/services/sqlite.service.ts
import { Injectable } from '@angular/core';
import { Capacitor } from '@capacitor/core';
import {
  CapacitorSQLite,
  SQLiteConnection,
  SQLiteDBConnection,
} from '@capacitor-community/sqlite';
import initSqlJs, { SqlJsStatic, Database as SqlJsDatabase } from 'sql.js';

@Injectable({ providedIn: 'root' })
export class SqliteService {
  private sqlite: SQLiteConnection | null = null;
  private db: SQLiteDBConnection | null = null;
  private sqlJsDb: SqlJsDatabase | null = null;
  private isBrowser = Capacitor.getPlatform() === 'web';

  async initialize(): Promise<void> {
    if (this.isBrowser) {
      const SQL = (await initSqlJs({
        locateFile: file => `/assets/${file}`,
      })) as SqlJsStatic;
      this.sqlJsDb = new SQL.Database();
      console.log('sql.js initialized for browser');
    } else {
      this.sqlite = new SQLiteConnection(CapacitorSQLite);
      this.db = await this.sqlite.createConnection(
        'myapp',          // database name
        false,            // encrypted?
        'no-encryption',  // mode
        1,                // version
        false             // readonly?
      );
      await this.db.open();
      console.log('Capacitor SQLite initialized for native');
    }
  }

  async executeQuery(
    query: string,
    params: any[] = []
  ): Promise<{ values: any[] }> {
    if (this.isBrowser) {
      if (!this.sqlJsDb) throw new Error('sql.js not initialized');
      const stmt = this.sqlJsDb.prepare(query);
      stmt.bind(params);
      const results: any[] = [];
      while (stmt.step()) {
        results.push(stmt.getAsObject());
      }
      stmt.free();
      return { values: results };
    }

    if (!this.db) throw new Error('Database not initialized');
    const ret = await this.db.query(query, params);
    return { values: ret.values ?? [] };
  }

  async close(): Promise<void> {
    if (this.isBrowser) {
      this.sqlJsDb?.close();
      this.sqlJsDb = null;
    } else {
      if (this.db) {
        await this.db.close();
        this.db = null;
      }
      if (this.sqlite) {
        await this.sqlite.closeAllConnections();
        this.sqlite = null;
      }
    }
  }
}

  1. Copy WASM

    cp node_modules/sql.js/dist/sql-wasm.wasm src/assets/

  2. Ensure assets are configured in angular.json:

    "assets": [
  "src/favicon.ico",
  "src/assets"
]

Step 3: Configure main.ts for Ionicons + SQLite Initialization

Use bootstrapApplication and an APP_INITIALIZER to call your service before the app starts:

// src/main.ts
import { enableProdMode, APP_INITIALIZER } from '@angular/core';
import { bootstrapApplication } from '@angular/platform-browser';
import {
  provideRouter,
  withPreloading,
  PreloadAllModules,
  RouteReuseStrategy,
} from '@angular/router';
import { IonicRouteStrategy, provideIonicAngular } from '@ionic/angular/standalone';
import { addIcons } from 'ionicons';
import { home, settings } from 'ionicons/icons';

import { AppComponent } from './app/app.component';
import { routes } from './app/app.routes';
import { environment } from './environments/environment';
import { SqliteService } from './app/services/sqlite.service';

if (environment.production) {
  enableProdMode();
}

addIcons({ home, settings });

export function initSqlite(sqlite: SqliteService) {
  return () => sqlite.initialize();
}

bootstrapApplication(AppComponent, {
  providers: [
    provideIonicAngular(),
    { provide: RouteReuseStrategy, useClass: IonicRouteStrategy },
    provideRouter(routes, withPreloading(PreloadAllModules)),
    SqliteService,
    {
      provide: APP_INITIALIZER,
      useFactory: initSqlite,
      deps: [SqliteService],
      multi: true,
    },
  ],
}).catch(err => console.error(err));

Step 4: Create & Route a Sample Component

Generate a standalone Home component:

ng generate component components/sql-test--standalone
// src/app/components/home/home.component.ts
// src/app/components/home/home.component.ts
import { Component, OnInit } from '@angular/core';
import { CommonModule } from '@angular/common';
import { IonicModule } from '@ionic/angular';
import { SqliteService } from '../../services/sqlite.service';

@Component({
  selector: 'app-sql-test',
  templateUrl: './sql-test.component.html',
  styleUrls: ['./sql-test.component.scss'],
  standalone: true,
  imports: [CommonModule, IonicModule],
})
export class SqlTestComponent implements OnInit {
  results: any[] = [];

  constructor(private sqliteService: SqliteService) {}

  async ngOnInit() {
    try {
      // Create a sample table and insert data
      await this.sqliteService.executeQuery(`
        CREATE TABLE IF NOT EXISTS users (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          name TEXT
        )
      `);
      await this.sqliteService.executeQuery('INSERT INTO users (name) VALUES (?)', ['Bob Dillion']);
      await this.sqliteService.executeQuery('INSERT INTO users (name) VALUES (?)', ['Chet Cheterbrain']);
      const result = await this.sqliteService.executeQuery('SELECT * FROM users');
      this.results = result.values;
    } catch (error) {
      console.error('SQLite error:', error);
    }
  }
}
<!-- src/app/components/home/home.component.html -->
<ion-header>
  <ion-toolbar>
    <ion-title>SQL-Test</ion-title>
  </ion-toolbar>
</ion-header>
<ion-content class="ion-padding">
  <ion-list>
    <ion-item *ngFor="let user of results">
      <ion-label>{{ user.name }}</ion-label>
    </ion-item>
  </ion-list>
</ion-content>
/* src/app/components/home/home.component.scss */
@import "bootstrap/scss/bootstrap";

ion-content {
  @extend .container;
  max-width: 600px;
}

Route to Sql-testComponent

// src/app/app.routes.ts
import { Routes } from '@angular/router';

export const routes: Routes = [
  {
    path: '',
    loadComponent: () =>
      import('./components/home/sql-test.component').then(m => m.SqltestComponent),
  },
];

Step 5: Configure Global Styles

/* src/styles.scss */
@import "bootstrap/scss/bootstrap";

body {
  @extend .bg-light;
}

Step 6: Test in Browser

ng serve
  • Open http://localhost:4200
  • Check console for “sql.js initialized for browser”
  • Verify “John Doe” appears in the Home list

Step 7: Build & Test on Native

  1. Build Angular: 
    ng build
  2. Sync Capacitor: 
    npx cap sync
  3. Android: 
    npx cap open android
  4. iOS: 
    npx cap open ios

Look for “Capacitor SQLite initialized for native” in device logs.

Best Practices & Troubleshooting

  • Abstracted Service: SqliteService handles both platforms.
  • Error Handling: Wrap all calls in try/catch.
  • Performance: For bulk inserts/reads, batch your SQL.
  • Persistence: Browser is in-memory—export if you need to save.
  • Encryption: Enable via capacitor.config.ts flags for production.
  • Common Errors:
    • “sql-wasm.wasm not found” ⇒ verify src/assets.
    • “Database not initialized” ⇒ ensure initialize() runs first.

Example: Another Standalone Component

// src/app/components/settings/settings.component.ts
import { Component } from '@angular/core';
import { CommonModule } from '@angular/common';
import { IonicModule } from '@ionic/angular';
import { SqliteService } from '../../services/sqlite.service';

@Component({
  selector: 'app-settings',
  standalone: true,
  imports: [CommonModule, IonicModule],
  template: `
    <ion-header>
      <ion-toolbar><ion-title>Settings</ion-title></ion-toolbar>
    </ion-header>
    <ion-content class="ion-padding">
      <ion-button (click)="addUser()">Add User</ion-button>
    </ion-content>
  `,
  styles: [`
    @import "\~bootstrap/scss/bootstrap";
    ion-content { @extend .container; }
  `],
})
export class SettingsComponent {
  constructor(private sqlite: SqliteService) {}

  async addUser() {
    try {
      await this.sqlite.executeQuery(
        'INSERT INTO users (name) VALUES (?)',
        ['Jane Doe']
      );
      console.log('User added');
    } catch (e) {
      console.error(e);
    }
  }
}
⚠️ **GitHub.com Fallback** ⚠️