CorDapp Database Setup-Development Perspective

This blog post shows you how to configure your node to connect to a Postgres database in development mode, instead of H2 which is used by default.

Generally, when connecting to a database we consider two perspectives-development and production. From a development perspective, Corda lets the node connect to the database using admin permissions, which lets the node create the database schemas automatically. Automatically running schemas against a database is controlled by the runMigration flag, which we will set to true in dev mode. So, from a development perspective, developers can quickly get started with connecting to a non H2 database.

But from a production perspective, we don’t want the node to perform any schema creation automatically on the database. This can be controlled by setting the runMigration flag to false. We use the database management tool for generating the database-specific scripts. We will talk more about this in the next blog.

In this post, we will look at how to set up a PostgreSQL database for an IOU application. To do this, we will start a PostgreSQL container using docker and PSQL to connect to the database.

Corda Enterprise supports four commercial databases: PostgreSQL (also supported in Corda Open Source), SQL Server, Oracle, Azure SQL. For this blog, we will be connecting to PostgreSQL.

Prerequisites

1. We will use the IOU application from the sample directory. We will configure 3 nodes — Notary, PartyA, PartyB. The Notary will connect to a H2 database (by default), hence no explicit database configuration is required. PartyA and PartyB will connect to the PostgreSQL database, which will require configuring the steps mentioned below.
2. The latest version of PostgreSQL and JDBC PostgreSQL driver can be used. PostgreSQL 9.6 is the lowest acceptable version. For this demo, I used PostgreSQL 11 and docker to quickly create a database.

There are 3 steps to connect to a PostgreSQL database.

1. Creating a database user with administrative schema permissions.
2. Node Configuration (set runMigration = true).
3. Start the node.

Step 1: Creating a database user with schema permissions, start docker

1. Create file party_a.sql for PartyA node containing scripts to create a user, schema, and assign administrative access for the schema to user party_a. The PartyA node will connect to the database using user party_a.

CREATE USER "party_a" WITH LOGIN PASSWORD 'my_password';
CREATE SCHEMA "party_a_schema";
GRANT USAGE, CREATE ON SCHEMA "party_a_schema" TO "party_a";
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON ALL tables IN SCHEMA "party_a_schema" TO "party_a";
ALTER DEFAULT privileges IN SCHEMA "party_a_schema" GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON tables TO "party_a";
GRANT USAGE, SELECT ON ALL sequences IN SCHEMA "party_a_schema" TO "party_a";
ALTER DEFAULT privileges IN SCHEMA "party_a_schema" GRANT USAGE, SELECT ON sequences TO "party_a";
ALTER ROLE "party_a" SET search_path = "party_a_schema";

Lines 1/2 We create party_a user and schema party_a_schema.

Line 3 allows the user to access the schema and create objects in that schema.

Line 4/5/6/7 is about adding permissions for current tables in that schema and the tables created in the future.

By default, in postgresql the default schema resolves to the user name. But, since our schema name is diff than the user name, Corda adds schema namespace prefix to most of the SQL queries but not all. So we can use set search_path to set the default schema name.

2. The same should be done for PartyB, below is what that configuration would look like.

CREATE USER "party_b" WITH LOGIN PASSWORD 'my_password';
CREATE SCHEMA "party_b_schema";
GRANT USAGE, CREATE ON SCHEMA "party_b_schema" TO "party_b";
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON ALL tables IN SCHEMA "party_b_schema" TO "party_b";
ALTER DEFAULT privileges IN SCHEMA "party_b_schema" GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON tables TO "party_b";
GRANT USAGE, SELECT ON ALL sequences IN SCHEMA "party_b_schema" TO "party_b";
ALTER DEFAULT privileges IN SCHEMA "party_b_schema" GRANT USAGE, SELECT ON sequences TO "party_b";
ALTER ROLE "party_b" SET search_path = "party_b_schema";

3. Start a docker Postgres container by running the below command. The container is named postgres_for_corda.

docker run --name postgres_for_corda -e POSTGRES_PASSWORD=my_password -d -p 5432:5432 postgres:11

4. Connect to the database using admin access and execute above created scripts to create users, schema’s and assign permissions. Use the docker exec command to execute party_a.sql.

cat party_a.sql | docker exec -i postgres_for_corda psql -h localhost -p 5432 -U postgres

cat party_b.sql | docker exec -i postgres_for_corda psql -h localhost -p 5432 -U postgres

In the last section below, I have mentioned queries to list the users and schemas created. Run these queries to ensure the users and schemas have been created successfully.

Step 2: Node Configuration

1. Create party_a_database.conf for PartyA specifying the database connection properties to connect to the database.

dataSourceProperties {
    dataSource {
        password="my_password"
        url="jdbc:postgresql://localhost:5432/postgres"
        user="party_a"
    }
    dataSourceClassName="org.postgresql.ds.PGSimpleDataSource"
}
database {
   transactionIsolationLevel=READ_COMMITTED
   schema="party_a_schema" 
   runMigration=true
}
# When JDBC driver is not placed into node's 'drivers' directory then add absolute path:
# jarDirs = ['absolute_path_to_directory_with_jdbc_driver']

2. The same should be done for PartyB, below is what that configuration would look like.

dataSourceProperties {
    dataSource {
        password="my_password"
        url="jdbc:postgresql://localhost:5432/postgres"
        user="party_b"
    }
    dataSourceClassName="org.postgresql.ds.PGSimpleDataSource"
}
database {
   transactionIsolationLevel=READ_COMMITTED
   schema="party_b_schema"
   runMigration=true
}
# When JDBC driver is not placed into node's 'drivers' directory then add absolute path:
# jarDirs = ['absolute_path_to_directory_with_jdbc_driver']

Since we are running this in development mode, we are setting runMigration to true.

The Corda documentation talks more about writing scripts for Azure, Oracle and Sql Server as well.

3. Deploy your IOU CorDapp and run the command below to update each node’s node.conf. This will append the database connection properties to node.conf.

cat party_a_database.conf >> ../../build/nodes/PartyA/node.conf

cat party_b_database.conf >> ../../build/nodes/PartyB/node.conf

You can even add the database configuration to node attribute in deployNodes task.

node {
  ……………
 extraConfig = [
'dataSourceProperties.dataSourceClassName':'org.postgresql.ds.PGSimpleDataSource',
'dataSourceProperties.dataSource.url':'jdbc:postgresql://localhost:5432/postgres',
'dataSourceProperties.dataSource.password' : 'my_password',
'dataSourceProperties.dataSource.user' : 'party_a',
'database.transactionIsolationLevel' : 'READ_COMMITTED',
'database.schema' : 'party_a_schema',
'database.runMigration' : 'false' ]
 drivers = ['absolute_path_to_directory_with_jdbc_driver']
}

4. Copy the PostgreSQL jdbc driver to node’s driver directory. You could also specify the driver’s absolute path in jarDirs property in node.conf.

cp postgresql-42.1.4.jar nodes/PartyA/drivers/
cp postgresql-42.1.4.jar nodes/PartyB/drivers/

Step 3 : Run the node

1. The node connects to the database using administrative access, and automatically creates the required tables.

./runnodes

2. Verify if the tables are created using

docker exec -i postgres_for_corda psql -U postgres -p 5432 -h localhost postgres -c "dt party_a_schema."

Node Upgrade

If you want to perform node upgrade from version x to y follow below steps.

1. Stop the node
2. Replace the current corda.jar with the upgraded corda.jar.
3. Start the node. The node connects to the database, automatically detects schema changes in the new version and applies them automatically on the database.

Points to Note:

1. To download the IOU application used in this post you can the clone url below, switch to database-migration-tutorial branch and use the database-migration-tutorial project.

git clone https://github.com/corda/samples.git

2. To connect to databases other than PostgreSQL visit documentation which provides scripts for each.

3. runMigration flag is only used in Corda Enterprise. For Corda Open Source you could use initialiseSchema to control the automatic schema creation. The runMigration flag only enables the automatic application of schemas onto the database. At the end of the day, which scripts get executed depends on the way the node is connected to the database, and if it’s connected using admin or restrictive access.

Some useful docker commands to use

LIST ALL ROLES

docker exec -i postgres_for_corda psql -U postgres -p 5432 -h localhost postgres -c “dg”

LIST ALL SCHEMAS

docker exec -i postgres_for_corda psql -U postgres -p 5432 -h localhost postgres -c “dn”

ALL TABLES IN A SCHEMA

docker exec -i postgres_for_corda psql -U postgres -p 5432 -h localhost postgres -c “dt party_a_schema.*”

SELECT CUSTOM TABLE ROWS

docker exec -i postgres_for_corda psql -U postgres -p 5432 -h localhost postgres -c “select * from party_a_schema.iou_states”

DROP SCHEMA

docker exec -i postgres_for_corda psql -U postgres -p 5432 -h localhost postgres -c “drop schema party_a_schema cascade”

DROP USER

docker exec -i postgres_for_corda psql -U postgres -p 5432 -h localhost postgres -c “REASSIGN OWNED BY party_a TO my_user”

docker exec -i postgres_for_corda psql -U postgres -p 5432 -h localhost postgres -c “DROP role party_a”

SHOW SEARCH PATH

docker exec -i postgres_for_corda psql -U postgres -p 5432 -h localhost postgres -c “show search_path”

Next Steps

Tutorial 2 : Connect to PostgreSQL from production perspective

Thanks to Szymon Sztuka and The Corda Team

Thanks for reading — Sneha Damle, Developer Evangelist (R3).

---

CorDapp Database Setup-Development Perspective was originally published in Corda on Medium, where people are continuing the conversation by highlighting and responding to this story.