Tworzenie bazy danych SQLite: jak utworzyć nowy plik .db

Baza SQLite to po prostu plik

Jeśli korzystasz z MySQL lub Postgres, znasz schemat: uruchamiasz CREATE DATABASE myapp;, a serwer gdzieś poza zasięgiem wzroku rejestruje nową bazę. SQLite tak nie działa. Nie ma serwera. Nie ma instrukcji CREATE DATABASE. Baza danych to jeden zwykły plik na dysku, zwykle z rozszerzeniem .db, .sqlite lub .sqlite3, choć rozszerzenie to tylko konwencja.

Aby ją utworzyć, wskazujesz narzędziu sqlite3 nazwę pliku, który jeszcze nie istnieje:

sqlite3 mydata.db

I tyle. Jeśli mydata.db nie istnieje, SQLite jest gotowy, żeby go utworzyć. Jeśli istnieje, SQLite go otwiera. To samo polecenie w obu przypadkach. Model myślowy jest bliższy "otwarciu dokumentu" niż "uruchomieniu serwera bazy danych".

Plik pojawia się dopiero po zapisie

Oto coś, co zaskakuje wiele osób. Uruchom powyższe polecenie, wpisz .quit i zajrzyj do katalogu. Pliku nie ma.

SQLite jest leniwy. Nie tworzy pliku na dysku, dopóki nie ma czego w nim zapisać. W chwili, gdy utworzysz tabelę lub zatwierdzisz dane, plik się pojawia:

CREATE TABLE notes (
    id INTEGER PRIMARY KEY,
    body TEXT
);

Teraz mydata.db istnieje na dysku. Do pierwszego zapisu "baza" żyje tylko w pamięci połączenia. Czasem to myli, a czasem się przydaje, bo przerwana sesja nie zostawia śladu.

Tworzenie bazy z CLI

Cały proces od nowej powłoki:

$ sqlite3 mydata.db
SQLite version 3.45.0
Enter ".help" for usage hints.
sqlite> CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
sqlite> .databases
main: /home/you/mydata.db r/w
sqlite> .quit

Kilka przydatnych poleceń CLI na czas pracy:

• .databases: lista dołączonych baz i ścieżek ich plików.
• .tables: lista tabel w bieżącej bazie.
• .schema: instrukcje CREATE TABLE dla wszystkiego.
• .quit: wyjście.

Jeśli jesteś już w sqlite3 bez otwartego pliku (uruchomionym bez argumentu), możesz otworzyć plik przez .open:

sqlite> .open mydata.db

Ta sama zasada: tworzy plik, jeśli go brakuje, i otwiera, jeśli istnieje.

"If not exists" jest wbudowane

Częsta obawa z innych baz: co, jeśli plik już istnieje? Czy go nadpiszę? Nie. Otwarcie istniejącego pliku po prostu go otwiera. Nic nie zostaje nadpisane. Najbliższy odpowiednik CREATE DATABASE IF NOT EXISTS to... po prostu otwarcie pliku. SQLite obsługuje oba przypadki tym samym poleceniem.

Jeśli celowo chcesz zacząć od zera, najpierw usuń plik:

rm mydata.db
sqlite3 mydata.db

Upewnij się jednak, że tego chcesz. Nie ma cofania ani narzędzia administracyjnego, które by go odzyskało.

Tworzenie bazy z Pythona

Przez większość czasu nie będziesz w CLI, tylko będziesz tworzyć bazy z kodu aplikacji. Python ma w bibliotece standardowej moduł sqlite3, więc nic nie trzeba instalować:

-- Odpowiednik w Pythonie:
-- import sqlite3
-- conn = sqlite3.connect("mydata.db")
-- conn.execute("CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT)")
-- conn.commit()
-- conn.close()

CREATE TABLE notes (
    id INTEGER PRIMARY KEY,
    body TEXT
);

sqlite3.connect("mydata.db") działa według tej samej zasady co CLI: tworzy plik, jeśli go brakuje, i otwiera, jeśli istnieje. Każde inne powiązanie językowe (better-sqlite3 w Node, sterownik database/sql w Go, rusqlite w Rust itd.) działa tak samo, bo wszystkie opakowują pod spodem tę samą bibliotekę C.

Jeden skrót specyficzny dla Pythona: przekazanie ":memory:" jako ścieżki daje bazę, która żyje w całości w RAM i znika po zamknięciu połączenia. O tym na następnej stronie.

Gdzie powinien leżeć plik?

Skoro baza jest plikiem, pytanie "gdzie ją umieścić?" jest całkiem realne. Kilka praktycznych zasad:

• W aplikacji: gdzieś w katalogu danych użytkownika albo w katalogu roboczym aplikacji. Nie umieszczaj jej obok pliku wykonywalnego w systemach, gdzie ta ścieżka nie jest zapisywalna.
• W projekcie: w katalogu głównym repozytorium albo w folderze data/. Dodaj ją do .gitignore, jeśli przechowuje lokalny stan: wrzucanie binarnej bazy do Gita rzadko dobrze się kończy.
• W testach: użyj :memory: albo pliku tymczasowego. Szybkie i samo po sobie sprząta.

Uprawnienia też mają znaczenie. Użytkownik, na którym działa proces, potrzebuje prawa odczytu i zapisu zarówno do pliku, jak i do katalogu nadrzędnego (SQLite tworzy obok bazy pliki blokad podczas zapisów).

Szybki test poprawności

Aby sprawdzić, że cały proces działa od początku do końca:

CREATE TABLE greetings (
    id INTEGER PRIMARY KEY,
    message TEXT NOT NULL
);

INSERT INTO greetings (message) VALUES ('hello'), ('world');

SELECT * FROM greetings;

Powinny wrócić dwa wiersze. Jeśli to działa, plik bazy istnieje, schemat jest w nim zapisany, a dane można odpytywać. To cała historia "tworzenia bazy" w SQLite: bez serwera, bez użytkowników, bez tabeli uprawnień. Tylko plik.

Dalej: bazy danych w pamięci

To samo wywołanie sqlite3.connect(...), które tworzy plik, z odpowiednim argumentem utworzy bazę, która w ogóle nie dotyka dysku. Bazy w pamięci to najszybszy sposób na uruchamianie testów, prototypowanie schematu czy przechowywanie danych tymczasowych. O tym na następnej stronie.