Invoice Generator uses Turso, a distributed SQLite database built on libSQL. This guide covers database setup, configuration, and schema management.
Prerequisites
- A Turso account (sign up at turso.tech)
- Turso CLI installed (optional, for local management)
Database client
The application uses the @libsql/client package to connect to Turso:
import { createClient } from "@libsql/client";
const db = createClient({
url: process.env.TURSO_DATABASE_URL!,
authToken: process.env.TURSO_AUTH_TOKEN,
});
export default db;
app/lib/turso.ts:1-8
Environment variables
Configure these environment variables in your .env.local file:
TURSO_DATABASE_URL=libsql://your-database.turso.io
TURSO_AUTH_TOKEN=your-auth-token
Getting your credentials
-
Create a database in the Turso dashboard or CLI:
turso db create invoice-generator
-
Get the database URL:
turso db show invoice-generator --url
-
Generate an auth token:
turso db tokens create invoice-generator
The TURSO_AUTH_TOKEN is optional for local development with libsql:// URLs but required for production.
Database schema
The application uses a comprehensive SQLite schema with foreign key constraints enabled.
Core tables
Customers
Stores customer information for invoices:
CREATE TABLE IF NOT EXISTS customers (
id TEXT NOT NULL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL,
address TEXT NOT NULL,
city TEXT NOT NULL,
state TEXT NOT NULL,
zip_code TEXT NOT NULL,
country TEXT NOT NULL,
logo TEXT
);
app/lib/schema.sql:4-14
Invoices
Stores invoice data with status tracking:
CREATE TABLE IF NOT EXISTS invoices (
id TEXT NOT NULL PRIMARY KEY,
invoice_number TEXT NOT NULL UNIQUE,
date TEXT NOT NULL,
due_date TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','sent','paid','cancelled')),
customer_id TEXT,
customer_snapshot TEXT NOT NULL,
notes TEXT,
tax_rate REAL,
currency TEXT NOT NULL DEFAULT 'GBP',
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
);
app/lib/schema.sql:16-30
Status values: draft, sent, paid, cancelled
Invoice items
Stores line items for each invoice:
CREATE TABLE IF NOT EXISTS invoice_items (
id TEXT NOT NULL PRIMARY KEY,
invoice_id TEXT NOT NULL,
description TEXT NOT NULL,
quantity REAL NOT NULL,
rate REAL NOT NULL,
discount REAL,
tax_rate REAL,
FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
);
app/lib/schema.sql:32-41
Templates
Stores invoice templates for quick creation:
CREATE TABLE IF NOT EXISTS templates (
id TEXT NOT NULL PRIMARY KEY,
name TEXT NOT NULL,
description TEXT,
customer_partial TEXT NOT NULL,
items_partial TEXT NOT NULL,
notes TEXT,
tax_rate REAL,
currency TEXT NOT NULL DEFAULT 'GBP',
created_at TEXT NOT NULL
);
app/lib/schema.sql:43-53
Configuration tables
Company details
CREATE TABLE IF NOT EXISTS company_details (
id TEXT NOT NULL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL,
phone TEXT,
address TEXT NOT NULL,
city TEXT NOT NULL,
state TEXT NOT NULL,
zip_code TEXT NOT NULL,
country TEXT NOT NULL,
website TEXT,
tax_id TEXT,
logo TEXT,
is_default INTEGER NOT NULL DEFAULT 0,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL
);
app/lib/schema.sql:55-71
Account details
Stores bank account information for invoices:
CREATE TABLE IF NOT EXISTS account_details (
id TEXT NOT NULL PRIMARY KEY,
account_holder_name TEXT NOT NULL,
bank_name TEXT NOT NULL,
account_number TEXT NOT NULL,
sort_code TEXT,
routing_number TEXT,
iban TEXT,
swift_bic TEXT,
currency TEXT,
payment_reference TEXT,
notes TEXT,
is_default INTEGER NOT NULL DEFAULT 0,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL
);
app/lib/schema.sql:73-88
Settings
CREATE TABLE IF NOT EXISTS settings (
id TEXT NOT NULL PRIMARY KEY DEFAULT 'default',
default_currency TEXT NOT NULL DEFAULT 'GBP',
updated_at TEXT NOT NULL
);
-- Seed default settings row
INSERT OR IGNORE INTO settings (id, default_currency, updated_at)
VALUES ('default', 'GBP', datetime('now'));
app/lib/schema.sql:90-98
Authentication table
Users
Stores user accounts for NextAuth.js:
CREATE TABLE IF NOT EXISTS users (
id TEXT NOT NULL PRIMARY KEY,
name TEXT,
email TEXT UNIQUE,
emailVerified TEXT,
image TEXT,
password TEXT,
created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
app/lib/schema.sql:101-109
The password field is NULL for users who sign in with OAuth providers like Google.
Running migrations
The application includes a migration script to set up the database schema.
Development migration
This runs the migration using your .env.local configuration.
Production migration
This runs the migration using your .env.production configuration.
Migration script
The migration script executes the SQL schema file:
import { createClient } from "@libsql/client";
import { readFileSync } from "fs";
import { join, dirname } from "path";
import { fileURLToPath } from "url";
const __dirname = dirname(fileURLToPath(import.meta.url));
const schema = readFileSync(join(__dirname, "../app/lib/schema.sql"), "utf8");
if (!process.env.TURSO_DATABASE_URL) {
console.error("Error: TURSO_DATABASE_URL is not set.");
process.exit(1);
}
const db = createClient({
url: process.env.TURSO_DATABASE_URL,
authToken: process.env.TURSO_AUTH_TOKEN,
});
console.log(`Running migration on: ${process.env.TURSO_DATABASE_URL}`);
await db.executeMultiple(schema);
console.log("Migration complete.");
scripts/migrate.mjs:1-27
Foreign keys
The schema uses foreign key constraints to maintain referential integrity:
PRAGMA foreign_keys = ON;
app/lib/schema.sql:2
Key relationships:
invoices.customer_id → customers.id (SET NULL on delete)invoice_items.invoice_id → invoices.id (CASCADE on delete)
When a customer is deleted, invoices keep the customer_snapshot but lose the reference. When an invoice is deleted, all items are automatically removed.
Database operations
The application uses the Turso client for database queries:
Query example
const result = await db.execute({
sql: "SELECT id, name, email FROM users WHERE email = ? LIMIT 1",
args: [email],
});
const user = result.rows[0];
auth.ts:27-30
Insert example
await db.execute({
sql: `INSERT INTO users (id, name, email, password, created_at)
VALUES (?, ?, ?, ?, datetime('now'))`,
args: [id, name, email, hashedPassword],
});
app/api/auth/register/route.ts:42-46
Best practices
- Use parameterized queries - Always use the
args parameter to prevent SQL injection
- Handle errors - Wrap database calls in try/catch blocks
- Use transactions - For operations that modify multiple tables
- Index frequently queried fields - Add indexes for email and foreign keys if needed
- Validate input - Check data before inserting into the database
Troubleshooting
Connection errors
If you can’t connect to the database:
- Verify
TURSO_DATABASE_URL is correct
- Check that
TURSO_AUTH_TOKEN is valid
- Ensure your Turso database is active
- Test the connection with the Turso CLI:
turso db shell invoice-generator
Migration failures
If migrations fail:
- Check that
TURSO_DATABASE_URL is set
- Verify the schema.sql file exists
- Look for syntax errors in the SQL
- Check database permissions
Foreign key violations
If you get foreign key errors:
- Verify foreign keys are enabled:
PRAGMA foreign_keys = ON;
- Check that referenced records exist before inserting
- Delete child records before parent records (or use CASCADE)
Local development
For local development without Turso, you can use a local SQLite file:
const db = createClient({
url: "file:local.db",
});
Local SQLite files are not recommended for production. Use Turso for production deployments.