SQLite Create Database

Creating a SQLite database takes one command. There is no CREATE DATABASE statement like in MySQL. In SQLite, the database is the file. The moment you open a file name through the shell or your code, SQLite treats it as the database.

How Database Creation Works in SQLite

Think of SQLite like a notebook. When you say "Open notebook called school.db", SQLite looks for a file with that name. If it finds one, it opens it. If it does not find one, it prepares a blank notebook ready for your first entry. The file only appears on disk after you write something into it.

SCENARIO 1: File does not exist yet
────────────────────────────────────────────
$ sqlite3 school.db
         │
         ▼
SQLite checks disk: school.db? Not found.
         │
         ▼
Opens in-memory. File created on first write.
         │
         ▼
sqlite> CREATE TABLE students (...);
         │
         ▼
school.db NOW appears on disk ✓

SCENARIO 2: File already exists
────────────────────────────────────────────
$ sqlite3 school.db
         │
         ▼
SQLite finds file. Opens existing data.

Creating a Database from the Shell

Method 1: Pass Filename at Startup

$ sqlite3 school.db
SQLite version 3.46.0
sqlite>

Method 2: Open from Inside the Shell

$ sqlite3
sqlite> .open school.db
sqlite>

Method 3: In-Memory Database (Temporary)

$ sqlite3
sqlite>

An in-memory database disappears when you close the shell. Use it for temporary work or testing.

Method 4: Named In-Memory Database

$ sqlite3 :memory:
sqlite>

The special name :memory: explicitly creates an in-memory database.

Database File Naming Rules

GOOD DATABASE FILE NAMES
───────────────────────────────
school.db
myapp.sqlite
inventory_2024.db
test_data.sqlite3

AVOID
───────────────────────────────
"my database.db"    ← spaces cause shell issues
data.txt            ← misleading extension

Common extensions are .db and .sqlite. Both work identically — the extension is just a label for humans. SQLite identifies its files by file content, not extension.

Verifying the Database Exists

sqlite> .databases
main: /home/user/school.db  r/w

$ ls -lh school.db
-rw-r--r-- 1 user user 8.0K Jul 24 10:00 school.db

Creating a Database in Python

import sqlite3

# Creates school.db if it doesn't exist
conn = sqlite3.connect("school.db")

print("Database opened successfully")

conn.close()

Creating a Database in Node.js

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

// Creates school.db if it doesn't exist
const db = new Database('school.db');

console.log("Database opened successfully");

db.close();

Database File Structure Overview

A newly created, empty SQLite database file is 0 bytes on disk. After the first table gets created, SQLite writes a header and at least one page of data (4096 bytes by default).

SQLITE FILE LAYOUT (simplified)
─────────────────────────────────────────────
[ Header: 100 bytes ]
  - Magic string "SQLite format 3"
  - Page size (default 4096 bytes)
  - Version info

[ Page 1: Root page of schema table ]
  - Stores all table definitions
  - Every CREATE TABLE you run is recorded here

[ Page 2+ : Your actual data ]
  - Each table and index gets its own pages

Practical Example: Build a Library Database

Step 1: Create the database
$ sqlite3 library.db

Step 2: Add tables
sqlite> CREATE TABLE books (
          id    INTEGER PRIMARY KEY,
          title TEXT NOT NULL,
          author TEXT
        );

Step 3: Confirm it exists
sqlite> .tables
books

Step 4: Exit
sqlite> .quit

Step 5: Check the file
$ ls -lh library.db
-rw-r--r-- 1 user user 4.0K Jul 24 library.db

Copying and Moving a Database

A SQLite database is a plain file. Copy it exactly like any other file.

# Backup
$ cp school.db school_backup.db

# Move to another folder
$ mv school.db /home/user/projects/school.db

# Send to another computer (via scp)
$ scp school.db user@server:/home/user/school.db

Key Takeaways

• SQLite has no CREATE DATABASE command — the database is the file
• Run sqlite3 myfile.db to create or open a database
• The file appears on disk only after you write the first data
• Use :memory: for temporary in-memory databases
• A SQLite database file is portable — copy or move it like any regular file