PostgreSQL Database

Pre-requisites

Cloudflare does not host PostgreSQL databases, you need to setup your own PostgreSQL database.

See a list of professional PostgreSQL hosting providers.

If you prefer to use Cloudflare services, you can use Cloudflare D1 which is built on SQLite, see our Database section.

Setup

Make sure to use the @nuxthub/core module, see the installation section for instructions.

nuxt.config.ts

export default defineNuxtConfig({
  modules: ['@nuxthub/core'],
});

The module ensures that you can connect to your PostgreSQL database using Cloudflare TCP Sockets

Install the postgres NPM package in your project.

npx nypm i postgres

That's it, you can now use the postgres package to connect to your PostgreSQL database.

Please note that in order to use pg a minimum version of 8.13.0 is required, alongside wrangler 3.78.7 or later and compatibility_date = "2024-09-23".

Usage

We can add our PostgreSQL database connection in our .env file.

.env

NUXT_POSTGRES_URL=postgresql://user:password@localhost:5432/database

Then, we can create a usePostgres() server util to connect to our database in our API route.

server/utils/postgres.ts

import postgres from 'postgres'

export function usePostgres () {
  if (!process.env.NUXT_POSTGRES_URL) {
    throw createError('Missing `NUXT_POSTGRES_URL` environment variable')
  }

  return postgres(process.env.NUXT_POSTGRES_URL as string, {
    ssl: 'require'
  })
}

We can now use the usePostgres() function to connect to our database in our API route.

server/api/db.ts

export default eventHandler(async (event) => {
  const sql = usePostgres()

  const products = await sql`SELECT * FROM products`

  // Ensure the database connection is closed after the request is processed
  event.waitUntil(sql.end())
  return products
})

You may notice that we don't import usePostgres(). This is because Nuxt auto-imports the exported variables & functions from server/utils/*.ts when used.

Hyperdrive is a Cloudflare service that accelerates queries you make to existing databases, making it faster to access your data from across the globe. By maintaining a connection pool to your database within Cloudflare’s network, Hyperdrive reduces seven round-trips to your database before you can even send a query: the TCP handshake (1x), TLS negotiation (3x), and database authentication (3x).

Hyperdrive is only available on the Workers Paid plan ($5/month), learn more.

To use Hyperdrive in your Nuxt application:

Create a Hyperdrive configuration
Add your Hyperdrive ID in your nuxt.config.ts file

nuxt.config.ts

export default defineNuxtConfig({
  modules: ['@nuxthub/core'],
  hub: {
    bindings: {
      hyperdrive: {
        // <BINDING_NAME>: <HYPERDRIVE_ID>
        POSTGRES: 'your-hyperdrive-id'
      }
    }
  }
})

Update our usePostgres() function to use the POSTGRES binding when available.

server/utils/postgres.ts

import type { Hyperdrive } from '@cloudflare/workers-types'
import postgres from 'postgres'

export function usePostgres() {
  const hyperdrive = process.env.POSTGRES as Hyperdrive | undefined
  const dbUrl = hyperdrive?.connectionString || process.env.NUXT_POSTGRES_URL
  if (!dbUrl) {
    throw createError('Missing `POSTGRES` hyperdrive binding or `NUXT_POSTGRES_URL` env variable')
  }

  return postgres(dbUrl, {
    ssl: !hyperdrive ? 'require' : undefined
  })
}

Hyperdrive is currently not available in development mode at the moment. We are working on a solution to make it work in development mode and remote storage with an upcoming hubHyperdrive().

Deploy your application with the NuxtHub CLI or Admin to make sure the Hyperdrive bindings are set.

You can now access your PostgreSQL database from anywhere in the world at maximum speed.