Getting Started

In order for you to be able to execute PartiQL statements in your language of choice, you will be required to add the language dependency to your preferred build tool. The most common examples are listed below:

Java

Maven

Gradle


dependencies {
    compile "software.amazon.qldb:amazon-qldb-driver-java:1.0.1"
}

NodeJS


{
  "dependencies": {
    "amazon-qldb-driver-nodejs": "0.1.0-preview.2"
   }
}

The first step in using QLDB is to create a ledger. There are a number of options for this.

Create Ledger

Create ledger via AWS Console

The easiest way to get started is by creating a ledger through the AWS Console. With this option, you simply specify a name for the ledger, and any option tags.

Create Ledger through Console

Create ledger via AWS CLI

You can also create a ledger directly via the AWS Command Line Interface (CLI), using the createLedger call. With this, you must specify a ledger name and a permissions mode. The only permissions mode currently supported is ALLOW_ALL


aws qldb create-ledger --name qldb-guide --permissions-mode ALLOW_ALL

When you create a ledger, deletion protection is enabled by default. This is a feature in QLDB that prevents ledgers from being deleted by any user. You can disable deletion protection on ledger creation by using the --no-deletion-protection parameter.

Optionally, you can also specify tags to attach to your ledger.

Create ledger via AWS CloudFormation

You can create a ledger using CloudFormation. The example file below uses the same details as the CLI example above.


create-ledger-cf.json
{
  "AWSTemplateFormatVersion" : "2010-09-09",
  "Resources" : {
    "myQLDBLedger": {
      "Type": "AWS::QLDB::Ledger",
      "Properties": {
        "DeletionProtection": true,
        "Name": "",
        "PermissionsMode": "ALLOW_ALL",
        "Tags": [
          {
            "Key": "name",
            "Value": "qldb-guide"
          }
        ]
      }
    }
  }
}

To deploy the template, run the following from a terminal window in the same directory:


aws cloudformation deploy --template-file ./create-ledger-cf.json --stack-name qldb-demo 

Create ledger via Serverless Framework

QLDB is a fully serverless database, and it is likely that many people will use AWS Lambda to integrate with it. Many frameworks exist for building serverless applications such as AWS SAM and the Serverless Framework.

Serverless Framework allows you to use CloudFormation in a resources section. The example below will create a QLDB ledger using the same parameters as the other examples.


service: qldbguidedemo

provider:
  name: aws
  runtime: nodejs10.x
  stage: dev
  region: eu-west-1

...

resources:
  Resources:
    qldbGuideLedger:
      Type: AWS::QLDB::Ledger
      Properties:
        Name: 
        DeletionProtection: false
        PermissionsMode: ALLOW_ALL
        Tags:
          - 
            Key: name
            Value: qldb-guide

Create Tables

Once you have a ledger, the next step is to create a table in the ledger. When you interact with QLDB, you use a SQL-compatible language called PartiQL. We have already covered the fact that QLDB uses a journal-first architecture, where no record can be updated without going through the journal first. Once committed to the journal, the changes are then projected into user created tables, which can be queried.

QLDB can be considered a schemaless database, as there is no schema enforced on the data in documents within any table. Any schema can only be enforced by an application once the data has been read.

Database design is important in QLDB. PartiQL has support for nested content which can significantly simplify how you interact with a ledger. In addition, currently QLDB has limited indexing capability and does not support all PartiQL operations. There are restrictions that you take into account.

  • Indexes can improve query performance, however
    • They can only be created on empty tables
    • They can only be created on a single field
    • They cannot be dropped once created
    • There is a maximum of 5 indexes per table
    • Query performance is only improved when you use an equality predicate e.g. fieldName = XYZ
  • There is a maximum of 20 active tables per ledger

Create table via AWS Console

The simplest way to get started is to create a table through the AWS Console. With this option, you specify the ledger previously created, click on query ledger, and enter the PartiQL statement in the query editor window as shown below:

Create Table through Console

Create table via custom resource

There is currently no way of creating a table and an index in CloudFormation or via the CLI. One way to ensure that the table and indexes are created along with the ledger is to make use of a custom resource in CloudFormation. Custom resources enable you to write custom provisioning logic in templates that AWS CloudFormation runs anytime you create, update (if you changed the custom resource), or delete stacks.

The following is a snippet from a serverless.yml file to show how this is achieved:


resources:
  Resources:
    qldbGuideLedger:
      Type: AWS::QLDB::Ledger
      Properties:
        Name: qldb-simple-demo-${self:provider.stage}
        DeletionProtection: false
        PermissionsMode: ALLOW_ALL
        Tags:
          - 
            Key: name
            Value: qldb-simple-demo

    qldbTable:
      Type: Custom::qldbTable
      DependsOn: qldbGuideLedger
      Properties:
        ServiceToken: !GetAtt CreateTableLambdaFunction.Arn
        Version: 1.0  #change this to force redeploy

    qldbIndex:
      Type: Custom::qldbIndexes
      DependsOn: qldbTable
      Properties:
        ServiceToken: !GetAtt CreateIndexLambdaFunction.Arn
        Version: 1.0  #change this to force redeploy

This shows how the custom Lambda function to create the index is only invoked once the Lambda function to create the table has successfully run, and this in turn is dependent on the creation of the ledger itself.

The ServiceToken is the ARN of the function that CloudFormation invokes when you create, update or delete the stack. The name of CreateTableLamdaFunction.ARN is the Logical ID in the CloudFormation that is created by the Serverless Framework for a function defined as createTable in the functions section.

The full working example can be found in QLDB Simple Demo and has been tagged using v0.2

Insert, Query and Modify Data

In order to prepare an application to use QLDB the following elements should be added to your build tool. In this example we have used Maven:

So that it is possible to map between entities and the Ion dataformat, Jackson Object Mapper now supports converting between Ion and Java DTO's or JSON.

To add this dependency, please add the following to your pom.file:

Please note: in some of the associated demo applications the following library is also added as a dependency com.amazonaws aws-java-sdk-qldb 1.11.693 This is due to the Jackson Mapper using older versions of the classes and means it is not possible to map between some of the types under the new package structure.

QLDB Ledger Connection Class

To be able to initialise PartiQL statements with the QLDB Driver, as per the AWS examples, a Ledger Connection class is created:


public static PooledQldbDriver pooledDriver = createPooledQldbDriver();

/**
 * Method to create a pooled qldb driver for creating sessions
 *
 * @return pooled qldb driver
 */
public static PooledQldbDriver createPooledQldbDriver() {
    AmazonQLDBSessionClientBuilder builder = AmazonQLDBSessionClientBuilder.standard();
    builder.setRegion(LedgerConstants.REGION);
    if(null != endpoint) {
        builder.setEndpointConfiguration(new AwsClientBuilder.EndpointConfiguration(endpoint, region));
    }
    if(null != credentialsProvider) {
        builder.setCredentials(credentialsProvider);
    }
    return PooledQldbDriver.builder()
        .withLedger(LedgerConstants.LEDGER_NAME)
        .withSessionClientBuilder(builder)
        .build();
}

In the LedgerConnection it will also be worth adding a function to get the AmazonQLDB Client if you intend on running queries to verify documents which involve getting revisions. The following codeblock can be used to enable this:


/**
 * Method to create an amazon qldb client that can be used when
 * verifying documents and getting revisions.
 *
 * @return amazon qldb client
 */
public static AmazonQLDB createQLDBClient() {
    AmazonQLDBClientBuilder builder = AmazonQLDBClientBuilder.standard();
    builder.setRegion(LedgerConstants.REGION);
    if(null != endpoint) {
        builder.setEndpointConfiguration(new AwsClientBuilder.EndpointConfiguration(endpoint, region));
    }
    if(null != credentialsProvider) {
        builder.setCredentials(credentialsProvider);
    }
    return builder.build();
}

To create a QLDB Session it is useful to add a helper function that can be used later in the repository classes.


public static QldbSession createQldbSession() {
    return driver.getSession();
}

Repository Setup

In the examples in this section we have used the Spring data JPA repository which provides the scaffolding for Auto-wiring and method structure.

Longer term we will look to add support to the Spring data repositories much like those familiar with Spring will have seen support for other common databases https://spring.io/projects/spring-data.

To use the Spring or Spring boot support, the following can be added to the pom.xml file. Please note that Spring is not necessary or used within te Repository class itself and does not need to be used.

If you wish to use the Spring Repository style implementation then it can be referenced using @Autowired or by injecting via the instantation through Constructors as follows:


    // calling class
    
   .....
   
   private BicycleLicenceQldbRepository repository;
   
   public MyCallingClass(BicycleLicenceQldbRepository qldbRepository) {
       this.repository = qldbRepository;
   }

Inserting Documents

The CrudRepository interface forces method signature implementations for the key Create, Retrive, Update and Delete functions. The create method utilises the LedgerConnection pooled QLDB Driver to insert records in to the ledger.

Retrieving the QLDB Document Id

It is possible that you can use the returning Result class to get hold of the generated Document Id, this will be demonstrated when we add query support to the QLDB Repository where the Result class is parsed to a List of IonStruct.

Please note in this example it is possible to get a document based on email as it has to be unique in this demo

If you wish to query for the document Id explicitly it is possible to do this as follows:

Querying Documents

Querying documents in QLDB is very similar in process to that of creating documents. The key areas highlighted in the example below demonstrate how to use the list of ION structs returned from the query execution to map back to Java using Jackson.