Collections

Collections are similar to a table in a traditional database. You must create a collection before adding data to Polybase DB.

Collections (aka database tables) define the fields and rules for a collection of records. All records created by a collection are guaranteed to follow the rules of that collection. This is in contrast to other smart contract languages, where each smart contract is equivalent to a single record.

Each Polybase DB record within a collection has its own unique identifier id which must be of type string.

You can view our example app Polybase DB Social (opens in a new tab) to see it working in action.

Create a collection

Create collection using the Explorer (recommended)

Navigate to the Explorer (opens in a new tab), and click on "Create Collection". You will then be prompted to sign in.

If you create a collection in the explorer, the collection will be owned by the account you used to sign in.

Create collection programmatically

To create a schema programatically, you can use the applySchema method on your Polybase DB client instance.

In this example, we create a collection called City that has name and country fields, and a setCountry() function. You can define multiple collections in a single applySchema call.

import { Polybase } from "@polybase/client"
 
const db = new Polybase({ defaultNamespace: "your-namespace" })
 
await db.applySchema(`
  @public
  collection City {
    id: string;
    name: string;
    country?: string;
 
    constructor (id: string, name: string) {
      this.id = id;
      this.name = name;
    }
 
    setCountry (country: string) {
      this.country = country;
    }
  }
 
  @public
  collection Country {
    id: string;
    name: string;
 
    constructor (id: string, name: string) {
      this.id = id;
      this.name = name;
    }
  }
`,
  "your-namespace"
); // your-namespace is optional if you have defined a default namespace

import { Polybase } from "@polybase/client"
 
const db = new Polybase({ defaultNamespace: "your-namespace" })
 
// If you want to edit the contract code in the future,
// you must sign the request when calling applySchema for the first time
db.signer((data) => {
  return {
    h: 'eth-personal-sign',
    sig: ethPersonalSign(wallet.privateKey()), data)
  }
})
 
const createResponse = await db.applySchema(`
  @public
  collection CollectionName {
    id: string;
    country?: string;
    publicKey?: PublicKey;
 
    constructor (id: string, country: string) {
      this.id = id;
      this.country = country;
 
      // Assign the public key of the user making the request to this record
      if (ctx.publicKey)
        this.publicKey = ctx.publicKey;
    }
  }
`, 'your-namespace') // your-namespace is optional if you have defined a default namespace

Define a Collection

Collections are defined using Polylang (the language for Polybase DB), that is very similar to Javascript/TypeScript. Collections allow you define the rules and indexes for your collection records. The following is a valid collection definition.

@public
collection CollectionName {
  // id is mandatory on all collections and must be unique
  id: string;
  // name is defined as required
  name: string;
  // age is defined as optional
  age?: number;
 
  constructor (id: string, name: string, age?: number) {
    this.id = id;
    this.name = name;
    this.age = age;
  }
 
  // Only required for multi-field indexes, as each field is
  // automatically indexed
  @index(name, age);
}

The above would allow you to insert/create a record with a name of type string and optional age property of type number.

Constructor

The collection constructor function lets you create new records in your collection, and is called when you create a record. The constructor function must assign this.id and any additional required fields defined in your collection. An error will be returned if a record already exists with the assigned id is provided, as each record in a collection must have a unique id.

Fields

You can specify the fields that are allowed in your collection. These should be at the top most part of your collection. A collection should always have a id field (id is unique). By default, all fields are required.

@public
collection CollectionName {
  id: string;
  age: number;
 
  ...
}

id Field

The id fields is required on every Polybase DB collection. It must be assigned in the constructor. Each record must have a unique id across the entire collection. id must be of type string.

Nested Fields

You can also define nested fields:

@public
collection User {
  id: string;
  name: string;
  address: {
    street: string;
    city: string;
    state: string;
    zip: number;
  };
}

Optional Fields

All fields are required by default, if you want to make a field optional simply append ? after the field name and before the :. For example:

@public
collection CollectionName {
  id: string;
  required: number;
  optional?: number;
 
  ...
}

You can also pass in optional values to function parameters, for example:

@public
collection CollectionName {
  id: string;
  required: number;
  optional?: number;
 
  constructor (id: string, required: number, optional?: number) {
    this.id = id;
    this.required = required;
    this.optional = optional;
  }
}

Field Types

The following types are supported:

• string
• number
• boolean
• bytes
• PublicKey
• Collection
• string[], number[], boolean[], PublicKey[] and Collection*[]
• map<string | number, T>

Where Collection* is the name of a specific collection in the same namespace.

Here are some examples of fields being defined:

@public
collection User {
  id: string;
  age: number;
  alive: boolean;
  icon: bytes;
  publicKey: PublicKey;
  bestFriend: User;
  friends: User[];
  currency: map<string, number>;
}

Primitives

The following primitive types are supported:

• string
• number
• boolean

Bytes

The bytes type allows you to store arbitary bytes in Polybase DB, without having to encode the data as a string.

If you use our client library @polybase/client (opens in a new tab), the bytes type will be returned as a Uint8Array.

Collection

Collection fields reference another collection in the same namespace. This allows you to create relationships between records in the same or different collections. Similar to how you have relationships in SQL.

You can define the field on the collection, and then also pass a parameter as a Collection type.

@public 
collection Pet {
  id: string;
 
  // This field is referencing the User collection below
  owner: User;
 
  constructor (id: string, owner: User) {
    this.id = id;
    this.owner = owner;
  }
}
 
@public
collection User {
  id: string;
  ...
}

You can then call the constructor using create() passing in the reference to a specific record of that collection:

import { Polybase } from "@polybase/client";
 
const db = new Polybase({
  defaultNamespace: "your-namespace",
});
 
db.collection('Pet').create(
  [
    "pet1", 
    // This is referencing the User collection
    db.collection('User').record("user1")
  ]
);

Public Key

The PublicKey type is a special type that is used to represent a public key. Currently, we only support Ethereum (secp256k1) public keys.

.toHex()

If you need a string hex representation of the public key, you can use this.publicKey.toHex(). This will return a string starting with 0x, and the hexidecimal representation of the 64 byte public key.

Arrays

You can create arrays of string, number, boolean, PublicKey and Collection type. For example:

@public
collection User {
  id: string;
  codes: string[];
  ages: number[];
  attempts: boolean[];
  friends: User[];
  keys: PublicKey[];
}

You can initialize an array with a default value, as you would normally do in JavaScript, and push items on to the array.

@public
collection User {
  id: string;
  codes: string[];
 
  constructor () {
    this.codes = [];
  }
 
  addCode (code: string) {
    this.codes.push(code);
  }
}

Maps

You can create map, these allow you to create key value pairs. For example:

@public
collection User {
  id: string;
  currency: map<string, number>
 
  constructor () {
    this.currency = {};
  }
 
  setUSD (val: number) {
    this.codes["USD"] = val;
  }
}

Functions

Field values can only be modified using functions.

@public
collection Account {
  id: string;
  name: string;
  balance: number;
  publicKey: PublicKey;
 
  constructor (name: string) {
    this.id = ctx.publicKey.toHex();
    this.name = name;
    this.publicKey = ctx.publicKey;
    this.balance = 0;
  }
 
  // Fn ignores all above rules, so anything needed must be reimplemented
  transfer (b: Account, amount: number) {
    //ctx is in global scope of fn
    // error() is in global scope of fn
    if (this.publicKey == ctx.publicKey) {
       throw error('invalid user');
    }
 
    // can edit both records
    this.balance -= amount
    b.balance += amount
 
    // min has to be reimplemented/declared b/c field @ rules do not apply
    // inside fns
    if (a.balance < 0) {
      throw error('insufficient balance');
    }
  }
}

You can call your custom functions using .call(functionName, args):

const db = new Polybase({ defaultNamespace: "your-namespace" });
const col = db.collection("account");
await col.record("id1").call("transfer", [c.record("id2"), 10]);

Context

The global variable ctx is available inside any Polybase DB function.

Public Key

ctx.publicKey is populated when you provide a signer function to the Polybase DB client.

import { Polybase } from '@polybase/client'
import { ethPersonalSign } from '@polybase/eth'
 
const db = new Polybase({
  signer: (data) => {
    return {
      h: 'eth-personal-sign',
      sig: ethPersonalSign(wallet.privateKey()), data)
    }
  })
})

You can then use the publicKey of the wallet used to sign the request in your collection code, for example see how in setName we check the ctx.publicKey and compare it against the records own publicKey:

collection CollectionName {
  id: string;
  name?: string;
  publicKey?: PublicKey;
 
  constructor (id: string) {
    this.id = id;
    if (ctx.publicKey)
      this.publicKey = ctx.publicKey;
  }
 
  setName (name: string) {
    if (this.publicKey != ctx.publicKey) {
      error("you do not have permission to do that");
    }
    this.name = name;
  }
}

.toHex()

If you need a string hex representation of the public key, you can use ctx.publicKey.toHex(). This will return a string starting with 0x, and the hexidecimal representation of the 64 byte public key.

Indexes

Indexes are a list of fields in addition to the record's id field that should be indexed. You need to ensure that all fields that are included in a where or sort clause are included in the indexes.

All collection fields are automatically indexed, which means you only need to specify multi-field indexes.

const db = new Polybase({ defaultNamespace: "your-namespace" });
const collectionReference = db.collection("cities");
const records = await collectionReference
  .where("name", "==", "abc")
  .where("country", "==", "UK")
  .get();

You would need the following schema:

@public
collection cities {
  name: string;
  country: string;
 
  @index(name, country);
 
  ...
}

If you want to order your results, you also need to include that in the index:

const db = new Polybase({ defaultNamespace: "your-namespace" });
const collectionReference = db.collection("cities");
const records = await collectionReference
  .where("name", "==", "abc")
  .sort("population", "desc")
  .get();

You would need the following schema:

@public
collection cities {
  name: string;
  country: string;
  population: number;
 
  @index(name, [population, desc]);
}

Permissions

By default, collections in Polybase DB are private and their records cannot be accessed. To make a collection public, add the @public directive.

To allow users to access and manipulate their records, you can use the following directives:

@public on collections

Allows anyone to read all records and call functions in the collection (it's the equivalent of adding @read and @call). You can still further restrict write permissions by adding custom code to your collections functions.

@public
collection Response {
  ...
}

@read on collections

Allows anyone to read all records in the collection (but calls to functions are still restricted).

@read
collection Response {
  ...
}

@read on fields

Allows anyone who can sign using the specified public key to read the record.

collection Response {
  @read
  publicKey: PublicKey;
  ...
}

@call on collections

Allows anyone to call functions that do not have a @call directive.

@call
collection Form {
  ...
 
  updateTitle (title: string) { ... }
}

@call on functions

Allows anyone who can sign using the specified public key to call a given function.

collection Form {
  @read
  creator: PublicKey;
 
  @call(creator);
  update ()  {
    ...
  }
}

@delegate

Delegation allows you to create rules across multiple records, allowing for complex permissions to be defined.

A Polybase collection is just like an Ethereum contract, but instead of being for a single record, collections describes the rules for a set of records.

You can annotate your collections with @read, @call and @delegate directives to control who can read, call and delegate data.

Example of delegating read access to Response to a user with given publicKey:

Response → Form → User → publicKey:

collection Response {
  // Delegate read permission to form/Form
  @read
  form: Form;
}
 
collection Form {
  // Delegate read permission to User
  @delegate
  creator: User;
}
 
collection User {
  // Delegate read permission to publicKey
  @delegate
  publicKey: PublicKey;
}

For more on delegating permissions, see Delegating Permissions.

Reference a collection

To reference a collection, you can call .collection("collection-name") on your Polybase DB instance. The returned collection instance relates to a specific collection of data and allows you .write() and .read() data from Polybase DB.

Relative Path (with default namespace)

The following would return a collection with path: your-namespace/cities:

const db = new Polybase({ defaultNamespace: "your-namespace" });
const collection = db.collection("cities");

Relative Path (without default namespace)

The following would return a collection with path: your-namespace/cities:

const db = new Polybase();
const collection = db.collection("your-namespace/cities");

Absolute Path

To override the default namespace, you can use an absolute path by specifying a / at the start of the path.

The following would return a collection with path: alt-namespace/cities:

const db = new Polybase({ defaultNamespace: "your-namespace" });
const collection = db.collection("/alt-namespace/cities");