Skip to content

Latest commit

 

History

History
131 lines (98 loc) · 4.46 KB

SQL_DOCUMENT.md

File metadata and controls

131 lines (98 loc) · 4.46 KB

godynamo - Supported statements for document

  • INSERT
  • SELECT
  • UPDATE
  • DELETE

INSERT

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() returns 1, 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.

SELECT

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.

Sample result:

active app user
true "frontend" "user1"

Since v0.3.0, godynamodb supports LIMIT clause for SELECT statement. Example:

  dbrows, err := db.Query(`SELECT * FROM "session" WHERE app='frontend' LIMIT 10`)

Note:

  • The LIMIT clause is extension offered by godynamodb and is not part of PartiQL syntax.
  • The value for LIMIT must be a positive integer.

Since v0.4.0, godynamodb supports ConsistentRead for SELECT statement via clause WITH ConsistentRead=true or WITH 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.

UPDATE

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.

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.

DELETE

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.

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.