Tableland 🛠 Composable data powered by smart contracts + SQL

[Applause] [Music] super excited to have dan here um from the table end team um table land is building really one of the most useful primitives you could probably have in your hack uh if you want everyone ever want to use a database in it um so dan's going to walk you through kind of how to get up and running with it but uh but yeah i mean really psyched to have tableland as a sponsor for i think including textile the third or third hack fs in a row so really thankful to have them be a part of these events over the last couple years um and uh yeah i mean really excited to see the kind of next development of what they've been working on with tableland so maybe i'll just turn it over to dan right away and then if you want to get started um we'd love to hear awesome thank you so appreciate everyone joining we're going to walk through a couple things here today so we're going to start off with just some basic overview of table and breeze through those real quick a couple slides and then we're going to touch on just a very basic um example of minting an nft with metadata on tableland so if you're familiar with like ipfs we're going to walk through some of the benefits of using using tableland in a scenario like that and then we're also going to end with what we're calling more like a sql-powered nft so making things dynamic and do actually a live demonstration of that here today so excited to kick things off so what is table land so table end is a network for relational data making things naturally mutable dynamic composable and really allowing sort of mutable data to live with immutable rules so we're gonna let's just dive into what that means because that's obviously you know a mouthful so just talking about the current state of things where are people storing data so it might be in smart contracts might be in private or centralized databases it might be in decentralized file storage solutions like um in these examples you could say you know storing a smart contract maybe that's on ethereum maybe it's on optimism polygon etc but there's limited things that you can do with that because of just the complexity of you know composability with that the cost implications depending on the chain that you're minting on if we look at a private database for example aws or google cloud there are a lot of limitations with that if you're storing something like nft metadata on those solutions because it kind of takes away the whole point of decentralization and enabling composability in a straightforward fashion so not the best solutions available in smart contracts not the best solutions available for storing things like metadata in private databases and then even decentralized storage like if you look at how nft metadata is stored today a lot of it is on things like ipfs which is you know files coin ipf has great solutions for file storage but a byproduct of that persistence is this immutable cid so if you're trying to make something like metadata dynamic like how do you do that it gets really complex uh if you're trying to do that with something like a file storage solution so that's where table lands come into play really augments something like ipfs or filecoin and allows you to actually create tables insert data into those tables all using just native sql so you can sort of touched on that a moment ago but you can sort of think of table end basically turning any blockchain into an application database so we take that that if you just you know use a sql lite database as an example traditional web 2 you're going to have acls and actual tables that exist within that and we're just saying hey let's break that apart relational tables live in table and the acls live on chain using smart contracts and actual like accounts to basically dictate who can write to a table who can do perform serpent actions on the table as well and then just generally speaking all data is open and readable to enable that true composability across even different chains so you can have cross-chain queries going on with just native sql so as far as the stack you'll see this throughout the demo the couple demos that we'll we'll talk through but um there are a few different ways to think about tableland so we have smart contracts on chain which are basically this registry contract that's where every table is actually minted as an erc 721 so every table that you create is actually an nft but then off chain what's happening is that um table end is a network of validators that are listening for these events and then basically what happens is that we run the sql that those events are telling us to do so if someone tries to create a table someone tries to mutate a table we're listening for those events making sure the proper access control is also met with a separate smart contract and then within our network a validator is performing and processing that sql as a whole so it's really powerful um and i think the the final example that we'll walk through with like actual smart contracts creating tables and running sql um is pretty interesting so on the left-hand side this is what we'll start with just some basic usage of tableland and our javascript sdk so importing connecting to table and creating a table actually inserting data what that looks like we're going to do that in hard hat and then on the right hand side go into some detail with actually using sql on the blockchain and that will end in a demo that actually you can even test out yourself on something like gurley so just kicking things off simple nfc with metadata the way we're going to do this is a mint a table a single table so if you're used to that traditional ipfs workflow you have all your metadata like name description attributes etc we're going to mint that in a table on tableland but as a best practice we actually recommend even like separating something like this so if you have like an attributes table and a metadata table you can join those together and it really just shows like just a simple example of how powerful that that sql-based um you know relational data metadata can be but just for simplicity's sake we'll just start off with that single metadata table now as far as um contract setup or project setup we can kind of skip over this aspect and dive into the structure so we're going to have some local assets some images that are just going to be deployed to ipfs we have some metadata files that you don't know what the image is yet so we're going to write those to the metadata data files and actually take those files so like your classic um erc7021 721 metadata we're going to take that and then import it into a sql statement essentially and then write that to the blockchain upon deploying our smart contract so let's dive into things cool so if we look at just our general product project structure again you can see that we're starting here with just a general erc2721 so we have some basic code set up we are going to share these repositories after the call so just a heads up if you want to see what exactly i'm doing here not only this but the the final uh smart contracts and gain that we're going to show will also be shared so just walking through the code okay we have a basic nft we've all done this we have our public base uri we have our table name which we'll come back to we have our token ids that we're incrementing as we mint a table and then again just some really basic erc721 so the goal of this first demonstration is really just to show you okay you have a basic nft we've all done this how do you take that typical use case and put it onto tableland so in doing so there is going to be one interesting aspect that will come to at the end around actually querying or placing like a a sql squery a sql query on the end of your token uri so we'll hit on that in a second as well but to also show you some of the data that we'll be using in this upload in this minting process we have our assets so a couple of cool rigs nfts which is a huge component of the table and ecosystem so if you haven't checked those out head over to our our website tableland.xyz but then we also have some metadata which is going to be initialized with an empty string here in the image actual field so what we're going to be doing is start off with just uploading data to ipfs we're going to sort of skip over this because there's a lot of different ways to do this but the general thing that you should understand is that we have a few different scripts here we have upload metadata to ipfs we have prepare sql and then we have deploy which is where we're actually going to connect to tableland and create and insert this metadata in here we're basically just reading files parsing it converting it to json and then we're going to use that in this prepare sql statement so we're loading files uploading those images to ipfs parsing the metadata returning it so then and also preparing it so then table end in this repair sql script has all the data that we need all the json um for our nfts and then we're going to use that to prepare to prepare an sql statement so now let's start talking a little bit more about tableland so if we look at this example if you're familiar with sql there are common sql statements that people are going to be using and like any time that you're going to be using table end there's really three or four steps that you're going to take it's one let's connect to tableland we have that all built into our javascript sdk to make it super simple two you're going to create a table and then three you're going to insert metadata into that table and the way that you do it depending on how you want to set up your scripts and how you want to do everything it's going to differ based on your use case we're going through the most simplistic use case here so just keep that in mind but as part of that deploy script and we're going to take a step back and walk through that connect and create workflow but as part of that deploy script we're going to be inserting metadata and here just gives you an example of what that metadata might look like so you'll see again if you know if you know sql you know save a land essentially so you have insert into name we'll come back to this this is going to be the table name based on the tableland smartcon registry smart contract which is going to be minting tables you also have this schema that you see here which will match our create table schema that we'll show you in a minute and then you actually have the values here that are getting inserted into the table so that came from that initial step i walk through where data is uploaded to ipfs and actually inserted return as json and insert it into this sql insert statement okay cool so prepare sql for one table just we have a one table example but as noted a best practice is actually creating two tables where instead of having something like id name description attributes you would actually only include this first subset and then have some join with an attributes table so again i've said this a lot but if you know sql you know tableland so diving into the deploy script some helper functions up at the top but focusing in on what tableland offers you start with that connect so you're going to import this connect statement from earth this connect function from the table and sdk so you start there import it up at the top and note that in terms of installing it's you know we're on npm you can install with yarn as well so npm installed table and sdk and you're good to go and then simply just import it as you see here this is you know it's common.js if you have if you're using es6 or above or something like that you can also just do a standard import um here i'm using a little bit different setup there so just the rationale behind that now diving into our main function a few different things that we've set up so we have um just some some classic hard hat if you're familiar with hard hats some classic hard hat setup where we have a signer for who's going to be actually deploying this table but then when we look at the next step okay now let's get into table end so first up let's connect to the table on network connect to in the background i have some providers set up so making sure that we have connection established with the actual blockchains here i'm going to be deploying something on polygon so it starts with this connect step next you'll see the schema so we've actually made it really simple to create a table in tableland it's like send basically give us the schema give us an optional prefix which is going to let you have like a human readable name on your table itself and then you simply just pass this table and create function so connect create pass the schema pass the optional prefix and then what you're going to get back is a unique name that's actually unique not just only to the chain but across anything in tableland so every unique tape every table is unique so if you want to have some cross chain query for example you can do that all with sql joins and just passing the proper information and we'll show you what one of those sql statements look like here in a second um okay cool so we're going to connect we're going to create we're going to verify the table is created just a helper function to make sure that when you call this table create function what's actually happening is that on our registry smart contract we're creating a table which is submitting an event and then the table and network of validators is listening for that event and then processing the sql so we verify everything has been created once we're good to go there we call that prepare sql statement that we showed there a minute ago and then what that's going to do is actually as a predeploy script for the the smart contract insert the metadata itself into these tables using just the the standard sql insert statement so that is in particular using this tableland rights so you can call table and create you can call table and write if you're actually trying to create read something you can call table and read so just select all from table etc and then lastly is this base uri so if we just take a step back okay what have we done we connected we created we wrote metadata into a table and then here we have our base uri which has this query s at the end so this is actually allows you to have just a pure select all from table select these particular columns from table wear id etc so you can append just a strictly sql query onto this base uri which is pointing to the tableland network and as a response what this will give you is as an example like your json metadata for an nft these last few steps are just classic deploying smart contracts a blockchain so we're getting that tableland nft um smart contract that we walk through we're actually deploying it with this base uri and the name of the table that is unique to each and every table across any networking table end cool so let's uh let's run it and see what happens you can see i have hard hats set up there's some example scripts that you can see if you check this out later hard hat run script deploy on mumbai there were some issues today with mumbai so we'll see how it's working um tbd so let's just uh walk through this deploy script um and as that's going so what we're gonna walk through next is actually a sql powered nft and it's a pretty cool use case as i'll show you a slide there in a second but i think what's cool here is just to actually show you what's happening in the script so you can see that we have these sql insert statements that are occurring we have some hashes that are returned from the actual blockchain that says hey this is the transaction hash of the actual write statement and then we can actually even go to the source we can see the the actual contract and we have that select statement that is embedded into our token uri query this is going to be pretty important for just you know allowing you to append those strictly select all from um you know type of statements and note that we have some helpers to get rid of some of this encoding formatting but this allows you to mint a table allows you to um call that uh if you look at up in the url bar you can see the select statement so there's a lot of different ways that you can actually just write a query um in this section so instead of time let's drop over to the last part we've got about 10 minutes left here so let's create a sql powered nft game this is a pretty cool idea so really what we're proposing is like okay let's let's kind of change the the flip the script with nfts and allow mutability and dynamism to happen um in this like on-chain capacity so we're going to walk through creating a sql-powered nft picture it has like a canvas game where you have different pixels and every owner of a pixel is actually an owner and a nft so you can mint an nft it's going to mint back this pixel initialized at a 0 0 address i'll show you what this looks like in a second but then you can actually write sql to the blockchain update the metadata so in real time you can see what's happening across all users playing this game so you can have like a global state of a metadata table and then individual users are minting their own pixel which are inserted into the table as their own nft so that's what i just talked through um right there's the sort of an overview of step one and then step two is what we're gonna release at a different point don't have enough time to really walk through it in detail but basically adding an interface layer on top of this smart contract layer that i'm going to walk through and then just to give you sort of an architecture overview this is really what's happening so on the right hand side you can see some of the game rules that we have to give you give you some additional context you look at the architecture diagram that was describing that two-step approach so we have potentially like this nft html front end it's going to be calling a smart contract which is going to be updating the metadata within the tableland network and that can all be read and written um continuously just with pure sql so now diving into the what we call this the canvas game i'm gonna walk through some some cool things here so um classic nft we have that base uri string from the previous example if we look at the constructor method so when we're actually deploying this smart contract we're going to start with creating a table and this is calling the table and registry smart contract and basically this table is going to be holding the global game state and it's actually minted as an nft on chain so you can see the exact you can actually have this like global state of um all these players moves as they're inserting data into the table and it's actually an nfc itself create table schema okay good to go when you deploy a contract you're creating a table it's it's all happening on the blockchain it's not like that last example it's a pre-deploy step you're actually creating a table upon constructing um upon calling this constructor function or upon you know deploying your your nft the next step is actually statementing or minting your nft as well and this is another super cool feature so okay last step we did everything as a predeploy script here we're going to actually do it in real time and and every time a user mints a table it's calling this run sql function so that was actually that tableland.write method in the pure javascript sdk and what this is doing is inserting um inserting data into our global state and it's minting an nft in the process so inserting the data into that global nft state um we're going to have an id we're going to have an external link to some potential image and then we have these xy coordinates which are initialized to zero and then lastly make moves so if i want to overwrite and update mutate my current position so instead of just initializing and staying at zero zero forever which is what happens if you use something like um persisted file storage uh in a decentralized capacity like if you deploy this metadata file on ipfs okay how do you actually overwrite and change these zero zeros these x y coordinates it's pretty complicated and it's pretty complex and it if you've ever tried to do this just try out tableland because it's a way easier way to do everything like just just try it out it's awesome but okay so if we go to make move what exactly is happening well it allows players to insert and update their actual position uh within the game itself so you can you see this uh this sql statement here we have an update set and where so based on the owner of that nft there's proper sort of another thing to keep in mind is that we also have what we call a controller smart contract for acls making sure only the owner of that nft can write and call this make move function but basically what it's going to do is update the metadata so that the x and y coordinates reflect what that owner wants upon writing um if i'm calling that right so cool let's uh let's dive into a live demo of what this looks like so just uh to keep things i'll share this in the chat um with anyone that is curious check out gurley it's live right now um but basically what happens is that and i just did this real quick um to make sure that it was straightforward to you know to demonstrate but um ahead of time i had minted a an nft and we can even do this again if we'd like but simply copy and paste your wallet address here we're going to call mint or right which is going to call safe mint we're going to confirm the transaction and then we can view our transaction and while that's loading let's look at one that i already invented so you can see that i'm the owner of token id number two if we go back to the canvas game let's actually change let's actually change the metadata let's make this dynamic immutable so tokenite id2 it was initialized when i minced it to a 0-0 address as shown in the smart contract but let's change it up let's say i want to go to number two two so let's write that to the smart contract let's write that and update some metadata cool and just for demonstration purposes we'll show you what that initial state looks like so i also have token number four so i'm a multi multi-pixel nft owner awesome so i have some trouble okay cool so up at the top you'll see that sql statement you'll see the metadata returned at that location and let's insert right here this is the one that i just minted so zero zero address and then if we go back and recall that i had also been the owner of token id number two and then i had just said make move let's overwrite the metadata let's make it mutable let's see what that looks like cool xy is now at a different location let's even do it again we actually we have a minute here so let's make this five five five so it goes from two two to zero zero to two two to five five right confirm and it looks like i closed out mine four and well that's loading um we're running close up here on time so i know we breezed through a lot we talked about what is table and what can you do with it how do you make uh change really everything from being this static state of metadata to making nfts live and empowering them with sql using joins mutates inserts etc to really make everything composable and even have some cross-chain capabilities we walk through the basics of just using the table on js sdk deploying something on hard hat creating a very straightforward nft and then also this unique use case with actually doing things in real time doing things upon creating your smart contract in general creating tables minting tables upon just user calls directly to a smart contract such as this example that you see here with statements with making the move and it really opens the door for really anything you can imagine so many different use cases for leveraging cableland and doing it all with the beloved sql that we we've known for decades so i'll pause there we have a minute i know there's some questions i don't know if um we want to answer any of those right now but i appreciate the time and everyone for hopping on the call to talk about table and learn a little bit more also note that we're obviously have a channel so hop in there i'm dan if you have any questions shout out and i'm happy to help help you guys build some cool things awesome thanks dan um like super informative workshop i hope everyone enjoyed it um yeah a few since we're running up close on time um if you do have any questions um or just want to chat about like all the cool stuff you can do with tableland um hit up their discord and yeah happy hacking everyone and thanks again dan thank you appreciate it bye everyone