Creating a Guestbook with Astro DB

March 16, 2024 — Front End Development

My favorite framework and its amazing team of developers released a new feature this week that I am very excited about: Astro DB.

What is Astro DB?

As you may expect, the DB stands for database - however it’s not just any database.

Astro DB was built on top of LibSQL to be integrated directly with Astro and I gotta say it works very well.

The integration provides you with a few tools: a no-fuss local development database, an awesome API to work with said database, and a cloud database platform which can be wired up for your dynamic production content with Astro server-side rendering.

Creating a Guestbook

I just read the 29th edition of People & Blogs which features webring and guestbook aficionado, Sara Joy, which got me thinking about Guestbooks - an excellent project to test out Astro DB.

Setup Astro

Let’s scaffold a new Astro project using create-astro. The absolute best place to start an Astro project is the Astro docs, Astro’s documentation team is great and I have never had issues finding what I need.

In your terminal, navigate to where you’d like to create your project and run:

npm create astro@latest

Follow the prompts, selecting which preferences are best for you - in my case, I created an empty Astro project, with Strict TypeScript settings.

Now, open up that directory in your editor of choice so we can dig into Astro DB!

Setup Astro DB

First things first, let’s install the Astro DB integration:

npx astro add db

This will install the Astro DB package into your project and will ask if it is allowed to update your Astro config file, say yes.

Astro DB also scaffolds out a db/ folder containing a config.ts file for configuring your database, and a seed.ts file for seeding the database for development.

Open up the db/config.ts file, it’s empty at the moment, so let’s add our table.

To add a table, we will need to import the defineTable function from astro:db:

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

We also need to create a column on the table, so import column as well:

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

Great! Now let’s instantiate a table called GuestBook:

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

const GuestBook = defineTable({
  columns: {

  }
});

Our table schema is going to be simple on purpose, we are building a guestbook after all. We are looking to store:

id - The guestbook entry’s unique identifier
author - a string containing who left the guestbook entry
content - another string featuring the main content of the guestbook entry
timestamp - a timestamp to capture when the entry was added

We can configure this schema for our table column like so:

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

const GuestBook = defineTable({
  columns: {
    id: column.number({ primaryKey: true }),
    author: column.text(),
    content: column.text(),
    timestamp: column.date({ default: NOW }),
  }
});

We can indicate the type of data for each column. For the timestamp property, we can set the default value to the most current time using the NOW function provided by Astro DB (don’t forget to import it!).

Checkout the Astro Docs’ Table Configuration Reference for more information on how to set up columns, advanced configuration, and types of columns available.

Finally we will want to export the Guestbook table, we can do this using the defineDb function provided by Astro DB:

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

const GuestBook = defineTable({
  columns: {
    id: column.number({ primaryKey: true }),
    author: column.text(),
    content: column.text(),
    timestamp: column.date({ default: NOW }),
  }
});

// https://astro.build/db/config
export default defineDb({
  tables: { GuestBook},
});

That is all the table we need for our simple guestbook, so let’s talk about seeding the database.

Seed Database

To populate the database with some test data for development, open up the db/seed.ts file.

To actually use our GuestBook table we created in our db/config.ts file, we can simply import it from the database.

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

Then, inside the seed() function, we can insert data like so:

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

// https://astro.build/db/seed
export default async function seed() {
	await db.insert(GuestBook).values([
		{
			author: 'John Doe',
			content: 'You are so cool!'
		},
		{
			author: 'John Doe, The Second',
			content: 'I agree with my dad!'
		}
	]);
}

We only need to provide the author and the content as the id and timestamp will automatically be set based on the schema setup in the db/config.ts file.

Now we have our database configured and the seed file ready, let’s fire up the Astro development server!

Render to the Browser

For the purposes of this demonstration, I’m creating this guestbook on a single page.

To output the guestbook data on the homepage, open up src/pages/index.astro and import the db and our GuestBook table from Astro DB.

// src/pages/index.astro
---
import { db, GuestBook } from 'astro:db';

---

DrizzleORM is built into Astro DB, so querying the database is super easy. Let’s grab everything from the GuestBook table and render it to the page:

// src/pages/index.astro
---
import { db, GuestBook } from 'astro:db';

// Selects all items from the GuestBook table
const guestBook = await db.select().from(GuestBook);
---

<dl>
{
  guestBook
    .sort((a, b) => new Date(b.timestamp) - new Date(a.timestamp))
    .map(({author, content, timestamp}) => (
    <div>
      <dt>{author}</dt>
      <dd>{content}</dd>
      <dd>{timestamp}</dd>
    </div>
  ))
}
</dl>

This will iterate over the guestBook array, destructuring author, content, and timestamp for use within the template.

Personally, I want the latest entry in the guestbook at the top of the page, which we can achieve by chaining a sort() array method onto guestBook:

{
  guestBook
    .sort((a, b) => new Date(b.timestamp) - new Date(a.timestamp))
    .map(({author, content, timestamp}) => (
    <div>
      <dt>{author}</dt>
      <dd>{content}</dd>
      <dd>{timestamp}</dd>
    </div>
  ))
}

This will output the stored data from the GuestBook table onto the page!

BUT WAIT! How do we add things to the database? Well, like any good web application: Forms.

Creating a Form

If you have ever created an HTML form, you already know what to do, here is the form I created:

<form method="POST">
  <label for="author">
    Author
    <input id="author" name="author" />
  </label>

  <label for="content">
    Content
    <textarea id="content" name="content"></textarea>
  </label>

    <button type="reset">Cancel</button>
    <button type="submit">Leave your mark</button>
</form>

Oh no! The form is setup to post to itself, we need to handle that! Luckily Astro already knows what to do. We can check the page for POST requests using an if check on Astro.request.method:

// src/pages/index.astro
---
import { db, GuestBook } from 'astro:db';

// Watch for POST requests
if(Astro.request.method === 'POST') {

}

// Selects all items from the GuestBook table
const guestBook = await db.select().from(GuestBook);
---

// ...template

Once we catch a POST request, we will want to do a few things before adding the item to the database.

Parse the formData

Let’s grab the author and content submitted through the form:

// src/pages/index.astro
---
import { db, GuestBook } from 'astro:db';

// Watch for POST requests
if(Astro.request.method === 'POST') {

  // Parse form data
  let formData = await Astro.request.formData();
  let author = formData.get('author');
  let content = formData.get('content');
}

// Selects all items from the GuestBook table
const guestBook = await db.select().from(GuestBook);
---

// ...template

Data Validation

Prior to inserting the data to the database, it is generally a good idea to add some validation to make sure what is being submitted can be safely added. For the purposes of this simple demo, I’m checking to ensure the data types are valid - be sure to apply proper validation to projects you create!

// src/pages/index.astro
---
import { db, GuestBook } from 'astro:db';

// Watch for POST requests
if(Astro.request.method === 'POST') {

  // Parse form data
  let formData = await Astro.request.formData();
  let author = formData.get('author');
  let content = formData.get('content');

  // Validate that author and content are both of type 'string'
  if (typeof author === 'string' && typeof content === 'string') {
    
    // Insert data into the database
    await db.insert(GuestBook).values({ author, content });
  }
}

// Selects all items from the GuestBook table
const guestBook = await db.select().from(GuestBook);
---

// ...template

Now when submitting the guestbook form, new items will be added to the database! There is one bug though, if you try to refresh the page after submitting the form, it will cause a re-submit and duplicate the entry in the database. My friend Mayank reminded me that you can utilize Astro.redirect to move users to different pages in Astro, but in this case we just want it to redirect the page back to itself.

// src/pages/index.astro
---
import { db, GuestBook } from 'astro:db';

// Watch for POST requests
if(Astro.request.method === 'POST') {

  // Parse form data
  let formData = await Astro.request.formData();
  let author = formData.get('author');
  let content = formData.get('content');

  // Validate that author and content are both of type 'string'
  if (typeof author === 'string' && typeof content === 'string') {
    
    // Insert data into the database
    await db.insert(GuestBook).values({ author, content });

    return Astro.redirect('/');
  }
}

// Selects all items from the GuestBook table
const guestBook = await db.select().from(GuestBook);
---

// ...template

Spam Filtering

I was discussing this project over on the Frontend Horse Discord, where Sara Joy had the excellent suggestion of implementing spam filtering.

She had already asked about implementing spam filtering on the Astro Discord, where they gave a great run-down of how to accomplish it:

Have a <form> on your page for submitting to the guestbook, including the honeypot input.

Have a POST endpoint handler in your Astro site that receives the form data.

If the honeypot input has been filled, throw away that submission and return some error.

If not, call the Akismet API to get another check on whether it’s spam or not.

If all is well, add the comment to the database and return a successful response.

— Chris, from the Astro Discord

Honeypot

We already have our form on the page, so to include a honeypot field, we can inlude just a normal text input that is hidden.

The idea is that bots and automated tools that may attempt to fill out the form will also fill out the hidden field. If the hidden field has been filled out, we can disregard the entire submission as it is most likely a spam post.

<form method="POST">
  <label for="author">
    Author
    <input id="author" name="author" />
  </label>
  
  <label for="special">
    <input id="special" name="special" />
  </label>

  <label for="content">
    Content
    <textarea id="content" name="content"></textarea>
  </label>

    <button type="reset">Cancel</button>
    <button type="submit">Leave your mark</button>
</form>

I have included an input with the id of special. This can essentially be any unique id that you want it to be, we just need to know which field to look for when there has been a post submitted. I learned that using sort of obvious identifiers like the word honeypot may not work on some spam bots, as they most likely account for that to be a thing.

Inside our POST request check, we can grab the value of the special input and wrap our validation check within another if check, this time making sure the special input is of a falsy value.

// src/pages/index.astro
---
import { db, GuestBook } from 'astro:db';

// Watch for POST requests
if(Astro.request.method === 'POST') {

  // Parse form data
  let formData = await Astro.request.formData();
  let author = formData.get('author');
  let content = formData.get('content');
  let special = formData.get('special');

  // if special is falsy, proceed to validation
  if(!special) {
    // Validate that author and content are both of type 'string'
    if (typeof author === 'string' && typeof content === 'string') {
      
      // Insert data into the database
      await db.insert(GuestBook).values({ author, content });
  
      return Astro.redirect('/');
    }
  }
}

// Selects all items from the GuestBook table
const guestBook = await db.select().from(GuestBook);
---

// ...template

Akismet API

Honeypot checks are a great first step to cutting down on the number of spam submissions you might receive, however there are always going to be some that slip through. A second line defense against spam is reaching out to a service to help determine if the submission is ham or spam, well at least that is how Akismet defines it.

Akismet is a spam filtering service that is used all over the web, particularly due to it’s integration within the WordPress platform. They also provide a super generous free tier for their API! If you’d like to use Akismet, you will need to create a free account and register your application to receive an API token.

Once you have an API token, we can include it in the project as an environment variable. In the root of the project, create a .env file with the following contents:

AKISMET_TOKEN={YOUR_TOKEN_HERE}

Copy your API token from Akismet and paste it directly after the = sign. We’ll use this in a moment, where we will read the environment variable within our Astro page using the method used by Vite, import.meta.env.

There is a lovely node package useful to work with the Akismet API called @cedx/akismet, which also has excellent documentation.

With it, we can do a few things:

Verify our Akismet API to make sure we are even permitted to check if a message is spam
Pass our form data items to Akismet
Determine whether the submission contains ham or spam

Install the package by running the following in your terminal:

npm install @cedx/akismet

There are several changes we are going to make to our src/pages/index.astro file, so I’ll show you up front what it looks like, then I will explain in more detail.

Updated file:

// src/pages/index.astro
---
import { db, GuestBook } from 'astro:db';
import { Blog, Client, Comment, Author, CheckResult } from '@cedx/akismet';

// Watch for POST requests
if (Astro.request.method === 'POST') {
  // Setup Akismet client
  const blog = new Blog({ url: 'https://www.ryantrimble.com/' });
  const client = new Client(import.meta.env.AKISMET_TOKEN, blog);

  // Parse form data
  let formData = await Astro.request.formData()
  let author = formData.get('author');
  let special = formData.get('special');
  let content = formData.get('content');

  // Will be used to validate the Akismet API key
  let isValid = false;

  if (!special) {
    // Check if Akismet API key is valid
    try {
      isValid = await client.verifyKey();
    } catch (error) {
      handleError(error);
    }

    if (isValid) {
      // Check if comment is spam or not
      if (typeof author === 'string' && typeof content === 'string') {
        try {
          let commentAuthor = new Author({
            name: author,
          });

          let comment = new Comment({
            author: commentAuthor,
            content,
          });

          const result = await client.checkComment(comment);

          if (result === CheckResult.ham) {
            // If comment is not spam, push the entry to the database.
            await db.insert(GuestBook).values({ author, content });

            // Finally redirect back to homepage!
            return Astro.redirect('/');
          } else {
            // Otherwise disregard comment.
            throw new Error('This comment has not been published.');
          }
        } catch (error) {
          if (typeof error === 'string' || typeof error === 'object') {
            handleError(error);
          }
        }
      }
    }
  }
}

// @ts-ignore
function handleError(error) {
  if (typeof error === 'string' || typeof error === 'object') {
    const message = error instanceof Error ? error.message : String(error);
    console.log(`An error occurred: ${message}`);
  }
}

const guestBook = await db.select().from(GuestBook);
---

// ...template

We are now importing a few useful tools from the @cedx/akismet package:

Blog - This class is what the @cedx/akismet package uses to represent the URL transmitted when making requests.
Client - Another useful class used to submit items to the Akismet service.
Author - class representing properties common to authors, such as names, email address, and more.
Comment - Similar to Author, the Comment class represents properties common to comments such as authors, content, dates, and more.
CheckResult - Checks whether a comment is ham or spam.

We then instantiate new Blog and Client objects. Pass the same address you used to setup your Akisment API into the Blog.

For the Client, we need to pass in our Akismet API token, which is stored as a environment variable. We can use import.meta.env to access the environment variables within Vite projects such as Astro.

I added an isValid variable which is utilized to validate the Akismet API token. We do this using the client.verifyKeys() method. If we have a valid API token, we are free to move on to the next part.

Next we create two objects for Author and Comment, including the properties we want to have checked by Akismet. To check the items with Akismet, we use the client.checkComment() method.

To recap, here are all the levels of protection we now have in place:

Honeypot field to cut down on the number of spam submissions processed
API token verification, ensures that the validation request is being sent by us.
Akismet API spam checking to catch messages that may have gotten through Honeypot,
JavaScript typechecks in place to make sure the content being posted is in the proper formats

I have a full implementation of this included in the Github Repo!

Deploying

There are few things necessary to deploy this application:

Astro must be in server-side rendering mode to dynamically update the page. This can be enabled within the astro.config.mjs file by adding output: 'server' to the configuration.
Astro must utilize one of the hosting adapter integrations, I’m using the Netlify adapter.
Push project up to Github.
Set up your project within Astro Studio with these instructions.
Connect your site to your hosting provider. Make sure to include your Astro Studio API token as an environment variable on your production builds.

Now, once the site builds and deploys to the hosting provider of your choice, you should have a fully working Guestbook!

Wrap up

The completed project is available to view at: https://astro-guestbook.netlify.app/

Feel free to check it out and leave a comment! You can find all the code here: https://github.com/mrtrimble/astro-guestbook

I’m SUPER excited to see what the creative folks of the web dev community can come up with using Astro DB.