Blog
Blog

Crafting NFT Metadata with SQL

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

  1. Extract the right row given a tokenId.
  2. 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=1in 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}
Note that the statement uses table name aliases (t1 and t2) for readability. Aliases aren’t required.

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 ids 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.