Cơ sở dữ liệu SQLite
SQLite là một database engine độc lập, dựa trên file. Khác với MySQL hoặc PostgreSQL, SQLite không yêu cầu server - toàn bộ database được lưu trữ trong một file duy nhất trên Mac của bạn.
Thiết lập nhanh
Mở Form kết nối
Click New Connection từ màn hình Welcome hoặc File > New Connection
Chọn SQLite
Chọn SQLite từ danh sách loại cơ sở dữ liệu
Chọn file Database
Click Browse và chọn file .sqlite, .db, hoặc .sqlite3 của bạn
Tạo kết nối
Đặt tên cho kết nối và click Create
Cài đặt kết nối
Kết nối SQLite rất đơn giản vì không có server:
| Trường | Mô tả |
|---|
| Name | Tên thân thiện cho kết nối này |
| File Path | Đường dẫn đầy đủ đến file database SQLite |
Không cần username, password hoặc cài đặt mạng cho database SQLite.
Vị trí SQLite phổ biến
Database ứng dụng
Nhiều ứng dụng macOS sử dụng SQLite:
| Ứng dụng | Vị trí Database |
|---|
| Safari | ~/Library/Safari/History.db |
| Photos | ~/Pictures/Photos Library.photoslibrary/database/Photos.sqlite |
| Messages | ~/Library/Messages/chat.db |
| Notes | ~/Library/Group Containers/group.com.apple.notes/NoteStore.sqlite |
Database hệ thống có thể bị khóa khi ứng dụng cha đang chạy. Thoát ứng dụng trước khi mở database của nó.
Database phát triển
Vị trí database phát triển phổ biến:
# Ruby on Rails
./db/development.sqlite3
# Django
./db.sqlite3
# iOS Simulator
~/Library/Developer/CoreSimulator/Devices/*/data/Containers/Data/Application/*/Documents/*.sqlite
# Core Data
*.sqlite (trong container của app)
Tạo Database mới
Để tạo database SQLite mới:
- Tạo kết nối mới
- Nhập đường dẫn cho file chưa tồn tại
- TablePro sẽ tạo file khi bạn kết nối
Hoặc sử dụng command line:
sqlite3 ~/Documents/newdatabase.sqlite "SELECT 1;"
Tính năng
Trình duyệt Database
Sidebar hiển thị tất cả bảng và views trong database SQLite của bạn:
Thông tin bảng
Cho mỗi bảng, TablePro cung cấp:
- Structure: Tên cột, kiểu và constraints
- Indexes: Tất cả indexes bao gồm UNIQUE constraints
- DDL: Câu lệnh CREATE TABLE
Bảng hệ thống SQLite
TablePro có thể hiển thị các bảng nội bộ của SQLite:
| Bảng | Mục đích |
|---|
sqlite_master | Thông tin schema |
sqlite_sequence | Theo dõi AUTOINCREMENT |
Thực thi truy vấn
Thực thi truy vấn với hỗ trợ đầy đủ cú pháp SQLite:
-- JSON functions (SQLite 3.38+)
SELECT json_extract(data, '$.name') as name
FROM users
WHERE json_extract(data, '$.active') = true;
-- Window functions
SELECT
category,
product,
price,
rank() OVER (PARTITION BY category ORDER BY price DESC) as rank
FROM products;
-- CTEs
WITH RECURSIVE
cnt(x) AS (
SELECT 1
UNION ALL
SELECT x+1 FROM cnt WHERE x < 10
)
SELECT x FROM cnt;
-- Full-text search (nếu FTS được bật)
SELECT * FROM documents WHERE documents MATCH 'sqlite AND database';
-- UPSERT (INSERT OR REPLACE)
INSERT INTO settings (key, value)
VALUES ('theme', 'dark')
ON CONFLICT(key) DO UPDATE SET value = excluded.value;
Kiểu dữ liệu SQLite
SQLite sử dụng hệ thống kiểu động với type affinity:
| Affinity | Mô tả | Ví dụ |
|---|
| TEXT | Dữ liệu chuỗi | VARCHAR, TEXT, CLOB |
| INTEGER | Signed integer | INT, INTEGER, BIGINT |
| REAL | Floating point | REAL, DOUBLE, FLOAT |
| NUMERIC | Numeric hoặc text | DECIMAL, BOOLEAN, DATE |
| BLOB | Binary data | BLOB, raw bytes |
SQLite chấp nhận bất kỳ kiểu dữ liệu nào trong bất kỳ cột nào (trừ INTEGER PRIMARY KEY). Kiểu là gợi ý, không phải constraint.
Làm việc với file lớn
Kích thước file
Database SQLite có thể phát triển khá lớn. TablePro xử lý:
- Database lên đến vài GB
- Bảng với hàng triệu dòng
- Dữ liệu BLOB (hình ảnh, files)
Mẹo hiệu năng
- Sử dụng phân trang: Bật phân trang trong settings cho bảng lớn
- Thêm indexes: Tạo indexes cho các cột được query thường xuyên
- Sử dụng LIMIT: Luôn giới hạn result sets khi khám phá
- Vacuum định kỳ: Thu hồi không gian với
VACUUM
-- Kiểm tra kích thước database
SELECT page_count * page_size as size FROM pragma_page_count(), pragma_page_size();
-- Phân tích bảng cho tối ưu hóa query
ANALYZE;
-- Thu hồi không gian không dùng
VACUUM;
Khắc phục sự cố
Database is Locked
Triệu chứng: “database is locked” hoặc “database table is locked”
Nguyên nhân:
- Ứng dụng khác đang mở database
- Truy vấn trước đó vẫn đang chạy
- Xung đột chế độ Write-ahead logging (WAL)
Giải pháp:
- Đóng các ứng dụng khác sử dụng database
- Chờ các truy vấn đang chạy hoàn thành
- Kiểm tra các file WAL:
ls -la /path/to/database.sqlite*
# Bạn có thể thấy: database.sqlite-wal, database.sqlite-shm
Cannot Open Database
Triệu chứng: “unable to open database file”
Nguyên nhân và Giải pháp:
- File không tồn tại: Xác minh đường dẫn chính xác
- Permission denied: Kiểm tra quyền file
ls -la /path/to/database.sqlite
chmod 644 /path/to/database.sqlite
- Đường dẫn chứa ký tự đặc biệt: Sử dụng đường dẫn tuyệt đối không có khoảng trắng
Corrupt Database
Triệu chứng: “database disk image is malformed”
Tùy chọn khôi phục:
-
Kiểm tra tính toàn vẹn:
-
Thử khôi phục:
sqlite3 corrupt.sqlite ".recover" | sqlite3 recovered.sqlite
-
Khôi phục từ backup (nếu có)
Thay đổi Schema không hiển thị
Triệu chứng: Bảng hoặc cột bạn tạo không hiển thị
Giải pháp: Làm mới kết nối:
- Right-click kết nối
- Chọn Refresh
Hoặc ngắt kết nối và kết nối lại.
SQLite Pragmas
TablePro hỗ trợ lệnh PRAGMA SQLite cho cấu hình:
-- Kiểm tra phiên bản SQLite
SELECT sqlite_version();
-- Hiển thị thông tin bảng
PRAGMA table_info(users);
-- Kiểm tra foreign keys có được bật không
PRAGMA foreign_keys;
-- Bật thực thi foreign key
PRAGMA foreign_keys = ON;
-- Kiểm tra chế độ journal
PRAGMA journal_mode;
-- Sử dụng chế độ WAL cho đồng thời tốt hơn
PRAGMA journal_mode = WAL;
Backup và Restore
Backup thủ công
Database SQLite chỉ là files. Để backup:
# Copy đơn giản (đảm bảo database không được ghi)
cp database.sqlite database-backup.sqlite
# Lệnh backup SQLite (an toàn hơn, xử lý locks)
sqlite3 database.sqlite ".backup backup.sqlite"
Export sang SQL
Export database của bạn dưới dạng SQL statements:
sqlite3 database.sqlite .dump > backup.sql
Restore từ SQL
sqlite3 newdatabase.sqlite < backup.sql
Khác biệt với Server Databases
| Tính năng | SQLite | MySQL/PostgreSQL |
|---|
| Yêu cầu server | Không | Có |
| Multiple connections | Giới hạn | Không giới hạn |
| Xác thực người dùng | Không | Có |
| Truy cập mạng | Dựa trên file | TCP/IP |
| Concurrent writes | Giới hạn | Hỗ trợ đầy đủ |
| Stored procedures | Không | Có |
| Kiểu dữ liệu | Dynamic | Static |
Các trường hợp sử dụng phổ biến
SQLite lý tưởng cho:
- Phát triển cục bộ: Tạo prototype ứng dụng nhanh chóng
- Embedded apps: Mobile apps, desktop apps
- Single-user apps: Database cá nhân
- Phân tích dữ liệu: Khám phá dữ liệu nhanh
- Testing: Unit tests với isolated databases
- Prototyping: Trước khi migrate sang server database
Bước tiếp theo
