Anthony Jekanyika

Posted on Jun 9

Using SQLite (Fast SQL) with Drizzle ORM in Capacitor 8

#sqlite #capacitor #drizzle #fastsql

In this post we will be looking into how to implement SQLite with Drizzle ORM in Capacitor 8 using Nuxt 4 as our Vite wrapper (You can use any other Vite framework). We will be using the Fast SQL for our Android and iOS implementation and SQLocal for the Web implementation. This post will also how to manage Drizzle migrations inside Capacitor 8.

Here is the demo repo of the full implementation to compare with.

Lets get started

Firstly, we need to create our Nuxt 4 app

npm create nuxt@latest capacitor-nuxt-fastsql-drizzle

cd capacitor-nuxt-fastsql-drizzle

Install core Capacitor and CLI

npm i @capacitor/core && npm i -D @capacitor/cli

Install the plugins

npm i @capacitor/app @capacitor/keyboard @capacitor/splash-screen @capacitor/status-bar @capacitor/preferences

Initialize Capacitor with our project details

npx cap init my-app com.example.myapp --web-dir .output/public

Replace my-app with your app name and com.example.myapp with your app ID (reverse domain notation).

Update the capacitor.config.ts file with the following configurations

import type { CapacitorConfig } from '@capacitor/cli';

const config: CapacitorConfig = {
  appId: 'com.example.myapp',
  appName: 'my-app',
  webDir: '.output/public',
  plugins: {
    SplashScreen: {
      launchShowDuration: 2000,
      launchAutoHide: true,
      androidScaleType: 'CENTER_CROP',
      splashFullScreen: true,
      splashImmersive: true,
    },
    Keyboard: {
      resize: 'body',
      resizeOnFullScreen: true,
    },
    StatusBar: {
      overlaysWebView: false,
      style: 'dark',
      backgroundColor: '#ffffffff'
    },
  },
};

export default config;

Install native platforms

npm i @capacitor/ios @capacitor/android

Add the native platform folders

npx cap add ios
npx cap add android

Capacitor will create ios and android folders at the root of your project containing the native projects.

To build the Android project, you need Android Studio. For iOS, you need a Mac with Xcode.

Add these scripts to your package.json

{
  "scripts": {
    "dev": "nuxt dev",
    "build": "nuxt build",
    "generate": "nuxt generate",
    "preview": "nuxt preview",
    "mobile": "npm run generate && npx cap sync",
    "mobile:ios": "npm run mobile && npx cap open ios",
    "mobile:android": "npm run mobile && npx cap open android"
  }
}

Lets test if we did the setup correctly
For Android

npm run mobile:android

For iOS

npm run mobile:ios

Build and run the app

In Android Studio, wait for the project to be ready, and then click on the “Run” button to deploy the app to a connected device or emulator.

In Xcode, set up your signing account to deploy the app to a real device. If you haven’t done this before, Xcode will guide you through the process (note that you need to be enrolled in the Apple Developer Program). Once set up, click on the “Play” button to run the app on your connected device.

If you have been successful you should see this on your device. If not go over the steps again and try again.

Setup

SQLocal makes it easy to run SQLite3 in the browser, backed by the Origin Private File System which provides high-performance read/write access to a SQLite database file stored on the user's device. We will be this for the web implementation of our application.

Install SQLocal, Fast SQL and Drizzle

npm i sqlocal drizzle-orm@1.0.0-beta.22 drizzle-kit@1.0.0-beta.22 @noble/hashes

At the time of writing this blog v1.0.0-beta.22 is the latest version of Drizzle.

In order to persist data to the Origin Private File System, SQLocal relies on APIs that require cross-origin isolation, so we have to globally add the these headers to all the pages of our application:

Cross-Origin-Embedder-Policy: require-corp
Cross-Origin-Opener-Policy: same-origin

We will do this by updating our nuxt.config.ts file to the following:

import sqlocal from 'sqlocal/vite';

export default defineNuxtConfig({
  vite: {
    plugins: [
      sqlocal()
    ],
    server: {
      headers: {
        'Cross-Origin-Opener-Policy': 'same-origin',
        'Cross-Origin-Embedder-Policy': 'require-corp'
      }
    },
    optimizeDeps: {
      exclude: ['@sqlite.org/sqlite-wasm'],
    },
    assetsInclude: [
      "**/*.sql"
    ]
  }
})

For Android and iOS we will be using Fast SQL a high-performance SQLite Database that locally hosts an HTTP server on your device to greatly increase the speed and throughput of our SQL queries.

Install Fast SQL and sync capacitor

npm i @capgo/capacitor-fast-sql
npx cap sync

Add Fast SQL configurations to capacitor.config.ts file

import type { CapacitorConfig } from '@capacitor/cli';

const config: CapacitorConfig = {
  appId: 'com.example.myapp',
  appName: 'my-app',
  webDir: '.output/public',
  plugins: {
    SplashScreen: {
      launchShowDuration: 2000,
      launchAutoHide: true,
      androidScaleType: 'CENTER_CROP',
      splashFullScreen: true,
      splashImmersive: true,
    },
    Keyboard: {
      resize: 'body',
      resizeOnFullScreen: true,
    },
    StatusBar: {
      overlaysWebView: false,
      style: 'dark',
      backgroundColor: '#ffffffff'
    },
    CapacitorSQLite: {
      iosDatabaseLocation: 'Library/CapacitorDatabase',
      iosIsEncryption: true,
      iosKeychainPrefix: 'nuxt-sqlocal-capacitor',
      iosBiometric: {
        biometricAuth: false,
        biometricTitle : "Biometric login for capacitor sqlite"
      },
      androidIsEncryption: true,
      androidBiometric: {
        biometricAuth : false,
        biometricTitle : "Biometric login for capacitor sqlite",
        biometricSubTitle : "Log in using your biometric"
      },
      electronIsEncryption: true,
      electronWindowsLocation: "C:\\ProgramData\\CapacitorDatabases",
      electronMacLocation: "/Volumes/Development_Lacie/Development/Databases",
      electronLinuxLocation: "Databases"
    }
  }
};

export default config;

Sync capacitor

npx cap sync

Create the Drizzle Schema

Create the db/schema.ts file and add the following:

import { sqliteTable, int , text } from 'drizzle-orm/sqlite-core';

export const groceries = sqliteTable('groceries', {
    id: int('id').primaryKey({ autoIncrement: true }),
    name: text('name').notNull(),
    quantity: int('quantity').notNull()
});

See Drizzle Documentation to see how to define your schema using the functions that Drizzle ORM provides.

Instantiate Drizzle

This will allow us to use only one instance of Drizzle in our application and to have it defined globally across our application without needing to import it.

Create the file app/utils/db.ts and copy add the following:

import { SQLocalDrizzle } from 'sqlocal/drizzle';
import { type SqliteRemoteDatabase, drizzle } from 'drizzle-orm/sqlite-proxy';
import { Capacitor } from '@capacitor/core';
import { FastSQL } from '@capgo/capacitor-fast-sql';

let db: SqliteRemoteDatabase<Record<string, never>, any>;
const DATABASE_NAME = "database";
if(Capacitor.getPlatform() === "web") {
    const { driver, batchDriver } = new SQLocalDrizzle('database.sqlite3');
    db = drizzle(driver, batchDriver);
} else {
    try {
        const conn = await FastSQL.connect({ database: DATABASE_NAME });

        const driver = async (sql: string, params: (string | number)[], method: 'get' | 'all' | 'values' | 'run') => {
            try {
                sql = sql.trim().replace(/[\r\n\t]+/gm, " ");

                const result = await conn.execute(sql, params);

                if(method === 'get') {
                    return { rows: result.rows.flatMap(row => Object.values(row)) }
                } else {
                    return { rows: result.rows.map(row => Object.values(row)) }
                }
            } catch (e: any) {
                alert('Error from sqlite proxy server: ' + e.response.data)
                return { rows: [] };
            }
        }

        const batchDriver = async (queries: { sql: string, params: any[], method: 'all' | 'run' | 'get' | 'values'}[]) => {
            try {
                const result = await conn.executeBatch([
                    ...queries.map(({ sql, params }) => ({
                        statement: sql,
                        params
                    }))
                ]);

                return result
            } catch (e: any) {
                alert('Error from sqlite proxy server: ' + e);
                throw e;
            }
        }

        db = drizzle(driver, batchDriver);
    } catch(error) {
        alert(error)
    }
}

export { db }

Add Pages

Replace the contents of your app/app.vue file with this

<template>
  <NuxtPage />
</template>

Create the folder app/pages and then add the file app/pages/index.vue and add the following contents

<template>
    <main>
        <h2>Create Groceries</h2>
        <form @submit.prevent="createGroceries">
            <div>
                <input v-model="name" placeholder="Enter the name" required />
            </div>
            <div>
                <input v-model="quantity" type="number" placeholder="Enter the quantity" validate required />
            </div>
            <button type="submit">Submit</button>
        </form>

        <h2>Grocery List ({{ grocery_list.length }})</h2>
        <div>
            <input v-model="search_query" placeholder="Search" validate required />
        </div>
        <table>
            <thead>
                <tr>
                    <th>ID</th>
                    <th>Name</th>
                    <th>Quantity</th>
                </tr>
            </thead>
            <tbody>
                <tr v-for="{ id, name, quantity} of grocery_list">
                    <td>{{ id }}</td>
                    <td>{{ name }}</td>
                    <td>{{ quantity }}</td>
                </tr>
            </tbody>
        </table>
    </main>
</template>

<script setup lang="ts">
    import { like } from 'drizzle-orm';
    import { groceries } from "~~/db/schema";
    import { migrate } from '~~/db/migrator'

    const name = ref<string>("");
    const quantity = ref<number>(0);
    const search_query = ref<string>();
    const grocery_list = ref<typeof groceries.$inferInsert[]>([]);

    watch(search_query, (value) => search(value))

    onMounted(async () => {
        await migrate()
        grocery_list.value = await db.select().from(groceries)
    })

    const createGroceries = async () => {
        try {
            await db.insert(groceries).values({
                name: name.value,
                quantity: quantity.value
            })

            grocery_list.value = await db.select({
                id: groceries.id,
                name: groceries.name,
                quantity: groceries.quantity
            })
            .from(groceries)

            // Clear the form
            name.value = "";
            quantity.value = 0;
        } catch (error) {
            alert(error)
        }
    }

    const search = async (query: string | undefined) => {
        if(search_query.value) {
            grocery_list.value = await db.select()
                .from(groceries)
                .where(like(groceries.name, `%${ query }%`))
        } else {
            grocery_list.value = await db.select().from(groceries)
        }
    }
</script>

Drizzle Migrations

When ever you change the Drizzle schema db/schema.ts. You need to create the migration files. You can do this by first creating the drizzle.config.ts file in your project's root directory and then adding the following:

import { defineConfig } from 'drizzle-kit'

export default defineConfig({
  out: './db/migrations',
  schema: './db/schema.ts',
  dialect: 'sqlite'
})

Update the scripts in your package.json to the following:

{
  "scripts": {
    "dev": "nuxt dev",
    "build": "nuxt build",
    "generate": "nuxt generate",
    "preview": "nuxt preview",
    "mobile": "npm run generate && npx cap sync",
    "mobile:ios": "npm run mobile && npx cap open ios",
    "mobile:android": "npm run mobile && npx cap open android",
    "drizzle:generate": "drizzle-kit generate --dialect=sqlite --driver=durable-sqlite --schema=./db/schema.ts --out=./db/migrations"
  }
}

Then to generate the migration files you need to run the following:

npm run drizzle:generate

This will create SQL migration files in the db/migrations directory.

Drizzle Client-Side Migration

Drizzle does not have an in-built system to track migration files on the client-side of your add as drizzle-kit, the Drizzle migration management CLI tool, doesn't run on the client-side. We are going to have to implement our own custom solution to apply the migration files and keep track of the migrations that have already been applied in a _drizzle_migrations table. To do this we need to create the db/migrator.ts file and add the following:

import { sha256 } from "@noble/hashes/sha2.js";
import { Buffer } from "buffer";
import { sql } from "drizzle-orm";
import { db } from "../app/utils/db";
import migration_summary from "./migrations/migrations.js"

export const migrate = async () => {
    // Create the migration table to keep track of all the applied migrations
    const MIGRATIONS_TABLE_NAME = sql.identifier("_drizzle_migrations");

    await db.run(
        sql`
          CREATE TABLE IF NOT EXISTS ${ MIGRATIONS_TABLE_NAME } (
            id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
            name TEXT NOT NULL,
            hash TEXT NOT NULL,
            created_at INTEGER NOT NULL
          );
        `,
    );

    type AppliedMigration = {
        id: number,
        name: string,
        hash: string,
        created_at: number
    }

    const appliedMigrations = (await db.all(
        sql`SELECT * FROM ${ MIGRATIONS_TABLE_NAME };`,
    )) as AppliedMigration[];

    await db.transaction(async (tx) => {
        for(let migration_name of Object.keys(migration_summary.migrations)) {
            // Get migration file a raw text
            let migration_sql = (await import(`./migrations/${ migration_name }/migration.sql?raw`)).default;

            migration_sql = migration_sql.trim().replace(/[\r\n\t]+/gm, " ");

            // Generate a hash of the migration file
            const hash = Buffer.from(sha256(Buffer.from(migration_sql))).toString("hex")

            // Check if the migration file was applied to the SQLite DB  
            if (appliedMigrations.some((applied_migration) => applied_migration?.hash === hash)) continue;

            // Now run the migration on to the SQLite DB
            try {
                await tx.run(migration_sql)
            } catch(error) {
                console.error(error)
            }

            // Log this migration as applied onto the SQLite DB
            await tx.run(
                sql`
                    INSERT INTO ${ MIGRATIONS_TABLE_NAME } ("name", "hash", "created_at") VALUES (
                    ${ sql.raw(`'${ migration_name }'`) },
                    ${ sql.raw(`'${ hash }'`) },
                    ${ sql.raw(`${ Date.now() }`) }
                    );
                `,
            );
        }
    });
}

Deploy

Now that we are done creating our app, let us generate the static files for our app and deploy them to either Android Studio or Xcode

For Android Studio

npm run mobile:android

For Xcode

npm run mobile:ios

If you have been successful, you should have an app that you can use to store grocery lists like this:

Checkout the demo repo with everything that we have discussed above.

Feel free to ask me any questions in the comments section below and I will try to respond to them as quickly as I can. Thank you guys and have a great day.

Reference List

DEV Community