People that know me or have read my previous articles know that I typically work in one of those other React frameworks that we will not name here. I skipped the line on learning web development and reached for one of the easiest when I decided to learn Javascript after using Python for shipping sites.

What do I know about using Astro? I would say that I know very little. I have only built a few micro-sites and on the one I spent the most time on, I was building a React component for all the heavy lifting of the site. So I was literally learning as I was building a site to connect with Astro’s new db feature.

Where I Started

I had an idea of what I wanted to do from the beginning. Previously, I build a small project to simulate a full-stack app that could create, read, update, and delete data that I was accessing through GraphQL. I wrote an article about that and you can read more about that experience here. That is what I’m using as my frame of reference.

I created my astro app with the same setup as usual using npm create astro@latest. Then, I knew I wanted to add Tailwind to this so I used the Astro commands to do that. I cleared out most of the boilerplate and added a new component to my components list named ReadForm. I then imported that and put the component at the bottom of my index.astro page.

The db had to be set up as well, so I used the Astro commands to do that. When you create your db, you get a new directory set up with a couple files that you’ll use.

Working with the DB

I used the Astro documentation (which is superb by the way!) to help get everything set up correctly. There were some flashbacks for sure. When I first started developing websites, I was using Python with the Flask library and SQLlite. Astro:db uses SQL as well. When installing astro:db, you get the two files that you need to set up: config.ts and seed.ts. Config is used to set up the schema for your data and seed is used to create some starter data and seed your data types.

I first set up my config.ts file.

//./db/config.ts
import { defineDb, defineTable, column } from 'astro:db';

const User = defineTable({
  columns: {
    userId: column.number({ primaryKey: true}),
    userName: column.text(),
    userScore: column.number(),
  },
  })

export default defineDb({
  tables: { User },
});

I just wanted something basic for the Users with an id, name, and score. Then after that I put some data into my seed file to get the db started with some information that I could query.

./db/seed.ts
import { db, User } from 'astro:db';

export default async function seed() {
    await db.insert(User).values([
    { userId: 1, userName: 'Rust', userScore: 100},
    { userId: 2, userName: 'Marty', userScore: 90},
    { userId: 3, userName: 'Audrey', userScore: 80},
    { userId: 4, userName: 'Errol', userScore: 70},
    { userId: 5, userName: 'Thomas', userScore: 60},
    { userId: 6, userName: 'Maynard', userScore: 50}
])
}

Now that I had some data locally, I was ready to see if I could query it with a form.

Setting Up the Form For Reading Data

I wanted my form to function this way. If the form entry was blank or could not find the data, to not display anything. If the form entry was the text “all”, I wanted the form to fetch all the data and show it. Lastly, if the form entry was a specific user name, then I wanted the form to only fetch that row from my table and show it.

To do this in Astro, I had to create a form in my component and then I put the logic for fetching the data in the frontmatter of the component. This is similar to using markdown files to drive a site. The important piece of fetching from the db is simple. I fetched all the data at once with:

users = await db.select().from(User);

and then I used conditional statements from the form to filter my data. This is not the best way, I know. I still need to learn the correct syntax for filtering the data fetch. It’s on my TODO list.

Then from there, you can put a conditional statement in your component that if there are any users, to display those in your HTML.

Local First

The cool thing about the db is that its local first. So, let’s say you have a site that you’re building that’s just blog posts. You can insert your blog post onto your local db and then when you ship it, it will pull from that db in the build commands. There’s no reason to have your blog site continually do data fetches if you can build it straight into the HTML.

Now, I wanted to play around with how it would work hosted remotely. So, I set up an account with Astro Studio. When you create your project with Astro Studio, you can link it to a Github repo which I did. In addition, you want to create a token for when you ship your site. But, before I got into all that, I wanted to test it in development. Using Astro commands, I linked my project on my computer to the project I had created on Astro Studio using astro link. Then, I was able to push all of my data in my seed file to what was being hosted on Astro Studio.

To run the site using the remote data, you just add the flag –remote to your dev and build commands in your config file. There was a small bug with getting it to find the remote db, but I just needed to update in npm and it was working fine.

Wrapping It Up

Finally at the end, I shipped it with Netlify. Astro makes all of these steps super easy. Using the Netlify adapter in my config on my Astro project and then putting my token on Netlify connected all of my data well and it works. The next step for me is to get better with the data query and start adding in other pieces like the insert and delete for the db.

You can see the site live at https://aesthetic-truffle-e7d729.netlify.app/ and the repo is available at https://github.com/ryanjames1729/testing-astro-db.

This article was originally posted on https://ryan-james.dev,