Database schema setup with Postgres SQL

Summary

This page contains the instructions of setting up database schema for Corda with Postgres SQL. It is intended to be a reference and not a tutorial of how database schemas are managed in Corda. 

To get more thorough introduction of DB management, please refer to the following official documentations first:

Also, it's assumed that you have basic understanding of SQL database and SQL queries. If you are not, it is strongly recommended that you go through some basic tutorial or reading on that first. There are tons of online materials on SQL, here are some of them:

英語

https://dataschool.com/learn-sql/

日本語

https://www.techscore.com/tech/sql/index.html/

https://db-study.com/archives/category/sql%E5%85%A5%E9%96%80

Prerequisites

  • Corda Enterprise 4.3.1 or 4,2
  • Database Management Tool 4.3.1 or 4.2 (included in Corda Enterprise)
  • PostgresSQL 9.6 - for the DB client, you can use any client application that supports Postgres SQL.

Create users

First we need to create users in the database. We will be creating 2 users, one with administrative permissions, the other with restricted permissions. The one with restricted permission will be used by the Corda node.

  • User with administrative permission - my_admin_user
  • User with restricted permission - my_user

The following query is copied fromhttps://docs.corda.net/docs/corda-enterprise/4.3/node-database-admin.html#postgresql

CREATE USER "my_admin_user" WITH LOGIN PASSWORD 'my_password';
CREATE USER "my_user" WITH LOGIN PASSWORD 'my_password';
GRANT "my_user" TO "my_admin_user";

 

Create schema

We also need to create a schema for Corda, and set up related access for the users created in the previous step. We will name the schema my_schema.

The following query is copied from https://docs.corda.net/docs/corda-enterprise/4.3/node-database-admin.html#postgresql

CREATE SCHEMA "my_schema";
GRANT USAGE, CREATE ON SCHEMA "my_schema" TO "my_admin_user";
ALTER DEFAULT privileges IN SCHEMA "my_schema" GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON tables TO "my_admin_user";
ALTER DEFAULT privileges IN SCHEMA "my_schema" GRANT USAGE, SELECT ON sequences TO "my_admin_user";
ALTER ROLE "my_admin_user" SET search_path = "my_schema",public;

GRANT USAGE ON SCHEMA "my_schema" TO "my_user";
ALTER ROLE "my_user" SET search_path = "my_schema";

 

Create Liquibase management tables

Corda uses Liquibase to manage database migration. After the users and schema are created, we are ready to create management tables used by Liquibase.

The following query is copied from https://docs.corda.net/docs/corda-enterprise/4.3/node-database-admin.html#postgresql-1

CREATE TABLE "my_schema".databasechangelog (
id varchar(255) NOT NULL,
author varchar(255) NOT NULL,
filename varchar(255) NOT NULL,
dateexecuted timestamp NOT NULL,
orderexecuted int4 NOT NULL,
exectype varchar(10) NOT NULL,
md5sum varchar(35) NULL,
description varchar(255) NULL,
comments varchar(255) NULL,
tag varchar(255) NULL,
liquibase varchar(20) NULL,
contexts varchar(255) NULL,
labels varchar(255) NULL,
deployment_id varchar(10) NULL);

CREATE TABLE "my_schema".databasechangeloglock (
id int4 NOT NULL,
locked bool NOT NULL,
lockgranted timestamp NULL,
lockedby varchar(255) NULL,
CONSTRAINT pk_databasechangeloglock PRIMARY KEY (id));

REVOKE INSERT, UPDATE, DELETE ON TABLE "my_schema".databasechangelog FROM my_user;

 

Node Configuration required for Database Management Tool

We will be using Database Management Tool included in Corda Enterprise to extract and apply migration scripts. Before doing that, you need to add the following settings in the node.conf config file.

The following configuration is copied from https://docs.corda.net/docs/corda-enterprise/4.3/node-database-admin.html#postgresql-2 and slightly modified. 

dataSourceProperties = {
    dataSourceClassName = "org.postgresql.ds.PGSimpleDataSource"
    dataSource.url = "jdbc:postgresql://<host>:<port>/<database>"
    dataSource.user = my_admin_user
    dataSource.password = "my_password"
}

database = {
    transactionIsolationLevel = READ_COMMITTED
    schema = my_schema
    runMigration = false
}

 

You need to replace the placeholders with proper values. For example, if the database server is setup locally at port 5432, and you have a database named "partyA", then the configurations will look like the this:

dataSourceProperties = {
    dataSourceClassName = "org.postgresql.ds.PGSimpleDataSource"
    dataSource.url = "jdbc:postgresql://localhost:5432/partyA"
    dataSource.user = my_admin_user
    dataSource.password = "my_password"
}

database = {
    transactionIsolationLevel = READ_COMMITTED
    schema = my_schema
runMigration = false
}

 

Also runMigration is not required as the default value is false. It's shown here so you are aware of this important config property.

*Supposedly we should use a user with restricted permission to connect to the DB from Corda node, but for some reason there’s a permission issue. So I’m using a user (my_admin_user) with administrative permission here.

 

Extracting and applying migration scripts with Database Management Tool

Before proceeding to the next step, make sure:

  1. Database Management Tool jar file ( tools-database-manager-4.2.<release version>.jar ) is placed under the root of the node.
  2. JDBC driver is placed in the drivers directory under the node's root directory.

You can download JDBC driver for Postgres SQL here: https://jdbc.postgresql.org/download.html

So for example, 

~/sandbox/simple-db-schema/partyA
12:24 $ ls drivers/
postgresql-42.2.8.jar
~/sandbox/simple-db-schema/partyA

where 

~/sandbox/simple-db-schema/partyA

is the root directory of the node. 

Extract DDL script for Corda

 Run the tool with the dry-run command:

java -jar tools-database-manager-4.3-RC02.jar dry-run -b ./

Upon successful execution, you should see output similar to this:

12:15 $ java -jar tools-database-manager-4.3-RC02.jar dry-run -b ./
Exporting the current database migrations ...
Successfully exported to migration20200416121547.sql

 

Extract DDL scripts for Cordapps (optional)

A CorDapp installed on your node might need to store data in custom database tables. If that's the case, the migration script files should be included in the jar file. You can verify it by running the following command:

jar -tf <cordapp.jar> | grep -E 'migration.*\.(xml|yml|sql)'

 If that's the case, you can extract the migration scripts by using create-migration-sql-for-cordapp sub-command of the Database Management Tool:

java -jar tools-database-manager-4.3-RC02.jar create-migration-sql-for-cordapp -b ./

 

Apply migration scripts 

Now we are ready to apply the migration scripts (DDL) extracted in the previous two steps. To do that, use execute-migration sub-command:

java -jar tools-database-manager-4.3-RC02.jar execute-migration -b ./

If successful, you should see an output similar to the following

Running the database migration on .
Changesets to run: 121 (migration/node-metering.changelog-v1.xml::metering_data_table::R3.Corda, migration/node-metering.changelog-v1.xml::cordapp_metadata_table::R3.Corda, migration/common.changelog-init.xml::1511451595465-1.3::R3.Corda, ...)
Migration completed successfully

 

Now you are ready to start up the node by running:

java -jar corda.jar

Once the node is started up, you should be able to see the tables created by Corda in your database.

Screen_Shot_2020-04-20_at_12.06.50.png

You can also start making queries. For example, to query for known identities in the network:

SELECT * FROM my_schema.node_named_identities LIMIT 1000;

 

この記事は役に立ちましたか?
1人中1人がこの記事が役に立ったと言っています