SQLite — практическая работа и API

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВ

Разработчику Аналитику Инженеру

Другие СУБД в этом цикле: PostgreSQL · MySQL · Microsoft SQL Server · SQL — о разделе.

SQLite — встраиваемая реляционная СУБД. Она работает как библиотека в процессе приложения и хранит базу в одном файле (.sqlite, .db, .sqlite3).

Подходит для:

• мобильных и десктопных приложений;
• локальных инструментов и CLI-утилит;
• тестовых стендов и прототипов;
• edge/embedded-сценариев с одним файлом БД.

---

Как работает SQLite

• Нет отдельного сервера и демона.
• Подключение идёт к файлу БД.
• Транзакции соответствуют ACID.
• Параллельность построена вокруг файловых блокировок, режим WAL повышает конкурентность чтения и записи.
• Типизация динамическая через type affinity.

PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;
PRAGMA synchronous = NORMAL;

---

Подключение и запуск

CLI

sqlite3 app.db

Полезные мета-команды SQLite CLI:

.tables
.schema
.indexes users
.mode column
.headers on
.import users.csv users
.output dump.sql
.dump

URI-подключение

file:app.db
file:app.db?mode=ro
file:memdb1?mode=memory&cache=shared

---

Типы данных и синтаксис

SQLite использует storage classes:

• NULL
• INTEGER
• REAL
• TEXT
• BLOB

Type affinity колонок:

• INTEGER
• TEXT
• BLOB
• REAL
• NUMERIC

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    age INTEGER,
    balance NUMERIC,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

---

Возможности SQLite

• Транзакции (BEGIN, COMMIT, ROLLBACK, SAVEPOINT)
• Индексы, составные индексы, частичные индексы
• CHECK, UNIQUE, FOREIGN KEY (при PRAGMA foreign_keys = ON)
• VIEW, TRIGGER
• CTE и рекурсивные CTE
• Оконные функции (современные версии SQLite)
• UPSERT через ON CONFLICT
• JSON-функции (с расширением JSON1, обычно включено)
• FTS-поиск (FTS5)

---

Доступные и недоступные команды

Категория	Команда / конструкция	Статус в SQLite	Комментарий
DML	SELECT, INSERT, UPDATE, DELETE	Доступно	Базовый SQL поддерживается
DDL	CREATE TABLE, CREATE INDEX, ALTER TABLE, DROP	Доступно частично	ALTER TABLE ограничен
Транзакции	BEGIN, COMMIT, ROLLBACK, SAVEPOINT	Доступно	Полная транзакционная модель
Конфликты	INSERT ... ON CONFLICT DO ...	Доступно	Полноценный UPSERT
Чтение схемы	PRAGMA table_info, sqlite_master	Доступно	Системные метаданные
Сессии/пользователи	CREATE USER, GRANT, REVOKE	Недоступно	Нет встроенной серверной модели ролей
Серверные функции	LISTEN/NOTIFY, фоновые worker-процессы	Недоступно	Нет серверного процесса
Хранимые процедуры	CREATE PROCEDURE	Недоступно	Логика через SQL + приложение
Партиционирование	декларативное партиционирование	Недоступно	Реализуют на уровне приложения
Материализованные представления	CREATE MATERIALIZED VIEW	Недоступно	Можно имитировать таблицей и триггерами
Права на схемы	ACL/roles как в PostgreSQL	Недоступно	Контроль доступа на уровне файла ОС

---

Практика SQL в SQLite

Подключение ограничений

PRAGMA foreign_keys = ON;

CRUD

CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  done INTEGER NOT NULL DEFAULT 0
);

INSERT INTO tasks(title) VALUES ('Пройти SQLite');
SELECT id, title, done FROM tasks;
UPDATE tasks SET done = 1 WHERE id = 1;
DELETE FROM tasks WHERE id = 1;

UPSERT

INSERT INTO tasks(id, title, done)
VALUES (1, 'Обновлённая задача', 0)
ON CONFLICT(id) DO UPDATE SET
  title = excluded.title,
  done = excluded.done;

Транзакция

BEGIN;
INSERT INTO tasks(title) VALUES ('A');
INSERT INTO tasks(title) VALUES ('B');
COMMIT;

---

Браузеры и инструменты

• DB Browser for SQLite
• SQLiteStudio
• DBeaver
• DataGrip
• VS Code + SQLite extensions
• Beekeeper Studio

---

API по языкам — подключение, CRUD, библиотеки, классы и функции

Принцип чтения API-блоков

В каждом блоке указаны: библиотека, ключевые классы/типы, свойства и методы, затем рабочий пример подключения и CRUD.

---

C# — Microsoft.Data.Sqlite

Библиотека

• Microsoft.Data.Sqlite (NuGet)

Ключевые классы

• SqliteConnection
• SqliteCommand
• SqliteTransaction
• SqliteDataReader
• SqliteParameter
• SqliteConnectionStringBuilder

Свойства и методы

• SqliteConnection.ConnectionString, Open(), Close(), BeginTransaction()
• SqliteCommand.CommandText, Parameters.AddWithValue(), ExecuteNonQuery(), ExecuteScalar(), ExecuteReader()
• SqliteDataReader.Read(), индексатор reader["column"]

using Microsoft.Data.Sqlite;

var cs = new SqliteConnectionStringBuilder
{
    DataSource = "app.db",
    Mode = SqliteOpenMode.ReadWriteCreate
}.ToString();

using var conn = new SqliteConnection(cs);
conn.Open();

using (var cmd = conn.CreateCommand())
{
    cmd.CommandText = """
        CREATE TABLE IF NOT EXISTS users(
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL
        );
    """;
    cmd.ExecuteNonQuery();
}

using (var tx = conn.BeginTransaction())
{
    var insert = conn.CreateCommand();
    insert.Transaction = tx;
    insert.CommandText = "INSERT INTO users(name) VALUES ($name)";
    insert.Parameters.AddWithValue("$name", "Anna");
    insert.ExecuteNonQuery();
    tx.Commit();
}

var select = conn.CreateCommand();
select.CommandText = "SELECT id, name FROM users";
using var reader = select.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine($"{reader["id"]}: {reader["name"]}");
}

---

Python — sqlite3 (stdlib)

Библиотека

• стандартный модуль sqlite3

Ключевые классы

• sqlite3.Connection
• sqlite3.Cursor
• sqlite3.Row

Свойства и методы

• sqlite3.connect(path)
• Connection.execute(), cursor(), commit(), rollback(), close()
• Cursor.execute(), executemany(), fetchone(), fetchall()
• Connection.row_factory

import sqlite3

conn = sqlite3.connect("app.db")
conn.row_factory = sqlite3.Row
cur = conn.cursor()

cur.execute("""
CREATE TABLE IF NOT EXISTS users(
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
)
""")

cur.execute("INSERT INTO users(name) VALUES (?)", ("Anna",))
conn.commit()

cur.execute("SELECT id, name FROM users")
rows = cur.fetchall()
for row in rows:
    print(row["id"], row["name"])

cur.execute("UPDATE users SET name=? WHERE id=?", ("Ann", 1))
cur.execute("DELETE FROM users WHERE id=?", (1,))
conn.commit()
conn.close()

---

Java — JDBC + sqlite-jdbc

Библиотека

• org.xerial:sqlite-jdbc

Ключевые классы

• java.sql.Connection
• PreparedStatement
• Statement
• ResultSet
• DriverManager

Свойства и методы

• DriverManager.getConnection("jdbc:sqlite:app.db")
• Connection.prepareStatement(), setAutoCommit(false), commit(), rollback()
• PreparedStatement.setString(), executeUpdate(), executeQuery()
• ResultSet.next(), getInt(), getString()

import java.sql.*;

try (Connection conn = DriverManager.getConnection("jdbc:sqlite:app.db")) {
    try (Statement st = conn.createStatement()) {
        st.execute("""
            CREATE TABLE IF NOT EXISTS users(
                id INTEGER PRIMARY KEY,
                name TEXT NOT NULL
            )
        """);
    }

    try (PreparedStatement ins = conn.prepareStatement(
            "INSERT INTO users(name) VALUES (?)")) {
        ins.setString(1, "Anna");
        ins.executeUpdate();
    }

    try (PreparedStatement sel = conn.prepareStatement(
            "SELECT id, name FROM users");
         ResultSet rs = sel.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getInt("id") + " " + rs.getString("name"));
        }
    }
}

---

C — нативный SQLite C API

Библиотека

• sqlite3 (официальная библиотека)

Ключевые типы

• sqlite3* (connection handle)
• sqlite3_stmt* (prepared statement)

Ключевые функции

• Подключение: sqlite3_open(), sqlite3_open_v2(), sqlite3_close_v2()
• Выполнение: sqlite3_exec()
• Подготовленные запросы: sqlite3_prepare_v2(), sqlite3_bind_*(), sqlite3_step(), sqlite3_finalize()
• Чтение данных: sqlite3_column_int(), sqlite3_column_text()
• Ошибки: sqlite3_errmsg(), sqlite3_errcode()
• Транзакции: обычные SQL-команды BEGIN/COMMIT/ROLLBACK

#include <sqlite3.h>
#include <stdio.h>

int main(void) {
    sqlite3 *db = NULL;
    if (sqlite3_open("app.db", &db) != SQLITE_OK) return 1;

    sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY, name TEXT NOT NULL);", 0, 0, 0);

    sqlite3_stmt *stmt = NULL;
    sqlite3_prepare_v2(db, "INSERT INTO users(name) VALUES (?);", -1, &stmt, NULL);
    sqlite3_bind_text(stmt, 1, "Anna", -1, SQLITE_TRANSIENT);
    sqlite3_step(stmt);
    sqlite3_finalize(stmt);

    sqlite3_prepare_v2(db, "SELECT id, name FROM users;", -1, &stmt, NULL);
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        int id = sqlite3_column_int(stmt, 0);
        const unsigned char *name = sqlite3_column_text(stmt, 1);
        printf("%d %s\n", id, name);
    }
    sqlite3_finalize(stmt);
    sqlite3_close_v2(db);
    return 0;
}

---

C++ — SQLiteCpp и нативный C API

Библиотеки

• SQLiteCpp (обёртка)
• нативный sqlite3 через extern "C"

Ключевые классы SQLiteCpp

• SQLite::Database
• SQLite::Statement
• SQLite::Transaction
• SQLite::Column
• SQLite::Exception

Свойства и методы

• Database("app.db", SQLite::OPEN_READWRITE|SQLite::OPEN_CREATE)
• exec()
• Statement.bind(), executeStep(), exec()
• Column.getInt(), Column.getString()

#include <SQLiteCpp/SQLiteCpp.h>
#include <iostream>

int main() {
    SQLite::Database db("app.db", SQLite::OPEN_READWRITE | SQLite::OPEN_CREATE);
    db.exec("CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY, name TEXT NOT NULL)");

    {
        SQLite::Statement q(db, "INSERT INTO users(name) VALUES (?)");
        q.bind(1, "Anna");
        q.exec();
    }

    SQLite::Statement s(db, "SELECT id, name FROM users");
    while (s.executeStep()) {
        std::cout << s.getColumn(0).getInt() << " " << s.getColumn(1).getString() << "\n";
    }
}

---

Go — database/sql + драйвер SQLite

Библиотеки

• database/sql (стандарт)
• драйвер github.com/mattn/go-sqlite3 или modernc.org/sqlite

Ключевые типы

• *sql.DB
• *sql.Tx
• *sql.Stmt
• *sql.Rows
• sql.Result

Свойства и методы

• sql.Open("sqlite3", "app.db")
• DB.Exec(), DB.Query(), DB.QueryRow(), DB.Begin()
• Tx.Exec(), Tx.Commit(), Tx.Rollback()
• Rows.Next(), Rows.Scan()

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/mattn/go-sqlite3"
)

func main() {
	db, _ := sql.Open("sqlite3", "app.db")
	defer db.Close()

	db.Exec(`CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY, name TEXT NOT NULL)`)
	db.Exec(`INSERT INTO users(name) VALUES (?)`, "Anna")

	rows, _ := db.Query(`SELECT id, name FROM users`)
	defer rows.Close()
	for rows.Next() {
		var id int
		var name string
		rows.Scan(&id, &name)
		fmt.Println(id, name)
	}
}

---

PHP — PDO_SQLITE и SQLite3

Библиотеки

• PDO + DSN sqlite:app.db
• встроенный класс SQLite3

Ключевые классы PDO

• PDO
• PDOStatement

Ключевые классы SQLite3

• SQLite3
• SQLite3Stmt
• SQLite3Result

Свойства и методы

• new PDO('sqlite:app.db'), prepare(), execute(), fetchAll()
• new SQLite3('app.db'), prepare(), bindValue(), query(), querySingle()

<?php
$db = new PDO('sqlite:app.db');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$db->exec("CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY, name TEXT NOT NULL)");

$stmt = $db->prepare("INSERT INTO users(name) VALUES (:name)");
$stmt->execute([':name' => 'Anna']);

$rows = $db->query("SELECT id, name FROM users")->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
    echo $row['id'] . " " . $row['name'] . PHP_EOL;
}

---

Kotlin — JDBC и Android SQLite API

Библиотеки и платформы

• JVM: JDBC (sqlite-jdbc)
• Android: SQLiteOpenHelper, SQLiteDatabase

Ключевые классы JVM

• java.sql.Connection
• PreparedStatement
• ResultSet

Ключевые классы Android

• SQLiteOpenHelper
• SQLiteDatabase
• ContentValues
• Cursor

Методы Android

• onCreate(), onUpgrade()
• writableDatabase, readableDatabase
• insert(), update(), delete(), query(), rawQuery()

import java.sql.DriverManager

fun main() {
    DriverManager.getConnection("jdbc:sqlite:app.db").use { conn ->
        conn.createStatement().use { st ->
            st.execute("CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY, name TEXT NOT NULL)")
        }
        conn.prepareStatement("INSERT INTO users(name) VALUES (?)").use { ps ->
            ps.setString(1, "Anna")
            ps.executeUpdate()
        }
        conn.prepareStatement("SELECT id, name FROM users").use { ps ->
            ps.executeQuery().use { rs ->
                while (rs.next()) println("${rs.getInt("id")} ${rs.getString("name")}")
            }
        }
    }
}

---

Rust — rusqlite

Библиотека

• rusqlite

Ключевые типы

• Connection
• Transaction
• Statement
• Row
• Result<T>

Методы

• Connection::open()
• execute(), prepare(), query_map()
• transaction(), commit(), rollback()
• макрос params![]

use rusqlite::{params, Connection, Result};

fn main() -> Result<()> {
    let conn = Connection::open("app.db")?;
    conn.execute(
        "CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY, name TEXT NOT NULL)",
        [],
    )?;
    conn.execute("INSERT INTO users(name) VALUES (?1)", params!["Anna"])?;

    let mut stmt = conn.prepare("SELECT id, name FROM users")?;
    let rows = stmt.query_map([], |row| {
        Ok((row.get::<_, i64>(0)?, row.get::<_, String>(1)?))
    })?;

    for row in rows {
        let (id, name) = row?;
        println!("{id} {name}");
    }
    Ok(())
}

---

JavaScript — Node.js (better-sqlite3 и sqlite3)

Библиотеки

• better-sqlite3 (синхронный, быстрый, простой API)
• sqlite3 (асинхронный callback API)

Ключевые классы/объекты better-sqlite3

• Database
• Statement
• методы prepare(), run(), get(), all(), transaction()

Ключевые объекты sqlite3

• Database
• методы run(), get(), all(), serialize(), close()

import Database from "better-sqlite3";

const db = new Database("app.db");
db.exec("CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY, name TEXT NOT NULL)");

const ins = db.prepare("INSERT INTO users(name) VALUES (?)");
ins.run("Anna");

const rows = db.prepare("SELECT id, name FROM users").all();
for (const row of rows) {
  console.log(row.id, row.name);
}

db.prepare("UPDATE users SET name = ? WHERE id = ?").run("Ann", 1);
db.prepare("DELETE FROM users WHERE id = ?").run(1);
db.close();

---

Минимальный шаблон безопасной работы

• Всегда используйте параметризованные запросы (?, $1, :name).
• Включайте foreign_keys.
• Явно управляйте транзакциями в пакетных операциях.
• Делайте резервную копию файла БД перед миграциями.
• Для многопоточного чтения/записи включайте WAL.

PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;

---

Когда SQLite подходит и когда лучше серверная СУБД

SQLite подходит:

• локальное приложение и один файл данных;
• небольшая и средняя нагрузка;
• автономный режим и простой деплой.

Серверная СУБД лучше:

• много одновременных записей;
• централизованные роли и права;
• горизонтальное масштабирование;
• репликация и кластерные сценарии.

---