Skip to main content
Version: 3.13

Getting Started with ScalarDB

This getting started tutorial explains how to configure your preferred database in ScalarDB and illustrates the process of creating a sample e-commerce application, where items can be ordered and paid for with a credit card by using ScalarDB. The sample e-commerce application shows how users can order and pay for items by using a line of credit.

warning

Since the focus of the sample application is to demonstrate using ScalarDB, application-specific error handling, authentication processing, and similar functions are not included in the sample application. For details about exception handling in ScalarDB, see How to handle exceptions.

Install a JDK​

Because ScalarDB is written in Java, you must have one of the following Java Development Kits (JDKs) installed in your environment:

note

We recommend using the LTS versions mentioned above, but other non-LTS versions may work.

In addition, other JDKs should work with ScalarDB, but we haven't tested them.

Clone the ScalarDB samples repository​

Open Terminal, then clone the ScalarDB samples repository by running the following command:

$ git clone https://github.com/scalar-labs/scalardb-samples

Then, go to the directory that contains the sample application by running the following command:

$ cd scalardb-samples/scalardb-sample

Set up your database for ScalarDB​

Select your database, and follow the instructions to configure it for ScalarDB.

For a list of databases that ScalarDB supports, see Supported Databases.

Run JDBC databases locally

You can run MySQL or PostgreSQL in Docker Compose by using the docker-compose.yml file in the scalardb-samples/scalardb-sample directory.

To start MySQL, run the following command:

$ docker compose up -d mysql

To start PostgreSQL, run the following command:

$ docker compose up -d postgres

Configure ScalarDB

The database.properties file in the scalardb-samples/scalardb-sample directory holds database configurations for ScalarDB. The following is the configuration for MySQL and PostgreSQL.

note

Be sure to uncomment the scalar.db.contact_points, scalar.db.username and scalar.db.password for the database you are using.

# The JDBC database storage implementation is used for Consensus Commit.
scalar.db.storage=jdbc

# For MySQL.
# scalar.db.contact_points=jdbc:mysql://localhost:3306/
# scalar.db.username=root
# scalar.db.password=mysql

# For PostgreSQL.
# scalar.db.contact_points=jdbc:postgresql://localhost:5432/
# scalar.db.username=postgres
# scalar.db.password=postgres

Create and load the database schema​

You need to define the database schema (the method in which the data will be organized) in the application. For details about the supported data types, see Data type mapping between ScalarDB and other databases.

For this tutorial, create a file named schema.json in the scalardb-samples/scalardb-sample directory. Then, add the following JSON code to define the schema.

{
  "sample.customers": {
    "transaction": true,
    "partition-key": [
      "customer_id"
    ],
    "columns": {
      "customer_id": "INT",
      "name": "TEXT",
      "credit_limit": "INT",
      "credit_total": "INT"
    }
  },
  "sample.orders": {
    "transaction": true,
    "partition-key": [
      "customer_id"
    ],
    "clustering-key": [
      "timestamp"
    ],
    "secondary-index": [
      "order_id"
    ],
    "columns": {
      "order_id": "TEXT",
      "customer_id": "INT",
      "timestamp": "BIGINT"
    }
  },
  "sample.statements": {
    "transaction": true,
    "partition-key": [
      "order_id"
    ],
    "clustering-key": [
      "item_id"
    ],
    "columns": {
      "order_id": "TEXT",
      "item_id": "INT",
      "count": "INT"
    }
  },
  "sample.items": {
    "transaction": true,
    "partition-key": [
      "item_id"
    ],
    "columns": {
      "item_id": "INT",
      "name": "TEXT",
      "price": "INT"
    }
  }
}

To apply the schema, go to the scalardb Releases page and download the ScalarDB Schema Loader that matches the version of ScalarDB that you are using to the scalardb-samples/scalardb-sample directory.

Then, run the following command, replacing <VERSION> with the version of the ScalarDB Schema Loader that you downloaded:

$ java -jar scalardb-schema-loader-<VERSION>.jar --config database.properties --schema-file schema.json --coordinator
note

The --coordinator option is specified because a table with transaction set to true exists in the schema. For details about configuring and loading a schema, see ScalarDB Schema Loader.

Schema details​

As shown in schema.json for the sample application, all the tables are created in the sample namespace.

  • sample.customers: a table that manages customer information
    • credit_limit: the maximum amount of money that the lender will allow the customer to spend from their line of credit
    • credit_total: the amount of money that the customer has spent from their line of credit
  • sample.orders: a table that manages order information
  • sample.statements: a table that manages order statement information
  • sample.items: a table that manages information for items to be ordered

The Entity Relationship Diagram for the schema is as follows:

ERD

Load the initial data​

Before running the sample application, you need to load the initial data by running the following command:

$ ./gradlew run --args="LoadInitialData"

After the initial data has loaded, the following records should be stored in the tables.

sample.customers table

customer_idnamecredit_limitcredit_total
1Yamada Taro100000
2Yamada Hanako100000
3Suzuki Ichiro100000

sample.items table

item_idnameprice
1Apple1000
2Orange2000
3Grape2500
4Mango5000
5Melon3000

Execute transactions and retrieve data in the sample application​

The following sections describe how to execute transactions and retrieve data in the sample e-commerce application.

Get customer information​

Start with getting information about the customer whose ID is 1 by running the following command:

$ ./gradlew run --args="GetCustomerInfo 1"

You should see the following output:

...
{"id": 1, "name": "Yamada Taro", "credit_limit": 10000, "credit_total": 0}
...

Place an order​

Then, have customer ID 1 place an order for three apples and two oranges by running the following command:

note

The order format in this command is ./gradlew run --args="PlaceOrder <CUSTOMER_ID> <ITEM_ID>:<COUNT>,<ITEM_ID>:<COUNT>,...".

$ ./gradlew run --args="PlaceOrder 1 1:3,2:2"

You should see a similar output as below, with a different UUID for order_id, which confirms that the order was successful:

...
{"order_id": "dea4964a-ff50-4ecf-9201-027981a1566e"}
...

Check order details​

Check details about the order by running the following command, replacing <ORDER_ID_UUID> with the UUID for the order_id that was shown after running the previous command:

$ ./gradlew run --args="GetOrder <ORDER_ID_UUID>"

You should see a similar output as below, with different UUIDs for order_id and timestamp:

...
{"order": {"order_id": "dea4964a-ff50-4ecf-9201-027981a1566e","timestamp": 1650948340914,"customer_id": 1,"customer_name": "Yamada Taro","statement": [{"item_id": 1,"item_name": "Apple","price": 1000,"count": 3,"total": 3000},{"item_id": 2,"item_name": "Orange","price": 2000,"count": 2,"total": 4000}],"total": 7000}}
...

Place another order​

Place an order for one melon that uses the remaining amount in credit_total for customer ID 1 by running the following command:

$ ./gradlew run --args="PlaceOrder 1 5:1"

You should see a similar output as below, with a different UUID for order_id, which confirms that the order was successful:

...
{"order_id": "bcc34150-91fa-4bea-83db-d2dbe6f0f30d"}
...

Check order history​

Get the history of all orders for customer ID 1 by running the following command:

$ ./gradlew run --args="GetOrders 1"

You should see a similar output as below, with different UUIDs for order_id and timestamp, which shows the history of all orders for customer ID 1 in descending order by timestamp:

...
{"order": [{"order_id": "dea4964a-ff50-4ecf-9201-027981a1566e","timestamp": 1650948340914,"customer_id": 1,"customer_name": "Yamada Taro","statement": [{"item_id": 1,"item_name": "Apple","price": 1000,"count": 3,"total": 3000},{"item_id": 2,"item_name": "Orange","price": 2000,"count": 2,"total": 4000}],"total": 7000},{"order_id": "bcc34150-91fa-4bea-83db-d2dbe6f0f30d","timestamp": 1650948412766,"customer_id": 1,"customer_name": "Yamada Taro","statement": [{"item_id": 5,"item_name": "Melon","price": 3000,"count": 1,"total": 3000}],"total": 3000}]}
...

Check credit total​

Get the credit total for customer ID 1 by running the following command:

$ ./gradlew run --args="GetCustomerInfo 1"

You should see the following output, which shows that customer ID 1 has reached their credit_limit in credit_total and cannot place anymore orders:

...
{"id": 1, "name": "Yamada Taro", "credit_limit": 10000, "credit_total": 10000}
...

Try to place an order for one grape and one mango by running the following command:

$ ./gradlew run --args="PlaceOrder 1 3:1,4:1"

You should see the following output, which shows that the order failed because the credit_total amount would exceed the credit_limit amount.

...
java.lang.RuntimeException: Credit limit exceeded
        at sample.Sample.placeOrder(Sample.java:205)
        at sample.command.PlaceOrderCommand.call(PlaceOrderCommand.java:33)
        at sample.command.PlaceOrderCommand.call(PlaceOrderCommand.java:8)
        at picocli.CommandLine.executeUserObject(CommandLine.java:1783)
        at picocli.CommandLine.access$900(CommandLine.java:145)
        at picocli.CommandLine$RunLast.handle(CommandLine.java:2141)
        at picocli.CommandLine$RunLast.handle(CommandLine.java:2108)
        at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:1975)
        at picocli.CommandLine.execute(CommandLine.java:1904)
        at sample.command.SampleCommand.main(SampleCommand.java:35)
...

Make a payment​

To continue making orders, customer ID 1 must make a payment to reduce the credit_total amount.

Make a payment by running the following command:

$ ./gradlew run --args="Repayment 1 8000"

Then, check the credit_total amount for customer ID 1 by running the following command:

$ ./gradlew run --args="GetCustomerInfo 1"

You should see the following output, which shows that a payment was applied to customer ID 1, reducing the credit_total amount:

...
{"id": 1, "name": "Yamada Taro", "credit_limit": 10000, "credit_total": 2000}
...

Now that customer ID 1 has made a payment, place an order for one grape and one melon by running the following command:

$ ./gradlew run --args="PlaceOrder 1 3:1,4:1"

You should see a similar output as below, with a different UUID for order_id, which confirms that the order was successful:

...
{"order_id": "8911cab3-1c2b-4322-9386-adb1c024e078"}
...

Stop the database​

To stop the database, stop the Docker container by running the following command:

$ docker compose down

Reference​

To see the source code for the e-commerce application used in this tutorial, see Sample.java.