dynamosql - DynamoDB SQL Driver

dynamosql is a complete Go SQL driver for DynamoDB. It transforms SQL into DynamoDB requests like Query, Scan, PutItem, UpdateItem, DeleteItem, and maps the results back to SQL. It

• Makes working with DynamoDB much more pleasant.
• Transparently generate KeyConditionExpression, ConditionExpression, etc
• Supports advanced SQL driver features like named parameters, and slice/map parameters.
• Supports prepared statements
• Supports marshaling and unmarshaling using dynamodbattribute.
• Supports all DynamoDB functions
• Works with any library that supports database/sql.

NOTE: The SQL syntax for dynamosql is still in flux and is subject to change. It mostly follows ANSI SQL, but there are a few small extensions to make it match better to DynamoDB.

There is also a CLI in cmd/dynamosql (powered by usql) that can be used as a commandline interface to DynamoDB. For authentication, it accepts the same environment variables, config, and flags as the AWS CLI.

Inspiration

SQL is a great query language, but traditional SQL databases tend not to scale so well. DynamoDB is a great scalable database, but it's query language is lacking. So why not combine them? Hence the inspiration for dynamosql.

Limitations

dynamosql does not attempt to add additional features to DynamoDB. It's a convenience layer for interacting with DynamoDB. Thus, it suffers all the limitations imposed by DynamoDB. Known limitations are:

• Does not support JOIN and cross-partition queries.
• Max 25 items can be inserted in a batch, since that's the limit for TransactWriteItems.

Difference from PartiQL

AWS released PartiQL which also provides a SQL layer for DynamoDB. It appears to be aimed more at analytics and interactive use. It

• Is not a database/sql driver
• Does not support prepared statements
• Does not support DDL
• Does not support bulk inserts
• Drifts a bit further from SQL than dynamosql

Usage

CLI

go get github.com/mightyguava/dynamosql/cmd/dynamosql
aws configure # if you need to
dynamosql
> SELECT * FROM my_table WHERE hash_key = 123

Driver

There are 3 ways to open a connection.

via database/sql:

db, err := sql.Open("dynamodb", "?region=us-west-2")

passing a Session into the driver

sess, err := session.New(&aws.Config{
	Region: aws.String("us-west-2"),
})
db := dynamosql.NewWithSession(sess)

passing a DynamoDB client into the driver

ddb := dynamodb.New(sess)
db := dynamosql.NewWithClient(ddb)

Permissions

dynamosql requires the following permissions to be granted.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "DynamoDBTableAccess",
            "Effect": "Allow",
            "Action": [
                "dynamodb:BatchGetItem",
                "dynamodb:BatchWriteItem",
                "dynamodb:ConditionCheckItem",
                "dynamodb:PutItem",
                "dynamodb:DescribeTable",
                "dynamodb:DeleteItem",
                "dynamodb:GetItem",
                "dynamodb:Scan",
                "dynamodb:Query",
                "dynamodb:UpdateItem"
            ],
            "Resource": "arn:aws:dynamodb:us-west-2:123456789012:table/TableName"
        }
    ]
}

SQL Mappings

SQL	DynamoDB	Notes
SELECT	Query
(TODO) SCAN	Scan	Optionally turn SELECT into SCAN when no keys are present in WHERE
INSERT	PutItem/TransactWriteItem	Errors if key exists. Uses TransactWriteItem to insert up to 25 items
REPLACE ... RETURNING	PutItem/TransactWriteItem	Overwrites existing document. Uses TransactWriteItem to insert up to 25 items
(TODO) UPDATE	UpdateItem
(TODO) DELETE	DeleteItem
CREATE TABLE	CreateTable	supports global and local secondary indexes
(TODO) ALTER TABLE

Example

A fairly complete example of driver usage. Error checking omitted for brevity.

sess := session.Must(session.New(aws.Config{Region: aws.String()"us-west-2")}))
db := dynamosql.NewWithSession(sess)
_, err := db.Exec(`
CREATE TABLE movies (
  title string HASH KEY,
  year number RANGE KEY,
  director string,
  actors []string,

  LOCAL INDEX title_director RANGE(director) PROJECTION ALL,
  GLOBAL INDEX director_year HASH(director) RANGE(year)
      PROJECTION INCLUDE (title, actors)
      PROVISIONED THROUGHPUT READ 1 WRITE 1
)
PROVISIONED THROUGHPUT READ 1 WRITE 1;
`)
type Movie struct {
    title string
    year int
}
movies := []movie{
    {"Rush Hour", 1994},
    {"Die Hard", 1988},
}
_, err := db.Exec(`INSERT INTO movies VALUES (?)`, movies)
var rushHour Movie
row := db.QueryRow(`SELECT * FROM movies WHERE title = :name`, sql.Named("name", "Rush Hour"))
err := row.Scan(dynamosql.Document(&rushHour))
fmt.Println(rushHour)

Grammar

AST = (Select | InsertOrReplace | CreateTable | DropTable) ";"? .

Select = "SELECT" ProjectionExpression "FROM" <field> ("USE" "INDEX" "(" <ident> ")")? ("WHERE" AndExpression)? ("ASC" | "DESC")? ("LIMIT" <number>)? .
ProjectionExpression = ("*" | ("document" "(" "*" ")")) | (ProjectionColumn ("," ProjectionColumn)*) .
ProjectionColumn = FunctionExpression | DocumentPath .
FunctionExpression = <ident> "(" FunctionArgument ("," FunctionArgument)* ")" .
FunctionArgument = DocumentPath | Value .
DocumentPath = PathFragment ("." PathFragment)* .
PathFragment = <field> ("[" <number> "]")* .
Value = <number> | <string> | <bool> | <null> | (":" <ident>) | "?" .
AndExpression = Condition ("AND" Condition)* .
Condition = ("(" ConditionExpression ")") | ("NOT" NotCondition) | ConditionOperand | FunctionExpression .
ConditionExpression = AndExpression ("OR" AndExpression)* .
NotCondition = Condition .
ConditionOperand = DocumentPath ConditionRHS .
ConditionRHS = Compare | ("BETWEEN" Between) | ("IN" "(" In ")") .
Compare = ("<>" | "<=" | ">=" | "=" | "<" | ">" | "!=") Operand .
Operand = Value | DocumentPath .
Between = Operand "AND" Operand .
In = Value ("," Value)* .

InsertOrReplace = ("INSERT" | "REPLACE") "INTO" <field> "VALUES" "(" InsertTerminal ")" ("," "(" InsertTerminal ")")* ("RETURNING" ("NONE" | "ALL_OLD"))? .
InsertTerminal = <number> | <string> | <bool> | <null> | (":" <ident>) | "?" | JSONObject .
JSONObject = "{" (JSONObjectEntry ("," JSONObjectEntry)* ","?)? "}" .
JSONObjectEntry = (<ident> | <string>) ":" JSONValue .
JSONValue = <number> | <string> | <bool> | <null> | JSONObject | JSONArray .
JSONArray = "[" (JSONValue ("," JSONValue)* ","?)? "]" .

CreateTable = "CREATE" "TABLE" <field> "(" CreateTableEntry ("," CreateTableEntry)* ")" ProvisionedThroughput .
CreateTableEntry = GlobalSecondaryIndex | LocalSecondaryIndex | TableAttr .
GlobalSecondaryIndex = "GLOBAL" "SECONDARY"? "INDEX" <field> "HASH" "(" <field> ")" ("RANGE" "(" <field> ")")? "PROJECTION" Projection ProvisionedThroughput .
Projection = "KEYS_ONLY" | "ALL" | ("INCLUDE" (<field> ("," <field>)*)) .
ProvisionedThroughput = "PROVISIONED" "THROUGHPUT" "READ" <number> "WRITE" <number> .
LocalSecondaryIndex = "LOCAL" "SECONDARY"? "INDEX" <field> "RANGE" "(" <field> ")" "PROJECTION" Projection .
TableAttr = <field> <type> (("HASH" | "RANGE") "KEY")? .

DropTable = "DROP" "TABLE" <field> .