Web3 SQL Weekly #5: How to Identify All Bitcoin NFT Mints (Ordinal Inscriptions)
An easy guide to the Bitcoin NFT standard (Ordinal Inscription), which also shows how bitcoin scripting works.
Welcome back! I’ll be breaking down one (community submitted) query a week into byte-sized bits, making both the SQL and Blockchain concepts more digestible.
You can find all the weeks here, and submit/vote on future week queries here.
If you’re a complete beginner, you can reference this guide for any terms that are unfamiliar.
The video goes much more in the depth on explanations and examples, but you can also just read the written overview below:
What:
You’ve probably heard the words “Bitcoin NFT”, “Ordinals”, and “Inscriptions” all used interchangeably the last few weeks. This week I’ll explain what Bitcoin NFT mints are exactly, and how you can identify “mints”.
If you’re completely new to bitcoin, you should read my overview guide first. This query will be hard to follow if you don’t know what UTXOs are, what a satoshi is, and how Bitcoins are minted.
Blockchain concepts:
If you want the full history of how we got to the current state of Bitcoin NFTs, read this really well written summary. You can also dive into the data on Dune starting with this dashboard I’ve helped create.
So, let’s start by clearing up the terms NFT, ordinal, and inscription. Ordinal theory is a made up method of tracking sats (1e8 of a Bitcoin) starting from the block it was mined in. There’s a 20 line python script showing how to recreate this index, which basically does a FIFO method across every single input and output on every single transaction.
So this ordinal tracking gives us NFTs - but these tokens don’t have any content tied to them! That’s where inscriptions come in. Inscriptions are another made up pattern for attaching some content to an ordinal (a single sat). The below is the order of opcodes that must be combined/followed to be recognized on the ordinals explorer.
This standard will be referenced a lot in the query section!
You can inscribe an ordinal as many times as you want - there’s no way of removing them or changing them. It’s not some variable that’s stored, it’s a signature that’s saved in the witness data of a transaction.
I am counting each inscription as a mint. The ordinals or NFTs are technically just minted with each block, so that’s meaningless for us to track.
Here’s an example ordinal where sat number 1308063627208201 was inscribed, where they filled up the whole block (almost 4 MB).
If you’re from Ethereum land, remember that all of this is a socially agreed upon standard, versus some defined contract. There are pros and cons to each approach - I find this socially defined method a pretty interesting experiment!
SQL Query Walkthrough:
Here’s the query we’re going to create today. We’ll reference opcode scripts a lot.
We need to convert OP_FALSE OP_IF OP_PUSH "ord" OP_1 into hex code, which we can then filter for from bitcoin.transactions. By checking against the opcode scripts for the word-hex match, we can get OP_FALSE (0x00) OP_IF (0x63) OP_PUSHBYTES3 (0x03) "ord" (0x6f7264) OP_1 (0x01).
This gives us the hex string 0063036f726401. You can check 6f7264 is the hex form of “ord” with to_hex(to_utf8(‘ord’)) if you want. That means 3 bytes were pushed, which is OP_PUSH in this case is 0x03.
While this technically exists in input[1].witness_data, all data in a transaction is captured in the transaction hex column, so we’re going to filter on hex instead. We use a LIKE filter with %% wrapping our hex string to filter for only transactions where the inscription standard was used.
If you can check our hex string against the example inscription, you’ll find that the transaction is still returned - so our filter works! You can also ctrl+f to find it within witness data on the blockstream explorer.
Our next step is to pull out the content type, like "image/jpeg" in our example ordinal. To get the length of bytes pushed, we need the first byte after our initial inscription hex string (which represents between 1 and 75 bytes pushed using OP_PUSH).
Our hex string is currently 16 characters long, so we offset the starting point of the substring accordingly. We also append a ‘0x’ before converting to integer because the bytearray_to_integer function requires it.
This is pulling out the “0a” bit after the “0063036f72640101” in the hex column, and changing it from hex to decimal. “0a” translates to 10, which is the length of “image/jpeg” in bytes 696d6167652f6a706567. Remember, 2 characters go into 1 byte so our length is actually 20 characters long.
Next, let’s take the substring starting from the end of the “0a” bit to get the full content type in bytes (this means using +18 instead of +16). We also return the hex string in human readable form by using the from_utf8(from_hex(content_hex)) functions.
This converts 0a translates 696d6167652f6a706567 into “image/jpeg”! So we have exactly what we're looking for.
Now we can wrap it up by removing the filters for the example inscription, and aggregating across all transactions! I added a split(…,’/’)[1] to get just the first part of each content type (“image” in our example case). This makes the chart much more readable.
Voila! You’ll end up with the chart below:
Minting momentum has definitely slowed since the hype from two weeks ago, but there are sure to be plenty of interesting experiments to come out of this as they finish building a wallet and marketplace for ordinals.
Most Bitcoin analysis involves finding some script pattern and then filtering upon bytes of data pushed before/after to decode the data you want. Multisigs work the same way - I’ll cover them in a different week.
Bonus question:
Can you create a SQL query to track sats based on the 20 line python script? I haven’t had time to look into it deeply, but if you can then please contact me 😊
I will find some prize for you if you do.
Hope you learned something! Don’t forget you can submit/vote on future week queries here.
This is a great article and walkthrough. Got idea of ordinals finally.
Does work now... :-(