Run a PartiQL statement on a DynamoDB table using an Amazon SDK - Amazon DynamoDB
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China.

Run a PartiQL statement on a DynamoDB table using an Amazon SDK

The following code examples show how to run a PartiQL statement on a DynamoDB table.

.NET
Amazon SDK for .NET
Tip

To learn how to set up and run this example, see GitHub.

Use an INSERT statement to add an item.

/// <summary> /// Inserts a single movie into the movies table. /// </summary> /// <param name="tableName">The name of the table.</param> /// <param name="movieTitle">The title of the movie to insert.</param> /// <param name="year">The year that the movie was released.</param> /// <returns>A Boolean value that indicates the success or failure of /// the INSERT operation.</returns> public static async Task<bool> InsertSingleMovie(string tableName, string movieTitle, int year) { string insertBatch = $"INSERT INTO {tableName} VALUE {{'title': ?, 'year': ?}}"; var response = await Client.ExecuteStatementAsync(new ExecuteStatementRequest { Statement = insertBatch, Parameters = new List<AttributeValue> { new AttributeValue { S = movieTitle }, new AttributeValue { N = year.ToString() }, }, }); return response.HttpStatusCode == System.Net.HttpStatusCode.OK; }

Use a SELECT statement to get an item.

/// <summary> /// Uses a PartiQL SELECT statement to retrieve a single movie from the /// movie database. /// </summary> /// <param name="tableName">The name of the movie table.</param> /// <param name="movieTitle">The title of the movie to retrieve.</param> /// <returns>A list of movie data. If no movie matches the supplied /// title, the list is empty.</returns> public static async Task<List<Dictionary<string, AttributeValue>>> GetSingleMovie(string tableName, string movieTitle) { string selectSingle = $"SELECT * FROM {tableName} WHERE title = ?"; var parameters = new List<AttributeValue> { new AttributeValue { S = movieTitle }, }; var response = await Client.ExecuteStatementAsync(new ExecuteStatementRequest { Statement = selectSingle, Parameters = parameters, }); return response.Items; }

Use a SELECT statement to get a list of items.

/// <summary> /// Retrieve multiple movies by year using a SELECT statement. /// </summary> /// <param name="tableName">The name of the movie table.</param> /// <param name="year">The year the movies were released.</param> /// <returns></returns> public static async Task<List<Dictionary<string, AttributeValue>>> GetMovies(string tableName, int year) { string selectSingle = $"SELECT * FROM {tableName} WHERE year = ?"; var parameters = new List<AttributeValue> { new AttributeValue { N = year.ToString() }, }; var response = await Client.ExecuteStatementAsync(new ExecuteStatementRequest { Statement = selectSingle, Parameters = parameters, }); return response.Items; }

Use an UPDATE statement to update an item.

/// <summary> /// Updates a single movie in the table, adding information for the /// producer. /// </summary> /// <param name="tableName">the name of the table.</param> /// <param name="producer">The name of the producer.</param> /// <param name="movieTitle">The movie title.</param> /// <param name="year">The year the movie was released.</param> /// <returns>A Boolean value that indicates the success of the /// UPDATE operation.</returns> public static async Task<bool> UpdateSingleMovie(string tableName, string producer, string movieTitle, int year) { string insertSingle = $"UPDATE {tableName} SET Producer=? WHERE title = ? AND year = ?"; var response = await Client.ExecuteStatementAsync(new ExecuteStatementRequest { Statement = insertSingle, Parameters = new List<AttributeValue> { new AttributeValue { S = producer }, new AttributeValue { S = movieTitle }, new AttributeValue { N = year.ToString() }, }, }); return response.HttpStatusCode == System.Net.HttpStatusCode.OK; }

Use a DELETE statement to delete a single movie.

/// <summary> /// Deletes a single movie from the table. /// </summary> /// <param name="tableName">The name of the table.</param> /// <param name="movieTitle">The title of the movie to delete.</param> /// <param name="year">The year that the movie was released.</param> /// <returns>A Boolean value that indicates the success of the /// DELETE operation.</returns> public static async Task<bool> DeleteSingleMovie(string tableName, string movieTitle, int year) { var deleteSingle = $"DELETE FROM {tableName} WHERE title = ? AND year = ?"; var response = await Client.ExecuteStatementAsync(new ExecuteStatementRequest { Statement = deleteSingle, Parameters = new List<AttributeValue> { new AttributeValue { S = movieTitle }, new AttributeValue { N = year.ToString() }, }, }); return response.HttpStatusCode == System.Net.HttpStatusCode.OK; }
Go
SDK for Go V2
Tip

To learn how to set up and run this example, see GitHub.

Use an INSERT statement to add an item.

// AddMovie runs a PartiQL INSERT statement to add a movie to the DynamoDB table. func (runner PartiQLRunner) AddMovie(movie Movie) error { params, err := attributevalue.MarshalList([]interface{}{movie.Title, movie.Year, movie.Info}) if err != nil { panic(err) } _, err = runner.DynamoDbClient.ExecuteStatement(context.TODO(), &dynamodb.ExecuteStatementInput{ Statement: aws.String( fmt.Sprintf("INSERT INTO \"%v\" VALUE {'title': ?, 'year': ?, 'info': ?}", runner.TableName)), Parameters: params, }) if err != nil { log.Printf("Couldn't insert an item with PartiQL. Here's why: %v\n", err) } return err }

Use a SELECT statement to get an item.

// GetMovie runs a PartiQL SELECT statement to get a movie from the DynamoDB table by // title and year. func (runner PartiQLRunner) GetMovie(title string, year int) (Movie, error) { var movie Movie params, err := attributevalue.MarshalList([]interface{}{title, year}) if err != nil { panic(err) } response, err := runner.DynamoDbClient.ExecuteStatement(context.TODO(), &dynamodb.ExecuteStatementInput{ Statement: aws.String( fmt.Sprintf("SELECT * FROM \"%v\" WHERE title=? AND year=?", runner.TableName)), Parameters: params, }) if err != nil { log.Printf("Couldn't get info about %v. Here's why: %v\n", title, err) } else { err = attributevalue.UnmarshalMap(response.Items[0], &movie) if err != nil { log.Printf("Couldn't unmarshal response. Here's why: %v\n", err) } } return movie, err }

Use a SELECT statement to get a list of items and project the results.

// GetAllMovies runs a PartiQL SELECT statement to get all movies from the DynamoDB table. // The results are projected to return only the title and rating of each movie. func (runner PartiQLRunner) GetAllMovies() ([]map[string]interface{}, error) { var output []map[string]interface{} response, err := runner.DynamoDbClient.ExecuteStatement(context.TODO(), &dynamodb.ExecuteStatementInput{ Statement: aws.String( fmt.Sprintf("SELECT title, info.rating FROM \"%v\"", runner.TableName)), }) if err != nil { log.Printf("Couldn't get movies. Here's why: %v\n", err) } else { err = attributevalue.UnmarshalListOfMaps(response.Items, &output) if err != nil { log.Printf("Couldn't unmarshal response. Here's why: %v\n", err) } } return output, err }

Use an UPDATE statement to update an item.

// UpdateMovie runs a PartiQL UPDATE statement to update the rating of a movie that // already exists in the DynamoDB table. func (runner PartiQLRunner) UpdateMovie(movie Movie, rating float64) error { params, err := attributevalue.MarshalList([]interface{}{rating, movie.Title, movie.Year}) if err != nil { panic(err) } _, err = runner.DynamoDbClient.ExecuteStatement(context.TODO(), &dynamodb.ExecuteStatementInput{ Statement: aws.String( fmt.Sprintf("UPDATE \"%v\" SET info.rating=? WHERE title=? AND year=?", runner.TableName)), Parameters: params, }) if err != nil { log.Printf("Couldn't update movie %v. Here's why: %v\n", movie.Title, err) } return err }

Use a DELETE statement to delete an item.

// DeleteMovie runs a PartiQL DELETE statement to remove a movie from the DynamoDB table. func (runner PartiQLRunner) DeleteMovie(movie Movie) error { params, err := attributevalue.MarshalList([]interface{}{movie.Title, movie.Year}) if err != nil { panic(err) } _, err = runner.DynamoDbClient.ExecuteStatement(context.TODO(), &dynamodb.ExecuteStatementInput{ Statement: aws.String( fmt.Sprintf("DELETE FROM \"%v\" WHERE title=? AND year=?", runner.TableName)), Parameters: params, }) if err != nil { log.Printf("Couldn't delete %v from the table. Here's why: %v\n", movie.Title, err) } return err }
JavaScript
SDK for JavaScript V3
Tip

To learn how to set up and run this example, see GitHub.

Create the client.

// Create the DynamoDB service client module using ES6 syntax. import { DynamoDBClient } from "@aws-sdk/client-dynamodb"; // Set the AWS Region. export const REGION = "eu-west-1"; // For example, "us-east-1". // Create an Amazon DynamoDB service client object. export const ddbClient = new DynamoDBClient({ region: REGION });

Create the DynamoDB document client.

// Create a service client module using ES6 syntax. import { DynamoDBDocumentClient } from "@aws-sdk/lib-dynamodb"; import { ddbClient } from "./ddbClient.js"; // Set the AWS Region. const REGION = "eu-west-1"; // For example, "us-east-1". const marshallOptions = { // Whether to automatically convert empty strings, blobs, and sets to `null`. convertEmptyValues: false, // false, by default. // Whether to remove undefined values while marshalling. removeUndefinedValues: false, // false, by default. // Whether to convert typeof object to map attribute. convertClassInstanceToMap: false, // false, by default. }; const unmarshallOptions = { // Whether to return numbers as a string instead of converting them to native JavaScript numbers. wrapNumbers: false, // false, by default. }; const translateConfig = { marshallOptions, unmarshallOptions }; // Create the DynamoDB document client. const ddbDocClient = DynamoDBDocumentClient.from(ddbClient, translateConfig); export { ddbDocClient };

Create an item using PartiQL.

// Import required AWS SDK clients and commands for Node.js. import { ExecuteStatementCommand } from "@aws-sdk/client-dynamodb"; import { ddbDocClient } from "../libs/ddbDocClient.js"; const tableName = process.argv[2]; const movieYear1 = process.argv[3]; const movieTitle1 = process.argv[4]; export const run = async (tableName, movieTitle1, movieYear1) => { const params = { Statement: "INSERT INTO " + tableName + " value {'title':?, 'year':?}", Parameters: [{ S: movieTitle1 }, { N: movieYear1 }], }; try { const data = await ddbDocClient.send(new ExecuteStatementCommand(params)); console.log("Success. Item added."); return "Run successfully"; // For unit tests. } catch (err) { console.error(err); } }; run(tableName, movieTitle1, movieYear1);

Get an item using PartiQL.

// Import required AWS SDK clients and commands for Node.js. import { ExecuteStatementCommand } from "@aws-sdk/client-dynamodb"; import { ddbDocClient } from "../libs/ddbDocClient"; const tableName = process.argv[2]; const movieTitle1 = process.argv[3]; export const run = async (tableName, movieTitle1) => { const params = { Statement: "SELECT * FROM " + tableName + " where title=?", Parameters: [{ S: movieTitle1 }], }; try { const data = await ddbDocClient.send(new ExecuteStatementCommand(params)); for (let i = 0; i < data.Items.length; i++) { console.log( "Success. The query return the following data. Item " + i, data.Items[i].year, data.Items[i].title, data.Items[i].info ); } return "Run successfully"; // For unit tests. } catch (err) { console.error(err); } }; run(tableName, movieTitle1);

Update an item using PartiQL.

// Import required AWS SDK clients and commands for Node.js. import { ExecuteStatementCommand } from "@aws-sdk/client-dynamodb"; import { ddbDocClient } from "../libs/ddbDocClient.js"; const tableName = process.argv[2]; const movieYear1 = process.argv[3]; const movieTitle1 = process.argv[4]; const producer1 = process.argv[5]; export const run = async (tableName, movieYear1, movieTitle1, producer1) => { const params = { Statement: "UPDATE " + tableName + " SET Producer=? where title=? and year=?", Parameters: [{ S: producer1 }, { S: movieTitle1 }, { N: movieYear1 }], }; try { const data = await ddbDocClient.send(new ExecuteStatementCommand(params)); console.log("Success. Item updated."); return "Run successfully"; // For unit tests. } catch (err) { console.error(err); } }; run(tableName, movieYear1, movieTitle1, producer1);

Delete an item using PartiQL.

// Import required AWS SDK clients and commands for Node.js. import { ExecuteStatementCommand } from "@aws-sdk/client-dynamodb"; import { ddbDocClient } from "../libs/ddbDocClient.js"; const tableName = process.argv[2]; const movieYear1 = process.argv[3]; const movieTitle1 = process.argv[4]; export const run = async (tableName, movieYear1, movieTitle1) => { const params = { Statement: "DELETE FROM " + tableName + " where title=? and year=?", Parameters: [{ S: movieTitle1 }, { N: movieYear1 }], }; try { const data = await ddbDocClient.send(new ExecuteStatementCommand(params)); console.log("Success. Item deleted."); return "Run successfully"; // For unit tests. } catch (err) { console.error(err); } }; run(tableName, movieYear1, movieTitle1);
  • For API details, see ExecuteStatement in Amazon SDK for JavaScript API Reference.

PHP
SDK for PHP
Tip

To learn how to set up and run this example, see GitHub.

public function insertItemByPartiQL(string $statement, array $parameters) { $this->dynamoDbClient->executeStatement([ 'Statement' => "$statement", 'Parameters' => $parameters, ]); } public function getItemByPartiQL(string $tableName, array $key): Result { list($statement, $parameters) = $this->buildStatementAndParameters("SELECT", $tableName, $key['Item']); return $this->dynamoDbClient->executeStatement([ 'Parameters' => $parameters, 'Statement' => $statement, ]); } public function updateItemByPartiQL(string $statement, array $parameters) { $this->dynamoDbClient->executeStatement([ 'Statement' => $statement, 'Parameters' => $parameters, ]); } public function deleteItemByPartiQL(string $statement, array $parameters) { $this->dynamoDbClient->executeStatement([ 'Statement' => $statement, 'Parameters' => $parameters, ]); }
Python
SDK for Python (Boto3)
Tip

To learn how to set up and run this example, see GitHub.

class PartiQLWrapper: """ Encapsulates a DynamoDB resource to run PartiQL statements. """ def __init__(self, dyn_resource): """ :param dyn_resource: A Boto3 DynamoDB resource. """ self.dyn_resource = dyn_resource def run_partiql(self, statement, params): """ Runs a PartiQL statement. A Boto3 resource is used even though `execute_statement` is called on the underlying `client` object because the resource transforms input and output from plain old Python objects (POPOs) to the DynamoDB format. If you create the client directly, you must do these transforms yourself. :param statement: The PartiQL statement. :param params: The list of PartiQL parameters. These are applied to the statement in the order they are listed. :return: The items returned from the statement, if any. """ try: output = self.dyn_resource.meta.client.execute_statement( Statement=statement, Parameters=params) except ClientError as err: if err.response['Error']['Code'] == 'ResourceNotFoundException': logger.error( "Couldn't execute PartiQL '%s' because the table does not exist.", statement) else: logger.error( "Couldn't execute PartiQL '%s'. Here's why: %s: %s", statement, err.response['Error']['Code'], err.response['Error']['Message']) raise else: return output
  • For API details, see ExecuteStatement in Amazon SDK for Python (Boto3) API Reference.

For a complete list of Amazon SDK developer guides and code examples, see Using DynamoDB with an Amazon SDK. This topic also includes information about getting started and details about previous SDK versions.