1
2
3
4
5
6
# Open SQLite shell
sqlite3 database.db

# Execute SQL from command line
sqlite3 database.db "SELECT * FROM users;"

1
2
3
4
5
6
7
8
9
10
11
12
13
14
import sqlite3

# Create/Connect to database
conn = sqlite3.connect('database.db')
cursor = conn.cursor()

# Execute query
cursor.execute('SELECT * FROM users')
results = cursor.fetchall()

# Commit and close
conn.commit()
conn.close()

1
2
3
4
5
6
7
8
9
10
11
12
const Database = require('better-sqlite3');
const db = new Database('database.db');

// Simple query
const users = db.prepare('SELECT * FROM users').all();

// Transaction
const insert = db.prepare('INSERT INTO users (name) VALUES (?)');
const insertMany = db.transaction((users) => {
  for (const user of users) insert.run(user);
});

1
2
3
-- Creates a new database (or opens if exists)
sqlite3 newdatabase.db

1
2
3
4
5
6
7
8
9
10
-- Attach another database
ATTACH DATABASE 'secondary.db' AS secondary;

-- Query across databases
SELECT * FROM main.users u 
JOIN secondary.orders o ON u.id = o.user_id;

-- Detach database
DETACH DATABASE secondary;

1
2
3
4
5
6
7
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

1
2
3
4
5
6
-- Add column
ALTER TABLE users ADD COLUMN last_login DATETIME;

-- Rename table
ALTER TABLE users RENAME TO app_users;

1
2
3
-- Drop table
DROP TABLE IF EXISTS users;

1
2
3
4
5
6
7
8
9
-- Single insert
INSERT INTO users (username, email) 
VALUES ('johndoe', 'john@example.com');

-- Multiple insert
INSERT INTO users (username, email) VALUES 
('alice', 'alice@example.com'),
('bob', 'bob@example.com');

1
2
3
4
5
6
7
8
9
10
11
-- Basic select
SELECT * FROM users;

-- Filtered select
SELECT username, email FROM users 
WHERE created_at > date('now', '-30 days');

-- Pagination
SELECT * FROM users 
LIMIT 10 OFFSET 20;

1
2
3
4
5
-- Update specific rows
UPDATE users 
SET last_login = CURRENT_TIMESTAMP 
WHERE username = 'johndoe';

1
2
3
4
-- Delete specific rows
DELETE FROM users 
WHERE id = 5;

1
2
3
4
SELECT u.username, o.order_id 
FROM users u 
INNER JOIN orders o ON u.id = o.user_id;

1
2
3
4
SELECT u.username, o.order_id 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id;

1
2
3
4
5
6
-- Simple index
CREATE INDEX idx_username ON users(username);

-- Unique index
CREATE UNIQUE INDEX idx_email ON users(email);

1
2
3
4
5
6
7
8
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL CHECK (price > 0),
    category TEXT DEFAULT 'Misc',
    UNIQUE(name, category)
);

1
2
3
4
5
6
7
-- Concatenation, substring, length
SELECT 
    name || ' ' || lastname AS full_name,
    substr(name, 1, 3) AS first_three,
    length(name) AS name_length 
FROM users;

1
2
3
4
5
6
-- Current timestamp
SELECT datetime('now');

-- Date calculations
SELECT date('now', '+1 month');

1
2
3
4
5
6
-- Create FTS table
CREATE VIRTUAL TABLE docs USING fts5(title, content);

-- Search
SELECT * FROM docs WHERE docs MATCH 'sqlite tutorial';

1
2
3
4
5
6
7
CREATE TRIGGER update_timestamp 
AFTER UPDATE ON users
BEGIN
    UPDATE users SET updated_at = CURRENT_TIMESTAMP 
    WHERE id = NEW.id;
END;

1
2
3
4
5
6
7
8
9
-- Check journal mode
PRAGMA journal_mode;

-- Set journal mode
PRAGMA journal_mode = WAL;

-- Cache size
PRAGMA cache_size = -2000;  -- 2MB cache

1
2
3
-- Enable Write-Ahead Logging
PRAGMA journal_mode = WAL;

1
2
3
4
BEGIN TRANSACTION;
-- Multiple insert/update operations
COMMIT;

1
2
3
4
5
6
7
8
9
10
11
12
13
# Python example with connection pool
import sqlite3

class DatabaseManager:
    def __init__(self, db_path):
        self.db_path = db_path
        self.conn = None

    def get_connection(self):
        if not self.conn:
            self.conn = sqlite3.connect(self.db_path)
        return self.conn

1
2
3
# CLI Backup
sqlite3 source.db ".backup destination.db"

1
2
3
-- SQL Backup
BACKUP DATABASE TO 'backup.db';

1
2
3
# Restore from backup
sqlite3 destination.db ".restore backup.db"