SQLite

SQLite is supported by ShipAny. Follow the steps below to get started quickly.

Quick Start

Install SQLite

Install SQLite for your operating system (search and follow the appropriate installation guide). After installation, create a database file from the command line:

sqlite3 data/local.db

You can also skip this step. After configuring SQLite in the project, the database file will be created automatically when you run the migration commands.

Configure the database

In your project environment variable files, fill in the SQLite-related configuration.

Local development: .env.development
Production: .env.production

DATABASE_PROVIDER = "sqlite"
DATABASE_URL = "file:data/local.db"
DB_SCHEMA_FILE = "./src/config/db/schema.sqlite.ts"
DB_MIGRATIONS_OUT = "./src/config/db/migrations_sqlite"

DATABASE_PROVIDER: Required. Fixed value sqlite
DATABASE_URL: Required. The file path to your SQLite database. Format: file:file-path, where the file: prefix is fixed and file-path is customizable. You can use a relative path to point to any location in the project. We recommend using a .db / .sqlite suffix.
DB_SCHEMA_FILE: Required. Fixed value ./src/config/db/schema.sqlite.ts
DB_MIGRATIONS_OUT: Optional. Output path for migration files. Default is ./src/config/db/migrations. You can choose different output paths based on database type.

Modify the database Schema file

Using SQLite database, the data table structure is defined in the ./src/config/db/schema.sqlite.ts file, you need to export the data table from this file.

Open ./src/config/db/schema.ts file, modify the content to:

export * from './schema.sqlite';

Run migrations

Run the following commands to migrate your database tables:

pnpm db:generate
pnpm db:migrate

Manage the database

Run the following command locally:

pnpm db:studio

This will open the database admin panel.

Customization

Update tables

If you need to add new tables or modify table columns, edit ./src/config/db/schema.sqlite.ts to update the schema.

After making changes, run the following commands to generate and apply migrations:

pnpm db:generate
pnpm db:migrate

Update database connection

If you need to adjust connection parameters (e.g., custom options, connection pooling, etc.), update the logic in ./src/core/db/sqlite.ts.

By default, data operations use the db() instance exported from ./src/core/db/index.ts. Its return type is any, so in some cases you may not get good type hints.

import { db } from '@/core/db';
import { user } from '@/config/db/schema';

const [result] = await db().select().from(user).limit(1);

If you want a strongly typed database instance, you can use the following approaches when operating on data:

import { dbSqlite } from '@/core/db';
import { user } from '@/config/db/schema';

const [result] = await dbSqlite().select().from(user).limit(1);

Or:

import { getSqliteDb } from '@/core/db/sqlite';
import { user } from '@/config/db/schema';

const [result] = await getSqliteDb().select().from(user).limit(1);

Deploy to Cloudflare Workers

When deploying the project on Cloudflare Workers, using SQLite is not supported yet.

You can use a SQLite-compatible database service such as Turso.