Using SQLite in production on Schokr

Schokr has no built-in database server, and for most apps and bots running on one server, SQLite is the best choice: it is a single file in your app folder, needs no extra service, and handles far more traffic than most projects get.

When SQLite is a good fit#

• One server reads and writes the data.
• Moderate write volume (hundreds of writes per second is fine with WAL mode).
• You want zero setup and zero cost.

Use an external database provider instead if several servers must share the same data, or you need heavy concurrent writes. Free S3-compatible storage on Schokr is coming soon, which suits files but is not a replacement for a SQL database.

Setup by runtime#

Node.js

better-sqlite3 is fast and simple:

const Database = require('better-sqlite3')
const db = new Database('data/app.db')
db.pragma('journal_mode = WAL')
db.pragma('busy_timeout = 5000')

db.exec('create table if not exists users (id integer primary key, name text)')
const insert = db.prepare('insert into users (name) values (?)')
insert.run('Ada')

It is a native addon, so install it on the server rather than uploading node_modules from your computer.

Bun

import { Database } from 'bun:sqlite'
const db = new Database('data/app.db', { create: true })
db.exec('PRAGMA journal_mode = WAL;')

Python

import sqlite3
db = sqlite3.connect("data/app.db", timeout=5)
db.execute("PRAGMA journal_mode=WAL")

Recommended settings#

WAL mode creates two extra files next to your database, ending in -wal and -shm. They are part of the database. Do not delete them while the app is running.

Backups#

• Use your server's Backups and Snapshots features. Snapshots: 3 slots per server on the free package.
• Copying the .db file while the app writes to it can produce a broken copy. Either stop the server first, or create a consistent copy from your app:

src = sqlite3.connect("data/app.db")
dst = sqlite3.connect("data/backup.db")
src.backup(dst)

VACUUM INTO 'data/backup.db';

• Download backup copies with the file manager regularly so you have one off the server.

Avoiding "database is locked"#

• Set busy_timeout as above.
• Keep write transactions short.
• With gunicorn or uvicorn, several workers writing at once is fine in WAL mode, but do not run long writes inside request handlers.

ORMs#

Prisma, Drizzle, SQLAlchemy and Django all support SQLite. Point them at a file path such as file:./data/app.db (Prisma) or sqlite:///data/app.db (SQLAlchemy).