simpledbsql

README

AWS SimpleDB driver for Go's database/sql package

This package provides an AWS SimpleDB driver for Go's database/sql package. AWS SimpleDB is a highly available data store that requires no database administration on the part of the user. Although SimpleDB is a NoSQL datastore, it supports an SQL-like syntax for querying data.

This driver can be useful for applications that are using other AWS services, and have a need for a simple database that supports flexible querying. It can be handy when DynamoDB is not flexible enough, but an RDS instance seems like overkill.

Using the database/sql package to access SimpleDB provides an upgrade path to using a more fully-featured SQL database at a future time. If Aurora Serverless is available in your chosen AWS region, it might be a better alternative.

• Install
• Example
• SQL
  • Placeholders
  • id column
  • Select
  • Insert
  • Update
  • Delete
  • Consistent Read
  • Create Table / Drop Table
• Testing
• TODO

Install

go get github.com/jjeffery/simpledbsql

Requires go 1.10 or later.

Example

See also the GoDoc package example.

package main

import (
    "context"
    "database/sql"
    "fmt"
    "log"
    "time"

    _ "github.com/jjeffery/simpledbsql"
)

func main() {
    ctx := context.Background()

    // create DB handle using default AWS credentials
    db, err := sql.Open("simpledb", "")
    exitIfError(err)

    // create a table
    _, err = db.ExecContext(ctx, "create table temp_test_table")
    exitIfError(err)

    // insert some rows
    for i := 0; i < 10; i++ {
        id := fmt.Sprintf("ID%03d", i)
        name := fmt.Sprintf("name-%d", i)
        number := i * i
        _, err = db.ExecContext(ctx,
            "insert into temp_test_table(id, name, number) values(?, ?, ?)",
            id, name, number,
        )
        exitIfError(err)
    }

    // update a row
    _, err = db.ExecContext(ctx,
        "update temp_test_table set number = ? where id = ?",
        100, "ID007",
    )
    exitIfError(err)

    // delete a row
    _, err = db.ExecContext(ctx, "delete from temp_test_table where id = 'ID008'")
    exitIfError(err)

    // select rows
    rows, err := db.QueryContext(ctx,
        "consistent select id, name, number from temp_test_table where name is not null order by name desc",
    )
    exitIfError(err)

    for rows.Next() {
        var (
            id     string
            name   string
            number int
        )

        err = rows.Scan(&id, &name, &number)
        exitIfError(err)
        fmt.Printf("%s,%s,%d\n", id, name, number)
    }

    _, err = db.ExecContext(ctx, "drop table temp_test_table")
    exitIfError(err)
}

func exitIfError(err error) {
    if err != nil {
        log.Fatal(err)
    }
}

SQL

Placeholders

Placeholders can be used to substitute arguments.

select id, a, b, c from my_table where a = ?

id column

The column id is special, and refers to the SimpleDB item name.

Select

All the restrictions of the SimpleDB select statement apply.

select output_list
from domain_name
[where expression]
[sort_instructions]
[limit limit]

See the SimpleDB documentation for more details.

Insert

Insert statements can insert one row at a time. The id column is mandatory.

insert into my_table(id, a, b, c)
values (?, ?, ?, ?)

Update

Update statements can update one row at a time. The id column is the only column allowed in the where clause.

update my_table
set a = ?, b = ?, c = ?
where id = ?

Delete

Delete statements can delete one row at a time. The id column is the only column allowed in the where clause.

delete from my_table
where id = ?

Consistent Read

If the select statement starts with the word "consistent", then a consistent read will be performed.

consistent select id, a, b, c from my_table where a = ?

Create Table / Drop Table

Create and delete SimpleDB domains using the create table and drop table commands.

create table my_table

drop table my_table

Testing

There is no option for running SimpleDB locally, so all tests require a valid AWS account. The account credentials are detected using the default mechanism, using:

• Environment variables AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY
• Environment variable AWS_PROFILE and file ~/.aws/credentials
• Environment variable AWS_REGION, or if not set file ~/.aws/config

The tests require the following permissions:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "simpledb_driver_test",
      "Effect": "Allow",
      "Action": [
        "sdb:GetAttributes",
        "sdb:DeleteDomain",
        "sdb:PutAttributes",
        "sdb:DeleteAttributes",
        "sdb:Select",
        "sdb:CreateDomain"
      ],
      "Resource": "arn:aws:sdb:*:*:domain/temp_test_*"
    }
  ]
}

TODO

• Detect queries with a where clause matching where id = ?. Implement using the much faster SimpleDB GetAttributes method instead of the Select method.
• Support mapping table names to domain names. Prefix all table names with a schema name, and map individual table names to a different domain name.
• Support PostgreSQL-style placeholders ($1, $2, ...). This will help with migrating from SimpleDB to PostgreSQL should the queries get to a point where a real DB is requried.
• Support update statements with an extra column equality test using a SimpleDB UpdateCondition in the PutAttributes request.

      update tbl set a = ? where id = ? and b = ?

• Support wildcard query select * from table_name
• Support integer primary keys. Currently only strings supported.
• Provide options in the connection string, region, consistent read, etc

Documentation

Overview

Package simpledbsql provides an AWS SimpleDB driver for the database/sql package.

See the package example for an overview of how to use the driver.

SQL

Refer to https://github.com/jjeffery/simpledbsql for a description of the SQL dialect supported by this driver. The following examples can be used as a guide.

select id, a, b, c from my_table where a > ? and b = ? order by a

insert into my_table(id, a, b, c) values(?, ?, ?, 'c value')

update my_table set a = ?, b = ?, c = 'processed' where id = ?

delete from my_table where id = ?

create table my_table

drop table my_table

For consistent-read select statements, prefix the `select` with the word `consistent`

consistent select id, a, b, c from my_table where a = ?

Example

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"time"

	_ "github.com/jjeffery/simpledbsql"
)

func main() {
	db, err := sql.Open("simpledb", "")
	exitIfError(err)
	ctx := context.Background()

	// create a table
	_, err = db.ExecContext(ctx, "create table temp_test_table")
	exitIfError(err)
	waitForConsistency()

	// insert some rows
	for i := 0; i < 10; i++ {
		id := fmt.Sprintf("ID%03d", i)
		name := fmt.Sprintf("name-%d", i)
		number := i * i
		_, err = db.ExecContext(ctx,
			"insert into temp_test_table(id, name, number) values(?, ?, ?)",
			id, name, number,
		)
		exitIfError(err)
	}
	waitForConsistency()

	// update a row
	_, err = db.ExecContext(ctx,
		"update temp_test_table set number = ? where id = ?",
		100, "ID007",
	)
	exitIfError(err)

	// delete a row
	_, err = db.ExecContext(ctx, "delete from temp_test_table where id = 'ID008'")
	exitIfError(err)

	// select rows
	rows, err := db.QueryContext(ctx,
		"consistent select id, name, number from temp_test_table where name is not null order by name desc",
	)
	exitIfError(err)

	for rows.Next() {
		var (
			id     string
			name   string
			number int
		)

		err = rows.Scan(&id, &name, &number)
		exitIfError(err)
		fmt.Printf("%s,%s,%d\n", id, name, number)
	}

	_, err = db.ExecContext(ctx, "drop table temp_test_table")
	exitIfError(err)

}

func exitIfError(err error) {
	if err != nil {
		log.Fatal(err)
	}
}

// waitForConsistency waits a short time for the SimpleDB domain
// to be consistent across all copies.
func waitForConsistency() {
	time.Sleep(500 * time.Millisecond)
}

Output:

ID009,name-9,81
ID007,name-7,100
ID006,name-6,36
ID005,name-5,25
ID004,name-4,16
ID003,name-3,9
ID002,name-2,4
ID001,name-1,1
ID000,name-0,0

Index

• type Connector
  • func (c *Connector) Connect(ctx context.Context) (driver.Conn, error)
  • func (c *Connector) Driver() driver.Driver
• type Driver
  • func (d *Driver) Open(name string) (driver.Conn, error)

Examples

• Package

Types

type Connector

type Connector struct {
	// SimpleDB is the AWS SDK handle used for all SimpleDB operations.
	SimpleDB simpledbiface.SimpleDBAPI

	// Schema is used to derive the SimpleDB domain name from the
	// table name in the SQL. If Schema is not blank, then it is
	// prefixed in front of any table name with a period. So if
	// Schema is "dev" and table name is "tbl" then the corresponding
	// SimpleDB domain would be "dev.tbl".
	Schema string

	// Synonyms is a map of table names to their corresponding SimpleDB
	// domain names. Useful in an environment where the SimpleDB domains
	// are created by CloudFormation and have randomly generated names.
	// Create an entry in Synonym and use a constant table name in the SQL.
	//
	// If a table name has an entry in Synonyms, Schema is ignored.
	Synonyms map[string]string
}

Connector implements the driver.Connector interface, and is useful for passing to the sql.OpenDB function.

func (*Connector) Connect

func (c *Connector) Connect(ctx context.Context) (driver.Conn, error)

Connect returns a connection to the database.

func (*Connector) Driver

func (c *Connector) Driver() driver.Driver

Driver returns the underlying Driver of the Connector.

type Driver

type Driver struct {
	// contains filtered or unexported fields
}

Driver implements the driver.Driver interface.

func (*Driver) Open

func (d *Driver) Open(name string) (driver.Conn, error)

Open returns a new connection to the database. The name is currently ignored and should be a blank string, but in future may include parameters like region, profile, consistent-read, schema, etc.