Simplified database schema setup for development

This document provides instructions on how to create database schema and configure a Corda node that is suitable for development and testing purposes. This setup allows the auto-creation of database objects by the node upon startup by connecting to a database with a user with administrative permissions. Please refer to Database schema setup if you are setting up a Corda database in a production environment.

The instructions cover all commercial third-party database vendors supported by Corda Enteprise (Azure SQL, SQL Server, Oracle and PostgreSQL), and the default embedded H2 database:

Setting up a Corda node to connect to a database requires:

Corda ships out of the box with an H2 database which doesn’t require any configuration (see the documentation on Database access when running H2, hence when using H2 database it’s sufficient to start the node and the database will be created.

Before running a Corda node you must create a database user and schema namespace with administrative permissions (except H2 database). This grants the database user full access to a Corda node, such that it can execute both DDL statements (to define data structures/schema content e.g. tables) and DML queries (to manipulate data itself e.g. select/delete rows). This permission set is more permissive and should be used with caution in production environments.

Variants of Data Definition Language (DDL) scripts to are provided for each supported database vendor. The example permissions scripts have no group roles nor specify physical database settings e.g. max disk space quota for a user. The scripts and node configuration snippets contain example values my_login for login, my_user/my_admin_user for user, my_password for password, and my_schema for schema name. These values are for illustration purposes only. Please substitute with actual values configured for your environment(s).

Creating database user with schema permissions for:

Connect to the master database as an administrator (e.g. jdbc:sqlserver://<database_server>.database.windows.net:1433;databaseName=master;[…]). Run the following script to create a user and a login:

CREATE LOGIN my_login WITH PASSWORD = 'my_password';
CREATE USER my_user FOR LOGIN my_login;

By default the password must contain characters from three of the following four sets: uppercase letters, lowercase letters, digits, and symbols, e.g. C0rdaAP4ssword is a valid password. Passwords are delimited with single quotes.

Connect to a user database as the administrator (replace master with a user database in the connection string). Run the following script to create a schema and assign user permissions:

CREATE SCHEMA my_schema;

CREATE USER my_user FOR LOGIN my_login WITH DEFAULT_SCHEMA = my_schema;
GRANT SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION, ALTER, REFERENCES ON SCHEMA::my_schema TO my_user;
GRANT CREATE TABLE TO my_user;
GRANT CREATE VIEW TO my_user;

Connect to the master database as an administrator (e.g. jdbc:sqlserver://:;databaseName=master). Run the following script to create a database, a user and a login:

CREATE DATABASE my_database;

CREATE LOGIN my_login WITH PASSWORD = 'my_password', DEFAULT_DATABASE = my_database;
CREATE USER my_user FOR LOGIN my_login;

By default the password must contain characters from three of the following four sets: uppercase letters, lowercase letters, digits, and symbols, e.g. C0rdaAP4ssword is a valid password. Passwords are delimited with single quotes.

You can create schemas for several Corda nodes within the same database (my_database), in which case run the first DDL statement (CREATE DATABASE my_database;) only once.

Connect to a user database as the administrator (replace master with my_database in the connection string). Run the following script to create a schema and assign user permissions:

CREATE SCHEMA my_schema;

CREATE USER my_user FOR LOGIN my_login WITH DEFAULT_SCHEMA = my_schema;
GRANT SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION, ALTER, REFERENCES ON SCHEMA::my_schema TO my_user;
GRANT CREATE TABLE TO my_user;
GRANT CREATE VIEW TO my_user;

This script uses the default tablespace users with unlimited database space quota assigned to the user. Revise these settings depending on your nodes sizing requirements.

CREATE USER my_user IDENTIFIED BY my_password DEFAULT TABLESPACE users QUOTA unlimited ON users;
GRANT CREATE SESSION TO my_user;
GRANT CREATE TABLE TO my_user;
GRANT CREATE VIEW TO my_user;
GRANT CREATE SEQUENCE TO my_user;
GRANT SELECT ON v_$parameter TO my_user;

Connect to the database as an administrator and run the following script to create a node user:

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

If you provide a custom schema name (different to the user name), then the last statement, setting the search_path, prevents querying the different default schema search path (default schema search path).

The following updates are required to a nodes filesystem configuration:

  • The Corda node configuration file node.conf needs to contain JDBC connection properties in the dataSourceProperties entry and other database properties (passed to nodes’ JPA persistence provider or schema creation/upgrade flag) in the database entry. For development convenience the properties are specified in the deployNodes Cordform task.
dataSourceProperties = {
   ...
   dataSourceClassName = <JDBC Data Source class name>
   dataSource.url = <JDBC database URL>
   dataSource.user = <Database user>
   dataSource.password = <Database password>
}
database = {
   schema = <Database schema name>
}

See Node configuration for a complete list of database specific properties, it contains more options useful in case of testing Corda with unsupported databases.

  • Run the run-migration-scripts sub-command to allow a Corda node to create database tables upon startup.
  • The Corda distribution does not include any JDBC drivers with the exception of the H2 driver. It is the responsibility of the node administrator or a developer to download the appropriate JDBC driver. Corda will search for valid JDBC drivers under the ./drivers subdirectory of the node base directory. Corda distributed via published artifacts (e.g. added as Gradle dependency) will also search for the paths specified by the jarDirs field of the node configuration. The jarDirs property is a list of paths, separated by commas and wrapped in single quotes e.g. jarDirs = [ '/lib/jdbc/driver' ].
  • Corda uses Hikari Pool for creating connection pools. To configure a connection pool, the following custom properties can be set in the dataSourceProperties section, e.g.:
dataSourceProperties = {
   ...
   maximumPoolSize = 10
   connectionTimeout = 50000
}

Configuration templates for each database vendor are shown below:

By default, nodes store their data in an H2 database. No database setup is needed. Optionally remote H2 access/port can be configured. See the documentation on Database access when running H2.

Node configuration for Azure SQL:

dataSourceProperties = {
    dataSourceClassName = "com.microsoft.sqlserver.jdbc.SQLServerDataSource"
    dataSource.url = "jdbc:sqlserver://<database_server>.database.windows.net:1433;databaseName=<my_database>;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30"
    dataSource.user = my_login
    dataSource.password = "my_password"
}
database = {
    schema = my_schema
}

Replace the placeholders <database_server> and <my_database> with appropriate values (<my_database> is a user database). The database.schema is the database schema name assigned to the user. You need administrative permissions in order to run the run-migration-scripts sub-command to initialise or migrate the database schema.

The Microsoft SQL JDBC driver can be downloaded from Microsoft Download Center, extract the archive and copy the single file mssql-jdbc-6.4.0.jre8.jar as the archive comes with two JARs. Common Configuration Steps paragraph explains the correct location for the driver JAR in the node installation structure.

Node configuration for SQL Server:

dataSourceProperties = {
    dataSourceClassName = "com.microsoft.sqlserver.jdbc.SQLServerDataSource"
    dataSource.url = "jdbc:sqlserver://<host>:<port>;databaseName=my_database"
    dataSource.user = my_login
    dataSource.password = "my_password"
}
database = {
    schema = my_schema
}

Replace the placeholders , with appropriate values, the default SQL Server port is 1433. By default the connection to the database is not SSL, for securing JDBC connection refer to Securing JDBC Driver Applications.

You can only run the run-migration-scripts sub-command to initialise or migrate the database schema when using administrative permissions. The database.schema is the database schema name assigned to the user.

Microsoft JDBC 6.2 driver can be downloaded from Microsoft Download Center, extract the archive and copy the single file mssql-jdbc-6.4.0.jre8.jar to the drivers folder, as the archive comes with two JARs.

Ensure JDBC connection properties match the SQL Server setup, especially when trying to reuse the Azure provided SQL JDBC URL which is invalid for SQL Server. This may lead to the node failing to start with the message: Caused by: org.hibernate.HibernateException: Access to DialectResolutionInfo cannot be null when ‘hibernate.dialect’ not set.

Node configuration for Oracle:

dataSourceProperties = {
    dataSourceClassName = "oracle.jdbc.pool.OracleDataSource"
    dataSource.url = "jdbc:oracle:thin:@<host>:<port>:<sid>"
    dataSource.user = my_user
    dataSource.password = "my_password"
}
database = {
    schema = my_user
}

Replace the placeholders , and with appropriate values, for a basic Oracle installation the default value is xe. If the user was created with administrative permissions the schema name database.schema will be the same as the user name (my_user).

When the database user has administrative permissions they can run the run-migration-scripts sub-command to initialise or migrate the database schema - this is not possible when using restricted permissions.

Copy the Oracle JDBC driver ojdbc6.jar for 11g RC2 or ojdbc8.jar for Oracle 12c to the node directory drivers.

Node configuration for PostgreSQL:

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

Replace the placeholders , and with appropriate values. The database.schema is the database schema name assigned to the user. The value of database.schema is automatically wrapped in double quotes to preserve case-sensitivity (e.g. AliceCorp becomes AliceCorp, without quotes PostgresSQL would treat the value as alicecorp), this behaviour differs from Corda Open Source where the value is not wrapped in double quotes.

You can only run the run-migration-scripts sub-command when using administrative permissions.

Copy the PostgreSQL JDBC Driver 42.2.8 version JDBC 4.2 to the node directory drivers.

For the node to create all database schema objects, you should run the run-migration-scripts sub-command.

Additionally, the node will create any tables for CorDapps containing Liquibase database migration scripts.

As the Corda node is configured to automatically run migrations on startup, no additional database update steps are required when upgrading Corda. See the Corda node upgrade notes for more information.

The procedure for Cordapp deployment is the same as for production systems apart from a simplified database update step. A CorDapp, which stores data in a custom table, should contain an embedded Liquibase database migration script. Liquibase is used by Corda for the database schema management.

To allow a Corda node to auto-update the database based on the content from a database migration script, ensure that:

  • the node can connect to the database with administrative permissions or runs with the default embedded H2 database.
  • you have administrative permissions and you run the run-migration-scripts sub-command with at least one of the following parameters: --core-schemas and --app-schemas.

Those requirements should already be set during the initial Corda node configuration.

You can optionally check if a CorDapp which is expected to store data in custom tables, is correctly built. To check the presence of script files inside migration directory, verify the content of the CorDapp JAR file with Java jar command, e.g. for Linux:

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

When an upgraded CorDapp contains a requires a database schema changes, the database is automatically updated during a node restart, see: database schema update for a new CorDapp.

When developing/testing CorDapps you may need cleanup the database between test runs (e.g. when running using the Gradle plugin Cordform deployNodes).

To remove node tables run the following SQL script against a user database:

DROP TABLE my_schema.DATABASECHANGELOG;
DROP TABLE my_schema.DATABASECHANGELOGLOCK;
DROP TABLE my_schema.NODE_ATTACHMENTS_SIGNERS;
DROP TABLE my_schema.NODE_ATTACHMENTS_CONTRACTS;
DROP TABLE my_schema.NODE_ATTACHMENTS;
DROP TABLE my_schema.NODE_CHECKPOINTS;
DROP TABLE my_schema.NODE_CHECKPOINT_BLOBS;
DROP TABLE my_schema.NODE_FLOW_RESULTS;
DROP TABLE my_schema.NODE_FLOW_EXCEPTIONS;
DROP TABLE my_schema.NODE_FLOW_METADATA;
DROP TABLE my_schema.NODE_TRANSACTIONS;
DROP TABLE my_schema.NODE_MESSAGE_IDS;
DROP TABLE my_schema.VAULT_STATES;
DROP TABLE my_schema.NODE_OUR_KEY_PAIRS;
DROP TABLE my_schema.NODE_SCHEDULED_STATES;
DROP TABLE my_schema.VAULT_FUNGIBLE_STATES_PARTS;
DROP TABLE my_schema.VAULT_LINEAR_STATES_PARTS;
DROP TABLE my_schema.VAULT_FUNGIBLE_STATES;
DROP TABLE my_schema.VAULT_LINEAR_STATES;
DROP TABLE my_schema.VAULT_TRANSACTION_NOTES;
DROP TABLE my_schema.NODE_LINK_NODEINFO_PARTY;
DROP TABLE my_schema.NODE_INFO_PARTY_CERT;
DROP TABLE my_schema.NODE_INFO_HOSTS;
DROP TABLE my_schema.NODE_INFOS;
DROP TABLE my_schema.CP_STATES;
DROP TABLE my_schema.NODE_CONTRACT_UPGRADES;
DROP TABLE my_schema.NODE_IDENTITIES;
DROP TABLE my_schema.NODE_NAMED_IDENTITIES;
DROP TABLE my_schema.NODE_NETWORK_PARAMETERS;
DROP TABLE my_schema.NODE_PROPERTIES;
DROP TABLE my_schema.CONTRACT_CASH_STATES;
DROP TABLE my_schema.NODE_MUTUAL_EXCLUSION;
DROP TABLE my_schema.PK_HASH_TO_EXT_ID_MAP;
DROP TABLE my_schema.STATE_PARTY;
DROP VIEW my_schema.V_PKEY_HASH_EX_ID_MAP;
DROP SEQUENCE my_schema.HIBERNATE_SEQUENCE;
DROP TABLE my_user.NODE_CORDAPP_METADATA;
DROP TABLE my_user.NODE_CORDAPP_SIGNERS;
DROP TABLE my_user.NODE_METERING_COMMANDS;
DROP TABLE my_user.NODE_METERING_CORDAPPS;
DROP TABLE my_user.NODE_METERING_DATA;
DROP TABLE my_user.NODE_RPC_AUDIT_DATA;

Additional tables for a Notary node:

DROP TABLE IF EXISTS my_schema.NODE_NOTARY_REQUEST_LOG;
DROP TABLE IF EXISTS my_schema.NODE_NOTARY_COMMITTED_STATES;
DROP TABLE IF EXISTS my_schema.NODE_NOTARY_COMMITTED_TXS;
DROP TABLE IF EXISTS my_schema.NODE_BFT_COMMITTED_STATES;
DROP TABLE IF EXISTS my_schema.NODE_BFT_COMMITTED_TXS;
DROP TABLE IF EXISTS my_schema.NODE_RAFT_COMMITTED_STATES;
DROP TABLE IF EXISTS my_schema.NODE_RAFT_COMMITTED_TXS;

Also delete CorDapp specific tables.

If you need to remove the schema and users, run the following script as a database administrator on a user database:

DROP SCHEMA my_schema;
DROP USER my_user;
DROP USER IF EXISTS my_admin_user;

To remove users’ logins, run the following script as a database administrator on the master database (skip the second statement if you haven’t created a my_admin_login login):

DROP LOGIN my_login;
DROP LOGIN my_admin_login;

To remove node tables run the following SQL script:

DROP TABLE my_user.DATABASECHANGELOG CASCADE CONSTRAINTS;
DROP TABLE my_user.DATABASECHANGELOGLOCK CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_ATTACHMENTS_SIGNERS CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_ATTACHMENTS_CONTRACTS CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_ATTACHMENTS CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_CHECKPOINTS CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_CHECKPOINT_BLOBS CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_FLOW_RESULTS CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_FLOW_EXCEPTIONS CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_FLOW_METADATA CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_TRANSACTIONS CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_MESSAGE_IDS CASCADE CONSTRAINTS;
DROP TABLE my_user.VAULT_STATES CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_OUR_KEY_PAIRS CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_SCHEDULED_STATES CASCADE CONSTRAINTS;
DROP TABLE my_user.VAULT_FUNGIBLE_STATES_PARTS CASCADE CONSTRAINTS;
DROP TABLE my_user.VAULT_LINEAR_STATES_PARTS CASCADE CONSTRAINTS;
DROP TABLE my_user.VAULT_FUNGIBLE_STATES CASCADE CONSTRAINTS;
DROP TABLE my_user.VAULT_LINEAR_STATES CASCADE CONSTRAINTS;
DROP TABLE my_user.VAULT_TRANSACTION_NOTES CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_LINK_NODEINFO_PARTY CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_INFO_PARTY_CERT CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_INFO_HOSTS CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_INFOS CASCADE CONSTRAINTS;
DROP TABLE my_user.CP_STATES CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_CONTRACT_UPGRADES CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_IDENTITIES CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_NAMED_IDENTITIES CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_NETWORK_PARAMETERS CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_PROPERTIES CASCADE CONSTRAINTS;
DROP TABLE my_user.CONTRACT_CASH_STATES CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_MUTUAL_EXCLUSION CASCADE CONSTRAINTS;
DROP TABLE my_user.PK_HASH_TO_EXT_ID_MAP;
DROP TABLE my_user.STATE_PARTY;
DROP VIEW my_user.V_PKEY_HASH_EX_ID_MAP;
DROP SEQUENCE my_user.HIBERNATE_SEQUENCE;

Additional tables for a Notary node:

DROP TABLE my_user.NODE_NOTARY_REQUEST_LOG CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_NOTARY_COMMITTED_STATES CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_NOTARY_COMMITTED_TXS CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_BFT_COMMITTED_STATES CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_BFT_COMMITTED_TXS CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_RAFT_COMMITTED_STATES CASCADE CONSTRAINTS;
DROP TABLE my_user.NODE_RAFT_COMMITTED_TXS CASCADE CONSTRAINTS;

Also delete CorDapps specific tables.

To remove node and CorDapp specific tables run the following SQL script:

DROP SCHEMA IF EXISTS "my_schema" CASCADE;
DROP OWNED BY "my_user";

Was this page helpful?

Thanks for your feedback!

Chat with us

Chat with us on our #docs channel on slack. You can also join a lot of other slack channels there and have access to 1-on-1 communication with members of the R3 team and the online community.

Propose documentation improvements directly

Help us to improve the docs by contributing directly. It's simple - just fork this repository and raise a PR of your own - R3's Technical Writers will review it and apply the relevant suggestions.

We're sorry this page wasn't helpful. Let us know how we can make it better!

Chat with us

Chat with us on our #docs channel on slack. You can also join a lot of other slack channels there and have access to 1-on-1 communication with members of the R3 team and the online community.

Create an issue

Create a new GitHub issue in this repository - submit technical feedback, draw attention to a potential documentation bug, or share ideas for improvement and general feedback.

Propose documentation improvements directly

Help us to improve the docs by contributing directly. It's simple - just fork this repository and raise a PR of your own - R3's Technical Writers will review it and apply the relevant suggestions.