DEV Community: Kinsly

💾 Why I Chose SQLite for My Startup — The Most Underrated Database You're Probably Ignoring

Sergey Podgorny — Mon, 20 Oct 2025 16:15:00 +0000

For years, I ignored SQLite. I assumed it was only good for toy apps, quick experiments, or maybe some desktop utilities. Like many developers, I immediately jumped to Postgres or MySQL for any "serious" project. I even paid for a managed AWS RDS instance, believing I was preparing for scale.

But over time, I learned something that changed how I build small and medium-sized systems:

👉 For 90% of applications, SQLite is not only enough — it's often better.

My entire application runs on a single VPS, serving just a few requests per second. Most startups never reach the mythical "millions of requests per minute". For this scale, running a full database server is like renting a truck to deliver a pizza.

🧩 Why SQLite Is So Powerful

SQLite is a serverless, file-based, self-contained database engine. It's just a single file (e.g., data.db) that your app reads and writes to — no network connections, no daemons, no setup.

Yet it supports:

Full ACID transactions
Foreign keys, indexes, views, triggers
Concurrency (especially in WAL mode)
Gigabytes of data and millions of rows

And it's blazing fast when used correctly.

⚡ The Secret Sauce: WAL Mode

By default, SQLite stores changes using a rollback journal — it writes updates directly to the main database file and keeps a small backup journal during each transaction for safety.

WAL (Write-Ahead Logging) changes this strategy completely.

Instead of touching the main DB directly, all writes are appended to a separate .db-wal file. Readers keep reading from the main file while writers add to the WAL log. Later, the changes are merged automatically.

That means:

Readers and writers don't block each other.
Concurrent operations become much faster.
Crashes don't corrupt your main data file.

WAL mode can significantly boost performance in multi-threaded or multi-request apps, like Go web servers, where reads and writes happen at the same time.

But if your app is tiny and writes only occasionally, the default mode (DELETE) is perfectly fine. You can always switch later with:

PRAGMA journal_mode = WAL;

Other journal modes include:

DELETE (default on many builds): makes a rollback file, then deletes it after writing. When you start a transaction, SQLite writes a rollback journal file (data.db-journal). After committing, it deletes that file. It's the safest and most widely compatible default. This is what you get if you don't set journal_mode at all.
TRUNCATE: same as DELETE, but instead of removing the journal file, it just truncates it to zero bytes and reuses it.
PERSIST: same idea, but keeps the journal file contents and just overwrites a header.
MEMORY: journal is only in RAM → faster, but data can be lost on crash.
OFF: no journaling at all → faster, but unsafe if the app or OS crashes, the database can be corrupted on crash.
WAL: the special one we just discussed.

⚙️ What Are PRAGMAs?

In SQLite, PRAGMAs are configuration switches that control database behavior — kind of like settings in a config file, but stored inside the DB engine itself.

You can enable them either by running SQL commands:

PRAGMA foreign_keys = ON;
PRAGMA synchronous = NORMAL;

Or pass them directly in the Go connection string:

dsn := "file:data.db?_pragma=busy_timeout(10000)&_pragma=foreign_keys(ON)"

Here are a few useful ones:

busy_timeout(10000) tells SQLite: if the database is locked, wait up to 10 seconds before giving up (instead of failing immediately with database is locked). Useful when multiple goroutines or processes may try to write at the same time. It is good for web apps; safer than retrying manually.
journal_mode(WAL) enables Write-Ahead Logging instead of the default rollback journaling. It makes reads/writes more concurrent.
journal_size_limit(200000000) sets a cap on the WAL file size (here: ~200 MB). Normally, the WAL file can grow until checkpointed (merged back into the main DB). This prevents it from growing forever. If your DB is small, it will never hit this size anyway.
foreign_keys(ON) enables foreign key constraints (OFF by default in SQLite). If you define relations like user_id REFERENCES users(id), this ensures referential integrity. It is always good practice if you use relationships.
temp_store(MEMORY) tells SQLite to keep temporary data (like for ORDER BY, GROUP BY, and indexes while querying) in RAM instead of on disk. Faster for queries, but uses more memory.
cache_size(-16000) sets the page cache size. The negative number means "KB" instead of pages. Here: -16000 = about 16 MB of cache. This is how much SQLite will keep in memory for speeding up queries.
synchronous(NORMAL) controls how careful SQLite is about flushing data to disk.

FULL (default): every write is forced to disk immediately → safest, but slowest.
NORMAL: doesn't flush on every step, but still durable enough for most apps (data is safe unless the OS/hardware crashes at a very unlucky moment).
OFF: fastest, but risk of corruption on crash.

ℹ️ NORMAL is a common compromise in web apps: faster inserts, still quite safe.

Not all are required — start simple and tune only when needed.

🧠 When You Should (and Shouldn't) Use SQLite

SQLite is perfect for:

Small to medium web apps
APIs on a single server
Prototypes, MVPs, side projects
Command-line or desktop tools
Local caching layers or analytics snapshots

But you'll hit limits if:

You need multiple servers writing to the same DB file
You expect thousands of writes per second
You require fine-grained access control
You need replication or clustering

That's when Postgres or MySQL makes sense. Until then, SQLite saves you time, money, and complexity.

🐹 Example: Using SQLite with Go

Let's set up a connection in Go using the excellent cgo-free driver modernc.org/sqlite:

$ go get modernc.org/sqlite

package main

import (
    "context"
    "database/sql"
    "fmt"
    "log"
    "time"

    _ "modernc.org/sqlite"
)

func main() {
    // Open a database file (or ":memory:" for in-memory)
    dsn := "file:data.db?_pragma=busy_timeout(5000)&_pragma=journal_mode(WAL)"
    db, err := sql.Open("sqlite", dsn)
    if err != nil {
        log.Fatalf("open: %v", err)
    }
    // Configure pool: keep a small pool for sqlite; tune for your workload
    db.SetMaxOpenConns(10)  // readers can have several
    db.SetMaxIdleConns(5)
    db.SetConnMaxLifetime(0)

    // Ping to verify connection
    ctx, cancel := context.WithTimeout(context.Background(), 2*time.Second)
    defer cancel()
    if err := db.PingContext(ctx); err != nil {
        log.Fatalf("ping: %v", err)
    }

    fmt.Println("DB open, WAL enabled and busy_timeout set (via DSN).")
}

SQLite allows many concurrent readers but only one writer at a time. A common pattern:

Use one DB instance restricted to a single writer (SetMaxOpenConns(1)) for all writes.
Use a separate DB instance with a larger pool for readers.

// writer
writerDSN := "file:test.db?_pragma=busy_timeout(5000)&_pragma=journal_mode(WAL)"
writerDB, _ := sql.Open("sqlite", writerDSN)
writerDB.SetMaxOpenConns(1) // single writer connection
writerDB.SetMaxIdleConns(1)

// readers
readerDSN := "file:test.db?_pragma=busy_timeout(5000)"
readerDB, _ := sql.Open("sqlite", readerDSN)
readerDB.SetMaxOpenConns(20) // allow concurrent readers
readerDB.SetMaxIdleConns(10)

This reduces SQLITE_BUSY when multiple goroutines try to write. Practical guides and community posts recommend separating writer/reader pools.

🧭 Navigating SQLite from the Terminal

SQLite also comes with a lightweight console client: sqlite3.

Open your database:

sqlite3 data.db

Inside, these dot-commands make life easier:

.help             # list all available commands
.databases        # show loaded databases (usually just `main` pointing to your file)
.tables           # list all tables
.schema           # show schema of all tables
.schema table     # show schema of a specific table
.headers on       # turn on column headers in query output
.mode column      # format results in a nicely aligned table
.mode line        # show each row vertically, one field per line (great for wide tables)
.mode list         # results as plain text separated by | (default mode)
.width 20 30 15   # set column widths when using .mode column
.nullvalue NULL   # choose how NULLs are displayed
.quit or .exit    # leave the CLI

Example session:

sqlite> .headers on
sqlite> .mode column
sqlite> SELECT * FROM users;
id  email             created_at
--  ----------------  ---------------------
1   test@example.com  2025-10-07 14:30:00

Once you learn .headers on and .mode column, the CLI feels surprisingly pleasant.

If you prefer certain settings to be enabled by default, then it makes sense to customize the configuration file to suit your needs. If the initialization file ~/.sqliterc exists, sqlite3 will read it to set the configuration of the interactive environment. This file should generally only contain meta-commands.

.headers on
.mode column
.nullvalue NULL

🧩 Conclusion

SQLite is like the pocketknife of databases — tiny, portable, and incredibly capable when used properly. For many apps, it's a smarter default than running a full database server.

For Kinsly's current stage, SQLite is the perfect choice:

It's blazing fast, even on cheap VPS hardware
It simplifies deployment
It requires zero configuration
It saves money (no RDS bills)
It's easy to move or back up (just copy the file)

When the time comes to scale, migration will be straightforward.
Until then, SQLite lets me focus on what matters: building the product, not managing infrastructure.

So before spinning up another managed Postgres instance, consider starting with SQLite. You might be surprised how far it takes you.

🚀 How I Deployed My Startup's Server Without Kubernetes or Docker (Yet)

Sergey Podgorny — Tue, 07 Oct 2025 14:00:00 +0000

Almost every article these days talks about setting up Kubernetes clusters, writing Terraform scripts, or diving deep into AWS-managed services. While those tools are powerful, I believe that for most small projects, they are unnecessary overhead.

For Kinsly, my still-small project, I chose a simpler and more old-school approach. It's lighter, uses less memory, has fewer layers of abstraction, and remains secure. In this post, I'll walk through how I deployed my server, why I avoided AWS/GCP at this stage, and the exact setup I used, from SSH security to deployment automation.

🌍 Choosing the Right Infrastructure

For years, I favored DigitalOcean: simple, affordable, and reliable. But for this first phase, I went even more cost-effective and chose Contabo VPS. Although their SLA looks low on paper, performance in practice has been surprisingly stable.

Unlike AWS or GCP, where you can easily drown in unnecessary configuration, here I get just what I need: a straightforward server that I fully control. And importantly, I can move it anywhere at any time, without being locked into provider-specific services.

💡 Lesson: Keep deployments provider-agnostic so you can move quickly when needed.

🔐 Securing Access with SSH

The first thing I did was lock down access. By default, servers allow SSH login with a password. That's a major attack vector: bots constantly attempt brute-force logins with common credentials. An SSH key is far more secure.

Secure SSH key was created locally using this command:

$ ssh-keygen -t ed25519 -f ~/.ssh/deploy_server.pem -C "deploy@gitlab"

And then the local public key was uploaded to the server:

# This command is the easiest way to install your key.
$ ssh-copy-id -i ~/.ssh/deploy_server.pem deploy@[target-ip]

Next, I hardened the SSH configuration on the server.

$ sudo vim /etc/ssh/sshd_config

Inside this file, I made sure the following lines were set. This completely disables password logins and ensures only key-based access is allowed:

PermitRootLogin prohibit-password
# Disallow password authentication entirely.
PasswordAuthentication no
# Ensure public key authentication is enabled.
PubkeyAuthentication yes
# Also disable challenge-response auth, which can sometimes be a password fallback.
ChallengeResponseAuthentication no

Finally, I applied the changes by restarting the SSH service:

$ sudo systemctl restart sshd

From now on, only valid SSH keys can connect. This instantly shuts down most automated attacks.

⚠️ IMPORTANT: Before you close your current terminal session, open a new terminal and confirm you can still log in with your SSH key. If you don't, you could lock yourself out of your own server!

🌐 Cloudflare for Domains & Security

I registered my domain through Cloudflare. Unlike GoDaddy or Namecheap, Cloudflare sells domains at cost price (no markup) and forces you to use its DNS — this turned out to be a blessing. DNS hosting is free and comes with Cloudflare Proxy.

With just the free plan, I got:

Hidden server IP (Cloudflare Proxy)
Free DDoS protection
Global caching across regions for speed
Basic analytics
Built-in security filtering

👉 In the past, I even paid AWS money just to register DNS records. Cloudflare gives more for free.

⚔️ Hardening the Server Firewall

As soon as I installed nginx, bots started probing for vulnerabilities (checking for /wp-login.php, .git folders, etc.). To prevent direct access to my VPS, I restricted requests to Cloudflare IPs only.

To prevent this, ufw (Uncomplicated Firewall) method is the best practice. It provides the highest level of security and efficiency. It is a one-time setup, though you should plan to update the IP list once or twice a year, as Cloudflare occasionally adds new ranges.

First, always allow SSH, or you'll lock yourself out.

$ sudo ufw allow ssh

$ sudo ufw allow 22/tcp

Then allow traffic from all of Cloudflare's IPs on port 443 (HTTPS). You can get the latest lists from cloudflare.com/ips. You can run a simple loop to add all the current IPv4 and IPv6 addresses:

# For IPv4
$ for ip in $(curl -s https://www.cloudflare.com/ips-v4); do sudo ufw allow from $ip to any port 443 proto tcp; done

# For IPv6
$ for ip in $(curl -s https://www.cloudflare.com/ips-v6); do sudo ufw allow from $ip to any port 443 proto tcp; done

After specifically allowing Cloudflare IPs, you can now safely deny all other traffic on ports 80 and 443.

$ sudo ufw deny http
$ sudo ufw deny https

This forces all web traffic through Cloudflare's secure proxy, effectively locking the back door to the server.

Then, if it's not already enabled, turn on the firewall. It will ask for confirmation.

$ sudo ufw enable

Verify your rules are in place.

$ sudo ufw status verbose

You should see a list showing that port 22 is allowed from anywhere, port 443 is allowed from Cloudflare's IPs, and ports 80/443 are denied from anywhere else.

You can also do this within nginx itself, though it's slightly less efficient as nginx has to process the connection before denying it.

You would create a file (/etc/nginx/snippets/cloudflare-ips.conf) containing allow rules for all of Cloudflare's IPs, and then include it in your server block, followed by deny all;.

# IPv4 Ranges
allow 173.245.48.0/20;
allow 103.21.244.0/22;
allow 103.22.200.0/22;
allow 103.31.4.0/22;
allow 141.101.64.0/18;
allow 108.162.192.0/18;
allow 190.93.240.0/20;
allow 188.114.96.0/20;
allow 197.234.240.0/22;
allow 198.41.128.0/17;
allow 162.158.0.0/15;
allow 104.16.0.0/13;
allow 104.24.0.0/14;
allow 172.64.0.0/13;
allow 131.0.72.0/22;

# IPv6 Ranges
allow 2400:cb00::/32;
allow 2606:4700::/32;
allow 2803:f800::/32;
allow 2405:b500::/32;
allow 2405:8100::/32;
allow 2c0f:f248::/32;
allow 2a06:98c0::/29;

deny all;

This snippet will then need to be included separately in the corresponding server directive.

server {
    # ...
    include /etc/nginx/snippets/cloudflare-ips.conf;
    # ...
}

🏗️ A Simple, Minimal Stack

My architecture is intentionally simple: instead of React or Angular SSR, the first version of Kinsly runs on a static HTML page. Form submissions go to a small Go service that listens only on the local loopback interface (127.0.0.1) and it's not exposed to the outside world. Nginx acts as a reverse proxy, forwarding public requests from api.getkinsly.com to this private port. This is a secure and standard pattern.

I also considered using Unix sockets, but TCP on localhost was good enough.

Here's a look at the core of my Nginx config for the API:

# /etc/nginx/sites-available/api.getkinsly.com
server {
    server_name api.getkinsly.com;

    listen 443 ssl http2;
    # ... (all my SSL and security headers go here) ...

    location / {
        # These two lines enable efficient keep-alive connections to the backend.
        proxy_http_version 1.1;
        proxy_set_header Connection "";

        # Pass essential client information to the Go application.
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;

        # The actual proxy pass to the local Go service.
        proxy_pass http://127.0.0.1:8001;
    }
}

For now, Kinsly just needs to collect form data. Deploying a full Postgres or MySQL server at this stage would be pure over-engineering, so I deliberately chose SQLite - it's file-based, requires zero configuration, incredibly fast, and perfect for the simple task of collecting waiting list emails.

Later, when scaling requires it, migrating to Postgres or MySQL will be straightforward.

⚙️ Keeping the App Alive with systemd

My Go application runs as a systemd service, ensuring it's always on. The service is configured to run as the non-privileged www-data user for security.

Example service file (/etc/systemd/system/kinsly-api.service):

[Unit]
Description=Kinsly API Service
# Start this service only after the network is available.
After=network.target

[Service]
# The user and group the service will run as.
# Running as a dedicated, non-sudo user is a critical security best practice.
User=www-data
Group=www-data

# The command to start the application. Make sure the binary is executable (chmod +x binary)
ExecStart=/opt/kinsly/current/kinsly-api
Restart=always
# Wait 5 seconds before restarting to prevent rapid-fire restarts.
RestartSec=5

# Set environment variables if your application needs them.
Environment="APPLICATION_MODE=prod"

[Install]
# This allows the service to be enabled to start on boot.
WantedBy=multi-user.target

With this, if the app crashes or the server reboots, systemd automatically restarts it.

👤 Creating a Safe Deploy User

My GitLab CI/CD pipeline, however, connects as a separate deploy user that does not have full sudo access. This creates a problem: how does the CI/CD pipeline restart the service after a new deployment?

The solution is to grant the deploy user permission to run only that one specific command. This is done via the sudoers file.

The ONLY safe way to edit the sudoers file is with visudo. It performs a syntax check on save to prevent you from breaking sudo.

$ sudo visudo -f /etc/sudoers.d/deploy-app

I added this single line to the bottom of the file. This allows the deploy user to restart the kinsly-api service without a password.

deploy ALL=(ALL) NOPASSWD: /usr/bin/systemctl restart kinsly-api.service

This is a secure and granular way to enable deployment automation without giving away root access. The CI/CD pipeline can now run sudo systemctl restart kinsly-api.service, and it's the only sudo command it's allowed to execute.

ℹ️ Make sure the directive @includedir /etc/sudoers.d is enabled in the main /etc/sudoers configuration file. If it's not there, you should add it using sudo visudo command (without -f argument).

Also, ensure correct file ownership & permissions:

$ sudo chown root:root /etc/sudoers.d/deploy-app
$ sudo chmod 0440 /etc/sudoers.d/deploy-app

Validate sudoers syntax, so you don't lock yourself out:

$ sudo visudo -c

🔄 Deployment via GitLab CI

Deployment is automated:

GitLab CI builds a Go binary.
It packages the binary + static HTML into a .tar.gz archive.
The archive is uploaded to the server and unpacks under new release folder /opt/kinsly/releases/<timestamp>.
A symlink current → <latest_release> is updated.
The service is restarted.
Old releases are pruned (keep the last 5 for rollback).

This is simple, reliable, and avoids the need for Docker at this stage.

🍪 Frontend Challenges: Analytics, Consent & Captcha

Even with a simple site, there are complexities. To use Google Analytics, I needed a cookie consent banner to comply with EU law. I chose axept.io, a free and Google-certified service.

I also added a free captcha (Altcha) to prevent bots from spamming my form with fake emails. Spam protection was essential. Without it, malicious bots could flood forms, causing emails to be marked as spam. While not perfect, it filters out the majority of malicious requests.

🛡️ Final Security Checks

Before going live, I checked open ports with nmap and online scanners to ensure only nginx was exposed. My Go app, database, and system processes remain inaccessible from outside.

$ nmap -sS -Pn -T5 -p- [target-ip]

✅ Conclusion

This setup may sound "basic" compared to a Kubernetes cluster on AWS, but for an early-stage project, it's exactly what I need:

Minimal cost
Maximum control
Easy portability
Strong enough security

As the project grows, I will likely containerize services and introduce Docker orchestration, but for now, this lean approach lets me move fast without unnecessary complexity.

It reminds me that sometimes the most elegant solution is the simplest one.