Schemas and Tables

Welcome to this introduction to Pact Schemas and Tables!

Topics covered in this tutorial

The goal of this tutorial is to get you familiar with what schemas and tables are, why they’re useful, and to demonstrate how to implement these within a Pact smart contract.

Subscribe to our YouTube channel to access the latest Pact tutorials.

Tables in Pact are responsible for holding all of the data for application. Data is stored in a key-row structure similar to other relational databases. Tables are defined by schemas that define both the field values and field types.

Before completing this tutorial, it may be helpful to read through some of the existing materials on schemas, types, and tables. This material includes some of the prerequisites needed to understand the conceptual foundations behind what tables are and how they provide value to your Pact smart contracts.

Pact Language Reference

Before creating a table in Pact, you need to define its schema, which describes the structure of the table. Schemas specify the columns and data types that are meant to be in a table. Schemas are defined within Pact modules using defschema and consist of a series of field names and field types.

Each field name specifies a column in the table, and each field type specifies the type of data held in that table.

The example table below contains field names and field types. In this example, there are 3 fields; balance, amount, and ccy (currency), each with their own field type.

Accounts Table

You can create this schema in Pact using defschema as shown below.

All table schemas you create will look similar to this but will contain different field names and types. Field names can include whatever works best for your application, and fields types can include any of the types supported by Pact.

Tables hold data created by the smart contract.

These are defined within Pact modules and there is no limit to the number of tables you can define. This data can be added, read, or changed using functions, and access to this data can be restricted using what is known as table guards.

Tables are defined after the prerequisite schema is defined, using deftable followed by the table name and a reference to the table’s schema.

Accounts Example

Given the schema from the earlier defined schema, you can define the table as shown below.

The schema name and table names must be different from one another. It’s best practice to make names that resemble each other but are not identical.

Keeping names similar helps you track your schemas and tables more effectively while avoiding confusion between their names.

Once tables are defined, they still need to be created.

Though tables are defined within the module, creating tables in Pact is done outside of the module. This is done to ensure that the module may be redefined or updated later without having to re-create the table.

The relationship of modules to tables is important, as described in table guards

Tables are created outside of the module using create-table followed by the table name as defined within the module.

This syntax for creating tables is simple. The main concern here is to make sure that you follow through with creating each of the tables that are defined within the module.

When working with tables, there are many built-in functions available for you to work with table data.

Notice that these functions are similar to common options available in other databases (CRUD - create, read, update, delete).

Insert functions are used to add new data into a table. These are useful when creating new artifacts such as entities, loans, accounts, and in any other case where you may want to add data.

For an example of an insert function, pretend you have the following empty table.

entity-table

To add a row to this table with an entityName of “My Entity” at the key of entity-1, you would write the following Pact code.

You can also place insert within a function and using the inputs to add new data to rows within a table. This will help you add rows more dynamically from within your application.

Here is an example using insert from within a function.

In each of the previous cases, the final result would insert the data similar to the following into the entity-table.

Read allows you to read rows from a table for a given key.

For this example, imagine you had the following table and you wanted to read the balance and currency at the key of entity-1.

Accounts Table

Using read, you can specify the id and you’ll get back the information you request.

Here is an example of how to get similar functionality by using read from within a function.

In each of the cases shown above, the functions would return the following values.

Update functions can be used to update a values in an existing row of a table. Updating is helpful in situations where you need to change the status of a column or amend the initial dataset to a new value.

Using update, you can specify the row id to update a value within that row. This value would generally be passed in by the user as a function parameter.

For example, pretend you had the following table and wanted to update the assetPrice.

Assets Table

The amend-assetPrice function below updates the assetId column of an assets-table. It then reads the value of the updated column.

This same pattern can be used in many different ways.

For example, you may be creating an asset and want to track its progress throughout the creation process with fields like todo, in progress, or done. In this case, you could use the function below asset-update, to change the status of the asset as it progresses through the process.

Select is used to read values from a table.

This is similar to read but select includes more specificity allowing you greater flexibility in what information you choose to select. The syntax for selecting from tables closely resembles SQL statements.

The simplest select statement you can create would be to select all values of an existing table.

Similar to other built-in functions, you can run this from within another function.

For example, here is the assets-table from earlier.

Assets Table

You can run the following select statement to return all values from this table.

This query will return the following values from the assets-table.

Along with select, you can also use a

wherestatement to further refine your query as shown below.

This query would return the following values from the assets-table.

You can also specify operators such as greater than or less than from within the WHERE clause as shown below.

This query would return the following values from the assets-table.

Keys allows you to return all the key values in a table.

Given the previously shown assets-table, you could return each of the keys using the code below.

This can also be done within a function as shown below.

That wraps up this tutorial on schemas and tables with Pact.

By completing this tutorial, you’ve mastered many of the core ideas surrounding schemas and tables. You’ve also seen some of the basic functions that are useful for manipulating data from within a table.

You can try building out your own database from scratch or by using existing database examples to work from. An excellent resource for finding existing schemas for well designed databases can be found

here. If you’re unfamiliar with database design, you can review most of what you need to know here. That website goes into a lots of detail, so if you’d like to focus more on specifically what might be useful to you right now, you can find that here.

Try finding a database you’re interested in to practice recreating some of its functionality. And if you’re not quite ready to try that, you can check out the next tutorial instead. You’ll see many more examples of tables, schemas, and their related functions throughout the rest of this series.