# Strutured Data

# Overview

Parcel offers a secure and auditable relational database for users familiar with SQL (opens new window). In this tutorial, we will guide you through creating a database and table on Parcel, uploading data, and processing queries using the Parcel SDK.

# Creating a Database

To create a database, we simply call the Parcel's createDatabase() method. Let's say we want to collect ongoing security concerns on a blockchain network, we can:

const name = 'Blockchain Security Database';

const database = await parcel.createDatabase({ name });

console.log(`Created database ${database.id} with name: ${database.name}`);
Created database S5TqfEsXtwP4feyuMpDH9gy with name: Blockchain Security Database

After creating a database, the database owner can:

  • update the database name
  • transfer ownership of the database to another identity
  • perform common SQL operations on the database
  • delete the database

# Creating a Table

You can create a table by issuing a database query. Suppose we want to add a table of known concerning transactions on our network, we can flexibly store information in a standardized manner using JSON columns:

const create = {
  sql: 'CREATE TABLE threat_intels (wallet TEXT, intel JSON, time DATETIME, severity INTEGER)',
  params: {},
};
await parcel.queryDatabase(database.id, create);

# Uploading and Retrieving Data

Now that we have created a threat_intels table, we can rows of data by issuing the INSERT query:

let insert = {
  sql: 'INSERT INTO threat_intels VALUES ($wallet, $intel, $time, $severity)',
  params: {
    $wallet: '0x1234',
    $intel: { event: 'transfer', value: 9999 },
    $time: new Date(),
    $severity: 3,
  },
};
await parcel.queryDatabase(database.id, insert);

Note that the query contains placeholders starting with $ in place of query parameters. We list parameters separately as keys inside the params field when issuing this query. Declaring params consistently will help us keep our code organized and reusable. To prevent SQL injections (opens new window), Parcel will automatically escape the parameters' values.

You can view rows of stored data inside a table via a SELECT query. To get all stored threat_intels:

const select = {
  sql: 'SELECT * FROM threat_intels WHERE severity = $severity',
  params: {
    $severity: 3,
  },
};
const results = await parcel.queryDatabase(database.id, select);
console.log(`Result row ${JSON.stringify(results[0])}`);

# Granting Access

Similar to documents, you can share your Parcel database with other users, or identities, on the Oasis platform. This allows multiple identities to upload data and issue queries to the same database.

Suppose we want to grant full access to our database with an app from the Acme Inc. company, we can store the app's identity inside the ACME_APP_ID environment variable, and create a grant in the following way:

await parcel.createGrant({
  grantee: process.env.ACME_APP_ID as AppId,
  condition: {
    'database.id': {
      $eq: database.id,
    },
  },
});

To learn more about the conditions under which an identity can access you data in Parcel, refer to the grants language chapter and the advanced grants example.

# Supported Features

# SQL Language

We maintain support for normal CRUD (opens new window) operations and the creation of indices on Structured Data database tables.

# Datatypes

A database can support tables with the following datatypes:

  • Integer
  • Real
  • Text
  • Blob
  • Json
  • Date
  • DateTime

EXAMPLE

The full Node.js code that creates a database, table, and a grant is available in the Parcel Examples repository.