PostgreSQL (Prisma)

developer-ready Express.js and Prisma ORM integration for PostgreSQL with modular REST APIs, CRUD-ready schema, and fully extensible data access layer.

postgresqlprismaexpressdatabasejavascriptormcrud

v1.0.0database

Overview

A scalable PostgreSQL database integration built with Prisma ORM and Express.js. It provides a clean REST API for data access, automatic schema migrations, and modular service layers for rapid backend development.

Installation

CLI

npx backternity add postgresql-prisma-express-js

What This Does

Installs a Prisma + Express setup with a connected PostgreSQL schema and modular routes for data operations.

Files & Folders Created

File / Path	Description
/prisma/schema.prisma	Prisma schema with a sample User model
/src/config/prismaClient.js	Initializes Prisma client
/src/services/userService.js	Handles Prisma data operations (CRUD)
/src/routes/user.js	Express routes for user endpoints
/server.js.example	Example Express server
/.env.example	Environment configuration for PostgreSQL

Files to be modified

File / Path	Description
server.js	Adds /api/user route integration
.env	Adds DATABASE_URL configuration

Configuration

# Database Configuration
DATABASE_URL=postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=public
PORT=8000

Frontend Integration

This frontend example shows how to interact with the Prisma + PostgreSQL backend API endpoints to create and view users. It demonstrates dynamic data fetching, form handling, and live updates using Next.js.

GET/api/user

Fetches all users stored in the PostgreSQL database via Prisma.

POST/api/user

Creates a new user record in the database.

GET/api/user/:id

Fetches a single user by ID.

Example

// app/components/PrismaUserManager.jsx
'use client';
import { useState, useEffect } from 'react';

export default function PrismaUserManager() {
  const [users, setUsers] = useState([]);
  const [form, setForm] = useState({ name: '', email: '' });
  const [loading, setLoading] = useState(false);
  const [message, setMessage] = useState('');

  async function fetchUsers() {
    try {
      const res = await fetch('/api/user');
      const data = await res.json();
      if (data.success) setUsers(data.users);
    } catch (err) {
      console.error('Failed to fetch users:', err);
    }
  }

  useEffect(() => {
    fetchUsers();
  }, []);

  async function handleSubmit(e) {
    e.preventDefault();
    setLoading(true);
    setMessage('');

    try {
      const res = await fetch('/api/user', {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify(form)
      });
      const data = await res.json();

      if (res.ok && data.success) {
        setMessage('✅ User created successfully');
        setForm({ name: '', email: '' });
        fetchUsers();
      } else {
        setMessage('❌ ' + (data.error || 'Error creating user'));
      }
    } catch (error) {
      setMessage('Error: ' + error.message);
    } finally {
      setLoading(false);
    }
  }

  return (
    <div className="max-w-md mx-auto bg-black/20 border border-white/10 rounded-xl p-6 text-white space-y-5">
      <h2 className="text-xl font-semibold">PostgreSQL + Prisma User Manager</h2>

      <form onSubmit={handleSubmit} className="space-y-3">
        <input
          type="text"
          placeholder="Name"
          value={form.name}
          onChange={(e) => setForm({ ...form, name: e.target.value })}
          className="w-full px-3 py-2 rounded-md bg-white/10 border border-white/20 focus:outline-none"
          required
        />
        <input
          type="email"
          placeholder="Email"
          value={form.email}
          onChange={(e) => setForm({ ...form, email: e.target.value })}
          className="w-full px-3 py-2 rounded-md bg-white/10 border border-white/20 focus:outline-none"
          required
        />
        <button
          type="submit"
          disabled={loading}
          className="w-full bg-emerald-500 hover:bg-emerald-600 text-white font-semibold py-2 rounded-md transition-colors"
        >
          {loading ? 'Creating...' : 'Add User'}
        </button>
      </form>

      {message && <p className="text-sm text-amber-300">{message}</p>}

      <div className="space-y-2">
        <h3 className="text-lg font-semibold border-b border-white/10 pb-1">All Users</h3>
        <ul className="divide-y divide-white/10">
          {users.length > 0 ? (
            users.map((u) => (
              <li key={u.id} className="py-2 flex justify-between">
                <span>{u.name}</span>
                <span className="text-white/70 text-sm">{u.email}</span>
              </li>
            ))
          ) : (
            <li className="text-sm text-white/50">No users found.</li>
          )}
        </ul>
      </div>
    </div>
  );
}

Usage

// Example server-side usage
const { getUsers, createUser, getUserById } = require('./src/services/userService');

(async () => {
  const newUser = await createUser({ name: 'John Doe', email: 'john@example.com' });
  console.log('Created user:', newUser);

  const users = await getUsers();
  console.log('All users:', users);
})();