Made withIonicTutorial

How to use SQLite in Ionic with Capacitor

S

Simon Grimm

Creator on the Ionic channel

Watch on YouTube ↗

26K viewsAug 8, 202315:17 runtime

Learn how to integrate a local SQLite database into your Ionic application using the latest Capacitor 5 community plugin. This tutorial provides a step-by-step guide for building a native app with persistent data storage. You'll see how to set up the database service in Angular…

Chapters

#ionic#tutorial#frontend

Project Setup

To use SQLite in an Ionic application, you need to install the Capacitor community plugin for SQLite and the Capacitor Splash Screen plugin.

npm install @capacitor-community/sqlite @capacitor/splash-screen

@capacitor-community/sqlite: The main plugin for database functionality.
@capacitor/splash-screen: Used to delay hiding the splash screen until the database is initialized.

Database Service (`database.service.ts`)

All database logic should be encapsulated within a dedicated Angular service.

Key Components:

State Management: The service uses an Angular Signal to hold the current list of users, making it reactive.

import { WritableSignal, signal } from '@angular/core';
// ...
private users: WritableSignal<User[]> = signal([]);

Connection Setup: An SQLiteConnection object is created by passing the Capacitor plugin to its constructor.

import { CapacitorSQLite, SQLiteConnection, SQLiteDBConnection } from '@capacitor-community/sqlite';
// ...
private sqlite = new SQLiteConnection(CapacitorSQLite);
private db!: SQLiteDBConnection;

Plugin Initialization

The most critical part is ensuring the database is ready before the app attempts to use it. This prevents race conditions.

Important
The Splash Screen Trick: Use the app's initial startup process to initialize the database. By awaiting the database setup before hiding the splash screen, you guarantee the database connection is established before any UI components try to read or write data.

In app.component.ts:

import { Component } from '@angular/core';
import { SplashScreen } from '@capacitor/splash-screen';
import { DatabaseService } from './services/database.service';

@Component(/* ... */)
export class AppComponent {
  constructor(private database: DatabaseService) {
    this.initializeApp();
  }

  async initializeApp() {
    await this.database.initializePlugin();
    SplashScreen.hide();
  }
}

Initialization Logic

In the DatabaseService, the initializePlugin() method performs several key steps:

Create Connection: this.sqlite.createConnection(...) with database name and settings.
Open Database: this.db.open()
Define & Execute Schema: A CREATE TABLE IF NOT EXISTS SQL statement is executed to set up the data structure.
Load Initial Data: A loadUsers() function is called to fetch all current records from the database and populate the users signal.

CRUD Operations

Standard SQL queries are used for Create, Read, Update, and Delete operations.

Create: Uses an INSERT INTO ... statement.
Read: A SELECT * FROM ... query is used in loadUsers() to refresh the local state.
Update: Uses an UPDATE ... SET ... WHERE ... statement.
Delete: Uses a DELETE FROM ... WHERE ... statement.

Tip
After every CUD (Create, Update, Delete) operation, call the loadUsers() function again. This re-queries the database and updates the Angular Signal, which automatically refreshes the UI.

UI Integration (`home.page.ts`)

The UI components inject the DatabaseService to interact with the data.

Displaying Data: The template uses the signal returned by the service. Calling the signal as a function, users(), retrieves its current value.
```
<ion-list>
  <ion-item *ngFor="let user of users()">
    
  </ion-item>
</ion-list>
```
Modifying Data: UI events (like button clicks) call the public methods on the DatabaseService (addUser, updateUser, etc.).

Project Setup & Plugin Installation

Hey everyone, what's up? This is Simon from the Ionic Academy, back with a new updated tutorial on how you can use SQLite in your Ionic application using Capacitor. So there were a lot of questions about SQLite, the last tutorial is already a bit outdated so we're doing this with Capacitor 5, and we're going to go through the basic usage of SQLite with Capacitor.

Quick note up front, there is an in-depth course inside the Ionic Academy on using SQLite where we also discuss things like debugging the SQLite database on both iOS and Android, how you can get that file, and how you can even add web support to use this in the browser. That was actually pretty epic, but it takes a few steps, so today we will just focus on the basic usage.

Now I've already started a blank new Ionic application using Angular, but you could easily do this with React or Vue as well. You could probably just put in my code into ChatGPT and it would be transformed to React.

Then we need the Capacitor Community SQLite plugin, and I will also add the Capacitor Splash Screen plugin for a specific reason that I'm going to tell you in about 8 minutes. Alright, I will also generate a service for my Angular application, and then I'm just going to do the native build because we're going to have to test this natively. As I said, I won't do the web stuff in here. So with that being said, let's get started.

Creating the Database Service

Actually, let's start by adding a bit of stuff above here in our database service. So this will be the database name and this is just an interface for a user. So we're just going to do like adding little users, setting them active, displaying them in a list. Could also be to-dos, products, notes, could be whatever. I just had to pick some topic, don't blame me.

Alright, in the database service, let's start by defining a private... no, a privateDB that would be even more private, right? An SQLiteConnection that's going to be new SQLiteConnection and then we pass in the CapacitorSQLite plugin here. So this defines our SQL connection.

On top of that, we also need the database. We're going to define this later, so let's just define SQLiteDBConnection and yes, this will be defined. Angular, just trust me.

Also, I want to keep track of my users. In the past I would have used... I would have used, what would I have used? A Behavior Subject. But we're cool kids, we're using Angular 16, so let's also use a Signal.

Now I can hear you scream, "Oh Simon, why are you doing the new things? We hate this!" No, it's cool, it's cool. And actually, it's pretty easy. So it's going to be of the type User[], and if I don't watch out, I'm doing mistakes. So that's better. A signal of the type User[], and if you want to, you can even add the type in here. So that would be a WritableSignal from Angular Core with type User[]. So now if you have that line in your code, you're absolutely on the... it's not bleeding edge, it's not... cutting edge maybe. But you're just so cool.

Initializing the Database Connection & Schema

Alright, the first function I'm going to set up is like an initializer. So yeah, we could call it init. Also I could call it initializePlugin. I think that's more appropriate in this case. So what we want to do is we want to initialize the connection to our database. And to do this, we now create a connection. We use with the createConnection function a few values. So first one is database, that's going to be the name for my database. I call it my-user-db, could be of course anything else. Encrypted? No, I don't want to use encrypted today, just make things harder to debug. Mode: we have no-encryption. Version we are setting this to 1 and readOnly we're setting this to false.

So this is the database we want to run our transactions on. And then we have to call this.database.open() to open a SQLite database connection. If you open it, you should probably also close it at some point, but since we just have the app open, I will keep it like this.

Now what I also want to do in here is of course define my table. In the past, we had a different tutorial here on building a SQLite Ionic app with Capacitor in which we actually used JSON to initialize the database. So that way would actually still work, you can check this out on my blog. You could have that file as well in your Ionic application and just initialize from a JSON file. But it's somewhat of like a little bit special case, I don't know if a lot of people actually using this import/export JSON stuff. So instead, let's just define the schema by creating a simple SQL statement: CREATE TABLE IF NOT EXISTS users with an ID, name, and active. I also found out that there's actually no boolean for SQLite, so just like the basic stuff. So we're just going to use active 0 or 1 in this case.

Now to run this transaction, Copilot already gives us the code. We're going to have to execute the SQL command and that's going to be how we do most of the other stuff in our application as well. Once we've done this, I also want to load my initial users. So I'm going to call this.loadUsers(). It's always best when not prepared for your own jokes. So I want to load my users in here simply because then we can set the data to our... oh Copilot, you're really smart. Yeah, mostly correct. So we're getting the users by calling this.db.query. So that would be our query function, instead of execute which would just execute this statement. Here we can also pass in values. I want to SELECT * FROM users of course, and then I don't call next but set on users.values. And this can be undefined, so let's also add a fallback. If users.values is not defined, we're just going to set our users to an empty array.

So while we initialize the plugin, so we set up the connection, we create the table if it doesn't exist, we execute this, we load the users, and finally we return true. We can actually do this before because we are not really worried about this one.

The Plugin Initialization Trick

Now we're coming back to, was it 8 minutes? It was almost 8 minutes. We're coming back to what I told you before, why we're using the splash screen plugin. So in order to not make any kind of loading request or even adding requests in your application up too early while the plugin is not even initialized, we have to make sure that this part here is finished. How do we do this?

We can do this with a little trick. If you enjoy tips and tutorials like this, you will love what I've created inside the Ionic Academy. The Ionic Academy is the best place to learn Ionic fast, with practical courses ranging from UI development to connecting to APIs or using services like Firebase. On top of that, you get support in our private Discord server, you get a weekly email about all the things going on in the Ionic ecosystem, so join today at ionic.academy.com.

So in my app component, by the way, I'm using standalone modules, but that's not like super important here. In my app component, which is the first thing that runs, I'm going to inject the database, DatabaseService, and I'm going to do a little async initApp. Yeah, I could also... could I use ngOnInit? I don't know, I actually want to do this more in the earliest possible way and that would be the constructor, so I'm doing it from here. And in my initApp, I'm going to call this.database.initializePlugin(). So nothing happens until it is initialized. And only then I'm going to hide the splash screen. So the splash screen stays open. It won't stay open for like seconds, it's just like probably 200 milliseconds or even less, but it's enough to make sure that we don't have any other race condition going on. So this is the simple trick, and it took us 8 minutes to do it. Oh, that was like a perfect estimation.

Implementing CRUD Operations

Okay, now let's add three more functions to the service, and they're going to be the basic CRUD operations. I can also add like here, CRUD stuff. So everything that we need to do. addUser, INSERT INTO users, and then we have the values, and then we execute the query and we update the users. And the same pattern for all three functions. So updateUserById, we're just setting the active field, we're loading it, and then loadUsers will simply update the data. And again, deleteUserById will delete it.

Now with that in place, and of course you can have like a lot more advanced queries in your application. Like if you're good with SQL and know more than INSERT, UPDATE, and DELETE like me, then you can of course do a lot more. But anyway, let's continue to the home page.

Building the Home Page UI

And on my home page, I want to get... that's not the one, this is the one I wanted. So let's inject my database in here. private database: DatabaseService. And to get the users, we're going to use the signal we defined. So this.database.users and we call it as a function. Oh yeah, we have to... I think we have to call getUsers, right? Yeah, I made this field private, so it's not changeable from the outside world. So let's add a function here to return... this.users. users does not exist. Oh yeah, it should be called users. Okay, then yes, I'm sorry about that one, but that one was important. So we can now just call getUsers here and that should be fine. I feel like this should actually return it like this. Well, we're going to try it like this. Let's try it like this. Normally we have to call the signal in another way, so I'm not exactly sure what we're going to get back here.

Nonetheless, let's also add a field to add a new user, so we're going to just need a little ngModel here, and then we need three functions to create the user, to update the user, or to delete the user. I'm going to import my interface, and this will take care of all the rest. And now for the home page, I will just quickly bring in some easy code which iterates our users.

Can't bind to 'ngModel'. Yep, that's actually a nice error. So if you're using standalone components with Ionic, you have to make sure that you import all the important stuff. So if you're using ngModel, it's the FormsModule, and I think we probably also use the CommonModule. Then I can just iterate my users. Now I'm calling this correctly, so here we have the writable signal, and by calling it, we're actually getting the right value. We can update the user, we get the username, and we have a button to delete the user. Now let's give this one a try.

Testing the App on iOS

Let's see. We're going to go with ionic cap build ios. We can also do run ios. I actually had some problems with the live reload. I'm not sure if it was about the plugin, if it was about Angular 16 and standalone components, I just wasn't really lucky with that one. So I'm just going to deploy it directly to my simulator here. So let's see. We got some logs in here, we can probably delete this.

Let's give it a try. First user, we're going to add that one, and it appears. And we also see some nice logs in here, values. You can even do some cool stuff if you're using a signal. So you could do something like effect. Use effect was React, I'm sorry. So I could use something like, users changed, and then I could do probably... I don't need to call users value. Just a few more brackets. No, it is not useEffect, sorry, it is just an effect that is called when the signal changes. So that effect runs. You can also do computed where you compute two signals into each other. But this is not a tutorial about signals. It's maybe the first time we're actually using signals here with Ionic and Angular.

But nonetheless, we should also now see... I added something last time, and now that the app comes up, we get the data because it was stored. So we can also put in more data, we can update the user, we can add more users and delete them. And of course, whatever I put in, if I now reload the application, it will come back up because it's stored in SQLite.

Key Takeaways & Conclusion

And again, if you need more information about using SQLite, check out the course in the Ionic Academy. Join us. You can also find the full source code for this tutorial in the Ionic Academy where we also do the debugging fun, where we add web support, so then we could run that application exactly like it is on the web with just a few tiny changes.

But nonetheless, I hope this gave you a first introduction in how you can use SQLite currently with Capacitor. It's a lot easier than it was in the past. So this was Capacitor 5. Now we don't need to change anything in the native classes and files what we had to do before. Just make sure that your plugin is initialized correctly, that you go through this, and you don't make any calls before your database connection is established. So that's the really most important part that you should take away from this quick win.

And of course if you're not yet subscribed, hit the subscribe button below the video and please click like so this video reaches more people. And I will hopefully catch you in the next video. So until then, happy coding. Simon.