PostgreSQL CRUD App with Python & Neon

Posted on December 29, 2025 • 4 minutes • 843 words

In this session, we’ll build a clean, object-oriented PostgreSQL CRUD application using Python and Neon, a modern serverless PostgreSQL platform. The best part? No local PostgreSQL installation is required.

This article is perfect for:

Python developers learning databases
Backend beginners
Developers preparing for interviews
Anyone exploring cloud PostgreSQL with Neon

Why Neon PostgreSQL?

Neon is a serverless, cloud-based PostgreSQL provider that removes the pain of local database setup. You get:

Instant PostgreSQL databases
Built-in SSL security
Generous free tier
Fully compatible with standard PostgreSQL tools

This makes Neon ideal for demos, side projects, and learning.

What We’re Building

We’ll create a User Management CRUD application that supports:

Create users
Read users
Update user email
Delete users

The project follows real-world backend architecture using:

Models (in-memory schema)
Clean separation of concerns

Tech Stack

Python 3
PostgreSQL (Neon)
psycopg2
Object-Oriented Programming (OOP)

Database Schema

Create the following table in your Neon PostgreSQL database:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Project Structure

postgres_crud_neon/
│
├── .env         # Database credentials
├── db.py        # Database connection
├── model.py     # User model
├── api.py       # CRUD logic
├── main.py      # Demo runner

This structure is similar to how frameworks like Django and FastAPI organize backend code internally.

Step 1: Database Connection (db.py)

We centralize the database connection logic using a factory-style class.

from dotenv import load_dotenv
import os
from urllib.parse import urlparse
import psycopg2

load_dotenv()


class Database:
    @staticmethod
    def get_connection():
        postSQL = urlparse(os.getenv("DATABASE_URL"))
        return psycopg2.connect(
            host=postSQL.hostname,
            user=postSQL.username,
            password=postSQL.password,
            database=postSQL.path.replace("/", ""),
            sslmode="require")

Neon requires SSL, so sslmode="require" is mandatory.

Step 2: User Model (model.py)

The User model represents our in-memory schema. This object is independent of the database.

class User:
    def __init__(self, name, email, age, user_id=None, created_at=None):
        self.id = user_id
        self.name = name
        self.email = email
        self.age = age
        self.created_at = created_at

    def __repr__(self):
        return f"User(id={self.id}, name='{self.name}', email='{self.email}', age={self.age})"

This approach mirrors how ORMs work internally.

Step 3: API Layer (api.py)

The API handles all database interactions. The rest of the app never talks to SQL directly.

Create User

def createUser(user: User):
    conn = Database.get_connection()
    cur = conn.cursor()

    cur.execute(
        """
        INSERT INTO users (name, email, age)
        VALUES (%s, %s, %s)
        RETURNING id, created_at
        """,
        (user.name, user.email, user.age)
    )

    user.id, user.created_at = cur.fetchone()
    conn.commit()
    cur.close()
    conn.close()
    return user

PostgreSQL’s RETURNING clause lets us fetch generated values instantly.

Read Users

def getAllUsers():
    conn = Database.get_connection()
    cur = conn.cursor()

    cur.execute("SELECT id, name, email, age, created_at FROM users ORDER BY id")
    rows = cur.fetchall()

    cur.close()
    conn.close()

    users = [
        User(
            userId=row[0],
            name=row[1],
            email=row[2],
            age=row[3]
            created_at = row[4]
        )
        for row in rows
    ]

    return users

def getUser(userId: int):
    conn = Database.get_connection()
    cur = conn.cursor()

    cur.execute(
        """
        SELECT id, name, email, age,created_at
        FROM users
        WHERE id = %s
        """,
        (userId,)
    )

    row = cur.fetchone()
    cur.close()
    conn.close()

    user = User(
        userId=row[0],
        name=row[1],
        email=row[2],
        age=row[3],
        created_at = row[4]
    )
    return user

Update User Email

def updateUserEmail(userId: int, newEmail: str):
    conn = Database.get_connection()
    cur = conn.cursor()

    cur.execute(
        """
        UPDATE users
        SET email = %s
        WHERE id = %s
        """,
        (newEmail, userId)
    )

    conn.commit()
    cur.close()
    conn.close()

Delete User

def deleteUser(userId: int):
    conn = Database.get_connection()
    cur = conn.cursor()

    cur.execute(
        """
        DELETE FROM users
        WHERE id = %s
        """,
        (userId,)
    )

    conn.commit()
    cur.close()
    conn.close()

Step 4: main.py

The main.py file provides a menu-driven CLI for demonstrating CRUD operations.

import api
import model

def show_menu():
    print("\n===== PostgreSQL CRUD with Python & Neon =====")
    print("1. Create User")
    print("2. View All Users")
    print("3. Update User Email")
    print("4. Delete User")
    print("5. Exit")


while True:
    show_menu()
    choice = input("Choose an option: ")

    if choice == "1":
        name = input("Name: ")
        email = input("Email: ")
        age = int(input("Age: "))

        saved_user = model.User(name,email,age)
        api.createUser(saved_user)

        print(f"User created --> {saved_user}")

    elif choice == "2":
        # get all users
        users = api.getAllUsers()

        print("\n--- Users ---")
        for user in users:
            print(user)

    elif choice == "3":
        userId = int(input("User ID: "))
        new_email = input("New Email: ")

        # update logic
        api.updateUserEmail(userId,new_email)

        print("Email updated successfully!")

        # get latest user info
        user = api.getUser(userId)

        if user is not None:
            print(user)

    elif choice == "4":
        userId = int(input("User ID: "))

        # delete
        api.deleteUser(userId)

        print("User deleted successfully!")

    elif choice == "5":
        print("Goodbye!")
        break

    else:
        print("Invalid option, try again!")

Common Mistake to Avoid

When passing parameters to psycopg2, always use tuples:

(user_id,)  # correct

Passing a single integer directly will raise a runtime error.

Conclusion

This implementation provides a clean, modular approach to PostgreSQL operations in Python. The separation of concerns (database connection, models, and operations) makes the code maintainable and scalable.