 Hello and welcome to this webinar on using the built-in SQL database with spin 1.4. Today we're going to take a look at this new feature in the latest version of spin. I am Melissa Klein, the open source program manager at Fermion. I'm going to start the webinar off with a quick introduction to spin and the built-in SQL database feature. Later I will be joined by Ryan Levick, a principal engineer at Fermion, and he will be doing the demo in the second half of this webinar. So let's get started. First, what is spin? Spin is an open source framework for building and running event-driven microservices with WebAssembly. The first release of spin was in spring of 2020. This past spring, we hit an important milestone with the 1.0 release. Since that release in March, we've done monthly releases with the current release of spin at 1.4 in July. Spin is open source and built on open standards, so you can take your spin applications and run them almost anywhere. There are spin implementations for local development, for self-hosted servers, for Kubernetes, and for cloud-hosted services. Spin aims to be an awesome developer experience. It is a single, binary tool to create, build, and run your applications. Spin comes with built-in templates and requires no boilerplate, so it is fast and easy to get started writing the logic of your applications. There are SDKs for multiple languages, including Rust, Go, Python, JavaScript, and TechScript. So let's take a step back and talk about WebAssembly or WASM for short. WASM is a W3C standard binary format for a stacked-based virtual machine, which makes it both cross-platform and cross-architecture. You may be thinking, I know WebAssembly as a browser-based technology. Why would I build microservices with it? Well, WASM is sandboxed, portable, and fast, with millisecond cold start times. These characteristics make it appealing for other use cases, such as mobile and edge devices and serverless applications. Additionally, because it is an open standard, many languages have added WASM implementations, which means you can get started with WASM without learning a new language. Now, let's take a look at how easy it is to get an app up and running with Spin. After installing Spin, we start by running the Spin new command. This command gives you a list of templates. Pick one that matches your application type and language. Here I picked an HTTP request handler using Python. I'm going to give the app a name, in this case PyTest, and accept the fault for the other options. Okay, so now there's a directory with my new app in it. So I'm going to change to the PyTest directory, and I see there's an app.py and a spin.toml file in this directory. I'm going to edit the app.py file, and I see I already have a basic HTTP request handler. I'm going to make a little, a few quick changes to this just to make it my own. And once I do that, I'm going to save this file and go back to the command line. Now we're ready to build. So we do a Spin build command, and it builds the application. Next, Spin up, we'll start the application running, and we can see that it's running at port 3000. So I go over to another terminal window, and I'm going to curl to that address. And now I see that I got my expected result. And there, that's how you run, get started running a Spin application. So as you can see, it is easy to get started with Spin. There are many more features you can use to build a Spin app. You can check out our documentation to learn more about those features. But now let's talk about accessing data sources from a Spin application. Even before our 1.0 release, Spin had support for bringing your own database. With this feature, you can host and manage a data store outside of Spin and use a Spin API to make outbound calls to it. There is support for relational databases such as MySQL and Postgres, and then also support for Redis data stores. This is useful when you have existing data you want to access with your new application. You can find an extensive example of how to use this feature in the example directory of the Spin refund. Next, Spin 1.0 introduced a built-in key value data store. This means there's no ops required for all the provisioning, configuration, and management is done by Spin. Components are not given access to the KV store by default. They must be granted access. To do this, you simply add one line to your application manifest file. Then you can access the key value store from the component of your application using the API. For examples on how to use this feature, you can see the examples folder in the Spin repo or in the language SDKs. So now if you want an SQL database but you don't want to host your own, with Spin 1.4 we now have a built-in SQL lite database. Again, no ops are required. Spin is going to create, configure, and manage the SQL database for you. Components do not have access to the database by default. Only one line needs to be added to the application manifest file to grant access to the components. And there's no credentials or connection strings to be managed. Spin handles all the communications securely. To get started using the SQL lite database feature, first you want to make sure you have Spin 1.4 or later installed. You also want to make sure that you're up to date with the latest versions of your language SDKs you wish to use. Then we're going to go through the Spin new with your desired template. Start your application. And then in the spin.toml file, the application manifest, you're going to add this SQL lite databases equals default. This will give access to the default database to any components that you wish to give access to. And that's it. Spin will create the database for you. And then you can access it from your code. Here you see an example Python file. Here you see it's using the Spin SQL lite module to access the database. It opens a connection to the default database. It runs an SQL statement. And returns the result. And then returns the HTTP request handler returns that result to the user. All of the language SDKs are using a common set of operations for interacting with the SQL database. Open, execute and close. The open and close operations open and close a connection to the database. The execute operation will execute an SQL statement. So for a more in-depth explanation of how to use these operations and how to use them with your preferred language, you can see the SQL storage API guide in the Spin documentation. So Spin start a brand new database for you. But that database does not include any tables. So you can write code to run SQL statements that will create the tables that you want. Or with your spin up command, you can use the SQL lite option to either run a file to set up the database. Or you can run the SQL lite option with SQL commands. You can also use the runtime configuration file or the runtime config.toml file to add additional SQL lite databases each with their own name. So if you reference just default, you'll get a default database, but then you can add additional ones with different names in different locations. You can also specify what location you want the default database to be. It will, if nothing is specified, it will save it in the doc spin directory. But if you want to save it for reference an existing database, you can set it in the runtime configuration file. Okay, so now it's time for a demo. I'd like to pass it off to Ryan Levick to do the demo. All right, everybody, are you ready to build a to do application using spin and SQL lite? Let's do it. So I've already created a little bit of our spin to do workspace here with a bit of the boilerplate already created for us so that we don't have to do that. But let's take a look at what we already have here. So I'm going to run tree with get ignore flag here in order to show what we already have. So we have a couple of things here that are probably familiar with to you if you've done any spin development before or if you're a Rust developer. But let's take a look at what that looks like. So we have a spin.toml manifest file here, and that describes what our spin application looks like. We'll take a look at that in just a second. We also have a cargo.toml file. That's a manifest file for our Rust application that we're building. And we have a source directory here that has lib.rs inside of it. And that's what the code that we'll be working on today. And lastly, we have a static assets file here. This is all of the front end code that we're going to need in order to run our application. I went ahead and did that ahead of time so that we don't have to worry about building the front end for us as we go. All right. So let's take a look at the spin.toml file in order to know what our spin application actually looks like. So here's what that looks like. And it should be familiar to you if you've seen a spin manifest file before, but if not, don't worry. It includes some metadata about our application here, including a description, the authors of the application name. And this is an important part here, the trigger. So what actually triggers our application to run? And this says that HTTP requests are what triggers it. We have other types of triggers in spin as well. And this application is composed of two components. One is the to do API component. And that's what we're going to be working on today. That's the API for our application. And the second one is a file server that serves all the static assets that we have over here and our static assets file. So including our index.html, CSS, favicons, app.js as well. And what we specify right here with our component trigger is anything, any request that comes in to slash API slash anything will be served by this component right here, our API component. Otherwise, we kind of have the fallback route of slash anything will be served by our static file server instead. All right. So then we can take a look at what our code actually looks like so far. And if you jump over here, you'll see that in fact we're just dealing with the spin sort of hello world or hello fermion example that we have that you create whenever you have a default application. So this is what we're going to be replacing with our API logic instead. All right. So let's go ahead and get this a run. We can do that by doing spin build here that builds our application. And then once we've built our application, we can run spin up. And that brings up our application here. So our application is up and it's getting up and running here. And now it's running on local host port 3000. And you can see that for slash API will be serving the API component and for anything else, the file server will be serving it. So let's take a look at what this looks like in practice. Here is our to do application front end already running looks great. But if we try and add a to do like do laundry or laundry instead, it doesn't work like it's not working here. And so let's take a look and see what's happening over here. We'll bump over here and we'll see. Oh, we're running into the fact that that hello fermion that we're returning from our API is not valid. Jason and certainly not the Jason or any response that are to do application is expecting. So this is an expected error. We want to go ahead and actually return back something that our application can can actually work with. All right. So let's return back to our code here. We'll go ahead and comment this out here. And the first thing that we're going to want to do is actually connect to our SQLite database so that we can actually query for data inside of it. So in order to do that, we're going to use this connection types that you can see right here comes from the spin SDK SQLite module. If we go ahead and bring that in, we can use open or rather open default here to open default database for our application. Every spin application comes by default with a default database that we can use where we don't really have to configure very much in order to use it. If we have more databases, then we can use the open method in order to open those databases by whatever name we decide to give them. So we're opening our database here and if it fails, we'll go ahead and return back that error. And once we have a connection here, then there's only really one thing we can do with it, and that's calling the execute method on it. So we can pass in some kind of SQL statement here in order to be executed. And in order to do that, what we can do is let's just run select star from to do's from some to do's table. And we have to pass in some parameters here, but we don't have any, so we'll just return an empty list. That's if you want to interpolate and some parameters and doing that in a way that's safe from SQL injection. And that can also fail. So we'll question mark that up as well here. And that will run our query that we have here and return back a result. So we'll do, we'll call that to do's here. And you'll see here that Rust analyzer is printing out that to do's is of type query results. So it returns back some object called query result that we can get the rows of our query from and then the columns from our query. And of course, we're still complaining that we have to return a response here. So let's let's uncomment this again, and at least return something here. But we'll go ahead in the meantime and print out the to do's that we have to do's. And in fact, I'll go ahead and print it out using that. So we'll see nice verbose output as well. All right, so let's go ahead and run this. Go in here, we'll run spin build, and then we'll do the dash you option that does spin build and then spin up all in one go. So we're just getting a warning that we're not using this request object here. That's fine. And now our application is being served on port 3000 again. We'll hop over to our front end. And we'll see that we're getting a 500 here. Uh-oh. So what's happening here? Well, we can see by the error message right here that we have access denied. So we're not being allowed access to our default database. What's what's up with that? Well, it turns out by default default databases or any database is not allowed or any application is not allowed to access a. Database unless it is explicitly allowed to do so. And we do that inside of our spin.toml file by coming in here finding the component that we want to give access to, which is our API component in this case and saying that the SQLite databases that it has access to are equal to the default database. So now our component will have access to the default database. So let's go ahead and try running that again. And you can see down here that we're actually accessing our default database as well. Okay, let's head over here and refresh. And you'll see, okay, we're still getting a 500. That's not good, but it's a different error. And if we head over to our terminal here, we'll see that the error that we're actually running into is that we do not have a table called to do's. We need our database, which is completely empty now to have some kind of schema and it's expecting to have the tables, the table called to do's and we're going to have to create that. So how do we do that? Well, we can take advantage of a flag on spin up called SQLite. And if we pass, we can pass SQLite flag here a string to execute or even a file of SQL to go ahead and execute. And we'll do that. We can specify a file by doing this at sign here and then doing the name of the file and we'll call it migration.sql. If we run this, we'll see it errors out because it doesn't know what migration.sql is. So let's go ahead and create that migration.sql. All right, we've created our migration.sql file here and we'll have to populate this with some SQL that we want to run. I'm going to come over to the off screen here and copy some SQL that I have over here. And in fact, I'll even simplify it a bit by just doing two fields here. So this SQL is pretty simple. It just creates a table. If it doesn't use this called to do's has an ID that's an auto incrementing primary key and a description that's text that can't be null. So pretty straightforward. And if we come back here and try and run this again. Seems like we have an error because I left a comment there when I shouldn't see SQL is very fickle. That's why I copied it and then I went ahead and edited it anyway. Oh, well, I guess I'll learn next time. So we see here now it's working because I fixed our error. It's up and running. And now we should have a table called to do's. So if we come back here, we'll see we're back to the original error that we had before. Where we're returning back hello fermion and it's not valid Jason like our like our app expects. All right, but we're now printing out the query result that we have and you can see that we have two columns ID and description and no rows. So no rows of data here. So that's great. We're making some progress here. So now inside of our app. What we have to do is somehow convert the result that we're getting back from from our query and turn it into an HTTP result. A response that we can return back to the client. All right. So in order to do this, I think it's better if we if we have some data to work with. And in fact, we can actually manipulate our database directly if we want to because all it is is a SQL database that exists in our dot spin file over here. And if you're not familiar with that spin, it just is a file where we stick some stuff that's of interest to you like logs and your default database. So we can use SQL light. Let me clear the screen here. And we can use SQL light three here to access our our database. And here and we'll go ahead and run insert into to do's. And I hope I remember the the incantation correctly. So insert into to do to do's description description. And then values do laundry. All right. And if we go ahead and run select star from to do's. Now, now we have one to do and our to do's table called do laundry. And let's go ahead and verify that our app sees the same thing as well by running spin up. And coming over here, refreshing the page. There we go. We're doing the same error, but you can see now that we actually are getting data returned back to us. So awesome. We're, we're, we're almost done. We're almost on our way. So now we need to convert this query result into to a response that we can return back to the to our front end. So we need to go ahead and do to do's and we can call the rows function on it in order to get an iterator of rows. And we'll just map over those rows here like this. And we can call let ID equals R dot get. And we can pass in the name of the column that we have. It's called ID and description is called description. All right. Now, of course, these things might not exist. There's no way to statically know that these things exist. So we're going to have to handle the, the optionality of it. And for now, because this is a demo, I'm going to go ahead and use unwrap. But of course you would want to handle that a little bit more elegantly in real life here. And the last thing that's complaining about is that it needs some specific type in order to convert into. So we'll automatically cast our the row result into what we expect for ID will do you 32 and for description will do a string of some sort. And then we're going to take that data and turn it into something that we can serialize as, as Jason. So I'm going to create a struct called to do here. And it's going to have an ID of type you 32 and a description of type string. And we're going to use our good old friend, sir day, in order to actually convert this into Jason. So sir day serialize. We're going to have to bring in sir day as a dependency and rust. So sir day, and it's going to be version 1.0. And it's going to use the feature of derive in order to be able to derive our serialization. So that brings in sir day now. This is all looking happy. And what is it complaining about here? It is not liking that. Not quite sure why that would be. Let's just real quick build this, figure that out. And in the meantime, what we're going to do is convert this into it to do. And of course, we're going to have to change our description, which is a string slice into an owned string. I like to run to owned on it in order to do that. Let's see. So for some reason, it's not playing well with me. And I'm sure people at home are sure why this isn't working, but that's fine. We'll go ahead and instead we'll pull in sir day Jason in order to do this. So we're bringing in sir day Jason here. And we're going to take our to do here and convert it into Jason. So let's go ahead and do that. So the first thing we do is collect into a back here. We have all of our to do's here. And now what we have to do is convert this into Jason that we can return back to the user. So we'll say let Jason equals and we will convert our to do's into a Jason object of some sort. So sir day Jason and we can use the Jason macro here in order to do this. So we can say ID is equal to T dot ID and description. Descript. I got to learn how to spell description is equal to T dot description here. All right. And we're just going to collect that into a vector and then we should be able to turn this into a body. And it's complaining here that it doesn't know how to turn this into a vector. That's fine. We can say Jason sir day Jason serialize sorry to string. We'll turn our Jason into a string here and then turn that into the bytes that we need. And of course we need to in case that are turning into string fails. All right. So we should be good here. Sorry about the sir day serialize. I'm sure after this I'll hit myself on the head for why that actually happened but we have something turned into Jason here. We're going to return it back to the user as well. So let's try and run this by running spin clear the screen for a spin build and then dash you and that's taking in the new dependencies there building them. And we're up and running and we can go ahead and there we go. We have do laundry then so we are up and running we have everything running. So just to repeat back what we got got going here is we started with not having a database. We went into our spin dot Tamil file here and allowed the usage of the database by using this line. Then inside of our code here what we were able to do is connect to the default database make a query to that database gets back some data converted into a model of some sort. Turn that model into Jason and around about way sorry about that again and then return it back to the user and the front end knows how to take that and actually display it to the user. And the UI so if you found this interesting and like to keep going you can go to this example demonstration here where we have this whole application written out with the ability to edit and to delete. And you can run this in your locally as well and try and build out the rest of the application for you for your own self. So I hope you enjoy this. Thank you very much.