INSERT
SELECT
UPDATE
DELETE
Syntax: PartiQL insert statements for DynamoDB
Example:
result, err := db.Exec(`INSERT INTO "session" VALUE {'app': ?, 'user': ?, 'active': ?}`, "frontend", "user1", true)
if err == nil {
numAffectedRow, err := result.RowsAffected()
...
}
Description: use the INSERT
statement to add an item to a table.
- If the statement is executed successfully,
RowsAffected()
returns1, nil
. - Note: the
INSERT
must follow PartiQL syntax, e.g. attribute names are enclosed by single quotation marks ('attr-name'), table name is enclosed by double quotation marks ("table-name"), etc.
Syntax: PartiQL select statements for DynamoDB
Example:
dbrows, err := db.Query(`SELECT * FROM "session" WHERE app='frontend'`)
if err == nil {
fetchAndPrintAllRows(dbrows)
}
Description: use the SELECT
statement to retrieve data from a table.
- Note: the
SELECT
must follow PartiQL syntax.
Sample result:
active | app | user |
---|---|---|
true | "frontend" | "user1" |
Since v0.3.0,
godynamodb
supportsLIMIT
clause forSELECT
statement. Example:dbrows, err := db.Query(`SELECT * FROM "session" WHERE app='frontend' LIMIT 10`)
Note:
- The
LIMIT
clause is extension offered bygodynamodb
and is not part of PartiQL syntax.- The value for
LIMIT
must be a positive integer.
Since v0.4.0,
godynamodb
supports ConsistentRead forSELECT
statement via clauseWITH ConsistentRead=true
orWITH Consistent_Read=true
. Example:dbrows, err := db.Query(`SELECT * FROM "session" WHERE app='frontend' WITH ConsistentRead=true`)
Note: the WITH clause must be placed at the end of the SELECT statement.
Syntax: PartiQL update statements for DynamoDB
Example:
result, err := db.Exec(`UPDATE "tbltest" SET location=? SET os=? WHERE "app"=? AND "user"=?`, "VN", "Ubuntu", "app0", "user1")
if err == nil {
numAffectedRow, err := result.RowsAffected()
...
}
Description: use the UPDATE
statement to modify the value of one or more attributes within an item in a table.
- Note: the
UPDATE
must follow PartiQL syntax.
Query
can also be used to fetch returned values.
dbrows, err := db.Query(`UPDATE "tbltest" SET location=? SET os=? WHERE "app"=? AND "user"=? RETURNING MODIFIED OLD *`, "VN", "Ubuntu", "app0", "user0")
if err == nil {
fetchAndPrintAllRows(dbrows)
}
Sample result:
location |
---|
"AU" |
If there is no matched item, the error
ConditionalCheckFailedException
is suspended. That means:
RowsAffected()
returns(0, nil)
Query
returns empty result set.
Syntax: PartiQL delete statements for DynamoDB
Example:
result, err := db.Exec(`DELETE FROM "tbltest" WHERE "app"=? AND "user"=?`, "app0", "user1")
if err == nil {
numAffectedRow, err := result.RowsAffected()
...
}
Description: use the DELETE
statement to delete an existing item from a table.
- Note: the
DELETE
must follow PartiQL syntax.
Query
can also be used to have the content of the old item returned.
dbrows, err := db.Query(`DELETE FROM "tbltest" WHERE "app"=? AND "user"=?`, "app0", "user1")
if err == nil {
fetchAndPrintAllRows(dbrows)
}
Sample result:
app | location | platform | user |
---|---|---|---|
"app0" | "AU" | "Windows" | "user1" |
If there is no matched item, the error
ConditionalCheckFailedException
is suspended. That means:
RowsAffected()
returns(0, nil)
Query
returns empty result set.