Database と SQL
Database を作り、許可された SQL で読み書きします。
Database を作ると、CLI や Handler から SQL でデータを読み書きできます。 まず小さなメモの表を作って、保存と読み取りを試します。
1. 表を作り、データを保存する
wings db create notes
wings db exec notes "CREATE TABLE notes (id INTEGER PRIMARY KEY, text TEXT)"
wings db exec notes "INSERT INTO notes (text) VALUES (?)" --params '["Hello"]'
wings db query notes "SELECT id, text FROM notes ORDER BY id DESC LIMIT 10"
text が Hello の行が表示されれば、保存できています。
すでに notes という Database がある場合は、別の名前で試してください。
2. Handler のコードを保存する
次を notes-reader.js として保存します。
export default async function (event, env) {
return env.db.query('SELECT id, text FROM notes WHERE id = ?', [event.id])
}
3. Handler を登録して確認する
wings handler deploy --name notes-reader --file notes-reader.js --db notes
wings handler list
一覧の ID を使って実行します。
wings run <handler-id> --event '{"id":1}'
wings handler logs <handler-id>
最初に保存した Hello の行が返り、Run が成功になれば確認完了です。
使える SQL と、使えないときの対処
1回の呼び出しで実行できるのは1文です。
読み取れるテーブルは最大1つです。
自分で作ったテーブルだけを使えます。
値は SQL に直接埋め込まず、上の例のように ? とパラメーターで渡してください。
使えない SQL は実行前に db_sql_forbidden で拒否します。
reason に理由、メッセージに代わりの方法を示します。
- 複数文をまとめるには
env.db.batch()を使います。 - JOIN の代わりに、別々の query の結果を JavaScript で組み合わせます。
- サブクエリの代わりに、先に値を取得して
IN (?, ?)へ渡します。 RETURNINGの行を読むには query を使います。exec と batch は件数だけを返します。
容量と実行時間
容量はアカウント全体の合計で数えます。 上限を超える書き込みは取り消します。 読み取り、容量が増えない書き込み、Database の削除は引き続き使えます。
DELETE で空いた領域は後で再利用されます。 ただし、表示される使用容量が減るとは限りません。 プランを下げた場合は、作成が古い Database から上限数まで書き込めます。 残りは読み取り専用です。
DB だけの1日の時間枠はありません。
CLI や Console からの操作も1日の利用時間に数えます。
操作が待たされる場合や一時停止中は、db_time_quota_exceeded の案内を確認してください。
SQL リファレンス
ここからは、文や関数の対応範囲を調べるための一覧です。 初めて使う場合は、上の3つの手順までで読み書きを試せます。
文と関数
使える文は SELECT、INSERT、REPLACE、UPDATE、DELETE、CREATE TABLE、CREATE INDEX、ALTER TABLE、DROP、EXPLAIN です。
SELECT は1つのテーブル、または json_each() / json_tree() を読み取れます。
集計には DISTINCT、GROUP BY、HAVING を使えます。
対応する Window 関数には OVER (PARTITION BY ... ORDER BY ...) を使えます。
| 種類 | 関数 |
|---|---|
| 一般 | abs, changes, char, coalesce, concat, concat_ws, glob, ifnull, iif, instr, last_insert_rowid, length, like, likelihood, likely, lower, ltrim, nullif, octet_length, random, round, rtrim, sign, substr, total_changes, trim, typeof, unhex, unicode, unlikely, upper |
| 日時 | date, time, datetime, julianday, unixepoch, strftime, timediff |
| 数学 | acos, acosh, asin, asinh, atan, atan2, atanh, ceil, cos, cosh, degrees, exp, floor, ln, log, log10, log2, mod, pi, pow, radians, sin, sinh, sqrt, tan, tanh, trunc |
| JSON | json, json_array, json_array_length, json_error_position, json_extract, json_insert, json_object, json_quote, json_remove, json_replace, json_set, json_type, json_valid, jsonb, jsonb_array, jsonb_extract, jsonb_insert, jsonb_object, jsonb_remove, jsonb_replace, jsonb_set |
| 集計 | count, sum, total, avg, min, max |
| Window | row_number, rank, dense_rank, percent_rank, cume_dist, ntile, lag, lead |
別名は元の関数と同じルールで確認します。
substring は substr、ceiling は ceil、power は pow として扱います。
format は printf、string_agg は group_concat として扱うため使えません。
式と行数の上限
trim、ltrim、rtrim、unhex は1引数だけ使えます。
instr の第2引数は64文字以下の文字列リテラルにします。
LIKE / GLOB のパターンは50バイトまでです。
文字連結には ||、concat、concat_ws を使います。
| 対象 | 上限 |
|---|---|
| 1操作で走査する行 | 10,000行 |
| 1操作で返す行 | 1,000行 |
| 1文の LIKE / GLOB / like() / glob() / instr() の合計 | 2回 |
| 1文の文字連結 | 8回 |
1文の関数呼び出し（-> / ->> を含む） |
16回 |
| 1文で列や式の値同士を比べる回数 | 16回 |
| 1つの IN に並べる値 | 100個 |
| 1つの CASE の WHEN | 32分岐 |
| batch 全体のパターン比較 | 4回 |
| batch 全体の文字連結 | 16回 |
| batch 全体の関数呼び出し | 64回 |
| batch 全体で列や式の値同士を比べる回数 | 64回 |
式の上限を超えた場合の reason は expression_limit です。
複数列を検索する場合は、Handler で検索用の列を作り、その列に LIKE を1回使ってください。
ID は crypto.randomUUID() などで Handler 側で作ります。
テーブル定義と外部キー
自動計算する列（generated column）、式を使う index、条件付きの index は使えません。
CHECK は、列や単純な関数を定数と比べる式などに限ります。
たとえば CHECK (length(name) <= 100) は使えます。
IN、BETWEEN、NULL 判定、NOT / AND / OR も使えます。
CHECK で使える関数は length、octet_length、typeof、abs、lower、upper と、1引数の trim、ltrim、rtrim です。
1文の CHECK は比較32回、関数8回、列や式の値同士の比較4回までです。
古い定義がこの制限に当たる表は読めますが、書き込めません。
問題の index を削除するか、新しい表にデータを移してください。
ALTER TABLE ... ADD COLUMN も、この制限では書き込みとして扱います。
外部キーの子列には、親キーの型・照合順序に合う index が必要です。
たとえば INTEGER の親キーには INTEGER の子列を使います。
foreign_key_index のメッセージに、必要な CREATE INDEX を示します。
遅延外部キーは使えません。
対応していないもの
VIEW への読み書きと、TRIGGER を持つ表への書き込みは拒否します。 VIEW が参照しているだけの通常の表は、書き込み禁止にはなりません。 古い VIEW や TRIGGER は DROP で削除できます。
JOIN、WITH、サブクエリ、複合 SELECT、集計 Window 関数、first_value、last_value、nth_value、名前付き Window、Window frame は使えません。
virtual table、PRAGMA、ATTACH、VACUUM、ANALYZE、REINDEX、トランザクション文にも対応していません。
sqlite_ と _cf_ で始まる名前は予約されています。
組み込みの sqlite_schema、sqlite_master、sqlite_stat1、sqlite_sequence、pragma_* は読めません。
次の関数は使えません。
json_group_array, json_group_object, jsonb_group_array, jsonb_group_object, group_concat, printf, replace, hex, quote, randomblob, zeroblob, json_pretty, json_patch, jsonb_patch