Improve User Experience and First-Time App Load with Expo and libSQL

Last edited: Tue Oct 22 2024

In mobile apps, user experience (UX) plays a critical role in user retention. Studies show that 53% of users abandon a mobile app if it takes longer than 3 seconds to load.

By embedding a local SQLite database, such as SQLite, directly into the app using Expo, you can drastically improve first-time load speeds and overall performance.

Coupled with Turso for real-time syncing, this setup ensures users get instant access to data on their first load, even offline, resulting in a smoother and more responsive experience.

This approach minimizes wait times and keeps users engaged from the very first interaction.

LibSQL

LibSQL is an open-source replacement for SQLite, offering enhanced features and performance optimizations. It introduces built-in support for distributed databases, enabling replication and synchronization across multiple nodes for improved scalability. Additionally, it provides real-time syncing capabilities, allowing seamless synchronization between local and remote databases.

libSQL on Expo

There are plenty of libraries for Expo that support SQLite. Expo-sqlite has become the default library for SQLite in the Expo ecosystem, lightweight and easy to use but lacks the advanced features found in other libraries.

There are plenty of libraries for SQLite in the Expo ecosystem, but expo-sqlite has become the go-to choice due to its lightweight nature and ease of use.

Op-SQLite

I came across OP-SQLite, known as the fastest SQLite library for React Native, created by Ospfranco. It has some cool features, like Async Operations, where some queries run in the background on a separate thread, so they don't slow down the app's UI. It also supports libSQL for remote syncing.

Let's Learn How to Use OP-SQLite

I assume you already have a Expo project set up. You will need to add @op-engineering/op-sqlite (version 7.3.0 or higher) or use project code : https://github.com/expo-starter/expo-opsqlite-libsql-turso

bun add @op-engineering/op-sqlite drizzle-orm @libsql/client

Next, configure libSQL by adding the following section to your package.json:

"op-sqlite": { "libsql": true }

A walkthrough of the code

Let’s take a look at some of the key aspects of this app, with the intention of understanding how libsql is helping out.

To start out, all the db schema is defined with drizzle-orm is in a single file, db/schema.ts, and all the data is stored in a single SQLite table called cards, which contains simple columns:

import { sql } from "drizzle-orm";
import { sqliteTable, text, integer } from "drizzle-orm/sqlite-core";
 
export const cardTable = sqliteTable("cards", {
  id: t.integer().primaryKey({ autoIncrement: true }),
  question: t.text().notNull(),
  answer: t.text().notNull(),
  archived: t
    .integer({
      mode: "boolean",
    })
    .default(false),
  createdAt: t.text("created_at").default(sql`(CURRENT_TIMESTAMP)`),
});

Let's create a Drizzle configuration to set up our database. This configuration file will define the necessary settings for Drizzle to interact with our Turso database.

First, create a new file called drizzle.config.ts in the root of your project:

import type { Config } from "drizzle-kit";
 
export default {
  schema: "./db/schema.ts",
  out: "./db/migrations",
  dialect: "turso",
  casing: "snake_case",
  dbCredentials: {
    url: process.env.EXPO_PUBLIC_TURSO_DATABASE_URL!,
    authToken: process.env.EXPO_PUBLIC_TURSO_AUTH_TOKEN,
  },
} satisfies Config;

You can choose to run either a local or remote database. For a local database, you can use the following commands:

brew install tursodatabase/tap/turso
turso dev

Let’s populate our database with seed data. The script will generate 10,000 rows for demonstration purposes.

import { createClient } from "@libsql/client";
import { drizzle } from "drizzle-orm/libsql";
import { cardTable } from "./schema";
import { faker } from "@faker-js/faker";
 
const remoteDb = createClient({
  url: process.env.EXPO_PUBLIC_TURSO_DATABASE_URL!,
  authToken: process.env.EXPO_PUBLIC_TURSO_AUTH_TOKEN,
});
 
export const db = drizzle(remoteDb);
 
async function seed() {
  const cards = [];
 
  for (let i = 0; i < 10000; i++) {
    cards.push({
      question: faker.lorem.sentence(),
      answer: faker.lorem.sentence(),
      archived: false,
    });
  }
 
  try {
    const result = await db.insert(cardTable).values(cards);
    console.log(`Successfully inserted ${result.rowsAffected} cards.`);
  } catch (error) {
    console.error("Error seeding database:", error);
  }
}
 
seed().catch(console.error);

Run the script to populate the database

bun db/seed.ts

import { drizzle } from "drizzle-orm/op-sqlite";
import {
  openSync,
} from "@op-engineering/op-sqlite";
 
export const sqlite = openSync({
  name: "local.db",
  url: process.env.EXPO_PUBLIC_TURSO_DATABASE_URL!,
  authToken: process.env.EXPO_PUBLIC_TURSO_AUTH_TOKEN,
  syncInterval: 1000,
});
 
try {
  sqlite.sync(); // Make the initial sync from the remote to the local database
} catch (e) {
  console.log(e);
}
export const db = drizzle(sqlite);

The configuration above sets up a local-first database architecture using Turso's embedded replicas and op-sqlite :

• Remote Sync with Turso: This local database is synchronized with a remote Turso database, leveraging Turso's embedded replicas technology.

• Read Performance Optimization: All database read operations are executed against the local SQLite database, ensuring extremely fast data access and reducing network latency.

• Write Synchronization: Write operations are sent to the remote Turso database and then automatically synchronized back to the local replica, maintaining data consistency across devices.

• Offline Capability: This architecture inherently supports offline functionality, as the local database can operate independently of network connectivity.

• Automatic Conflict Resolution: Turso's embedded replicas handle synchronization conflicts, ensuring data integrity across multiple devices or users.

Here is the home screen component that fetches and displays the cards from the local database:

import * as React from "react";
import { FlashList } from "@shopify/flash-list";
import { useQuery } from "@tanstack/react-query";
import { db, cardTable } from "@/lib/db";
import { eq, desc } from "drizzle-orm";
import { MemoryCard } from "@/components/card";
 
export default function CardsScreen() {
  const { data } = useQuery({
    queryKey: ["cards", "list"],
    queryFn: () => db.select().from(cardTable).orderBy(desc(cardTable.createdAt))
  });
  const renderItem = React.useCallback(
    ({ item }: { item: CardSchemaType }) => <MemoryCard {...item} />,
    [],
  );
  return (<FlashList
        estimatedItemSize={49}
        data={cards}
        renderItem={renderItem}
        keyExtractor={(item) => item.id}
      />
    </View>
  );
}

For testing :

bun android
bun ios

Demonstration

In the video below, I demonstrate the app loading 10,000 cards from the local database with instant performance. The demonstration showcases how the app:

1. Loads a large dataset (10,000 cards) almost instantaneously from the local SQLite database.
2. Maintains smooth scrolling and interaction despite the large number of items.
3. Syncs data seamlessly across different devices and platforms in real-time.

You can find the full code on Github.

Sounds interesting?

If this sounds interesting and you'd like help with your project, please reach out.