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