SQLite + Drizzle

Introduction

There are many flavours of AppKickstarter, each with a different storage layer. In this section, we'll talk about the local SQLite variation.

This flavour is ideal for:

• Local SQLite database, enabling an local-first architecture.
• High performance: powered by op-sqlite, the fastest sqlite library for React Native.
• Interfaced with DrizzleORM for streamlined schema management, migration, and usage.

Getting Started

After your purchase, you should have received an invite to all of the repos. This flavour of AppKickstarter is in the appkickstarter-template-sqlite repo.

Follow the steps in the Installation guide, except using appkickstarter-template-sqlite.git repo instead of the base appkickstarter-template.git.

Everything related to the database should be in packages/db:

File Structure

packages/db/
├── drizzle/             # Auto-generated migrations by `drizzle-kit generate`. DO NOT EDIT THESE FILES.
│   └── ...
├── drizzle.config.ts    # The primary Drizzle configuration file.
├── index.ts             # Initializes the database and exports the main `db`
├── schema.ts            # The Drizzle schema file. Defines the schema of your database using TypeScript.
│                        # Run `npx drizzle-kit generate` to generate migrations into `./drizzle`.
└── package.json

Drizzle

Drizzle is an ORM that makes it easier for developers to manage and interact with the database. Without it, we'd have to write raw SQL statements without type safety. Eventually, teams usually end up writing their own implementation of an ORM, so it's generally advised to use one from the start.

Defining the Database + Management

Drizzle lets you define a schema in TypeScript with various models and properties supported by the underlying database. When you define your schema, it serves as the source of truth for future modifications in queries (using Drizzle-ORM) and migrations (using Drizzle-Kit). This is known as the "code-first approach".

For example, your schema.ts may look like:

import { sqliteTable, integer, text } from "drizzle-orm/sqlite-core"

export const users = sqliteTable('users', {
  id: integer(),
  first_name: text(),
});

The above is a declarative definition of our database schema.

With the power of drizzle-kit, Drizzle will generate the SQL statements to migrate your database:

cd packages/db
npx drizzle-kit generate

This will produce the migrations under ./drizzle/*. When the app starts up, it automatically runs these migrations. See apps/native/app/_layout.tsx:

import { db } from "@repo/db";
import migrations from "@repo/db/drizzle/migrations";
import { useMigrations } from "drizzle-orm/op-sqlite/migrator";

function RootLayout() {
	const { success, error } = useMigrations(db, migrations);
	...

As your project grows, you may opt for splitting your schema into multiple files. See the Drizzle - Schema Declaration docs for additional information.

We highly recommend reading the Drizzle documentation:

• https://orm.drizzle.team/docs/column-types/sqlite: defining your schemas
• https://orm.drizzle.team/docs/drizzle-kit-generate: drizzle-kit generate options
• https://orm.drizzle.team/docs/connect-op-sqlite: our setup with OP-SQLite

Querying the Database

One of the benefits of using an ORM is that we get type-safety when querying the database. This means our IDE (via the TypeScript Language Server) can tell us what columns are available in each table, what values to expect, etc.

For example, we can use the query builder to read from the database:

import * as schema from './schema';
import { drizzle } from 'drizzle-orm/...';

const db = drizzle({ schema });

const result = await db.query.users.findMany({
	with: {
		posts: true
	},
});

result would return:

[{
	id: 10,
	name: "Dan",
	posts: [
		{
			id: 1,
			content: "SQL is awesome",
			authorId: 10,
		},
		{
			id: 2,
			content: "But check relational queries",
			authorId: 10,
		}
	]
}]

You can also use the Select API to read instead, the Insert syntax to add new records, the Update syntax to update existing records, etc.

await db.transaction(async (tx) => {
  await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100.00` }).where(eq(users.name, 'Dan'));
  await tx.update(accounts).set({ balance: sql`${accounts.balance} + 100.00` }).where(eq(users.name, 'Andrew'));
});

OPSQLite

OPSQLite was chosen as the SQLite library due to it's high performance. It is the fastest SQLite library available for React Native, and by a large margin.

Setup

The main setup is in packages/db/index.ts:

import { type DB, open } from "@op-engineering/op-sqlite";
import { drizzle } from "drizzle-orm/op-sqlite";

const opsqliteDb = open({
	name: "appkickstarter.db",
	encryptionKey: key.value,
});

export const db = drizzle(opsqliteDb);

Replace with expo-sqlite

If you run into any issues, and want to use an Expo-supported library instead, you can easily replace the op-sqlite driver with expo-sqlite:

cd packages/db
pnpm remove @op-engineering/op-sqlite

pnpm remove @op-engineering/-opsqlite -F @<your-org>/db

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

import { drizzle } from "drizzle-orm/expo-sqlite";
import { openDatabaseSync } from "expo-sqlite";

const expo = openDatabaseSync("appkickstarter.db");
const db = drizzle(expo);

"plugins": [
  [
    "expo-sqlite",
    {
      "enableFTS": true,
      "useSQLCipher": true,
      "android": {
        // Override the shared configuration for Android
        "enableFTS": false,
        "useSQLCipher": false
      },
      "ios": {
        // You can also override the shared configurations for iOS
        "customBuildFlags": ["-DSQLITE_ENABLE_DBSTAT_VTAB=1 -DSQLITE_ENABLE_SNAPSHOT=1"]
      }
    }
  ]
]

These steps are from the offical Expo setup guide: https://docs.expo.dev/versions/latest/sdk/sqlite/