Connect Astro to Postgres on Neon

If you do not have one already, create a Neon project. Save your connection details including your password. They are required when defining connection settings.

1. Navigate to the Projects page in the Neon Console.
2. Click New Project.
3. Specify your project settings and click Create Project.

1. Create an Astro project if you do not have one. For instructions, see Getting Started, in the Astro documentation.
2. Add project dependencies using one of the following commands:npm install @neondatabase/serverlessnpm install postgresnpm install pg

Astro requires an adapter to query databases at request time. Run:

npx astro add node

This enables on-demand rendering (also known as server-side rendering or SSR) so your pages can fetch fresh data on each request.

Add a .env file to your project directory and add your Neon connection string to it. You can find the connection string for your database by clicking the Connect button in the Console nav to open the Connect to your branch modal. For more information, see Connect from any application.

DATABASE_URL="postgresql://<user>:<password>@<endpoint_hostname>.neon.tech:<port>/<dbname>?sslmode=require&channel_binding=require"

Create a reusable database client that you can import throughout your application. This approach centralizes your database configuration and follows best practices for code organization.

Create a new file at src/lib/neon.ts (or src/lib/neon.js) with the following code:

import { neon } from '@neondatabase/serverless';

export const sql = neon(import.meta.env.DATABASE_URL);

import postgres from 'postgres';

export const sql = postgres(import.meta.env.DATABASE_URL, { ssl: 'require' });

import { Pool } from 'pg';

export const pool = new Pool({
  connectionString: import.meta.env.DATABASE_URL,
  ssl: true
});

There are multiple ways to make server side requests with Astro. See below for two of those options: Page Components (.astro files) and Server Endpoints (API Routes).

Page Components (.astro files)

In your .astro page components (for example, src/pages/index.astro), you can query the database in the frontmatter section (between the --- fences). Import the database client from your utility file:

---
import { sql } from '../lib/neon';

const response = await sql`SELECT version()`;
const data = response[0].version;
---

{data}

---
import { sql } from '../lib/neon';

const response = await sql`SELECT version()`;
const data = response[0].version;
---

{data}

---
import { pool } from '../lib/neon';

const client = await pool.connect();

let data = null;

try {
  const response = await client.query('SELECT version()');
  data = response.rows[0].version;
} finally {
  client.release();
}
---

{data}

Run the app

When you run npm run dev you can expect to see something like the following when you visit localhost:4321:

PostgreSQL 17.7 on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14+deb12u1) 12.2.0, 64-bit

Server Endpoints (API Routes)

In your server endpoints (API Routes) in Astro application, import the database client from your utility file:

// File: src/pages/api/index.ts

import { sql } from '../../lib/neon';

export async function GET() {
  const response = await sql`SELECT version()`;
  return new Response(JSON.stringify(response[0]), {
    headers: { 'Content-Type': 'application/json' },
  });
}

// File: src/pages/api/index.ts

import { sql } from '../../lib/neon';

export async function GET() {
  const response = await sql`SELECT version()`;
  return new Response(JSON.stringify(response[0]), {
    headers: { 'Content-Type': 'application/json' },
  });
}

// File: src/pages/api/index.ts

import { pool } from '../../lib/neon';

export async function GET() {
  const client = await pool.connect();
  let data = {};
  try {
    const { rows } = await client.query('SELECT version()');
    data = rows[0];
  } finally {
    client.release();
  }
  return new Response(JSON.stringify(data), { headers: { 'Content-Type': 'application/json' } });
}

Run the app

When you run npm run dev you can expect to see something like the following when you visit the localhost:4321/api route:

{ version: 'PostgreSQL 17.7 on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14+deb12u1) 12.2.0, 64-bit' }