SQLite for Production Small Sites and CLI

By Kokil Thapa | Last reviewed: September 2026

You do not always need MySQL running on a second port. SQLite for production small sites and CLI is a valid choice when traffic stays modest, the app runs on one server, and your team wants fewer moving parts. I have shipped brochure sites, internal tools, and CLI scripts this way. The database is one file on disk. No daemon. No connection pool drama. That simplicity cuts hosting cost and nightly ops work. It also sets hard ceilings you must respect. This guide covers when SQLite fits, how to configure it in Laravel and PHP, backup patterns from the shell, and the point where you should migrate to PostgreSQL or MySQL for production.

When should you use SQLite for production small sites and CLI?

SQLite shines where a full RDBMS server would be overkill. Think law-firm landing pages with a contact log, a one-person SaaS admin panel, or a Deployer hook that records release metadata. On sister sites I maintain with Laravel scheduled tasks in production, a SQLite file often backs lightweight reporting tables that never leave the VPS.

Good candidates share these traits:

• Single application server (or container) owns all writes
• Mostly read-heavy pages; writes are infrequent and short
• Traffic under roughly a few thousand page views per day
• No requirement for remote DB replicas or multi-app shared writes
• Team prefers simple database backup strategies for small servers over replication tooling

Poor candidates include multi-tenant carts, real-time chat, heavy background job fan-out, or any design that expects dozens of concurrent writers. SQLite allows one writer at a time per database file. Readers can proceed in WAL mode, but writers queue. That is fine for a contact form. It is not fine for an order pipeline during a sale.

For a public legal-guide site like Court Marriage In Nepal, SQLite would only work if lead volume stays low and you run a single VPS. Most client portals I build instead use MySQL because document uploads, payments, and concurrent staff logins arrive early. Be honest about the next six months, not just launch week.

How do you configure SQLite for production in Laravel?

Laravel treats SQLite as a first-class driver. Point DB_CONNECTION at sqlite, place the file outside the public web root, and run migrations normally. PHP 8.3 or 8.5 ships with PDO SQLite enabled on typical Ubuntu images. Laravel 12 and 13 both support this path; you do not need a database server package on the host.

Set environment variables

DB_CONNECTION=sqlite
DB_DATABASE=/var/www/myapp/shared/storage/database/database.sqlite

Keep the file in a persistent shared directory if you deploy with symlinked releases. On Deployer-style setups, the same pattern I use on off-site backup pipelines to S3, only storage/ and .env survive between releases. Never store the database under public/.

Enable WAL mode and pragmas

Default rollback journal mode blocks readers during writes. WAL (Write-Ahead Logging) lets readers and one writer overlap. That matters even on small sites when a cron job writes analytics while visitors browse.

Run this once after creating the database, or from a migration:

DB::statement('PRAGMA journal_mode=WAL;');
DB::statement('PRAGMA synchronous=NORMAL;');
DB::statement('PRAGMA busy_timeout=5000;');
DB::statement('PRAGMA foreign_keys=ON;');

synchronous=NORMAL trades a tiny durability edge for much better write speed on VPS disks. For a marketing site that is usually acceptable. For financial ledger data, stay on FULL and reconsider MySQL. The official WAL documentation is at sqlite.org/wal.html.

File permissions on Linux

PHP-FPM runs as www-data. Cron and CLI may run as deploy or root. Mismatch causes silent write failures.

1. Create the file ahead of deploy: touch /var/www/myapp/shared/storage/database/database.sqlite
2. Set group ownership: chown deploy:www-data database.sqlite
3. Allow group read/write: chmod 664 database.sqlite
4. Ensure the directory is group-writable so WAL files can appear

This mirrors permission fixes I apply during Linux system administration for PHP sites. Wrong ownership is a common post-deploy surprise when SQLite replaces MySQL on a small VPS.

Connection settings in config/database.php

'sqlite' => [
    'driver' => 'sqlite',
    'url' => env('DB_URL'),
    'database' => env('DB_DATABASE', database_path('database.sqlite')),
    'prefix' => '',
    'foreign_key_constraints' => true,
    'busy_timeout' => 5000,
    'journal_mode' => 'wal',
    'synchronous' => 'normal',
],

Laravel passes these options to PDO on connect. See the Laravel database documentation for driver-specific keys. Test migrations on staging that mirrors production file paths, as described in our guide to staging environments that mirror production.

What are the backup and concurrency limits of SQLite in production?

SQLite backups are file copies, not logical dumps. That is simpler until someone copies a live file mid-write and gets corruption. Use SQLite's online backup API or the .backup command so copies are consistent.

Safe CLI backup with sqlite3

sqlite3 /var/www/myapp/shared/storage/database/database.sqlite \
  ".backup '/backups/myapp-$(date +%F).sqlite'"
gzip "/backups/myapp-$(date +%F).sqlite"

Schedule that from cron after low-traffic hours in Nepal (often 02:00 NPT). Push the gzip to object storage using the same mindset as WordPress automated backups with WP-CLI. Keep seven daily and four weekly copies minimum. A Rs 500/month (~USD 3.70) object storage bucket is cheap insurance.

Concurrency and size ceilings

SQLite handles databases up to terabytes on paper. In practice, keep production files under a few gigabytes for sane backup times. Watch these signals:

• database is locked errors even with busy_timeout
• Queue workers and HTTP requests fighting over long transactions
• Backup windows exceeding available disk I/O during business hours
• Need for a separate reporting replica or BI tool connection

When any of those appear, start a migration plan. Website migration projects often include exactly this jump from file DB to managed MySQL. Do it before Dashain traffic spikes if you serve Nepal retail audiences.

How do you use SQLite from the command line for ops tasks?

The sqlite3 binary is where SQLite earns the CLI half of this topic. Ops scripts, one-off imports, and deploy smoke tests do not need the full app booted.

Inspect schema and row counts

sqlite3 database.sqlite ".tables"
sqlite3 database.sqlite "SELECT COUNT(*) FROM leads;"
sqlite3 -header -column database.sqlite "SELECT id, email, created_at FROM leads LIMIT 5;"

Pretty output helps when you SSH into a client VPS and need answers in thirty seconds. Pipe JSON through your own script, or paste results into the JSON formatter tool if you export query results for a ticket.

Run parameterized imports

sqlite3 database.sqlite <<'SQL'
BEGIN;
INSERT INTO tags (name, slug) VALUES ('news', 'news');
INSERT INTO tags (name, slug) VALUES ('guide', 'guide');
COMMIT;
SQL

Wrap bulk changes in transactions. SQLite commits are fast in WAL mode, but one row per commit on a thousand rows is painfully slow on HDD VPS plans common at Rs 800–1,500/month (~USD 6–11).

Artisan and SQLite together

php artisan migrate --force
php artisan db:show
php artisan tinker --execute="echo DB::table('users')->count();"

Use --force in production deploy hooks. I wire this into GitLab CI after symlink swap, same pattern as PHP-FPM reload on legal-tech sister sites. For heavier reporting, export CSV:

sqlite3 -header -csv database.sqlite "SELECT * FROM page_views;" > /tmp/views.csv

Feed that CSV into a spreadsheet or a log aggregation setup for small teams if you outgrow manual review but are not yet on MySQL.

SQLite vs MySQL: which fits small production sites?

Both run fine on a $5–15 VPS. The difference is operational shape, not raw SQL syntax.

Verdict: pick SQLite when you want the smallest ops surface and can accept single-writer limits. Pick MySQL when staff log in together, payments land, or you already plan e-commerce development features. A travel brochure site like Adventure Himalaya Nepal could start on SQLite; a booking CRM with supplier tables should not.

PHP without Laravel

Plain PHP scripts on PHP 8.5 work the same way through PDO:

$pdo = new PDO('sqlite:' . __DIR__ . '/data/app.sqlite');
$pdo->exec('PRAGMA journal_mode=WAL');
$pdo->exec('PRAGMA foreign_keys=ON');
$stmt = $pdo->prepare('INSERT INTO events (name) VALUES (:name)');
$stmt->execute(['name' => 'deploy']);

The PHP manual covers driver quirks at php.net PDO SQLite. This pattern suits deploy hooks and health checks where bootstrapping Laravel adds seconds you do not need.

Monitoring and testing

SQLite lacks SHOW PROCESSLIST. Monitor file size, lock errors in logs, and page latency instead. Add an artisan command or shell probe:

sqlite3 database.sqlite "PRAGMA integrity_check;"

Run weekly. Pair with application tests on every deploy. For teams building custom internal tools, custom software development engagements should document the migration trigger in the README so the next developer knows when to leave SQLite.

Cost angle matters for Nepal startups. Skipping MySQL saves roughly 200–400 MB RAM on a 1 GB VPS. That is meaningful at Rs 1,200/month hosting tiers. It is meaningless once lost orders cost more than the server bill. See cloud cost optimization for small startups for the wider picture.

Feature flags and gradual rollouts still apply. SQLite does not change how you ship code. You can toggle read-heavy modules first, as in feature flag rollout for small teams, before you split read replicas on MySQL later.

If you containerize, mount the database file on a persistent volume. Ephemeral container layers wipe SQLite on restart. Our Docker multi-stage Laravel guide covers volume placement. Without it, you will restore from backup on every redeploy.

Ongoing care belongs in a support plan. File permissions drift after manual edits. WAL files grow until checkpointed. Include SQLite checks in support and maintenance retainers the same way you would run mysqlcheck on larger stacks.

Key Takeaways

• Use SQLite for production small sites and CLI when one server, low write concurrency, and simple backups match your traffic reality.
• Enable WAL mode, busy_timeout, and foreign keys; store the file outside public/ with correct www-data permissions.
• Back up with sqlite3 .backup, never raw cp on a live file; gzip and push off-site nightly.
• Watch for lock errors, growing file size, and multi-user write needs — those are your MySQL migration triggers.
• CLI ops (sqlite3, Artisan, CSV export) stay fast for debugging without booting a database server.
• Document the upgrade path early so a successful small site does not stall on a file database past its limits.

People Also Ask

Is SQLite safe for production websites?

Yes, for small production sites with a single writer pattern and proper WAL configuration. High-traffic eCommerce, multi-user dashboards, and apps with many concurrent writes should use MySQL or PostgreSQL instead. Safety depends on workload fit, not the label "embedded database."

Can Laravel use SQLite in production?

Laravel 12 and 13 support SQLite in production through the standard sqlite driver. Run migrations, set pragmas, fix file permissions, and use the database queue driver only if job volume stays low. Most production Laravel apps still choose MySQL when Horizon, Sanctum token churn, or team scale enters the picture.

How do you back up SQLite without downtime?

Use the sqlite3 interactive .backup command or the online backup API. Both produce a consistent snapshot while the app serves traffic in WAL mode. Compress the result and store copies off-server. Test a restore quarterly.

When should you migrate from SQLite to MySQL?

Migrate when you see repeated lock timeouts, need remote database access from multiple apps, run heavy write queues, or require replication and point-in-time recovery. Planning the move before peak season beats emergency migration under load.

Ship small, plan the upgrade path

SQLite for production small sites and CLI is not a shortcut for every project. It is the right default when your app, your VPS, and your ops time are all small on purpose. Configure WAL, automate consistent backups, and write down the traffic signals that mean MySQL. If you want a second opinion on whether your brochure site, legal portal, or internal tool fits SQLite or needs a full database server, contact us or browse web development services and the portfolio for sites that started lean and scaled when the business asked.