8.4k

Connect Next.js to Xata

Learn how to connect your Next.js application to Xata's PostgreSQL platform. Get started with Next.js and PostgreSQL for scalable web applications.

Prerequisites

  • Xata account and project setup
  • Node.js 18+ installed
  • Basic knowledge of React and Next.js

Setup Xata Database

First, set up your Xata database with the common e-commerce dataset:

  1. Create a project and branch in the Xata console
  2. Navigate to the Queries tab in your branch
  3. Run the following SQL commands to create the initial schema:
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  price NUMERIC(7,2) NOT NULL,
  rating INTEGER
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE order_items (
  order_id INTEGER REFERENCES orders(id),
  product_id INTEGER REFERENCES products(id),
  qty INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Create Next.js Project

Create a new Next.js project with TypeScript:

npx create-next-app@latest my-xata-app --typescript --tailwind --eslint
cd my-xata-app

Initialize Xata Project

Initialize your Xata project configuration:

xata init

This will create a .xata directory with your project configuration.

Store Your Credentials

Create a .env.local file in your project root to store your Xata connection string:

# .env.local
DATABASE_URL="postgresql://username:password@host:port/database"

Get your connection string from the Xata console or CLI:

xata branch url

Important: Never commit your .env.local file to version control. Add it to your .gitignore file.

Install Dependencies

Install the PostgreSQL client:

npm install postgres
npm install -D @types/pg

Configure Database Connection

Create a database configuration file:

// lib/db.ts
import postgres from 'postgres';

const connectionString = process.env.DATABASE_URL;

if (!connectionString) {
  throw new Error('DATABASE_URL environment variable is required');
}

export const sql = postgres(connectionString);

Create API Routes

Create API routes for your products:

// app/api/products/route.ts
import { NextResponse } from 'next/server';
import { sql } from '@/lib/db';

export async function GET() {
  try {
    const products = await sql`SELECT * FROM products`;
    return NextResponse.json(products);
  } catch (error) {
    return NextResponse.json({ error: 'Failed to fetch products' }, { status: 500 });
  }
}

export async function POST(request: Request) {
  try {
    const body = await request.json();
    const [newProduct] = await sql`
      INSERT INTO products (name, price, rating)
      VALUES (${body.name}, ${body.price}, ${body.rating})
      RETURNING *
    `;
    return NextResponse.json(newProduct, { status: 201 });
  } catch (error) {
    return NextResponse.json({ error: 'Failed to create product' }, { status: 500 });
  }
}
// app/api/products/[id]/route.ts
import { NextResponse } from 'next/server';
import { sql } from '@/lib/db';

export async function GET(
  request: Request,
  { params }: { params: { id: string } }
) {
  try {
    const [product] = await sql`SELECT * FROM products WHERE id = ${params.id}`;
    
    if (!product) {
      return NextResponse.json({ error: 'Product not found' }, { status: 404 });
    }
    
    return NextResponse.json(product);
  } catch (error) {
    return NextResponse.json({ error: 'Failed to fetch product' }, { status: 500 });
  }
}

Create Components

Create a products list component:

// components/ProductsList.tsx
'use client';

import { useState, useEffect } from 'react';
import type { Product } from '@/lib/schema';

export default function ProductsList() {
  const [products, setProducts] = useState<Product[]>([]);
  const [loading, setLoading] = useState(true);

  useEffect(() => {
    fetch('/api/products')
      .then(res => res.json())
      .then(data => {
        setProducts(data);
        setLoading(false);
      })
      .catch(error => {
        console.error('Error fetching products:', error);
        setLoading(false);
      });
  }, []);

  if (loading) return <div>Loading products...</div>;

  return (
    <div className="grid grid-cols-1 md:grid-cols-2 lg:grid-cols-3 gap-4">
      {products.map((product) => (
        <div key={product.id} className="border rounded-lg p-4">
          <h3 className="text-lg font-semibold">{product.name}</h3>
          <p className="text-gray-600">${product.price}</p>
          {product.rating && (
            <div className="flex items-center mt-2">
              <span className="text-yellow-500">★</span>
              <span className="ml-1">{product.rating}/5</span>
            </div>
          )}
        </div>
      ))}
    </div>
  );
}

Update Main Page

Update your main page to display products:

// app/page.tsx
import ProductsList from '@/components/ProductsList';

export default function Home() {
  return (
    <main className="container mx-auto px-4 py-8">
      <h1 className="text-3xl font-bold mb-8">Xata E-commerce Store</h1>
      <ProductsList />
    </main>
  );
}

Run the Application

Start your development server:

npm run dev

Visit http://localhost:3000 to see your application.

Next Steps