Modern e-commerce caching with Astro + Turso: Database + authentication (part 2)

This is the second part of a three-part guide that focuses on achieving caching at scale for e-commerce websites using Astro and Turso. Here are the parts of the guide:

1. Overview: basic requirements, typical solutions with pros/cons, and a suggested optimization.
2. Database and authentication (this part): adding a products database and basic authentication, enabling a logged-in user to update products.
3. Cache tags and revalidation: adding cache tags and the code to detect changes and purge relevant tags.

#Astro and Turso make a great team

Netlify has loved Astro long before we established our partnership with them, which is why we’ve transitioned our main website to use it.

Much of Astro’s charm is in how it starts off with a lightweight, zero client-side JavaScript approach, then lets you add state-of-the-art dynamic behaviors while still providing freedom of choice. Want to use React? Great! But you could also choose from a whole list of other UI frameworks, or keep building without a UI framework at all (e.g. with actions).

Beyond Astro, the web frameworks class of ‘24 was really exciting, from Angular 19 to Tanstack Start. This also means that most of the caching functionality we offer applies to these frameworks as well.

#Turso plugs in easily to Astro and Netlify

If you’re using Astro already, you may already know how well Turso is integrated with the framework, via Astro DB. Astro DB works with any libSQL-compatible database, and Turso is the company behind the libSQL project.

We also recently launched a Turso integration, which makes it easy to get started with Turso in your Astro project.

For these reasons, Turso makes a great team with Astro and was the perfect fit for this caching guide.

#Forking the original template

The original storefront template from the Astro team was built to support pluggable e-commerce backends. (Astro built their own merch shop with it.)

We’ve created our own fork to demonstrate the caching functionality for scaling e-commerce sites, and this is what we’ll be using for the rest of the guide.

#Adding a database using Astro DB

The open-source template currently provides a simple mock implementation with a few products and collections hard-coded into the TypeScript code.

To realistically demonstrate caching, we need an external data source for our storefront, and a decently-sized product catalog. We chose Astro DB as the data access layer for storage, as it is very quick to start with.

#About Astro DB, SQLite, libSQL, and Turso

Astro DB is built on top of Drizzle ORM, a popular type-safe ORM, and libSQL, a fork of SQLite created and maintained by Turso. Astro DB takes care to generate the actual database schema from code, handle migrations, seed the database, and more.

By design, SQLite is an embedded library with no standard server process or network protocol of its own. The libSQL fork adds such a server, which Turso uses to offer a managed service.

During local development, Astro DB uses libSQL in in-memory mode so there’s no additional process for you to run. In production, you can easily connect to a remote server, either managed by Turso or by yourself.

#The content schema

Here is the full schema file for the example site:

import { defineDb, defineTable, column } from "astro:db";

const ProductsTable = defineTable({
  columns: {
    id: column.text({ primaryKey: true }),
    name: column.text(),
    slug: column.text(),
    tagline: column.text({ optional: true }),
    description: column.text({ optional: true }),
    price: column.number(),
    discount: column.number(),
    imageUrl: column.text(),
    collectionIds: column.json({ optional: true }),
    createdAt: column.date(),
    updatedAt: column.date(),
    deletedAt: column.date({ optional: true }),
  },
  indexes: [{ on: ["price"] }, { on: ["updatedAt"] }],
});

const CollectionsTable = defineTable({
  columns: {
    id: column.text({ primaryKey: true }),
    name: column.text(),
    description: column.text(),
    slug: column.text({ optional: true }),
    imageUrl: column.text({ optional: true }),
    createdAt: column.date(),
    updatedAt: column.date(),
    deletedAt: column.date({ optional: true }),
  },
});

const JobsTable = defineTable({
  columns: {
    name: column.text({ primaryKey: true }),
    lastSuccess: column.json({ optional: true }),
    lastFailure: column.json({ optional: true }),
  },
});

export default defineDb({
  tables: { ProductsTable, CollectionsTable, JobsTable },
});

A few notes on the above:

• Products and collections have the columns createdAt, updatedAt and deletedAt, which we’ll use later on for detecting changes. Most production systems have such columns and ensure that these are kept up-to-date on any mutation going through the data layer.
• For simplicity, we’ve modeled the many-to-many relationship of products and collections via the JSON-type column ProductsTable.collectionIds. We also skipped entirely the definition of product variants, which can get opinionated and complex in e-commerce systems.

Based on the above schema, Astro will take care to generate the actual database schema and TypeScript interfaces. Next, we’ll look at the data.

#Generating product and collection data

For the data, we’ve taken a subset of 10,000 products from a high-quality dataset of fashion products that is publicly available on Kaggle.

#Transforming the raw data

We used Claude AI to transform the raw data (which is never fully consistent for a real dataset of this kind, because humans x time = mess) into concise descriptions and catchy taglines for all products, and store it in a single JSON file. Additionally, there is a much smaller JSON file for product collections.

#Populating the database

To populate the databases, the seed function (db/seed.ts) reads the JSON files and inserts their content to the database. This takes place automatically every time you run astro dev, giving you a clean in-memory database.

#Provisioning the database

For production, provisioning the database should be done by you, either manually or as part of your infrastructure automation code. The needed steps are detailed in the next part of the guide.

#Fetching and querying data from Turso

Finally, we’ve modified the implementation of the mock e-commerce client module (src/lib/client.mock.ts) to fetch data from Turso, following the interface from the original Astro template with minimal changes.

When it comes to queries and updates (i.e. the DML part of SQL) Astro DB gets mostly out of the way and provides you with Drizzle ORM’s functionality as-is, without introducing new abstractions on top to complicate your life.

#Alternative: using your own data source

While Astro DB is great for greenfield projects, you may well already have a database, an e-commerce backend, a choice of CMS, etc. or just prefer a different approach to database access. Admittedly, magical type inference and all that jazz isn’t everyone’s cup of tea, especially the type warnings which can get enigmatic sometimes (this is a common gripe with TypeScript, really).

#Adding authentication

For the scope of the demo, we’ve laid out a bare-bones authentication mechanism. You don’t need to setup any extra software for it, but only define two environment variables in your .env file:

• Set BASIC_PASSWORD for a shared password-based login.
• Set JWT_SECRET to a random token (see .env.example for how to generate it).

This is needed for two reasons:

• To allow updating products, but only by logged-in users.
• For authenticating API calls to the POST|GET /api/revalidate endpoint. We’ll go back to this one later.

#How authentication works

Once a website visitor successfully logs-in via the /login page, the server sets a cookie with a JWT, based on your secret. This cookie is then used to verify relevant actions done via the browser.

For API calls, the endpoint checks for the presence of a valid JWT in the standard Authorization: Bearer <...> request header.

#Quick updates with Pokemons

With a database and a splash of authentication now in place, let’s provide a very quick way for logged-in users to update a product and witness revalidation in action.

Instead of a building a whole admin interface-like mechanism, we’ve added a one-click update button for products in the /search page that is available to logged-in users. This button adds a random Pokemon name to the end of a product’s name, or replaces that name with another one if such exists.

Here’s how that looks like in action:

#Next step: implementing caching

With all the infrastructure in place, it’s time for some cache tagging and purging in the final part of the guide.