SQLiteのSQL方言 — UPSERT・RETURNING・JSON関数・ALTER TABLE

CREATE TABLE page_views (
  path TEXT NOT NULL PRIMARY KEY,
  count INTEGER NOT NULL DEFAULT 0,
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
) STRICT;
 
INSERT INTO page_views (path, count) VALUES ('/books', 1)
ON CONFLICT(path) DO UPDATE SET
  count = count + 1,
  updated_at = datetime('now');

INSERT INTO users (email, name) VALUES ('a@example.com', '田中太郎')
ON CONFLICT(email) DO UPDATE SET
  name = excluded.name;  -- 新しく渡した '田中太郎' で上書き

INSERT INTO tags (name) VALUES ('SQLite')
ON CONFLICT(name) DO NOTHING;

INSERT INTO users (name, email) VALUES ('佐藤', 'sato@example.com')
RETURNING id, created_at;

-- RETURNINGがない場合の従来のやり方
INSERT INTO users (name) VALUES ('佐藤');
SELECT last_insert_rowid();

DELETE FROM sessions WHERE expires_at < datetime('now')
RETURNING id, user_id;

SELECT datetime('now');              -- 現在時刻（UTC）
SELECT date('2026-08-15 10:30:00');  -- 2026-08-15
SELECT datetime(1786000000, 'unixepoch');

SELECT datetime('now', 'localtime');  -- 実行環境のタイムゾーンに変換

-- 今月の初日
SELECT date('now', 'start of month');
 
-- 今月の最終日
SELECT date('now', 'start of month', '+1 month', '-1 day');
 
-- 30日以内に作成されたレコード
SELECT * FROM posts WHERE created_at >= datetime('now', '-30 days');

SELECT strftime('%Y年%m月%d日', 'now');  -- 2026年08月15日
SELECT strftime('%Y-%m', created_at) AS month, count(*)
FROM posts
GROUP BY month;

CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  payload TEXT NOT NULL CHECK (json_valid(payload))
) STRICT;
 
INSERT INTO events (payload) VALUES
  ('{"type":"click","user":{"id":1,"name":"田中"},"tags":["a","b"]}');

SELECT
  payload -> '$.user.name'  AS as_json,   -- "田中"（引用符付き）
  payload ->> '$.user.name' AS as_text,   -- 田中
  payload ->> '$.user.id'   AS user_id    -- 1（整数）
FROM events;

-- tags配列に 'a' を含むイベントを探す
SELECT e.id
FROM events e, json_each(e.payload, '$.tags') t
WHERE t.value = 'a';

ALTER TABLE events ADD COLUMN
  user_id INTEGER GENERATED ALWAYS AS (payload ->> '$.user.id') VIRTUAL;
 
CREATE INDEX idx_events_user ON events(user_id);

PRAGMA foreign_keys = OFF;
 
BEGIN;
 
CREATE TABLE users_new (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  age INTEGER
) STRICT;
 
INSERT INTO users_new (id, name, age) SELECT id, name, age FROM users;
 
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;
 
-- インデックス・トリガー・ビューを作り直す
 
PRAGMA foreign_key_check;
 
COMMIT;
 
PRAGMA foreign_keys = ON;

-- エラーになる
INSERT INTO users (email, name) VALUES ('a@example.com', '田中')
ON CONFLICT DO UPDATE SET name = excluded.name;
 
-- 対象列を明示する
INSERT INTO users (email, name) VALUES ('a@example.com', '田中')
ON CONFLICT(email) DO UPDATE SET name = excluded.name;

-- エラー
created_at TEXT DEFAULT datetime('now')
 
-- 正しい
created_at TEXT DEFAULT (datetime('now'))