Go database/sql driver for AWS DynamoDB
Tags: go, dynamodb, driver
Published: 2023-05-27 16:18:02

_This article was originally posted on [my personal notes](https://mpn.btnguyen2k.me/cms/programming/go-golang-dynamodb-sql/?l=en)._

[Amazon DynamoDB](https://aws.amazon.com/dynamodb/) is a fully managed NoSQL database service provided by Amazon as part of the Amazon Web Services (AWS) portfolio.

- AWS offers the [SDK for Go](https://github.com/aws/aws-sdk-go-v2), which facilitates seamless integration between Go applications and various AWS services, including DynamoDB.
- AWS provides users with the flexibility to utilize [PartiQL](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html#ql-reference.what-is), a SQL-like query language, for efficient manipulation of DynamoDB items via operations such as `INSERT`, `SELECT`, `UPDATE`, and `DELETE`.
- The Go programming language includes the [`database/sql` package](https://pkg.go.dev/database/sql), which offers a versatile and standardized interface for interacting with SQL databases or databases that support SQL-like functionality. 

To establish a seamless integration between the three components mentioned earlier, an essential component is required: a database driver that enables Go applications to interface with DynamoDB using the standard `database/sql` package. This article introduces [godynamo](https://github.com/btnguyen2k/godynamo) - a `database/sql` driver for AWS DynamoDB.

_Disclaimer: I am the author of [godynamo](https://github.com/btnguyen2k/godynamo)._

## Usage

Utilizing [godynamo](https://github.com/btnguyen2k/godynamo) to interact with AWS DynamoDB follows a familiar pattern akin to employing any `database/sql` driver for a database system, for example MySQL. The process involves a sequence of steps: first, importing the [godynamo](https://github.com/btnguyen2k/godynamo) driver into the project; next, initializing a `sql.DB` instance through using the `sql.Open(...)` function; and lastly, leveraging SQL statements to operate on the items within the DynamoDB table via the `sql.DB` instance.

Example:

```go
package main

import (
	"database/sql"
	"fmt"

	_ "github.com/btnguyen2k/gocosmos" // import driver
)

func main() {
    // build connection string
	driver := "godynamo"
	dsn := "Region=us-east-1;AkId=aws-access-key-id;SecretKey=aws-secret-key"
    // create the sql.DB instance
	db, err := sql.Open(driver, dsn)
	if err != nil {
		panic(err)
	}
	defer db.Close()

    // use SQL statement via the sql.DB instance
	dbrows, err := db.Query(`LIST TABLES`)
	if err != nil {
		panic(err)
	}
	for dbRows.Next() {
		var val interface{}
		err := dbRows.Scan(&val)
		if err != nil {
			panic(err)
		}
		fmt.Println(val)
	}
}
```

## Supported SQL statements

[godynamo](https://github.com/btnguyen2k/godynamo) supports 3 groups of SQL statements: _table_, _index_ and _document_.

**Table-related statement:**

👉 `CREATE TABLE`: create a new DynamoDB table.

👉 `LIST TABLES`: list all available tables.

👉 `DESCRIBE TABLE`: return info of a table.

👉 `ALTER TABLE`: change a table's RCU/WCU or table-class.

👉 `DROP TABLE`: remove an existing table.

**Index-related statements:**

👉 `DESCRIBE LSI`: return info of a `Local Secondary Index`.

👉 `CREATE GSI`: create a new `Global Secondary Index` on a table.

👉 `DESCRIBE GSI`: return info of a `Global Secondary Index`.

👉 `ALTER GSI`: change RCU/WCU settings of a `Global Secondary Index`.

👉 `DROP GSI`: remove an existing `Global Secondary Index`.

**Document-related statements:**

👉 `INSERT`: add an item to a table.

👉 `SELECT`: retrieve data from a table.

👉 `UPDATE`: modify the value of one or more attributes within an item in a table.

👉 `DELETE`: delete an existing item from a table.

Find more details on godynamo repository: https://github.com/btnguyen2k/godynamo.