Astro with PostgreSQL

#webdev #astro #postgres #diploi

Astro is a simple yet powerful front-end framework for content-driven websites. The common way of building websites with Astro involves getting dynamic data from an external CMS or API. If you do not need a fully fledged CMS, but would still like to dynamically change some content on your Astro site, all you might need is a PostgreSQL database.

Getting Started

I will be using Diploi to bootstrap this project. Diploi comes with an Astro project and PostgreSQL DB pre-configured, so no setup required. Simply choose the "Astro" template when creating a new project.

You can also follow the manual installation guide on the official Astro documentation. It can be found here: https://docs.astro.build/en/install/auto/.

In both cases, you will get an example project with a single demo page. We will continue from there.

Connecting to a PostgreSQL DB

Let's get our Astro page to talk with PostgreSQL. First thing we need to do is to install the node-postgres (or "pg" for short) library from NPM. We will use this client to query our PostgreSQL DB.

npm i pg
npm i --save-dev @types/pg

If you do not plan on using TypeScript, you can leave out the @types/pg package.

The Diploi Astro template comes with an optional PostgreSQL database, with ENV variables pre-configured. If you are not using Diploi, you may specify different ENV variables, or handle the configuration is some other way.

Here are the ENV variables Diploi gives us by default:

POSTGRES_HOST
POSTGRES_PORT
POSTGRES_DB
POSTGRES_USER
POSTGRES_PASSWORD

Create a db.ts file in the /src folder:

import pg from 'pg';

// Make sure we DO NOT "prerender" this function to allow the ENV variables to update on the fly
export const prerender = false;

const client = new pg.Client({
  host: import.meta.env.POSTGRES_HOST,
  port: import.meta.env.POSTGRES_PORT,
  database: import.meta.env.POSTGRES_DB,
  user: import.meta.env.POSTGRES_USER,
  password: import.meta.env.POSTGRES_PASSWORD,
});

await client.connect()

export { client as db };

This file will initialize our DB client, so we can use it in any Astro component!

Let's create an example component that queries the current time from the DB. Create a DB.astro file in the /src/components directory:

---
import { db } from '../db';

const { rows } = await db.query('SELECT NOW() as "time"');
---

<time>{rows[0].time}</time>

This component will connect to the PotsgreSQL DB, query the current time with NOW(), and finally render it on the page!

Try it out by adding it to the /src/pages/index.astro page. At the top, import the component with:

import DB from '../components/DB.astro';

And then include it anywhere on the page with:

<DB />

Wherever you placed you component, you should see a timestamp that updates on each page load!

That's It?

You can see how simple it is to talk to the DB directly, no HTTP API needed! This way you could use your PostgreSQL DB as a simple CMS, or just have some information easily changeable without code changes.

This data is not as dynamic as it may seem though. In development mode, Astro will query this data every time the page is loaded. In production, however, Astro will create a static build that will not query the DB on every page load. It only queries it once when the production build is made. If you do need the data to be updated dynamically, you can do so by enabling the fairly new SSR support in Astro.

Enabling SSR in Astro

SSR, or server-side rendering, means on-demand rendering on the server when a page is requested. This means that Astro will not build a static HTML version of your entire site, but acts more like a PHP server that builds the page on-demand when a user visits it.

On Diploi, the only change you have to make is a single line in your index.astro page:

export const prerender = false;

By exporting this line right after any import statements, we tell Astro to opt this page out of static rendering, and to render it on demand whenever a client requests it.

If you are not using Diploi, you will have to configure an SSR adapter for your Astro project. You will find a guide to do so in the official Astro documentation at https://docs.astro.build/en/guides/server-side-rendering/..

Diploi?

I have mentioned Diploi quite a few times in this blog post. Diploi is a single SaaS service for managing, developing and hosting your full application. We have been building Diploi for a while now, and would love for you to have a go (for free of course)!

Trying new frameworks is as-easy-as clicking a single button on Diploi, so you can focus on the fun parts, not the setup.