8.4k

Connect Remix to Xata

Learn how to connect your Remix application to Xata's PostgreSQL platform. Get started with Remix and PostgreSQL for modern, full-stack web apps.

Prerequisites

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

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 Remix Project

Create a new Remix project:

npx create-remix@latest
cd my-xata-app
npm install

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 file in your project root to store your Xata connection string:

# .env
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 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:

// app/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 Loader for Products

Create a loader to fetch products:

// app/routes/_index.tsx
import type { LoaderFunction } from '@remix-run/node';
import { useLoaderData } from '@remix-run/react';
import { json } from '@remix-run/node';
import { sql } from '~/db';

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

export default function Index() {
  const { products } = useLoaderData<typeof loader>();
  return (
    <main className="container mx-auto px-4 py-8">
      <h1 className="text-3xl font-bold mb-8">Xata E-commerce Store</h1>
      <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>
    </main>
  );
}

Run the Application

Start your development server:

npm run dev

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

Next Steps