What Is SQLite? The Database That Lives Inside Your App

Why AI Coders Need to Know This

You asked AI to build a task manager app. It wrote the code, set up the schema, and everything worked. But instead of connecting to a database server, it created a file called dev.db in your project folder. You can't open it in a browser. You're not sure if it's real. And you're wondering if AI just skipped the "real database" part.

It didn't skip anything. That .db file is the database. You just met SQLite.

SQLite is the most deployed database in the world — by a wide margin. It's not a toy or a shortcut. It's running inside every iPhone, every Android device, every Chrome and Firefox browser, every macOS system app, and hundreds of millions of other programs worldwide. The SQLite library has been downloaded more times than any other software in history.

The reason you've never heard of it is because it does its job quietly. You don't set it up. You don't log into it. You don't manage it. It just works — and that's exactly why AI reaches for it when building your prototype.

As a vibe coder, you need to understand three things about SQLite:

1. What it actually is — so you stop being confused by the .db file
2. When it's the right tool — so you know when AI made a good call vs. when you should upgrade
3. What breaks with it — so you can spot the one real limitation before it burns you

That's what this article covers.

Real Scenario: AI Built an App With a .db File

You expected Claude to set up PostgreSQL, write a connection string, maybe spin up Docker. Instead, it installed a package called better-sqlite3, created a file called tasks.db, and had your app reading and writing data in about 20 lines of code. No server. No config. No password.

You ran it, it worked, and then you paused: Is this a real database? Can I deploy this? What even is this file?

Let's look at exactly what AI generated — and what every part of it means.

What AI Generated

Here's the code Claude wrote for the task manager — using better-sqlite3, the most common SQLite library for Node.js:

// db.js — the database setup file AI created

const Database = require('better-sqlite3');

// This line creates (or opens) the database file
const db = new Database('tasks.db');

// Create the tasks table if it doesn't exist yet
db.exec(`
  CREATE TABLE IF NOT EXISTS tasks (
    id        INTEGER PRIMARY KEY AUTOINCREMENT,
    title     TEXT NOT NULL,
    status    TEXT NOT NULL DEFAULT 'pending',
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
  )
`);

module.exports = db;

// tasks.js — the functions your app uses to talk to the database

const db = require('./db');

// Add a new task
function createTask(title) {
  const stmt = db.prepare(
    'INSERT INTO tasks (title) VALUES (?)'
  );
  const result = stmt.run(title);
  return result.lastInsertRowid;
}

// Get all tasks, optionally filtered by status
function getTasks(status) {
  if (status) {
    const stmt = db.prepare(
      'SELECT * FROM tasks WHERE status = ? ORDER BY created_at DESC'
    );
    return stmt.all(status);
  }
  const stmt = db.prepare(
    'SELECT * FROM tasks ORDER BY created_at DESC'
  );
  return stmt.all();
}

// Mark a task complete
function completeTask(id) {
  const stmt = db.prepare(
    "UPDATE tasks SET status = 'complete' WHERE id = ?"
  );
  return stmt.run(id);
}

module.exports = { createTask, getTasks, completeTask };

This is clean, functional code. Your entire database is that tasks.db file. No separate process. No connection string. No server to keep running. Just a file sitting in your project folder.

If you prefer using an ORM, AI might have scaffolded the same thing with Drizzle ORM or Prisma instead — both support SQLite out of the box and give you a type-safe layer on top of the raw SQL shown above.

Here's what the same setup looks like with Drizzle:

// db.ts — Drizzle ORM with SQLite

import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';
import { sqliteTable, integer, text } from 'drizzle-orm/sqlite-core';

const sqlite = new Database('tasks.db');
export const db = drizzle(sqlite);

// Define the tasks table as a TypeScript object
export const tasks = sqliteTable('tasks', {
  id:        integer('id').primaryKey({ autoIncrement: true }),
  title:     text('title').notNull(),
  status:    text('status').notNull().default('pending'),
  createdAt: text('created_at').notNull().default(sql`(datetime('now'))`)
});

// tasks.ts — using Drizzle to query

import { db, tasks } from './db';
import { eq } from 'drizzle-orm';

export async function createTask(title: string) {
  return db.insert(tasks).values({ title }).returning();
}

export async function getTasks(status?: string) {
  if (status) {
    return db.select().from(tasks).where(eq(tasks.status, status));
  }
  return db.select().from(tasks);
}

export async function completeTask(id: number) {
  return db.update(tasks)
    .set({ status: 'complete' })
    .where(eq(tasks.id, id));
}

The Drizzle version gives you TypeScript types and autocomplete. The raw better-sqlite3 version is simpler and has no build step. Both use the same tasks.db file underneath. AI will pick based on your project setup.

Understanding Each Part

The Database File

const db = new Database('tasks.db');

This one line does everything a traditional database setup would require: it creates the database (if it doesn't exist), opens a connection, and gets it ready for use. The tasks.db file is your entire database — the schema, the data, the indexes, all of it, in one binary file.

Think of it like a spreadsheet. Microsoft Excel doesn't need a running Excel Server in the background to save your data. The file is the data. SQLite works the same way. The .db file is a self-contained store that your application reads and writes directly.

Server-Based vs. File-Based: What's the Actual Difference?

Most databases you've heard of — PostgreSQL, MySQL, MongoDB — are server-based. That means they run as a separate process on your computer or in the cloud. Your app connects to them over a network socket (even if "the network" is just loopback on your own machine). You need to:

• Install the database software
• Start the database server
• Create a user with a password
• Set a connection string in your environment variables
• Make sure the server is running every time your app runs

SQLite is file-based. There is no separate process. Your app includes the SQLite library directly (it's just a code package), and that library reads and writes a file on disk. There's nothing to install beyond the npm package, nothing to start, and no connection string — just a file path.

The construction analogy: a server-based database is like a lumber yard — you have to drive there, deal with their hours, wait for your order, and haul materials back. SQLite is like having your own on-site storage unit full of exactly what you need. You just open the door and grab it.

Prepared Statements

const stmt = db.prepare('INSERT INTO tasks (title) VALUES (?)');
stmt.run(title);

The ? is a placeholder — a slot where user input goes in safely. AI almost always writes SQLite code with prepared statements, and this matters: it prevents a common security vulnerability called SQL injection, where a malicious user could type database commands into a form field and corrupt your data. The ? placeholder ensures whatever the user types is treated as data, never as a database command.

CREATE TABLE IF NOT EXISTS

CREATE TABLE IF NOT EXISTS tasks (...)

This is the safety net that lets you run db.js as many times as you want without breaking anything. The first time it runs, it creates the table. Every subsequent run, it sees the table already exists and does nothing. Think of it like a contractor checking "does this wall already exist?" before building — you want them checking, not just swinging a hammer.

When SQLite Is the Perfect Tool

SQLite isn't a beginner's compromise. It's genuinely the best database choice for a specific set of situations:

• Prototypes and MVPs — Get a working data layer in minutes, not hours
• CLI tools and scripts — Command-line apps that need to persist data between runs
• Desktop apps (Electron) — Apps like VS Code use SQLite for their local storage
• Mobile apps — Every iOS and Android app already has SQLite built in
• Single-user web apps — If only one person uses it at a time, SQLite handles it perfectly
• Embedded applications — Hardware devices, IoT sensors, anything where installing a database server isn't feasible
• Testing and development — Fast, disposable, easy to reset (just delete the file)

When to Upgrade to PostgreSQL

SQLite has one meaningful limitation: it uses a single write lock. Only one write operation can happen at a time. For most apps this is invisible — writes are fast and your users aren't all submitting forms at the exact same millisecond. But for a real multi-user web app with concurrent traffic, this becomes a bottleneck.

Switch to PostgreSQL when:

• Multiple users are writing data simultaneously (think: a shared project management tool, not just your own task list)
• Your app is deployed on a cloud server that's separate from where the database lives
• Your dataset is growing past a few gigabytes
• You need advanced PostgreSQL features like full-text search, JSON operators, or row-level security
• Your hosting platform (like Railway, Render, or Supabase) provides a managed PostgreSQL instance

If none of those apply — and they often don't for early-stage projects — SQLite is not just acceptable, it's optimal. It's faster than PostgreSQL for single-user workloads because there's no network round-trip. Every query goes straight to a file on the same disk.

What AI Gets Wrong About SQLite

AI writes good SQLite code. But it makes a handful of consistent mistakes. Know these before you get burned.

1. Recommending SQLite for Multi-User Web Apps

This is the big one. If you describe a "web app" without being specific, AI sometimes defaults to SQLite even when you clearly intend multiple users to write data simultaneously. You'll end up with an app that works fine with one user and mysteriously fails when two people submit a form at the same time — with errors like:

SqliteError: database is locked

What to tell your AI: "This app will have multiple users writing data at the same time. Is SQLite still appropriate, or should we use PostgreSQL?" This forces AI to evaluate your actual use case rather than defaulting to simplicity.

2. Not Explaining Where the .db File Goes in Production

SQLite stores data in a file. In production, that file needs to live on persistent storage — not in a temporary directory that gets wiped on each deploy. Platforms like Vercel and Railway don't have persistent file systems by default, so a SQLite app deployed there will appear to work but lose all data every time it redeploys.

AI often generates the code without mentioning this. Ask explicitly: "Where does the .db file need to live when I deploy this? Does my hosting platform support persistent file storage for SQLite?"

3. SQLite Type Flexibility Causing Silent Bugs

SQLite is famously flexible with data types — you can store text in an INTEGER column and it won't complain. Most databases would throw an error; SQLite just stores what you give it. This is convenient but can hide bugs. AI might write code that accidentally stores a number as a string, and everything looks fine until you try to sort or compare those values numerically.

If you're using an ORM like Drizzle or Prisma, this is largely handled for you — the ORM enforces types before the data ever reaches SQLite. Without an ORM, add explicit type checking or use STRICT mode (SQLite 3.37+):

CREATE TABLE tasks (
  id      INTEGER PRIMARY KEY,
  title   TEXT NOT NULL,
  status  TEXT NOT NULL
) STRICT;

4. Mixing Up SQLite Syntax With PostgreSQL Syntax

SQLite and PostgreSQL both speak SQL, but they have dialect differences. The one that trips people up most often: SQLite doesn't have a dedicated BOOLEAN type (it stores true/false as integers 1/0). AI sometimes generates PostgreSQL-flavored schema inside a SQLite project:

-- ❌ PostgreSQL syntax — won't work in SQLite
CREATE TABLE tasks (
  id         SERIAL PRIMARY KEY,
  is_done    BOOLEAN DEFAULT FALSE
);

-- ✅ SQLite equivalent
CREATE TABLE tasks (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  is_done    INTEGER NOT NULL DEFAULT 0
);

If you get a "near X: syntax error" on a table creation, this is the first thing to check. Tell AI: "This error came from SQLite — is the schema using PostgreSQL syntax? Rewrite it for SQLite."

How to Debug SQLite Issues With AI

Here are the most common SQLite errors, what they mean in plain English, and exactly what to tell your AI to fix them.

"database is locked"

SqliteError: database is locked

Two things are trying to write to the database at the same time, and SQLite can only handle one at once. In development this usually means you have two instances of your app running. In production it means you've hit SQLite's concurrency limit.

"no such table: tasks"

SqliteError: no such table: tasks

The database file exists but the table hasn't been created yet. This usually means the setup code that runs CREATE TABLE didn't execute before the query ran, or the .db file is empty/new.

"SQLITE_CANTOPEN: unable to open database file"

SqliteError: unable to open database file

The file path you gave SQLite doesn't exist or can't be written to. Common in deployment environments where the directory doesn't have write permissions.

Data Disappears After Redeployment

This isn't a crash — it's a silent data loss. Your app works, users add data, you deploy an update, and all the data is gone. This means your SQLite file is stored on ephemeral (temporary) storage that gets cleared on each deploy.

Resetting Your Database in Development

One of SQLite's best features for development: resetting your database is just deleting a file. No commands, no DROP DATABASE, no password prompts:

# Nuke the database and start fresh
rm tasks.db

# Next time your app starts, it recreates the file automatically

This is enormously useful when you're iterating on your schema during early development. Change the table structure, delete the file, restart the app. Done.

Quick Reference: SQLite vs PostgreSQL

Common SQLite Operations

What to Learn Next

Now that you understand what SQLite is and when to use it, here are the natural next steps for building on this foundation:

• What Is a Database? The foundational guide — what databases are, why apps need them, and how the different types (SQL, NoSQL, file-based) compare. Start here if you want the full mental model.
• What Is PostgreSQL? When your project grows past SQLite, PostgreSQL is the most common upgrade path. Learn what it is, why developers love it, and how to migrate your SQLite schema.
• What Is Prisma? Prisma is the ORM AI reaches for most often — it works with both SQLite and PostgreSQL and gives you type-safe database access without writing raw SQL. A natural pair with SQLite for prototypes.
• What Is Drizzle ORM? The leaner ORM that's gaining fast adoption. Drizzle has excellent SQLite support, runs at the edge, and keeps your schema in TypeScript. AI increasingly scaffolds new projects with Drizzle.
• What Is Turso? What if you want SQLite's simplicity but with cloud hosting and multi-region replication? That's Turso — SQLite at the edge, with none of the file-storage headaches.