[20 Days of DynamoDB] Day 18 - Using a SQL-compatible query language

Posted: 6/Feb/2024

DynamoDB supports PartiQL to execute SQL-like select, insert, update, and delete operations.

Here is an example of how you would use PartiQL based queries for a simple URL shortener application. Notice how it uses a (generic) ExecuteStatement API to execute INSERT, SELECT, UPDATE and DELETE:

_, err := client.ExecuteStatement(context.Background(), &dynamodb.ExecuteStatementInput{
        Statement: aws.String("INSERT INTO url_metadata value {'longurl':?,'shortcode':?, 'active': true}"),
        Parameters: []types.AttributeValue{
            &types.AttributeValueMemberS{Value: "https://github.com/abhirockzz"},
            &types.AttributeValueMemberS{Value: uuid.New().String()[:8]},
        },
    })

    _, err := client.ExecuteStatement(context.Background(), &dynamodb.ExecuteStatementInput{
        Statement: aws.String("SELECT * FROM url_metadata where shortcode=? AND active=true"),
        Parameters: []types.AttributeValue{
            &types.AttributeValueMemberS{Value: "abcd1234"},
        },
    })

    _, err := client.ExecuteStatement(context.Background(), &dynamodb.ExecuteStatementInput{
        Statement: aws.String("UPDATE url_metadata SET active=? where shortcode=?"),
        Parameters: []types.AttributeValue{
            &types.AttributeValueMemberBOOL{Value: false},
            &types.AttributeValueMemberS{Value: "abcd1234"},
        },
    })

    _, err := client.ExecuteStatement(context.Background(), &dynamodb.ExecuteStatementInput{
        Statement: aws.String("DELETE FROM url_metadata where shortcode=?"),
        Parameters: []types.AttributeValue{
            &types.AttributeValueMemberS{Value: "abcd1234"},
        },
    })

Recommended reading:

• Amazon DynamoDB documentation on PartiQL support
• ExecuteStatement API docs