0
# PartiQL
1

2
SQL-compatible query language support for DynamoDB with batch execution capabilities. PartiQL provides a familiar SQL-like syntax for querying and manipulating DynamoDB data while maintaining DynamoDB's performance characteristics and data model.
3

4
## Capabilities
5

6
### Execute Statement
7

8
Execute a single PartiQL statement against DynamoDB.
9

10
```java { .api }
11
/**
12
 * Executes a single PartiQL statement and returns the result
13
 * @param request - The request containing PartiQL statement and parameters
14
 * @return Response containing query results and consumed capacity
15
 */
16
ExecuteStatementResponse executeStatement(ExecuteStatementRequest request);
17

18
class ExecuteStatementRequest {
19
    static Builder builder();
20
    
21
    /** The PartiQL statement representing the operation to run */
22
    String statement();
23
    Builder statement(String statement);
24
    
25
    /** The parameters for the PartiQL statement, if any */
26
    List<AttributeValue> parameters();
27
    Builder parameters(Collection<AttributeValue> parameters);
28
    
29
    /** The consistency of a read operation */
30
    Boolean consistentRead();
31
    Builder consistentRead(Boolean consistentRead);
32
    
33
    /** Set this value to get remaining results, if NextToken was returned in the previous response */
34
    String nextToken();
35
    Builder nextToken(String nextToken);
36
    
37
    /** The maximum number of items to return in the response */
38
    Integer limit();
39
    Builder limit(Integer limit);
40
    
41
    /** Determines the level of detail about consumed capacity returned */
42
    ReturnConsumedCapacity returnConsumedCapacity();
43
    Builder returnConsumedCapacity(ReturnConsumedCapacity returnConsumedCapacity);
44
}
45

46
class ExecuteStatementResponse {
47
    /** The response to a PartiQL statement */
48
    List<Map<String, AttributeValue>> items();
49
    
50
    /** If a read operation was used, this property will contain the NextToken */
51
    String nextToken();
52
    
53
    /** The capacity units consumed by an operation */
54
    ConsumedCapacity consumedCapacity();
55
    
56
    /** The primary key of the item where the operation stopped for pagination */
57
    Map<String, AttributeValue> lastEvaluatedKey();
58
}
59
```
60

61
**Usage Examples:**
62

63
```java
64
import software.amazon.awssdk.services.dynamodb.DynamoDbClient;
65
import software.amazon.awssdk.services.dynamodb.model.*;
66
import java.util.List;
67
import java.util.Map;
68

69
DynamoDbClient client = DynamoDbClient.builder().build();
70

71
// SELECT query with parameters
72
ExecuteStatementResponse response = client.executeStatement(
73
    ExecuteStatementRequest.builder()
74
        .statement("SELECT * FROM \"Users\" WHERE userId = ? AND age > ?")
75
        .parameters(List.of(
76
            AttributeValue.builder().s("user123").build(),
77
            AttributeValue.builder().n("18").build()
78
        ))
79
        .consistentRead(true)
80
        .limit(10)
81
        .build()
82
);
83

84
// Process results
85
List<Map<String, AttributeValue>> items = response.items();
86
for (Map<String, AttributeValue> item : items) {
87
    String name = item.get("name").s();
88
    String email = item.get("email").s();
89
    System.out.println("User: " + name + " (" + email + ")");
90
}
91

92
// INSERT statement
93
client.executeStatement(
94
    ExecuteStatementRequest.builder()
95
        .statement("INSERT INTO \"Users\" VALUE {'userId': ?, 'name': ?, 'email': ?, 'age': ?}")
96
        .parameters(List.of(
97
            AttributeValue.builder().s("user456").build(),
98
            AttributeValue.builder().s("Jane Doe").build(),
99
            AttributeValue.builder().s("jane@example.com").build(),
100
            AttributeValue.builder().n("25").build()
101
        ))
102
        .build()
103
);
104

105
// UPDATE statement
106
client.executeStatement(
107
    ExecuteStatementRequest.builder()
108
        .statement("UPDATE \"Users\" SET email = ? WHERE userId = ?")
109
        .parameters(List.of(
110
            AttributeValue.builder().s("newemail@example.com").build(),
111
            AttributeValue.builder().s("user123").build()
112
        ))
113
        .build()
114
);
115

116
// DELETE statement
117
client.executeStatement(
118
    ExecuteStatementRequest.builder()
119
        .statement("DELETE FROM \"Users\" WHERE userId = ?")
120
        .parameters(List.of(
121
            AttributeValue.builder().s("user789").build()
122
        ))
123
        .build()
124
);
125
```
126

127
### Batch Execute Statement
128

129
Execute multiple PartiQL statements in a batch for improved performance.
130

131
```java { .api }
132
/**
133
 * Executes multiple PartiQL statements in a batch
134
 * @param request - The request containing multiple PartiQL statements
135
 * @return Response containing results for each statement
136
 */
137
BatchExecuteStatementResponse batchExecuteStatement(BatchExecuteStatementRequest request);
138

139
class BatchExecuteStatementRequest {
140
    static Builder builder();
141
    
142
    /** The list of PartiQL statements representing the batch to run */
143
    List<BatchStatementRequest> statements();
144
    Builder statements(Collection<BatchStatementRequest> statements);
145
    
146
    /** Determines the level of detail about consumed capacity returned */
147
    ReturnConsumedCapacity returnConsumedCapacity();
148
    Builder returnConsumedCapacity(ReturnConsumedCapacity returnConsumedCapacity);
149
}
150

151
class BatchStatementRequest {
152
    static Builder builder();
153
    
154
    /** A valid PartiQL statement */
155
    String statement();
156
    Builder statement(String statement);
157
    
158
    /** The parameters associated with a PartiQL statement in the batch request */
159
    List<AttributeValue> parameters();
160
    Builder parameters(Collection<AttributeValue> parameters);
161
    
162
    /** The read consistency model for the batch operation */
163
    Boolean consistentRead();
164
    Builder consistentRead(Boolean consistentRead);
165
    
166
    /** An optional parameter for the ReturnValues on the PartiQL statement */
167
    ReturnValuesOnConditionCheckFailure returnValuesOnConditionCheckFailure();
168
    Builder returnValuesOnConditionCheckFailure(ReturnValuesOnConditionCheckFailure returnValuesOnConditionCheckFailure);
169
}
170

171
class BatchExecuteStatementResponse {
172
    /** The response to each PartiQL statement in the batch */
173
    List<BatchStatementResponse> responses();
174
    
175
    /** The capacity units consumed by the batch operation */
176
    List<ConsumedCapacity> consumedCapacity();
177
}
178

179
class BatchStatementResponse {
180
    /** The error associated with the failed PartiQL batch statement */
181
    BatchStatementError error();
182
    
183
    /** The table name associated with a failed PartiQL batch statement */
184
    String tableName();
185
    
186
    /** A PartiQL batch statement response */
187
    Map<String, AttributeValue> item();
188
}
189

190
class BatchStatementError {
191
    /** The error code associated with the failed PartiQL batch statement */
192
    BatchStatementErrorCodeEnum code();
193
    
194
    /** The error message associated with the failed PartiQL batch statement */
195
    String message();
196
    
197
    /** The item which caused the condition check to fail */
198
    Map<String, AttributeValue> item();
199
}
200
```
201

202
**Usage Examples:**
203

204
```java
205
// Batch read operations
206
List<BatchStatementRequest> statements = List.of(
207
    BatchStatementRequest.builder()
208
        .statement("SELECT * FROM \"Users\" WHERE userId = ?")
209
        .parameters(List.of(AttributeValue.builder().s("user1").build()))
210
        .build(),
211
    
212
    BatchStatementRequest.builder()
213
        .statement("SELECT * FROM \"Users\" WHERE userId = ?")
214
        .parameters(List.of(AttributeValue.builder().s("user2").build()))
215
        .build(),
216
    
217
    BatchStatementRequest.builder()
218
        .statement("SELECT * FROM \"Orders\" WHERE orderId = ?")
219
        .parameters(List.of(AttributeValue.builder().s("order123").build()))
220
        .build()
221
);
222

223
BatchExecuteStatementResponse response = client.batchExecuteStatement(
224
    BatchExecuteStatementRequest.builder()
225
        .statements(statements)
226
        .returnConsumedCapacity(ReturnConsumedCapacity.TOTAL)
227
        .build()
228
);
229

230
// Process results
231
List<BatchStatementResponse> responses = response.responses();
232
for (int i = 0; i < responses.size(); i++) {
233
    BatchStatementResponse statementResponse = responses.get(i);
234
    
235
    if (statementResponse.error() != null) {
236
        System.err.println("Statement " + i + " failed: " + 
237
            statementResponse.error().message());
238
    } else if (statementResponse.item() != null) {
239
        Map<String, AttributeValue> item = statementResponse.item();
240
        System.out.println("Statement " + i + " returned item: " + item);
241
    }
242
}
243

244
// Batch write operations (must be all reads or all writes)
245
List<BatchStatementRequest> writeStatements = List.of(
246
    BatchStatementRequest.builder()
247
        .statement("INSERT INTO \"Products\" VALUE {'productId': ?, 'name': ?, 'price': ?}")
248
        .parameters(List.of(
249
            AttributeValue.builder().s("prod1").build(),
250
            AttributeValue.builder().s("Widget A").build(),
251
            AttributeValue.builder().n("29.99").build()
252
        ))
253
        .build(),
254
    
255
    BatchStatementRequest.builder()
256
        .statement("UPDATE \"Inventory\" SET quantity = quantity - ? WHERE productId = ?")
257
        .parameters(List.of(
258
            AttributeValue.builder().n("5").build(),
259
            AttributeValue.builder().s("prod1").build()
260
        ))
261
        .build()
262
);
263

264
client.batchExecuteStatement(
265
    BatchExecuteStatementRequest.builder()
266
        .statements(writeStatements)
267
        .build()
268
);
269
```
270

271
### Execute Transaction
272

273
Execute multiple PartiQL statements within a single atomic transaction (covered in detail in the Transactions document).
274

275
```java { .api }
276
/**
277
 * Executes multiple PartiQL statements within a transaction block
278
 * @param request - The request containing PartiQL statements and options
279
 * @return Response containing statement results and consumed capacity
280
 */
281
ExecuteTransactionResponse executeTransaction(ExecuteTransactionRequest request);
282
```
283

284
## PartiQL Syntax and Features
285

286
### Supported Operations
287

288
PartiQL in DynamoDB supports the following SQL-like operations:
289

290
#### SELECT Statements
291

292
```java
293
// Basic SELECT
294
"SELECT * FROM \"TableName\" WHERE pk = ?"
295

296
// SELECT with projection
297
"SELECT userId, name, email FROM \"Users\" WHERE userId = ?"
298

299
// SELECT with conditions
300
"SELECT * FROM \"Orders\" WHERE customerId = ? AND orderDate BETWEEN ? AND ?"
301

302
// SELECT with LIMIT
303
"SELECT * FROM \"Products\" WHERE category = ? LIMIT 10"
304
```
305

306
#### INSERT Statements
307

308
```java
309
// INSERT with VALUE
310
"INSERT INTO \"Users\" VALUE {'userId': ?, 'name': ?, 'email': ?}"
311

312
// INSERT with explicit attributes
313
"INSERT INTO \"Users\" (userId, name, email) VALUES (?, ?, ?)"
314
```
315

316
#### UPDATE Statements
317

318
```java
319
// SET attributes
320
"UPDATE \"Users\" SET email = ?, lastLogin = ? WHERE userId = ?"
321

322
// ADD to numeric attributes
323
"UPDATE \"Users\" SET loginCount = loginCount + ? WHERE userId = ?"
324

325
// REMOVE attributes
326
"UPDATE \"Users\" REMOVE tempData WHERE userId = ?"
327
```
328

329
#### DELETE Statements
330

331
```java
332
// DELETE entire item
333
"DELETE FROM \"Users\" WHERE userId = ?"
334

335
// Conditional DELETE
336
"DELETE FROM \"Users\" WHERE userId = ? AND lastLogin < ?"
337
```
338

339
### Data Types and Literals
340

341
```java
342
// String literals
343
"'string value'"
344
"\"double-quoted string\""
345

346
// Number literals  
347
"123"
348
"123.45"
349
"-42"
350

351
// Boolean literals
352
"true"
353
"false"
354

355
// NULL literal
356
"null"
357

358
// Binary data (base64 encoded)
359
"'binary data'"
360

361
// List literals
362
"[1, 2, 3]"
363
"['a', 'b', 'c']"
364

365
// Map literals  
366
"{'key1': 'value1', 'key2': 123}"
367
```
368

369
### Operators and Functions
370

371
#### Comparison Operators
372

373
```java
374
"="     // Equal
375
"<>"    // Not equal  
376
"<"     // Less than
377
"<="    // Less than or equal
378
">"     // Greater than
379
">="    // Greater than or equal
380
"BETWEEN ? AND ?"   // Range comparison
381
"IN (?, ?, ?)"      // Set membership
382
```
383

384
#### Logical Operators
385

386
```java
387
"AND"   // Logical AND
388
"OR"    // Logical OR  
389
"NOT"   // Logical NOT
390
```
391

392
#### Conditional Functions
393

394
```java
395
// attribute_exists function
396
"SELECT * FROM \"Users\" WHERE attribute_exists(email)"
397

398
// attribute_not_exists function  
399
"UPDATE \"Users\" SET email = ? WHERE userId = ? AND attribute_not_exists(email)"
400

401
// attribute_type function
402
"SELECT * FROM \"Users\" WHERE attribute_type(age, 'N')"
403

404
// size function
405
"SELECT * FROM \"Users\" WHERE size(tags) > ?"
406

407
// begins_with function
408
"SELECT * FROM \"Users\" WHERE begins_with(name, ?)"
409

410
// contains function
411
"SELECT * FROM \"Users\" WHERE contains(tags, ?)"
412
```
413

414
### Working with Complex Data Types
415

416
#### Maps and Lists
417

418
```java
419
// Access nested map attributes
420
"SELECT user.profile.name FROM \"Users\" WHERE userId = ?"
421

422
// Access list elements by index
423
"SELECT tags[0] FROM \"Users\" WHERE userId = ?"
424

425
// Update nested attributes
426
"UPDATE \"Users\" SET profile.email = ? WHERE userId = ?"
427

428
// Add to list
429
"UPDATE \"Users\" SET tags = list_append(tags, ?) WHERE userId = ?"
430
```
431

432
#### Sets
433

434
```java
435
// String sets
436
"UPDATE \"Users\" SET skills = ? WHERE userId = ?"  // Parameters: SS attribute
437

438
// Number sets  
439
"UPDATE \"Products\" SET sizes = ? WHERE productId = ?"  // Parameters: NS attribute
440

441
// Add to set
442
"UPDATE \"Users\" SET skills = skills + ? WHERE userId = ?"
443
```
444

445
### Pagination
446

447
Handle large result sets with pagination:
448

449
```java
450
String nextToken = null;
451
List<Map<String, AttributeValue>> allItems = new ArrayList<>();
452

453
do {
454
    ExecuteStatementRequest.Builder requestBuilder = ExecuteStatementRequest.builder()
455
        .statement("SELECT * FROM \"LargeTable\" WHERE category = ?")
456
        .parameters(List.of(AttributeValue.builder().s("electronics").build()))
457
        .limit(100);
458
    
459
    if (nextToken != null) {
460
        requestBuilder.nextToken(nextToken);
461
    }
462
    
463
    ExecuteStatementResponse response = client.executeStatement(requestBuilder.build());
464
    allItems.addAll(response.items());
465
    nextToken = response.nextToken();
466
    
467
} while (nextToken != null);
468

469
System.out.println("Retrieved " + allItems.size() + " total items");
470
```
471

472
### Error Handling
473

474
```java
475
try {
476
    ExecuteStatementResponse response = client.executeStatement(request);
477
    // Process successful response
478
    
479
} catch (ValidationException e) {
480
    System.err.println("Invalid PartiQL syntax: " + e.getMessage());
481
    
482
} catch (ResourceNotFoundException e) {
483
    System.err.println("Table or index not found: " + e.getMessage());
484
    
485
} catch (ConditionalCheckFailedException e) {
486
    System.err.println("Condition expression failed: " + e.getMessage());
487
    
488
} catch (ProvisionedThroughputExceededException e) {
489
    System.err.println("Throughput exceeded, implement retry with backoff");
490
    
491
} catch (DynamoDbException e) {
492
    System.err.println("DynamoDB error: " + e.getMessage());
493
}
494
```
495

496
## PartiQL Best Practices
497

498
### Performance Considerations
499

500
1. **Use Parameters**: Always use parameterized queries to avoid SQL injection and improve performance:
501

502
```java
503
// Good - parameterized
504
"SELECT * FROM \"Users\" WHERE userId = ?"
505

506
// Avoid - string concatenation
507
"SELECT * FROM \"Users\" WHERE userId = '" + userId + "'"
508
```
509

510
2. **Efficient Queries**: Structure queries to use primary keys and indexes:
511

512
```java
513
// Efficient - uses primary key
514
"SELECT * FROM \"Orders\" WHERE customerId = ? AND orderDate = ?"
515

516
// Less efficient - requires full table scan
517
"SELECT * FROM \"Orders\" WHERE status = ?"
518
```
519

520
3. **Batch Operations**: Use batch execution for multiple related operations:
521

522
```java
523
// Better - batch multiple reads
524
List<BatchStatementRequest> batchReads = ...;
525
client.batchExecuteStatement(...);
526

527
// Less efficient - multiple individual calls
528
for (String userId : userIds) {
529
    client.executeStatement(...);
530
}
531
```
532

533
### Data Modeling
534

535
1. **Table and Attribute Names**: Always quote table and reserved word attribute names:
536

537
```java
538
"SELECT * FROM \"Users\" WHERE \"name\" = ?"  // 'name' is reserved
539
```
540

541
2. **Consistent Data Types**: Ensure parameters match expected data types:
542

543
```java
544
// Correct type matching
545
.parameters(List.of(
546
    AttributeValue.builder().s("string_value").build(),    // String
547
    AttributeValue.builder().n("123").build(),             // Number
548
    AttributeValue.builder().bool(true).build()            // Boolean
549
))
550
```
551

552
3. **Conditional Logic**: Use condition expressions for data integrity:
553

554
```java
555
"UPDATE \"Users\" SET email = ? WHERE userId = ? AND attribute_exists(userId)"
556
```