SQLite CLI: powłoka sqlite3 w wierszu poleceń

Czym jest CLI

sqlite3 to oficjalna powłoka wiersza poleceń dostarczana z SQLite. Otwiera plik bazy, daje ci znak zachęty i pozwala uruchamiać na niej SQL. Bez serwera, bez logowania, bez konfiguracji: tylko plik wykonywalny i plik bazy.

Będziesz jej używać do szybkich inspekcji, jednorazowych zapytań, zrzutów schematu i importów. To najszybszy sposób, by zajrzeć do bazy bez pisania kodu aplikacji.

$ sqlite3 mydata.db
SQLite version 3.45.0
Enter ".help" for usage hints.
sqlite>

Jeśli mydata.db jeszcze nie istnieje, SQLite utworzy go, gdy tylko wprowadzisz pierwszą zmianę. Uruchomienie sqlite3 bez nazwy pliku otwiera jednorazową bazę w pamięci.

Uruchamianie SQL przy znaku zachęty

Gdy już jesteś w powłoce, wpisuj SQL i kończ każdą instrukcję średnikiem. Powłoka czeka na ten średnik: jeśli naciśniesz Enter bez niego, pokaże tylko znak kontynuacji.

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  year INTEGER
);

INSERT INTO books (title, year) VALUES ('Dune', 1965);
INSERT INTO books (title, year) VALUES ('Neuromancer', 1984);

SELECT * FROM books;

Średnik to sygnał "gotowe, uruchom to". Zapomnij o nim, a powłoka będzie czekać ze znakiem ...>. Wpisz ; w nowej linii, by zakończyć.

Polecenia z kropką to nie SQL

Wszystko, co zaczyna się od ., to polecenie z kropką (dot command): instrukcja dla samej powłoki, a nie dla silnika SQL. Nie wymagają średników i działają tylko w powłoce sqlite3.

sqlite> .tables
books

sqlite> .schema books
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  year INTEGER
);

sqlite> .help

Te, których będziesz używać cały czas:

• .tables: wypisuje wszystkie tabele w bieżącej bazie.
• .schema [table]: pokazuje instrukcję CREATE TABLE dla jednej tabeli albo dla wszystkich.
• .databases: wypisuje podłączone pliki baz.
• .mode <format>: zmienia sposób wyświetlania wyników zapytań.
• .headers on: pokazuje nazwy kolumn nad wynikami zapytań.
• .read file.sql: wykonuje plik SQL.
• .exit lub .quit: zamyka powłokę.

.help wypisuje pełną listę. Poleceń są dziesiątki, ale będziesz korzystać może z dziesięciu.

Czytelny wynik

Domyślny wynik jest rozdzielony pionowymi kreskami i nie ma nagłówków: dobrze dla skryptów, brzydko do czytania. Dwa polecenia z kropką to naprawiają:

sqlite> .headers on
sqlite> .mode column
sqlite> SELECT * FROM books;
id  title        year
--  -----------  ----
1   Dune         1965
2   Neuromancer  1984

Inne przydatne tryby:

• .mode box: rysuje tabelę z ramkami Unicode. Ładnie wygląda na zrzutach ekranu.
• .mode csv: wartości rozdzielone przecinkami, dobre do przekazywania do innych narzędzi.
• .mode json: wynik w JSON, jeden obiekt na wiersz.
• .mode line: jedna kolumna na linię, przydatne przy szerokich wierszach.

Ustawienia resetują się przy każdym uruchomieniu powłoki. Aby były trwałe, wpisz je do pliku ~/.sqliterc, a sqlite3 wczyta go przy starcie.

Import i eksport CSV

CLI ma wbudowaną obsługę CSV. Przełącz się na tryb CSV, a potem zrób .import pliku do tabeli:

sqlite> .mode csv
sqlite> .import authors.csv authors
sqlite> SELECT * FROM authors LIMIT 3;

Jeśli tabela nie istnieje, .import tworzy ją, używając pierwszego wiersza jako nazw kolumn. Jeśli istnieje, pierwszy wiersz jest traktowany jako dane, chyba że przekażesz --skip 1.

W drugą stronę przekieruj wynik do pliku:

sqlite> .headers on
sqlite> .mode csv
sqlite> .output books.csv
sqlite> SELECT * FROM books;
sqlite> .output stdout

.output filename wysyła wynik każdego kolejnego zapytania do tego pliku, dopóki nie wrócisz przez .output stdout. To ten sam mechanizm bez względu na to, czy eksportujesz CSV, JSON, czy surowy zrzut schematu.

Uruchamianie plików SQL

Dla czegokolwiek dłuższego niż kilka instrukcji zapisz SQL w pliku i go uruchom. Wewnątrz powłoki:

sqlite> .read setup.sql

Albo z normalnego terminala, w ogóle bez wchodzenia do powłoki:

$ sqlite3 mydata.db < setup.sql
$ sqlite3 mydata.db "SELECT count(*) FROM books;"

Druga forma uruchamia jedno zapytanie i kończy działanie: idealne do skryptów powłoki albo szybkich sprawdzeń. Jeśli potrzebujesz sformatowanego wyniku, połącz ją z ustawieniami .mode przez flagę -cmd:

$ sqlite3 -header -column mydata.db "SELECT * FROM books;"

Badanie nieznanej bazy

Gdy ktoś wręcza ci plik .db, a ty nie masz pojęcia, co w nim jest, oto procedura:

$ sqlite3 mystery.db
sqlite> .tables
sqlite> .schema
sqlite> .indexes
sqlite> SELECT count(*) FROM some_table;

.schema bez argumentu wypisuje każde CREATE TABLE, CREATE INDEX i CREATE TRIGGER z pliku. .indexes wypisuje indeksy dla każdej tabeli. Pięć minut w powłoce mówi o bazie więcej niż zwykle jakikolwiek README.

Aby dostać pełny tekstowy zrzut, który odtworzysz gdzie indziej, użyj .dump:

sqlite> .output backup.sql
sqlite> .dump
sqlite> .output stdout

To zapisuje skrypt SQL, który po przepuszczeniu przez sqlite3 w pełni odtwarza bazę: schemat i dane.

Wychodzenie z powłoki

Trzy równoważne sposoby wyjścia:

sqlite> .exit
sqlite> .quit

Albo naciśnij Ctrl+D w macOS i Linuksie (Ctrl+Z, a potem Enter w Windows). Twoje zmiany są już zapisane: SQLite zatwierdza każdą instrukcję w chwili jej wykonania, chyba że jawnie rozpoczniesz transakcję.

Dalej: narzędzia GUI dla SQLite

CLI jest szybkie i łatwo je oskryptować, ale czasem chcesz przeklikać się przez tabele, sortować kolumny i edytować wiersze wizualnie. Jest kilka solidnych narzędzi GUI dla SQLite i o nich jest następna strona.