Free Resources
Getting Started with Eleventy
Recommended Articles
D1 SQLite: Writing queries with the D1 Client API
Writing queries with the D1 Client API In the previous post we defined our database schema, got up and running with migrations, and loaded some seed data into our database. In this post we will be working with our new database and seed data. If you want to participate, make sure to follow the steps in the first post. We’ve been taking a minimal approach so far by using only wrangler and sql scripts for our workflow. The D1 Client API has a small surface area. Thanks to the power of SQL, we will have everything we need to construct all types of queries. Before we start writing our queries, let's touch on some important concepts. Prepared statements and parameter binding This is the first section of the docs and it highlights two different ways to write our SQL statements using the client API: prepared and static statements. Best practice is to use prepared statements because they are more performant and prevent SQL injection attacks. So we will write our queries using prepared statements. We need to use parameter binding to build our queries with prepared statements. This is pretty straightforward and there are two variations. By default we add ? ’s to our statement to represent a value to be filled in. The bind method will bind the parameters to each question mark by their index. The first ? is tied to the first parameter in bind, 2nd, etc. I would stick with this most of the time to avoid any confusion. ` I like this second method less as it feels like something I can imagine messing up very innocently. You can add a number directly after a question mark to indicate which number parameter it should be bound to. In this exampl, we reverse the previous binding. ` Reusing prepared statements If we take the first example above and not bind any values we have a statement that can be reused: ` Querying For the purposes of this post we will just build example queries by writing them out directly in our Worker fetch handler. If you are building an app I would recommend building functions or some other abstraction around your queries. select queries Let's write our first query against our data set to get our feet wet. Here’s the initial worker code and a query for all authors: ` We pass our SQL statement into prepare and use the all method to get all the rows. Notice that we are able to pass our types to a generic parameter in all. This allows us to get a fully typed response from our query. We can run our worker with npm run dev and access it at http://localhost:8787 by default. We’ll keep this simple workflow of writing queries and passing them as a json response for inspection in the browser. Opening the page we get our author results. joins Not using an ORM means we have full control over our own destiny. Like anything else though, this has tradeoffs. Let’s look at a query to fetch the list of posts that includes author and tags information. ` Let’s walk through each part of the query and highlight some pros and cons. ` * The query selects all columns from the posts table. * It also selects the name column from the authors table and renames it to author_name. * It aggregates the name column from the tags table into a JSON array. If there are no tags, it returns an empty JSON array. This aggregated result is renamed to tags. ` * The query starts by selecting data from the posts table. * It then joins the authors table to include author information for each post, matching posts to authors using the author_id column in posts and the id column in authors. * Next, it left joins the posts_tags table to include tag associations for each post, ensuring that all posts are included even if they have no tags. * Next, it left joins the tags table to include tag names, matching tags to posts using the tag_id column in posts_tags and the id column in tags. * Finally, group the results by the post id so that all rows with the same post id are combined in a single row SQL provides a lot of power to query our data in interesting ways. JOIN ’s will typically be more performant than performing additional queries.You could just as easily write a simpler version of this query that uses subqueries to fetch post tags and join all the data by hand with JavaScript. This is the nice thing about writing SQL, you’re free to fetch and handle your data how you please. Our results should look similar to this: ` This brings us to our next topic. Marshaling / coercing result data A couple of things we notice about the format of the result data our query provides: Rows are flat. We join the author directly onto the post and prefix its column names with author. ` Using an ORM we might get the data back as a child object: ` Another thing is that our tags data is a JSON string and not a JavaScript array. This means that we will need to parse it ourselves. ` This isn’t the end of the world but it is some more work on our end to coerce the result data into the format that we actually want. This problem is handled in most ORM’s and is their main selling point in my opinion. insert / update / delete Next, let’s write a function that will add a new post to our database. ` There’s a few queries involved in our create post function: * first we create the new post * next we run through the tags and either create or return an existing tag * finally, we add entries to our post_tags join table to associate our new post with the tags assigned We can test our new function by providing post content in query params on our index page and formatting them for our function. ` I gave it a run like this: http://localhost:8787authorId=1&tags=Food%2CReview&title=A+review+of+my+favorite+Italian+restaurant&content=I+got+the+sausage+orchette+and+it+was+amazing.+I+wish+that+instead+of+baby+broccoli+they+used+rapini.+Otherwise+it+was+a+perfect+dish+and+the+vibes+were+great And got a new post with the id 11. UPDATE and DELETE operations are pretty similar to what we’ve seen so far. Most complexity in your queries will be similar to what we’ve seen in the posts query where we want to JOIN or GROUP BY data in various ways. To update the post we can write a query that looks like this: ` COALESCE acts similarly to if we had written a ?? b in JavaScript. If the binded value that we provide is null it will fall back to the default. We can delete our new post with a simple DELETE query: ` Transactions / Batching One thing to note with D1 is that I don’t think the traditional style of SQLite transactions are supported. You can use the db.batch API to achieve similar functionality though. According to the docs: Batched statements are SQL transactions ↗. If a statement in the sequence fails, then an error is returned for that specific statement, and it aborts or rolls back the entire sequence. ` Summary In this post, we've taken a hands-on approach to exploring the D1 Client API, starting with defining our database schema and loading seed data. We then dove into writing queries, covering the basics of prepared statements and parameter binding, before moving on to more complex topics like joins and transactions. We saw how to construct and execute queries to fetch data from our database, including how to handle relationships between tables and marshal result data into a usable format. We also touched on inserting, updating, and deleting data, and how to use transactions to ensure data consistency. By working through these examples, we've gained a solid understanding of how to use the D1 Client API to interact with our database and build robust, data-driven applications....
Dec 23, 2024
6 mins
Building a Stripe App: A Step-by-Step Guide to QR Code Generation
Building a Stripe App: A Step-by-Step Guide to QR Code Generation Why Build a Stripe App? I recently participated in an audio space with the Stripe team, and something they said really stuck with me: the Stripe app store is a growing area that isn't overly saturated yet. There's a lot of potential for new apps, and companies can use this opportunity to grow. I work at a company called This Dot Labs, and we've created several Stripe apps and even own one. After looking at the data, I can confirm that the Stripe team was right! Creating a QR Code Generator App For this tutorial, we'll build a QR code app that can take a URL and generate a code for it. This is a good use case to help you understand the ins and outs of Stripe's developer tools. Why QR Codes? QR codes are useful tools that have become common in e-commerce, restaurants, and other industries. While Stripe already has a QR code tool, we'll make our own to familiarize ourselves with their syntax and problem-solving approaches. Project Structure Before we dive into the implementation, let's look at the structure of our Stripe App QR Code project: * .vscode: Contains settings for Visual Studio Code * source/views: Holds the main application views * .gitignore: Specifies files to ignore in version control * stripe-app.json: Defines the Stripe app configuration * ui-extensions.d.ts: TypeScript declaration file for UI extensions * .build: this is where the built Stripe app gets placed. Step-by-Step Implementation 1\. Install Stripe Locally First, you need to install Stripe on your local machine. The documentation provides great instructions for this: * For Mac users: Use Brew to install * For Windows users: Download the package and add it to your environment variables You can find the details here in the stripe docs to install the Stripe CLI https://docs.stripe.com/stripe-cli When using Windows, you must do stripe login from Powershell, NOT from Git bash or any other tool. After the server is up, then you can continue using git bash for everything else. After stripe login, you need to enter stripe apps start. Once you do that, the server is up and running and you can go back to using git bash or any other tool. 2\. Install Dependencies We'll be using an extra package for QR code generation. Install it using npm: ` 3\. Set Up the Main Component Let's look at the home.tsx file, where we'll use Stripe's UI components: ` These components are similar to other UI libraries like Bootstrap or Tailwind CSS. 4\. Create the UI Structure Our app will have: * An input field for the URL * Validation using a regex pattern * Error handling for invalid URLs * QR code generation and display Here is the Home.tsx file that is located in the src/views folder ` * ContextView` is at the top level of the app where we see the Title and the link to the Stripe Docs that we placed in our Context View. * Box is how you use Divs. * Banners can be used to show notification errors or any other item you wish to display. * Textfields are input fields. * Everything else is pretty self-explanatory. 5\. Handle Content Security Policy One problem I personally ran into was when I tried to redirect users, the Stripe policies would block it since I did not express that I knew what it was doing. I had to go into the stripe-app.json file and mention the specific security policies. For this particular exercise, I kept these as null. This is my stripe-app.json file. ` 6\. Configure App Views As you can see here, the stripe-app.json file shows the views for each file I have. The Home.tsx file and the Invoice.tsx are also included This is our way of saying that for each view we have, show the app functionality on that page. Our stripe-app.json file will show it but also, the manifest.js file in our .build folder will also show the same. Any view that doesn't have a file will not show the application's functionality. So, if I were to go to transactions, the app would not show the same logic as the home or invoices page. By following these steps, you'll have a fully functional QR code generator app for Stripe. This is just a simple example, but the potential for Stripe apps is massive, especially for businesses serving e-commerce customers. If you need help or get stuck, don't hesitate to reach out, danny.thompson@thisdot.co. The Stripe team is also very active in answering questions, so leverage them as a resource. Happy coding!...
Dec 18, 2024
4 mins