SQLite - Die serverlose Datenbank für kleine Projekte

SQLite ist eine serverlose, dateibasierte Datenbank. Sie benötigt keine Installation eines Datenbankservers und eignet sich ideal für kleine Projekte, Entwicklung und eingebettete Systeme.

Vorteile von SQLite

Eigenschaften

- Keine Server-Installation nötig
- Gesamte Datenbank in einer Datei
- Zero-Configuration
- ACID-konform
- Cross-Platform
- Public Domain (keine Lizenzkosten)

Wann SQLite verwenden?

| Geeignet | Nicht geeignet | |----------|----------------| | Kleine Websites | Hochfrequente Schreibzugriffe | | Entwicklung/Tests | Viele gleichzeitige Schreiber | | Mobile Apps | Große Datenmengen (>1 TB) | | Desktop-Anwendungen | Verteilte Systeme | | Prototypen | Client/Server-Architektur | | IoT-Geräte | Komplexe Replikation |

Installation

Linux

# Debian/Ubuntu
apt install sqlite3

# CentOS/RHEL
dnf install sqlite

# Version prüfen
sqlite3 --version

PHP-Extension

# Debian/Ubuntu
apt install php-sqlite3

# Extension aktivieren
php -m | grep sqlite

# php.ini
extension=sqlite3

Python

# In Python3 standardmäßig enthalten
import sqlite3
print(sqlite3.sqlite_version)

Grundlagen

Datenbank erstellen

# Neue Datenbank erstellen/öffnen
sqlite3 mydb.db

# Befehle
sqlite> .help
sqlite> .tables
sqlite> .schema
sqlite> .quit

Tabellen erstellen

-- Tabelle erstellen
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    active INTEGER DEFAULT 1,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    title TEXT NOT NULL,
    content TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Datentypen

| SQLite-Typ | Beschreibung | |------------|--------------| | NULL | NULL-Wert | | INTEGER | Ganzzahl | | REAL | Gleitkommazahl | | TEXT | Zeichenkette | | BLOB | Binärdaten |

CRUD-Operationen

-- Einfügen
INSERT INTO users (name, email) VALUES ('Max', 'max@example.com');
INSERT INTO users (name, email) VALUES ('Anna', 'anna@example.com');

-- Lesen
SELECT * FROM users;
SELECT name, email FROM users WHERE active = 1;

-- Aktualisieren
UPDATE users SET active = 0 WHERE id = 1;

-- Löschen
DELETE FROM users WHERE id = 1;

PHP mit SQLite

PDO-Verbindung

<?php
// Verbindung öffnen
$db = new PDO('sqlite:/var/www/data/mydb.db');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Tabelle erstellen
$db->exec('
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE
    )
');

// Daten einfügen
$stmt = $db->prepare('INSERT INTO users (name, email) VALUES (:name, :email)');
$stmt->execute([':name' => 'Max', ':email' => 'max@example.com']);

// Daten abrufen
$stmt = $db->query('SELECT * FROM users');
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($users as $user) {
    echo $user['name'] . ' - ' . $user['email'] . "\n";
}

SQLite3-Klasse

<?php
$db = new SQLite3('/var/www/data/mydb.db');

// Query ausführen
$result = $db->query('SELECT * FROM users');

while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    echo $row['name'] . "\n";
}

// Prepared Statement
$stmt = $db->prepare('SELECT * FROM users WHERE id = :id');
$stmt->bindValue(':id', 1, SQLITE3_INTEGER);
$result = $stmt->execute();

$db->close();

Python mit SQLite

Grundlegende Operationen

import sqlite3
from contextlib import contextmanager

@contextmanager
def get_db(db_path):
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    try:
        yield conn
    finally:
        conn.close()

# Verwendung
with get_db('mydb.db') as conn:
    cursor = conn.cursor()

    # Tabelle erstellen
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT UNIQUE
        )
    ''')

    # Daten einfügen
    cursor.execute(
        'INSERT INTO users (name, email) VALUES (?, ?)',
        ('Max', 'max@example.com')
    )
    conn.commit()

    # Daten abrufen
    cursor.execute('SELECT * FROM users')
    for row in cursor.fetchall():
        print(dict(row))

Mit ORM (SQLAlchemy)

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    email = Column(String, unique=True)

# Engine erstellen
engine = create_engine('sqlite:///mydb.db')
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

# Benutzer erstellen
user = User(name='Max', email='max@example.com')
session.add(user)
session.commit()

# Abfragen
users = session.query(User).all()
for user in users:
    print(user.name)

Backup und Restore

Backup erstellen

# Kommandozeile
sqlite3 mydb.db ".backup backup.db"

# SQL-Dump
sqlite3 mydb.db ".dump" > backup.sql

# Einfache Kopie (wenn DB nicht benutzt)
cp mydb.db mydb_backup.db

Online-Backup (während Betrieb)

# Mit .backup (sicher bei Schreibzugriffen)
sqlite3 mydb.db ".backup /backup/mydb_$(date +%Y%m%d).db"

Wiederherstellen

# Aus Backup-Datei
cp backup.db mydb.db

# Aus SQL-Dump
sqlite3 mydb.db < backup.sql

Automatisches Backup

#!/bin/bash
# /usr/local/bin/backup-sqlite.sh

DB_PATH="/var/www/data/mydb.db"
BACKUP_DIR="/backup/sqlite"
DATE=$(date +%Y%m%d_%H%M%S)

sqlite3 "$DB_PATH" ".backup ${BACKUP_DIR}/mydb_${DATE}.db"

# Alte Backups löschen (älter als 7 Tage)
find "$BACKUP_DIR" -name "*.db" -mtime +7 -delete

Performance-Optimierung

Indizes

-- Index erstellen
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- Composite Index
CREATE INDEX idx_posts_user_date ON posts(user_id, created_at);

-- Index anzeigen
.indices users

-- Explain Query
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'max@example.com';

PRAGMA-Einstellungen

-- WAL-Modus (bessere Concurrent-Performance)
PRAGMA journal_mode = WAL;

-- Synchronisation (performance vs. sicherheit)
PRAGMA synchronous = NORMAL;

-- Cache-Größe (in KB)
PRAGMA cache_size = -10000;

-- Memory-Mapped I/O
PRAGMA mmap_size = 268435456;

-- Foreign Keys aktivieren
PRAGMA foreign_keys = ON;

PHP mit Optimierungen

<?php
$db = new PDO('sqlite:/var/www/data/mydb.db');

// WAL-Modus
$db->exec('PRAGMA journal_mode = WAL');
$db->exec('PRAGMA synchronous = NORMAL');
$db->exec('PRAGMA cache_size = -10000');
$db->exec('PRAGMA foreign_keys = ON');

Bulk-Inserts

<?php
// Langsam: Einzelne Inserts
foreach ($users as $user) {
    $stmt->execute($user);
}

// Schnell: Transaktion verwenden
$db->beginTransaction();
foreach ($users as $user) {
    $stmt->execute($user);
}
$db->commit();

Concurrent Access

WAL-Modus

-- Write-Ahead Logging aktivieren
PRAGMA journal_mode = WAL;

-- Vorteile:
-- - Mehrere Reader gleichzeitig
-- - Ein Writer blockiert keine Reader
-- - Bessere Performance

Locking

-- SQLite Locking Levels
-- UNLOCKED → SHARED → RESERVED → PENDING → EXCLUSIVE

-- Busy Timeout setzen
PRAGMA busy_timeout = 5000;

PHP mit Retry-Logik

<?php
function executeWithRetry($db, $query, $params = [], $maxRetries = 3) {
    $attempt = 0;
    while ($attempt < $maxRetries) {
        try {
            $stmt = $db->prepare($query);
            $stmt->execute($params);
            return $stmt;
        } catch (PDOException $e) {
            if (strpos($e->getMessage(), 'database is locked') !== false) {
                $attempt++;
                usleep(100000); // 100ms warten
                continue;
            }
            throw $e;
        }
    }
    throw new Exception("Database locked after $maxRetries attempts");
}

Sicherheit

Datei-Berechtigungen

# Nur Webserver darf zugreifen
chown www-data:www-data /var/www/data/mydb.db
chmod 600 /var/www/data/mydb.db

# Verzeichnis schützen
chmod 700 /var/www/data/

Außerhalb Webroot

# Datenbank außerhalb des Docroot speichern
/var/data/
├── mydb.db
└── mydb.db-wal

# In PHP
$db = new PDO('sqlite:/var/data/mydb.db');

SQL Injection verhindern

<?php
// FALSCH: Anfällig für Injection
$db->query("SELECT * FROM users WHERE name = '$name'");

// RICHTIG: Prepared Statement
$stmt = $db->prepare("SELECT * FROM users WHERE name = :name");
$stmt->execute([':name' => $name]);

Verschlüsselung

# SQLCipher für verschlüsselte Datenbanken
apt install sqlcipher

sqlcipher encrypted.db
sqlite> PRAGMA key = 'geheimes_passwort';
sqlite> CREATE TABLE users (id INTEGER, name TEXT);

CLI-Befehle

Wichtige Dot-Commands

sqlite3 mydb.db

# Tabellen anzeigen
.tables

# Schema anzeigen
.schema users

# Ausgabeformat
.mode column
.headers on

# Export
.output export.sql
.dump
.output stdout

# CSV-Export
.mode csv
.headers on
SELECT * FROM users;

# Import
.import data.csv users

Nützliche Queries

-- Tabelleninfo
PRAGMA table_info(users);

-- Datenbankgröße
SELECT page_count * page_size as size FROM pragma_page_count(), pragma_page_size();

-- Vakuum (Komprimierung)
VACUUM;

-- Analyze (Statistiken aktualisieren)
ANALYZE;

-- Integrity Check
PRAGMA integrity_check;

Migration zu MySQL/PostgreSQL

Export für MySQL

# Schema-Anpassungen nötig:
# - AUTOINCREMENT → AUTO_INCREMENT
# - TEXT → VARCHAR(255)
# - INTEGER für Boolean → TINYINT(1)

sqlite3 mydb.db .dump | \
    sed 's/AUTOINCREMENT/AUTO_INCREMENT/g' | \
    sed 's/INTEGER PRIMARY KEY AUTO_INCREMENT/INT AUTO_INCREMENT PRIMARY KEY/g' \
    > mysql_import.sql

Export für PostgreSQL

# Mit pgloader
pgloader sqlite:///mydb.db postgresql:///mydb

Zusammenfassung

| Befehl | Funktion | |--------|----------| | sqlite3 db.db | Datenbank öffnen | | .tables | Tabellen anzeigen | | .schema | Schema anzeigen | | .dump | Export als SQL | | .backup | Backup erstellen | | VACUUM | Komprimieren |

| PRAGMA | Empfehlung | |--------|------------| | journal_mode | WAL | | synchronous | NORMAL | | foreign_keys | ON | | cache_size | -10000 |

| Sprache | Verbindung | |---------|------------| | PHP | PDO('sqlite:path.db') | | Python | sqlite3.connect('path.db') | | Node.js | better-sqlite3 |

Fazit

SQLite ist die perfekte Wahl für kleine Projekte, Prototypen und eingebettete Anwendungen. Die Zero-Configuration macht den Einstieg einfach. WAL-Modus ermöglicht bessere Concurrent-Performance. Für größere Projekte oder hohe Schreiblast ist ein Server-basiertes DBMS wie MySQL oder PostgreSQL besser geeignet. Die Migration ist bei Bedarf möglich.