Beheer en Gebruik van SQLite 3 Database

SQLite 3 is een veelgebruikte embedded relationele databasebeheersysteem. Dankzij het lichte karakter, het ontbreken van een aparte serverproces, en de opslag van data in één enkel bestand, is SQLite 3 ideaal voor mobiele apps, embedded systemen en kleine desktopapplicaties (en lokale ontwikkelomgevingen). SQLite 3 is eenvoudig te beheren en te gebruiken. Dit artikel biedt ServBay-gebruikers een gedetailleerde handleiding voor efficiënt beheer en gebruik van SQLite 3 binnen de ServBay-omgeving, waaronder installatieverificatie, verbinding maken, basisoperaties, data back-up en herstel, prestatieoptimalisatie en beveiligingstips.

Overzicht

ServBay, als een complete lokale webontwikkelomgeving, bevat standaard SQLite 3 en de bijbehorende ontwikkelbibliotheken. Je kunt SQLite 3 direct benaderen en gebruiken via de commandoregel of via diverse programmeertalen die door ServBay worden ondersteund (zoals PHP, Node.js, Python, Go, enzovoort) — zonder extra installatie. Dit maakt het werken met SQLite 3 in lokale ontwikkeling aanzienlijk eenvoudiger.

Installatie en Configuratie van SQLite 3

SQLite 3 is standaard geïntegreerd in ServBay, waardoor geen extra installatie nodig is. Het uitvoerbare bestand en de gerelateerde libraries van SQLite 3 zijn onderdeel van het ServBay softwarepakket.

Verificatie van SQLite 3 Installatie

Je kunt snel verifiëren of het systeem toegang heeft tot het door ServBay geleverde SQLite 3 uitvoerbare bestand en de versie controleren met het volgende terminalcommando:

bash

sqlite3 --version

Als dit commando het versienummer van SQLite 3 retourneert (bijvoorbeeld 3.41.2 2023-03-26 11:52:19 ...), dan is SQLite 3 met succes geïntegreerd en klaar voor gebruik.

SQLite 3 Database Aanmaken en Verbinden

Een SQLite 3 database is in wezen één enkel bestand. Standaard adviseert ServBay om databasebestanden te plaatsen in de map /Applications/ServBay/db/sqlite, maar je kunt andere locaties kiezen afhankelijk van je projectwensen. Je kunt een database aanmaken of ermee verbinden via de sqlite3 commandoregeltool of via de SQLite bibliotheken in diverse programmeertalen.

Verbinden met de Commandoregeltool

De sqlite3 CLI-tool biedt een directe manier om je SQLite 3 database te beheren.

Database aanmaken of verbinden: Voer het volgende commando uit in de terminal. Als het databasebestand op het opgegeven pad nog niet bestaat, wordt het automatisch aangemaakt; als het wel bestaat, wordt verbinding gemaakt.
bash
```
sqlite3 /Applications/ServBay/db/sqlite/servbay.db
```
1
(We noemen deze voorbeeld database servbay.db ter illustratie van het ServBay merk.)
Zodra je de sqlite> prompt ziet, kun je SQL-commando’s uitvoeren of gebruik maken van .commands.
Afsluiten van SQLite 3: Typ het .exit commando bij de sqlite> prompt om de CLI-tool af te sluiten.
sql
```
.exit
```
1

Verbinden via Programmeertalen

Alle programmeertalen die door ServBay worden ondersteund bieden bibliotheken om verbinding te maken met en bewerkingen uit te voeren op SQLite 3 databases. Hieronder voorbeelden voor PHP en Node.js.

PHP Voorbeeld

In ServBay is doorgaans de PHP SQLite 3-extensie (php-sqlite3) geïntegreerd. Je kunt de ingebouwde SQLite3 klasse gebruiken om verbinding te maken.

php

<?php
// Pad naar databasebestand; aanbevolen locatie in ServBay’s db/sqlite map
$database_file = '/Applications/ServBay/db/sqlite/servbay.db';

// Verbinden met SQLite 3 database
// Als het bestand niet bestaat, wordt het automatisch aangemaakt door de SQLite3 constructor
$db = new SQLite3($database_file);

if (!$db) {
    die("Kan geen verbinding maken met SQLite 3 database: " . $db->lastErrorMsg());
}

echo "Succesvol verbonden met SQLite 3 database: " . $database_file . "\n";

// Tabel aanmaken (indien niet bestaand)
$create_table_sql = "CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE
)";
$db->exec($create_table_sql);
echo "Tabel 'users' gecontroleerd of aangemaakt.\n";

// Data invoegen
$name = 'ServBay Demo User';
$email = '[email protected]';
$insert_sql = $db->prepare('INSERT INTO users (name, email) VALUES (:name, :email)');
$insert_sql->bindValue(':name', $name, SQLITE3_TEXT);
$insert_sql->bindValue(':email', $email, SQLITE3_TEXT);

if ($insert_sql->execute()) {
    echo "Data succesvol ingevoegd.\n";
} else {
    echo "Data-invoer mislukt: " . $db->lastErrorMsg() . "\n";
}


// Data ophalen
$query_sql = "SELECT id, name, email FROM users";
$result = $db->query($query_sql);

if ($result) {
    echo "Queryresultaten:\n";
    while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
        echo "ID: " . $row['id'] . ", Name: " . $row['name'] . ", Email: " . $row['email'] . "\n";
    }
} else {
     echo "Query mislukt: " . $db->lastErrorMsg() . "\n";
}


// Databaseverbinding sluiten
$db->close();
unset($db); // Resources vrijgeven
echo "Databaseverbinding gesloten.\n";

?>

Node.js Voorbeeld

Voor gebruik van SQLite 3 in Node.js, installeer het sqlite3 npm-pakket. Open een terminal, ga naar je projectmap en voer uit:

bash

npm install sqlite3

Daarna gebruik je de library in je Node.js code voor databaseverbinding en -operaties:

javascript

const sqlite3 = require('sqlite3').verbose();
const path = require('path');

// Pad naar het databasebestand; gebruik path.join voor platformonafhankelijke compatibiliteit
const dbPath = path.join('/Applications/ServBay/db/sqlite', 'servbay.db');

// Verbinden met de SQLite 3 database
// Als bestand niet bestaat, wordt deze aangemaakt door sqlite3.Database
let db = new sqlite3.Database(dbPath, sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE, (err) => {
  if (err) {
    console.error('Kan geen verbinding maken met SQLite 3 database:', err.message);
  } else {
    console.log('Succesvol verbonden met SQLite database.');
  }
});

// Commando’s serialiseren voor juiste uitvoer volgorde
db.serialize(() => {
  // Tabel aanmaken (indien niet bestaand)
  db.run(`CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE
  )`, (err) => {
    if (err) {
      console.error('Tabel aanmaken mislukt:', err.message);
    } else {
      console.log('Tabel "users" gecontroleerd of aangemaakt.');
    }
  });

  // Data invoegen
  const name = 'ServBay Demo User';
  const email = '[email protected]';
  db.run(`INSERT INTO users (name, email) VALUES (?, ?)`, [name, email], function(err) {
    if (err) {
      // SQLITE_CONSTRAINT is de foutcode voor unieke beperkingen
      if (err.errno === 19) { // SQLITE_CONSTRAINT
         console.warn(`Gebruiker '${name}' (${email}) bestaat al, invoegen overgeslagen.`);
      } else {
         console.error('Data-invoer mislukt:', err.message);
      }
    } else {
      console.log(`Succesvol één rij ingevoegd, ID: ${this.lastID}`);
    }
  });

  // Data ophalen
  db.all(`SELECT id, name, email FROM users`, [], (err, rows) => {
    if (err) {
      throw err;
    }
    console.log('Queryresultaten:');
    rows.forEach((row) => {
      console.log(`ID: ${row.id}, Name: ${row.name}, Email: ${row.email}`);
    });
  });
});

// Databaseverbinding sluiten
db.close((err) => {
  if (err) {
    console.error('Sluiten van databaseverbinding mislukt:', err.message);
  } else {
    console.log('Databaseverbinding gesloten.');
  }
});

Let op: Zorg in de praktijk voor veilige databasepadinstellingen, degelijke foutafhandeling en aandacht voor gelijktijdige toegang tot bestanden.

Databasebeheer

Basis SQL-operaties

Na verbinding met een SQLite 3 database kun je standaard SQL-bewerkingen uitvoeren. Enkele veelvoorkomende voorbeelden:

Tabel aanmaken: Databasestructuur definiëren.

sql

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_name TEXT NOT NULL,
    price REAL DEFAULT 0.00
);

Gegevens invoegen: Nieuwe records toevoegen.

sql

INSERT INTO products (product_name, price) VALUES ('ServBay T-Shirt', 19.99);
INSERT INTO products (product_name, price) VALUES ('ServBay Sticker Pack', 4.99);

Gegevens opvragen: Records uit de tabel halen.

sql

SELECT * FROM products;
SELECT product_name, price FROM products WHERE price > 10.00;

Gegevens bijwerken: Bestaande records wijzigen.

sql

UPDATE products SET price = 24.99 WHERE product_name = 'ServBay T-Shirt';

Gegevens verwijderen: Records verwijderen.

sql

DELETE FROM products WHERE product_id = 1;
DELETE FROM products; -- Alle data uit tabel verwijderen

Tabel verwijderen: Hele tabel en data wissen.
sql
```
DROP TABLE products;
```
1

Deze commando’s kunnen direct in de sqlite3 CLI-tool of via een programmeertaal worden uitgevoerd.

Back-up en Herstel

Back-up en herstel in SQLite 3 zijn eenvoudig, aangezien de volledige database één bestand is.

Database Back-up

De makkelijkste manier is het maken van een kopie van het databasebestand. ServBay raadt aan om back-ups te bewaren in /Applications/ServBay/backup/sqlite.

Gebruik deze commando’s op de CLI:

bash

# Maak de back-upmap aan (indien nodig)
mkdir -p /Applications/ServBay/backup/sqlite

# Kopieer het databasebestand naar de back-upmap
# Voeg aan de bestandsnaam datum/tijd toe om versies te onderscheiden
cp /Applications/ServBay/db/sqlite/servbay.db /Applications/ServBay/backup/sqlite/servbay_$(date +%Y%m%d_%H%M%S).db

Je kunt voor live back-ups ook de .backup opdracht van de sqlite3 CLI gebruiken, maar bestand kopiëren is voor lokale ontwikkeling meestal afdoende.

Herstellen van database

Herstellen betekent de back-up terugzetten naar de originele locatie. Stop vóór herstel alle applicaties of ServBay-diensten die nog met de database werken, om bestandsslotproblemen te voorkomen en dataconsistentie te garanderen.

bash

# Stel dat je de nieuwste back-up wilt herstellen
# Zoek het laatste back-upbestand, bijvoorbeeld: servbay_20231027_103000.db
LATEST_BACKUP=$(ls -t /Applications/ServBay/backup/sqlite/servbay_*.db | head -n 1)

# Controleer of een back-upbestand gevonden is
if [ -z "$LATEST_BACKUP" ]; then
  echo "Fout: Geen SQLite back-up gevonden."
  exit 1
fi

echo "Te herstellen back-upbestand: $LATEST_BACKUP"

# Stop relevante services of apps... (afhankelijk van je ServBay configuratie)
# Bijvoorbeeld, als je een PHP-app gebruikt, zorg dat PHP-processen gestopt zijn

# Overschrijf het huidige databasebestand met de back-up
cp "$LATEST_BACKUP" /Applications/ServBay/db/sqlite/servbay.db

echo "Databaseherstel voltooid."

# Start de benodigde services of applicaties...

Belangrijk: Direct kopiëren kan leiden tot corruptie als het bestand nog actief wordt beschreven. Stop tijdens schrijven alle services, of gebruik de geavanceerde hot backup-API van SQLite, indien beschikbaar via je programmeertaal.

Prestatieoptimalisatie

SQLite 3 biedt verschillende PRAGMA-commando’s en indexeringsstrategieën om de prestaties te verhogen.

Indexoptimalisatie

Maak indexes aan voor kolommen die vaak voorkomen in WHERE-clausules, JOINs of ORDER BY, voor snellere queries.

sql

-- Unieke index op het 'email' veld in 'users'
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Index op 'product_name' in 'products'
CREATE INDEX idx_products_name ON products(product_name);

Query-optimalisatie

Gebruik EXPLAIN QUERY PLAN om te zien hoe SQLite je SQL uitvoert en knelpunten te ontdekken.

sql

EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = '[email protected]';
EXPLAIN QUERY PLAN SELECT product_name FROM products ORDER BY price DESC;

Bekijk of indexes worden gebruikt en of er volledige tabelscans optreden.

Configuratieoptimalisatie (PRAGMA)

Met PRAGMA’s stuur je de runtime-configuratie. Enkele relevante performance-PRAGMA's:

PRAGMA journal_mode; / PRAGMA journal_mode = mode;: Controleert het logboek. WAL (Write-Ahead Logging) biedt doorgaans betere prestaties en minder write locks bij gelijktijdige toegang. De standaardmode kan DELETE of TRUNCATE zijn. Voor betere write-prestaties stel je in op WAL:
sql
```
PRAGMA journal_mode = WAL;
```
1
PRAGMA synchronous; / PRAGMA synchronous = level;: Regelt de mate van bestandssysteem-synchronisatie. OFF (of 0) verhoogt vooral de schrijfsnelheid, met kans op dataverlies bij systeemcrashes. FULL (of 1) of NORMAL (of 2) bieden meer betrouwbaarheid maar zijn trager. Voor lokale ontwikkeling is OFF aanbevolen:
sql
```
PRAGMA synchronous = OFF;
```
1
PRAGMA cache_size; / PRAGMA cache_size = pages;: Bepaalt hoeveel pagina’s in het geheugen worden gebufferd. Vergroot de waarde voor minder disk-I/O en meer snelheid, met extra geheugengebruik.
sql
```
PRAGMA cache_size = -20000; -- Stelt cachegrootte op 20MB (negatief, dus in KB)
```
1

Let op: PRAGMA-instellingen gelden doorgaans alleen voor de huidige verbinding en moeten dus bij elke sessie/application-restart opnieuw worden toegepast.

Beveiligingsbeheer

Hoewel SQLite 3 een file-based database is, moet je ook in lokale omgevingen goede basisbeveiliging toepassen.

Bestandsrechten

Controleer of de bestandstoegang van de database strikt is, zodat enkel de ServBay-gebruiker (of de relevante applicatiegebruiker) lees- en schrijfrechten heeft, om ongeautoriseerde toegang te voorkomen.

bash

# Voorbeeld: eigendom aan huidige gebruiker, enkel lees/schrijfrechten voor eigenaar
# Pas aan afhankelijk van wie ServBay draait
chmod 600 /Applications/ServBay/db/sqlite/servbay.db

Data-encryptie

SQLite 3 bevat standaard geen encryptie. Als je met gevoelige data werkt en encryptie wilt, kun je uitbreidingen inzetten zoals SQLCipher. Hiervoor moet je extra libraries installeren en hun specifieke API’s gebruiken.

Veelgestelde Vragen en Oplossingen

Kan geen verbinding maken met SQLite 3 database

Controleer het databasepad: Controleer dat het pad in je verbindingsstring of CLI correct is en dat het bestand er daadwerkelijk staat.
Controleer bestandsrechten: Zorg dat de ServBay-gebruiker of je terminalgebruiker lees- en schrijfrechten heeft voor het databasebestand. Gebruik ls -l /Applications/ServBay/db/sqlite/servbay.db om te checken en corrigeer eventueel met chmod of chown.
Is het databasebestand corrupt? Probeer verbinding te maken via de sqlite3 CLI. Lukt dat niet of volgt er een foutmelding, dan is het bestand vermoedelijk corrupt. Herstel deze dan vanuit een back-up.

Database is locked (databasevergrendeling)

SQLite 3 vergrendelt het hele databasebestand tijdens schrijfbewerkingen. Als andere processen tegelijk proberen te schrijven, of onder sommige logmodes zelfs te lezen, kan vergrendeling optreden.

Controleer gelijktijdig gebruik: Zorg dat niet meerdere applicaties/servers/scripts proberen tegelijk naar dezelfde database te schrijven. Dit speelt vaak bij meerdere ontwikkelservers of scripts die hetzelfde bestand bewerken.
Gebruik WAL-mode: Door PRAGMA journal_mode = WAL; in te schakelen, verbeter je gelijktijdige lees/schrijfbewerkingen aanzienlijk. Stel deze PRAGMA direct na de databaseverbinding in.
Verwerk vergrendelingsfouten: Laat de applicatie bij een 'database is locked'-fout niet meteen falen, maar implementeer een retry-mechanisme met een korte wachttijd. SQLite libraries bieden vaak een ‘busy timeout’ functie, die standaard een bepaalde tijd herprobeert.
Bijvoorbeeld, in de Node.js sqlite3 library:
javascript
```
db.configure("busyTimeout", 5000); // Stel busy timeout in op 5000 ms (5 seconden)
```
1
In PHP's SQLite3-klasse bestaat geen directe busyTimeout methode; je kunt zelf een retry-loop implementeren, of laag-niveau funkties zoals sqlite_busy_timeout proberen (indien beschikbaar).

Samenvatting

ServBay is een krachtige, gebruiksvriendelijke lokale webontwikkelomgeving waarin SQLite 3 standaard geïntegreerd is. Met deze handleiding kun je installatie/verificatie, verbinden, basisbeheer, efficiënte back-ups en herstel, prestatieoptimalisatie en basale beveiliging toepassen op je SQLite 3 database in ServBay. Dankzij het lichte en gemakkelijke karakter is SQLite 3 uitermate geschikt voor lokale ontwikkeling, testen en kleine projecten. Gecombineerd met het brede gereedschapsaanbod van ServBay (PHP, Node.js, Python, Go en diverse webservers/databases) ontwikkel en test je probleemloos complexe applicaties.

Beheer en Gebruik van SQLite 3 Database ​

Overzicht ​

Installatie en Configuratie van SQLite 3 ​

Verificatie van SQLite 3 Installatie ​

SQLite 3 Database Aanmaken en Verbinden ​

Verbinden met de Commandoregeltool ​

Verbinden via Programmeertalen ​

PHP Voorbeeld ​

Node.js Voorbeeld ​

Databasebeheer ​

Basis SQL-operaties ​

Back-up en Herstel ​

Database Back-up ​

Herstellen van database ​

Prestatieoptimalisatie ​

Indexoptimalisatie ​

Query-optimalisatie ​

Configuratieoptimalisatie (PRAGMA) ​

Beveiligingsbeheer ​

Bestandsrechten ​

Data-encryptie ​

Veelgestelde Vragen en Oplossingen ​

Kan geen verbinding maken met SQLite 3 database ​

Database is locked (databasevergrendeling) ​

Samenvatting ​

Beheer en Gebruik van SQLite 3 Database

Overzicht

Installatie en Configuratie van SQLite 3

Verificatie van SQLite 3 Installatie

SQLite 3 Database Aanmaken en Verbinden

Verbinden met de Commandoregeltool

Verbinden via Programmeertalen

PHP Voorbeeld

Node.js Voorbeeld

Databasebeheer

Basis SQL-operaties

Back-up en Herstel

Database Back-up

Herstellen van database

Prestatieoptimalisatie

Indexoptimalisatie

Query-optimalisatie

Configuratieoptimalisatie (PRAGMA)

Beveiligingsbeheer

Bestandsrechten

Data-encryptie

Veelgestelde Vragen en Oplossingen

Kan geen verbinding maken met SQLite 3 database

Database is locked (databasevergrendeling)

Samenvatting