How to Add PostgreSQL
to an AI-Generated Frontend

Why AI-Generated Frontends Need PostgreSQL

Most AI-generated frontends begin with static state, mock arrays or local browser storage. That is fine for a demo, but it breaks quickly when the app needs real users. Local state cannot support shared data across devices, team collaboration, role-based permissions, audit logs, payments or admin reporting. A database gives your application memory.

PostgreSQL is a strong choice because it is reliable, open source and mature. It supports relational data, constraints, transactions, indexing and JSON features. The official PostgreSQL documentation describes SQL/JSON support for storing, generating and querying JSON data alongside regular relational data, which is useful when AI-generated apps start with flexible or semi-structured data models. PostgreSQL JSON documentation

In simple terms, PostgreSQL lets you model the real business logic behind your AI UI. A “tasks” screen becomes a tasks table. A “customers” dashboard becomes users, organizations, subscriptions and invoices. A “messages” component becomes conversations, participants and message rows. This shift from mock data to structured data is what turns a prototype into a product.

Recommended Architecture

The biggest mistake founders make is trying to connect the frontend directly to PostgreSQL. Do not expose your database connection string in frontend code. Browser code is public. Anyone can inspect bundled JavaScript, read environment variables that are exposed to the client and abuse database credentials.

A safer architecture looks like this:

This separation is important because the backend becomes the gatekeeper. The frontend asks for data. The backend decides whether the user is allowed to access it. The database stores it safely.

Step 1: Audit the AI-Generated Frontend

Before adding PostgreSQL, inspect the generated code. Find every place where the app uses hardcoded arrays, mock data or local state as if it were a real database. Search for files with names like mockData.ts, data.js, constants.ts or components that contain arrays directly inside the UI.

Make a list of the data entities your app already pretends to have. For example:

• Users: name, email, password hash, role and profile image.
• Projects: title, description, owner, status and timestamps.
• Tasks: title, priority, assignee, due date and completion status.
• Invoices: customer, amount, payment status and invoice number.
• Messages: sender, receiver, content, read status and thread ID.

This audit is the bridge between UI design and database design. If the frontend already has cards, tables and forms, it is telling you what database tables may be needed.

Step 2: Design the PostgreSQL Schema

Start with a small, normalized schema. Do not create one giant table that stores everything as JSON just because the prototype is flexible. PostgreSQL supports JSON, but relational tables with foreign keys are usually better for core business entities because they protect data consistency.

A simple task app might begin like this:

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  password_hash TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'user',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  description TEXT,
  status TEXT NOT NULL DEFAULT 'active',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE tasks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  priority TEXT NOT NULL DEFAULT 'medium',
  is_completed BOOLEAN NOT NULL DEFAULT false,
  due_date DATE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Add indexes for columns you will filter or join often, such as owner_id, project_id, email and created_at. Use constraints to prevent bad data instead of relying only on frontend validation. Frontend validation improves user experience; database constraints protect the system.

Step 3: Choose Prisma or node-postgres

In a Node.js backend, two common ways to connect to PostgreSQL are Prisma and node-postgres. Prisma gives you a schema-driven ORM, generated client and migration workflow. The Prisma PostgreSQL connector maps Prisma models to PostgreSQL tables, and newer Prisma versions configure the database URL through environment-based configuration. Prisma PostgreSQL documentation

node-postgres, also known as pg, is a lower-level PostgreSQL client for Node.js. It gives you direct SQL control and supports connection pools. Its documentation also explains TLS/SSL configuration for secure database connections. node-postgres SSL documentation

For most AI-generated SaaS MVPs, Prisma is often easier to maintain. For complex analytics, legacy SQL or high-control backend systems, pg may be a better fit.

Step 4: Create the Node.js API Layer

The backend API should sit between the frontend and PostgreSQL. A minimal Express route could receive a request from the AI-generated frontend, validate it, check authentication and then write to PostgreSQL.

import express from "express";
import cors from "cors";
import { Pool } from "pg";

const app = express();

app.use(cors({
  origin: process.env.FRONTEND_URL,
  credentials: true
}));

app.use(express.json());

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: process.env.NODE_ENV === "production"
    ? { rejectUnauthorized: true }
    : false
});

app.post("/api/projects", async (req, res) => {
  const { title, description } = req.body;

  if (!title || title.length < 3) {
    return res.status(400).json({ message: "Project title is required." });
  }

  const ownerId = req.user?.id; // set this from your auth middleware

  const result = await pool.query(
    "INSERT INTO projects (owner_id, title, description) VALUES ($1, $2, $3) RETURNING *",
    [ownerId, title, description || null]
  );

  return res.status(201).json(result.rows[0]);
});

app.listen(process.env.PORT || 5000);

Notice the important pattern: the frontend never receives the database password. It only calls /api/projects. The backend reads DATABASE_URL from a private environment variable and performs the database operation.

Step 5: Replace Mock Data with API Calls

Once the backend exists, update the AI-generated frontend to fetch real data. If the frontend currently imports an array like mockProjects, replace that import with an API request.

async function loadProjects() {
  const response = await fetch(`${import.meta.env.VITE_API_URL}/api/projects`, {
    credentials: "include"
  });

  if (!response.ok) {
    throw new Error("Failed to load projects");
  }

  return response.json();
}

From here, handle loading states, empty states and error states in the UI. This is where many AI-generated interfaces need cleanup. A real app must show the user what is happening when data is loading, when the server fails and when the user has no records yet.

Step 6: Add Authentication and Authorization

Persistent data creates security responsibilities. You must make sure users can access only their own records unless they have admin permissions. OWASP lists Broken Object Level Authorization as a major API risk because APIs commonly expose object IDs, and each function that accesses a data source using a user-provided ID needs authorization checks. OWASP API Security Top 10

In practice, that means this query is risky:

SELECT * FROM projects WHERE id = $1;

A safer user-scoped query is:

SELECT * FROM projects
WHERE id = $1 AND owner_id = $2;

That small difference prevents a logged-in user from guessing another project ID and reading someone else’s data. This is one of the most important changes when moving from a demo frontend to a real SaaS application.

Step 7: Use Migrations, Not Manual Production Edits

During the prototype phase, it is tempting to open a database console and manually change tables. Avoid that habit for production. Use migrations so your database changes are repeatable, reviewable and reversible.

With Prisma, you define models in your Prisma schema and create migrations. With SQL-first tools, you can use migration systems such as Knex, node-pg-migrate or a managed platform’s migration workflow. The important principle is that schema changes should live in version control alongside your code.

Good migrations answer three questions: what changed, when it changed and how to reproduce it in another environment. This matters when you have local, staging and production databases.

Deployment Checklist

Before deploying your AI-generated frontend with PostgreSQL, review the following checklist:

• Move all database credentials to private server-side environment variables.
• Use HTTPS in production and SSL/TLS for hosted PostgreSQL connections when required.
• Restrict CORS to your real frontend domain, not *.
• Add authentication middleware before protected API routes.
• Scope database queries by the authenticated user or organization.
• Use parameterized queries or an ORM to reduce SQL injection risk.
• Create database indexes for high-traffic filters and joins.
• Run migrations before deploying backend code that depends on new columns.
• Add logging for backend errors without leaking secrets to users.
• Back up the database and test restore procedures.

Common Mistakes to Avoid

The first mistake is exposing DATABASE_URL to the frontend. Only environment variables explicitly intended for the browser should be public. Your database connection string must stay on the server.

The second mistake is skipping authorization. Authentication answers “who are you?” Authorization answers “are you allowed to access this record?” A user being logged in does not automatically mean they can read every row in the database.

The third mistake is treating AI-generated code as final architecture. AI can create a useful starting point, but production systems need review. Look for duplicated logic, missing validation, insecure routes, weak error handling and database queries that do not match the real business model.

Frequently Asked Questions

Can I connect PostgreSQL directly to a React frontend?

Technically, browser code can call APIs, but it should not connect directly to PostgreSQL. Use a backend API because database credentials must remain private and every request needs validation and authorization.

Should I use Prisma or raw SQL?

Use Prisma if you want schema models, generated types and a structured migration workflow. Use raw SQL with node-postgres if you need maximum SQL control or already have strong SQL experience.

Is PostgreSQL good for AI-generated SaaS apps?

Yes. PostgreSQL is a strong choice for SaaS applications because it supports relational modeling, constraints, transactions, indexing and JSON features. It is especially useful when your prototype needs to become a stable multi-user product.

What is the safest first step?

Start by auditing your frontend’s mock data. Convert that mock data into a small database schema, then create a backend API route for one feature before migrating the entire app.

Final Thoughts

AI-generated frontends are excellent for speed, but databases require discipline. To add PostgreSQL properly, do not just paste a connection string into your UI. Design the data model, create a backend API, protect credentials, validate input, scope queries by user and manage schema changes with migrations.

The winning workflow is not “AI instead of engineering.” It is AI-assisted engineering. Use AI to move faster, then use PostgreSQL, Node.js and secure backend patterns to make the product real.