Insert

Bob を使用した SQLite の INSERT クエリの例を示します。

基本的な INSERT

INSERT INTO users (name, email) VALUES (?, ?)

sqlite.Insert("users").
    Columns("name", "email").
    Values(sqlite.Arg("John Doe"), sqlite.Arg("john@example.com"))

複数行の INSERT

INSERT INTO users (name, email) VALUES 
    (?, ?),
    (?, ?)

sqlite.Insert("users").
    Columns("name", "email").
    Values(sqlite.Arg("John Doe"), sqlite.Arg("john@example.com")).
    Values(sqlite.Arg("Jane Smith"), sqlite.Arg("jane@example.com"))

INSERT OR IGNORE

INSERT OR IGNORE INTO users (name, email) VALUES (?, ?)

sqlite.Insert("users").
    OrIgnore().
    Columns("name", "email").
    Values(sqlite.Arg("John Doe"), sqlite.Arg("john@example.com"))

INSERT OR REPLACE

INSERT OR REPLACE INTO users (id, name, email) VALUES (?, ?, ?)

sqlite.Insert("users").
    OrReplace().
    Columns("id", "name", "email").
    Values(sqlite.Arg(1), sqlite.Arg("John Doe"), sqlite.Arg("john@example.com"))

INSERT SELECT

INSERT INTO backup_users (name, email)
SELECT name, email FROM users WHERE active = ?

selectQuery := sqlite.Select("name", "email").
    From("users").
    Where(sqlite.Quote("active").EQ(sqlite.Arg(true)))

sqlite.Insert("backup_users").
    Columns("name", "email").
    Query(selectQuery)

DEFAULT VALUES

INSERT INTO users DEFAULT VALUES

sqlite.Insert("users").
    DefaultValues()

JSON データの INSERT

INSERT INTO users (name, metadata) VALUES (?, ?)

metadata := map[string]interface{}{
    "age": 30,
    "city": "Tokyo",
}

metadataJSON, _ := json.Marshal(metadata)

sqlite.Insert("users").
    Columns("name", "metadata").
    Values(sqlite.Arg("John Doe"), sqlite.Arg(string(metadataJSON)))

RETURNING 句（SQLite 3.35+）

INSERT INTO users (name, email) VALUES (?, ?) 
RETURNING id, created_at

sqlite.Insert("users").
    Columns("name", "email").
    Values(sqlite.Arg("John Doe"), sqlite.Arg("john@example.com")).
    Returning("id", "created_at")

ON CONFLICT 句

INSERT INTO users (email, name) VALUES (?, ?)
ON CONFLICT(email) DO UPDATE SET name = excluded.name

sqlite.Insert("users").
    Columns("email", "name").
    Values(sqlite.Arg("john@example.com"), sqlite.Arg("John Doe")).
    OnConflict("email").
    DoUpdate().
    Set("name", sqlite.Raw("excluded.name"))

UPSERT パターン

INSERT INTO counters (name, count) VALUES (?, ?)
ON CONFLICT(name) DO UPDATE SET count = count + 1

sqlite.Insert("counters").
    Columns("name", "count").
    Values(sqlite.Arg("page_views"), sqlite.Arg(1)).
    OnConflict("name").
    DoUpdate().
    Set("count", sqlite.Quote("count").Plus(sqlite.Arg(1)))

これらの例は、Bob を使用して SQLite で実行可能な様々な INSERT クエリパターンを示しています。

Delete Select