Create a Key-Value Store Table

Create a Key-Value Store Table

Did you know that tables can be used to store arbitrary key/value data?

Tableland is all about tables, and relationships between tables. But did you know that relational databases make excellent key/value stores? Plus, when you store key/value data in a relational database, your keys become relational links to other content and data.

So let's say you want to store key/value pairs that map strings to JSON data. Tableland can help! This is as easy as creating a table with these two column types, and specifying that your key column (e.g., k) is a int primary key, and you value column (e.g., v is text):

CREATE TABLE key_values (
    k text,
    v text,
		primary key (k)
);

Recall that each table has a unique value — here, let’s say the table name that was created is key_values_80001_2561. With the Tableland create methods, you simply need to pass the schema and an optional prefix like so (with the SDK):

// Assumes `@tableland/sdk` has been imported
// Assumes a connection has been established & saved as `tableland`

const create = await tableland.create(`k text, v text, primary key (k)`, { prefix: `key_values` } );

or with the CLI:

tableland create "k text, v text, primary key (k)" "key_values"

Now, adding key/value pairs to the table is as easy as passing the following to the corresponding write function or command (and be sure to replace key_values_80001_2561 with your table name and the proper chain):

INSERT INTO key_values_80001_2561 VALUES ('key-or-uuid', '{"some_json_key": "some_value"}');

And this is exactly the type of repetitive query string that you can easily wrap in an app or library to make it super simple. Note that if you’re using something like the command line and the Tableland CLI, you’ll want to make sure you properly escape any double quotes in the JSON for the proper serialization:

table write "INSERT INTO key_values_80001_2561 VALUES ('key-or-uuid', '{\"some_json_key\": \"some_value\"}');"

Check out our SDK docs for details on creating, updating, and querying tables from JavaScript. Once you have the table created, putting and getting values from it is as easy as the following pseudo JavaScript code:

// Assumes connection established and that the table's `name`
// has been saved to a variable `name`, like `key_values_80001_2561`
const name = `key_values_80001_2561`

async function put(k, v) {
	const json = JSON.stringify(v)
	return await tableland.write(`INSERT INTO ${name} VALUES ('${k}', '${json}');`)
}

async function get(k) {
	const value = await tableland.read(`SELECT * FROM ${name} WHERE k = '${k}'`)
	return await value.rows[0]
}

And there you have it, key/value data in relational tables. Now imagine linking arbitrary JSON data to other structured data via its key, or storing encrypted blobs of data in a textserial key/value-style table? The sky is the limit now that you have Tableland at your fingertips.

Full SDK Example

Putting everything together, the following details how this could be designed in the SDK:

import { Wallet, providers } from "ethers";
import { connect } from "@tableland/sdk";

// Instantiate a wallet
const privateKey = "PRIVATE_KEY_STRING";
const wallet = new Wallet(privateKey);

// An RPC provider must be provided to establish a connection to the chain
// Here, we're using the `AlchemyProvider` that comes with `ethers`
const provider = new providers.AlchemyProvider("maticmum", "ALCHEMY_API_KEY");
const signer = wallet.connect(provider);
const tableland = await connect({
	signer,
	chain: "polygon-mumbai"
});

// Create a new table with a supplied SQL schema and optional `prefix`
// (Same logic as the client code in the section above)
const { name } = await tableland.create(
	`k text, v text, primary key (k)`,
	{ prefix: `key_value` }
)

// Helpers to insert/get the key-value pairs
async function put(k, v) {
	const json = JSON.stringify(v)
	return await tableland.write(`INSERT INTO ${name} VALUES ('${k}', '${json}');`)
}

async function get(k) {
	const value = await tableland.read(`SELECT * FROM ${name} WHERE k = '${k}'`)
	return await value.rows[0][1]
}

// Insert a key-value pair and also retrieve it
const writeKeyValue = await put("key-or-uuid", { some_json_key: "some_value" })
const getKeyValue = await get("key-or-uuid")