JDBC driver

For connecting your favorite IDE, SQL Editor, or BI tool to Space and Time.

πŸ“˜

These instructions walk you through connecting to Space and Time via the open-source SQL editor DBeaver. The steps will vary slightly depending on your SQL editor, but the general concepts are the same.

Requirements

Setting up the JDBC Driver

Step 1: Add a new driver

Add the .jar file as a new driver in your SQL editor.

DBeaver instructions:

  • Open the "Database Menu"-->"Driver Manager"-->"New"
  • Select the "Libraries" tab and "Add File"
  • Navigate to the SxT JDBC driver .jar file linked in the Requirements section above
  • Once you have the .jar file selected, click on the "Find Class" button and select Driver Class io.spaceandtime.jdbc.JdbcDriver. If the Find Class button doesn't work, try saving the connection first, then returning and hitting Find Class again.
  • Hit "Ok" to save the new library and settings

Step 2: Configure the driver properties

🚧

JDBC works on password authentication, while Space and Time operates on more secure public/private key authentication. The JDBC configuration requires one non-standard properties to account for these differences.

Ensure that the following fields are populated:

DBeaver required driver properties:

  • Class Name: io.spaceandtime.jdbc.JdbcDriver
  • URL Template: jdbc:sxt:thin://{host}

Step 3: Create a new connection

DBeaver required connection properties:

  • Host - This will be the API base URL. Note: do not include http:// or https:// in this field.
  • Username - This will be the userId you registered with Space and Time. For easy registration, use the Space and Time CLI.
  • Password - This will be your privateKey generated during the registration process. Your private key will never leave your machine.
  • publicKey - This will be the public key corresponding to the above private key. Please note publicKey needs to be configured manually, as this field does not exist by default.

Step 4: Test your connection

Before continuing, test your connection to ensure all properties were entered correctly.

DBeaver instructions:

  • Select the "Test Connection" in the lower lefthand corner of the configuration screen

Accessing and permissions resources

  • (Optional) biscuit_<schema-name.table-name> - list of biscuits tied to permissioned tables (case-sensitive). For easy biscuit generation, use the Space and Time CLI.
    • Blockchain tables are publicly available to the network and do not require a biscuit
    • For private tables, currently, one biscuit is required per table
      (biscuit_<schema-name.table-name>)

πŸ“˜

The user's private key NEVER leaves the JDBC driver/user's computer. Rather, the private key is used to generate a cryptographic signature that allows Space and Time servers to validate key ownership without transmitting the key itself.

Biscuits

Space and Time use biscuits to manage the secure authorization between users and tables on a decentralized network. Establishing this relationship between the biscuit and the user does require one additional step for the JDBC driver.

Current requirements (JDBC)

Currently, Space and Time will require one biscuit per private table to be added to the JDBC properties. For example, if you wanted access to 3 private tables from the JDBC connection, "TableA", "TableB", and "TableC", you would need 3 new JDBC properties. Note that these properties are case-sensitive:

  • biscuit_Schema.TableA:
  • biscuit_Schema.TableB:
  • biscuit_Schema.TableC:

This guarantees correct authorization across a decentralized network.

Roadmap (JDBC)

Work is underway to make this process more dynamic for users, allowing the JDBC driver to be smarter when selecting a biscuit while enabling the biscuit scope to span beyond a single table. Check back periodically as we progress!

πŸ“˜

Blockchain data provided by Space and Time is public, meaning it is accessible by default and thus does not require biscuits to query. If you are only querying on-chain data, you can skip the biscuit setup process entirely.