About
Protocol
Walkthroughs
Integrations
Intro to NFT Metadata
Tutorials
Smart Contracts
Concepts
Playbooks
Learn
Crafting NFT Metadata with SQL
An introduction to writing SQL that turns tables into JSON objects for NFT metadata.
Sample Data
For the following examples, we’ll use sample data stored on Tableland as follows:
id | name | color |
0 | crow | black |
1 | eagle | brown |
Each row in our sample data is meant to be a single NFT in our collection.
For fun, here’s how you could create this table with the CLI:
tableland create "id int, name text, color text" "tokenuri_table"
tableland write "INSERT INTO tokenuri_table_{id} (0, 'crow', 'black');"
tableland write "INSERT INTO tokenuri_table_{id} (1, 'eagle', 'brown');"
JSON Metadata from SQL
To serve NFT metadata from a row in a table, we need to write a SQL statement that will
- Extract the right row given a tokenId.
- Convert the row into standards compliant metadata json.
Extracting a single row as SQL
This part is straight-forward. You’ll want to query the sample table above with a filter on the id
column. As follows:
SELECT * FROM tokenuri_table_1 WHERE id={id}
Convert the row into a JSON object matching the metadata standard
To do this part, we’ll lean heavily on the SQLite JSON functions to convert our table row back into JSON format. We have three columns in our table, id
, name
, and color
. Let’s just create a JSON object with each value as a top level key. To do this, we’ll use the json_object method to build our result.
SELECT
json_object(
'id', id, 'name', name, 'color', color
)
FROM
tokenuri_table_1
WHERE
id = {id}
Great, now our object for id=1
will come back as follows:
{
id: 1,
name: 'eagle',
color: 'brown'
}
Nested data attributes
The above example uses a simple, flat format metadata object. If you need a nested attribute, we can just write our SQL a bit differently. Let’s use the same example, but now add color
as a nested property in an attributes
field. This is handy because platforms like OpenSea will be able to read these different attributes and generate filters in the user interface for people to explore.
SELECT
json_object(
'id', id,
'name', name,
'attributes', json_group_array(
json_object('display_type', 'text', 'trait_type', 'color', 'value', color)
)
)
FROM tokenuri_table_1 WHERE id={id}
Now, if you queried for our id=1
in the sample data above, the result would look like,
{
id: 1,
name: 'eagle',
attributes: [
{
display_type: 'text',
trait_type: 'color',
value: 'brown'
}
]
}
Notice that the attributes are each an object (in this case only one for color) and that they are in an array.
You can now plug this SQL into the same templating and quoting steps outlined above for the same result!
Two or more tables with SQL
Let’s say you’ve launched an NFT with two tables — a main table and an attributes table. For simplicity, the main table will hold nearly all of the top-level metadata about a token (schema: id INT, name TEXT, description TEXT, image TEXT
), and a separate attributes table holds what is typically defined in an array of trait objects (schema: id INT, trait_type TEXT, value TEXT
). See this refresher for the standard ERC-721 metadata format.
Here, we’ll need a SQL JOIN
to compose the data from the main and attributes table. Example tables have been created at table_nft_main_80001_37
for the main table, and table_nft_attributes_80001_38
for the attributes table.
SELECT
json_object(
'id',
t1.id,
'name',
t1.name,
'description',
t1.description,
'attributes',
json_group_array(
json_object(
'trait_type', t2.trait_type,
'value', t2.value
)
)
)
FROM
table_nft_main_80001_37 t1
JOIN table_nft_attributes_80001_38 t2
WHERE
t1.id = t2.id
AND t1.id = {id}
First, we build the object using json_object
to map the expected keys to values from the metadata standard. Note that because the main table and the attributes table both have a primary key of id
and are being joined using it, you must specify which table id
refers to in order to avoid clashing. The json_group_array
holds a json_object
that takes the data from the attributes table a the JOIN
. The WHERE
clause is specifying that the id
s from each table must be equal and that the id
itself should be equal to some passed parameter {id}
.
To see an example of what this looks like in practice, see the link here for an {id}
that equals 0
.
← Previous