Turso LibSQL Testing Guide: Edge-Native SQLite Testing
Turso has emerged as a compelling database option for teams building edge-first applications. Built on LibSQL — an open-source fork of SQLite — Turso brings SQLite's simplicity and zero-latency reads to the edge, with built-in replication and branching for production workloads. But testing applications that depend on Turso requires a thoughtful approach, since you probably don't want your test suite making real network calls to a remote LibSQL server.
This guide covers testing patterns for LibSQL-based applications — from fast unit tests using in-memory SQLite to CI-safe integration tests with local LibSQL files.
Understanding the LibSQL Client
Turso's official client (@libsql/client) supports several connection modes that are directly useful for testing:
import { createClient } from '@libsql/client';
// Production: remote Turso database
const prodClient = createClient({
url: 'libsql://your-db-name.turso.io',
authToken: process.env.TURSO_AUTH_TOKEN,
});
// Test: local file-based SQLite (no network)
const fileClient = createClient({
url: 'file:./test-database.db',
});
// Test: in-memory SQLite (fastest, ephemeral)
const memoryClient = createClient({
url: ':memory:',
});The url: ':memory:' mode is your primary tool for unit and integration tests. It creates a full SQLite database in memory — no files, no cleanup required.
Project Structure for Testable LibSQL Apps
The key to making LibSQL apps testable is injecting the client rather than importing a singleton:
// src/database/client.ts
import { createClient, Client } from '@libsql/client';
export type DbClient = Client;
export function createDbClient(): DbClient {
if (process.env.NODE_ENV === 'test') {
return createClient({ url: ':memory:' });
}
return createClient({
url: process.env.TURSO_DATABASE_URL!,
authToken: process.env.TURSO_AUTH_TOKEN,
});
}
// Singleton for production use
let _client: DbClient | null = null;
export function getClient(): DbClient {
if (!_client) _client = createDbClient();
return _client;
}// src/repositories/user.repository.ts
import { Client } from '@libsql/client';
export class UserRepository {
constructor(private client: Client) {}
async migrate(): Promise<void> {
await this.client.executeMultiple(`
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
email TEXT UNIQUE NOT NULL,
name TEXT NOT NULL,
role TEXT DEFAULT 'user',
created_at TEXT DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS sessions (
id TEXT PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
expires_at TEXT NOT NULL
);
`);
}
async createUser(data: { email: string; name: string; role?: string }) {
const result = await this.client.execute({
sql: `
INSERT INTO users (email, name, role)
VALUES (:email, :name, :role)
RETURNING *
`,
args: { email: data.email, name: data.name, role: data.role ?? 'user' },
});
return result.rows[0];
}
async findByEmail(email: string) {
const result = await this.client.execute({
sql: 'SELECT * FROM users WHERE email = :email LIMIT 1',
args: { email },
});
return result.rows[0] ?? null;
}
async findAll(filters?: { role?: string }) {
let sql = 'SELECT * FROM users';
const args: Record<string, string> = {};
if (filters?.role) {
sql += ' WHERE role = :role';
args.role = filters.role;
}
sql += ' ORDER BY created_at DESC';
const result = await this.client.execute({ sql, args });
return result.rows;
}
async updateRole(id: number, role: string) {
await this.client.execute({
sql: 'UPDATE users SET role = :role WHERE id = :id',
args: { role, id },
});
}
async deleteUser(id: number) {
await this.client.execute({
sql: 'DELETE FROM users WHERE id = :id',
args: { id },
});
}
}In-Memory Testing with LibSQL
With the repository taking a client as a constructor argument, tests become straightforward:
// src/repositories/user.repository.test.ts
import { describe, it, expect, beforeEach } from 'vitest';
import { createClient } from '@libsql/client';
import { UserRepository } from './user.repository';
describe('UserRepository', () => {
let repo: UserRepository;
beforeEach(async () => {
// Fresh in-memory database for each test
const client = createClient({ url: ':memory:' });
repo = new UserRepository(client);
await repo.migrate();
});
it('creates a user', async () => {
const user = await repo.createUser({
email: 'alice@example.com',
name: 'Alice',
});
expect(user.email).toBe('alice@example.com');
expect(user.name).toBe('Alice');
expect(user.role).toBe('user');
expect(user.id).toBeDefined();
});
it('enforces unique email constraint', async () => {
await repo.createUser({ email: 'unique@example.com', name: 'First' });
await expect(
repo.createUser({ email: 'unique@example.com', name: 'Second' })
).rejects.toThrow(); // SQLite UNIQUE constraint violation
});
it('finds user by email', async () => {
await repo.createUser({ email: 'find@example.com', name: 'Find Me' });
const found = await repo.findByEmail('find@example.com');
expect(found).not.toBeNull();
expect(found!.name).toBe('Find Me');
});
it('returns null for non-existent email', async () => {
const result = await repo.findByEmail('nobody@example.com');
expect(result).toBeNull();
});
it('filters by role', async () => {
await repo.createUser({ email: 'admin@example.com', name: 'Admin', role: 'admin' });
await repo.createUser({ email: 'user@example.com', name: 'User', role: 'user' });
const admins = await repo.findAll({ role: 'admin' });
expect(admins).toHaveLength(1);
expect(admins[0].email).toBe('admin@example.com');
});
it('cascades delete to sessions', async () => {
const user = await repo.createUser({ email: 'cascade@example.com', name: 'Cascade' });
// Create a session for this user directly via client
const client = (repo as any).client;
await client.execute({
sql: `INSERT INTO sessions (id, user_id, expires_at) VALUES (:id, :uid, :exp)`,
args: { id: 'session-abc', uid: user.id, exp: '2099-01-01' },
});
// Delete user — sessions should cascade
await repo.deleteUser(Number(user.id));
const sessions = await client.execute({
sql: 'SELECT * FROM sessions WHERE user_id = :uid',
args: { uid: user.id },
});
expect(sessions.rows).toHaveLength(0);
});
});Testing Service Logic with Mocked Repositories
For testing business logic that depends on the repository, mock the repository interface rather than a database connection:
// src/services/user.service.ts
import { UserRepository } from '../repositories/user.repository';
export class UserService {
constructor(private userRepo: UserRepository) {}
async inviteUser(email: string, name: string, invitedBy: string) {
const existing = await this.userRepo.findByEmail(email);
if (existing) {
throw new Error(`User with email ${email} already exists`);
}
const user = await this.userRepo.createUser({ email, name });
// In real code: send invitation email here
return { user, invitedBy };
}
async promoteToAdmin(userId: number, requestedBy: string) {
await this.userRepo.updateRole(userId, 'admin');
// In real code: log the promotion event
return { userId, promotedBy: requestedBy };
}
}// src/services/user.service.test.ts
import { describe, it, expect, vi, beforeEach } from 'vitest';
import { UserService } from './user.service';
function createMockRepo() {
return {
findByEmail: vi.fn(),
createUser: vi.fn(),
updateRole: vi.fn(),
findAll: vi.fn(),
deleteUser: vi.fn(),
migrate: vi.fn(),
};
}
describe('UserService', () => {
let service: UserService;
let mockRepo: ReturnType<typeof createMockRepo>;
beforeEach(() => {
mockRepo = createMockRepo();
service = new UserService(mockRepo as any);
});
it('throws when inviting existing user', async () => {
mockRepo.findByEmail.mockResolvedValue({ id: 1, email: 'existing@example.com' });
await expect(
service.inviteUser('existing@example.com', 'Existing', 'admin')
).rejects.toThrow('User with email existing@example.com already exists');
expect(mockRepo.createUser).not.toHaveBeenCalled();
});
it('creates new user on successful invite', async () => {
mockRepo.findByEmail.mockResolvedValue(null);
mockRepo.createUser.mockResolvedValue({
id: 2,
email: 'new@example.com',
name: 'New User',
});
const result = await service.inviteUser('new@example.com', 'New User', 'admin');
expect(result.user.email).toBe('new@example.com');
expect(result.invitedBy).toBe('admin');
});
});Testing Batch Operations and Transactions
LibSQL supports transactions via executeMultiple and the transaction object:
// src/repositories/batch.repository.ts
export class BatchRepository {
constructor(private client: Client) {}
async importUsers(users: Array<{ email: string; name: string }>) {
// LibSQL batch: all statements execute atomically
const statements = users.map((u) => ({
sql: 'INSERT INTO users (email, name) VALUES (:email, :name)',
args: { email: u.email, name: u.name },
}));
await this.client.batch(statements, 'write');
}
}it('imports multiple users atomically', async () => {
const client = createClient({ url: ':memory:' });
const userRepo = new UserRepository(client);
await userRepo.migrate();
const batchRepo = new BatchRepository(client);
await batchRepo.importUsers([
{ email: 'a@test.com', name: 'A' },
{ email: 'b@test.com', name: 'B' },
{ email: 'c@test.com', name: 'C' },
]);
const all = await userRepo.findAll();
expect(all).toHaveLength(3);
});
it('rolls back batch on constraint violation', async () => {
const client = createClient({ url: ':memory:' });
const userRepo = new UserRepository(client);
await userRepo.migrate();
// Pre-existing user
await userRepo.createUser({ email: 'exists@test.com', name: 'Existing' });
const batchRepo = new BatchRepository(client);
// Batch includes a duplicate — should fail atomically
await expect(
batchRepo.importUsers([
{ email: 'new@test.com', name: 'New' },
{ email: 'exists@test.com', name: 'Duplicate' }, // Will fail
])
).rejects.toThrow();
// Neither user from the batch should have been inserted
const all = await userRepo.findAll();
expect(all).toHaveLength(1); // Only the pre-existing user
});CI Configuration for Turso Tests
Your in-memory tests need no CI configuration beyond installing dependencies. For file-based LibSQL integration tests, make sure the test runner cleans up between runs:
# .github/workflows/test.yaml
name: Test
on: [push, pull_request]
jobs:
test:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v4
- uses: actions/setup-node@v4
with:
node-version: '20'
cache: 'npm'
- run: npm ci
- name: Run unit and integration tests
run: npx vitest run
env:
NODE_ENV: test
# No TURSO_* vars needed — tests use in-memory SQLite
- name: Cleanup test files
if: always()
run: rm -f ./test-*.db ./test-*.db-shm ./test-*.db-walLocal vs. Remote: When to Test Against Real Turso
In-memory testing covers most scenarios. Use real Turso connections only for:
- Replication lag tests — verifying your app handles eventual consistency from Turso's edge replicas.
- Network failure tests — testing reconnection and retry logic.
- Turso-specific features — embedded replicas, branching, groups.
For these, use a dedicated Turso database group for testing, set up in your CI secrets:
# Create a test database group in Turso
turso db create my-app-test --group test-group
<span class="hljs-comment"># Use in CI
TURSO_DATABASE_URL=libsql://my-app-test.turso.io
TURSO_AUTH_TOKEN=<span class="hljs-variable">${{ secrets.TURSO_TEST_TOKEN }}End-to-End Coverage with HelpMeTest
In-memory and file-based tests validate your data layer in isolation. For end-to-end scenarios — verifying that your API routes correctly expose LibSQL-backed data to users — HelpMeTest provides a natural-language test layer on top. You can write scenarios like "Create a user, verify they appear in the admin panel, and confirm the count updates correctly" without writing any additional test infrastructure.
HelpMeTest's Robot Framework + Playwright foundation means it can exercise your edge-deployed app the same way a real user would, while your LibSQL tests continue to verify the database layer beneath.
Summary
Testing Turso LibSQL applications is significantly simpler than testing traditional networked databases, thanks to LibSQL's support for in-memory and file-based SQLite:
- Use
createClient({ url: ':memory:' })for fast, isolated unit and integration tests — no external services needed. - Inject the LibSQL client into repositories and services to enable clean mocking in service-layer tests.
- Test batch operations and transactions against in-memory SQLite to verify atomicity.
- Reserve real Turso connections for replication and network-specific scenarios.
- Use file-based SQLite (
file:./test.db) for tests that need persistence between operations within a test session.
The key insight: LibSQL's compatibility with standard SQLite means your production Turso database and your in-memory test database behave identically for most application-level queries. Build on that compatibility and your test suite becomes both fast and trustworthy.
