Add PostgreSQL Integration Example #129

NarayanaSabari · 2025-11-06T10:55:56Z

Add PostgreSQL Integration Example

Adds a PostgreSQL integration example following the guidance from Discussion #125.

Changes

Adds examples/integrations/postgresql/ with:

README.md

Docker-based setup instructions
Connection configuration
Troubleshooting guide

postgresql-example.deepnote (24 cells)

Connection setup and testing
CRUD operations on e-commerce database
Complex queries (JOINs, aggregations, views)
Geographic and time-series analysis

sample_data.sql

E-commerce schema (customers, products, orders, order_items)
Foreign keys, constraints, indexes
Two analytical views (order_summary, product_sales)
Sample data: 10 customers, 12 products, 14 orders

Testing

Tested locally with:

PostgreSQL 15 (Docker)
Python 3.11, psycopg2-binary 2.9.9, pandas 2.1.3
All cells execute successfully

Checklist

Thanks for reviewing! I'm excited to contribute to the Deepnote community and happy to make any adjustments based on your feedback. 🚀

cc: @mikayelh

Summary by CodeRabbit

Documentation
- Added a comprehensive PostgreSQL integration guide with setup steps, Docker and local instructions, workflows, and troubleshooting.
New Features
- Added a hands-on PostgreSQL example notebook demonstrating connection, data exploration, CRUD operations, and analytical queries.
- Included a complete sample e‑commerce database (schema, seed data, views, and indexes) for testing and demos.
Chores
- Updated .gitignore to exclude Jupyter notebook checkpoint files.

coderabbitai · 2025-11-06T10:56:05Z

📝 Walkthrough

Walkthrough

The PR adds a PostgreSQL integration example: a README with setup and troubleshooting, a Deepnote notebook demonstrating connection, queries, aggregations, inserts/updates and optional cleanup using psycopg2/pandas, and a sample_data.sql that creates an e-commerce schema (4 tables, generated subtotal column, 2 views, and 5 indexes). .gitignore is updated to exclude Jupyter checkpoint files (.ipynb_checkpoints).

Sequence Diagram(s)

sequenceDiagram
    autonumber
    participant Dev as Developer / Notebook
    participant Setup as sample_data.sql (DB setup)
    participant DB as PostgreSQL (Docker/local)
    participant Client as psycopg2 / pandas

    rect rgba(200,230,255,0.3)
    Note right of Dev: Prepare environment (README + Docker)
    Dev->>Setup: Run sample_data.sql (create schema, seed, views, indexes)
    Setup->>DB: Execute DDL & DML
    DB-->>Setup: Success notices
    end

    rect rgba(220,255,220,0.3)
    Note right of Dev: Notebook runtime cells
    Dev->>Client: Open connection (host/port/creds)
    Client->>DB: CONNECT
    DB-->>Client: Connection OK / version
    Dev->>Client: Run queries (SELECT, JOINs, aggregates)
    Client->>DB: SQL queries
    DB-->>Client: Result sets
    Client-->>Dev: DataFrames / prints
    Dev->>Client: INSERT / UPDATE (transactional)
    Client->>DB: DML + COMMIT
    DB-->>Client: Acknowledgement
    Dev->>Client: Optional cleanup (DROP statements commented)
    end

Pre-merge checks

✅ Passed checks (3 passed)

Check name	Status	Explanation
Description Check	✅ Passed	Check skipped - CodeRabbit’s high-level summary is enabled.
Title check	✅ Passed	The title accurately and clearly summarizes the main change—adding a PostgreSQL integration example to the examples directory.
Docstring Coverage	✅ Passed	No functions found in the changed files to evaluate docstring coverage. Skipping docstring coverage check.

📜 Recent review details

Configuration used: CodeRabbit UI

Review profile: ASSERTIVE

Plan: Pro

Disabled knowledge base sources:

Linear integration is disabled by default for public repositories

You can enable these sources in your CodeRabbit configuration.

📥 Commits

Reviewing files that changed from the base of the PR and between fbbccbb and 1515322.

📒 Files selected for processing (1)

examples/integrations/postgresql/postgresql-example.deepnote (1 hunks)

🔇 Additional comments (7)

examples/integrations/postgresql/postgresql-example.deepnote (7)

507-507: Previous sortingKey collision resolved.

The duplicate sortingKey issue from the prior review has been fixed. Cleanup blocks now use unique values (a23, a24).

Also applies to: 537-537

126-148: Connection pattern appropriate for notebook demo.

Each cell opens and closes its own connection, which is correct for independent notebook execution.

Also applies to: 162-187, 201-226, 232-254, 268-297, 311-333, 346-369, 383-403

418-454: Correct transaction and parameterization.

INSERT uses parameterized queries, proper commit/rollback, and RETURNING clause.

468-496: Correct UPDATE implementation.

Uses parameterization, conditional WHERE, and proper transaction handling.

73-112: Good error handling with helpful diagnostics.

Connection test includes try/except with troubleshooting guidance for common failures.

510-535: Cleanup safety mechanism well-designed.

Destructive DROP statements are commented out by default with explicit safety warning.

48-54: Hardcoded credentials appropriate for Docker demo.

Connection parameters match documented Docker setup. Acceptable for local examples.

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share

_{Comment @coderabbitai help to get the list of available commands and usage tips.}

coderabbitai

Actionable comments posted: 5

📜 Review details

Configuration used: CodeRabbit UI

Review profile: ASSERTIVE

Plan: Pro

Disabled knowledge base sources:

Linear integration is disabled by default for public repositories

You can enable these sources in your CodeRabbit configuration.

📥 Commits

Reviewing files that changed from the base of the PR and between b7c4907 and cf47ea8.

📒 Files selected for processing (4)

.gitignore (1 hunks)
examples/integrations/postgresql/README.md (1 hunks)
examples/integrations/postgresql/postgresql-example.deepnote (1 hunks)
examples/integrations/postgresql/sample_data.sql (1 hunks)

🧰 Additional context used

📓 Path-based instructions (1)

**/*.{md,yml,yaml}

📄 CodeRabbit inference engine (CLAUDE.md)

Use Prettier formatting for Markdown and YAML files

Files:

examples/integrations/postgresql/README.md

🪛 markdownlint-cli2 (0.18.1)

examples/integrations/postgresql/README.md

45-45: Fenced code blocks should be surrounded by blank lines

(MD031, blanks-around-fences)

83-83: Fenced code blocks should be surrounded by blank lines

(MD031, blanks-around-fences)

🔇 Additional comments (1)

.gitignore (1)

12-12: Standard and appropriate addition.

.ipynb_checkpoints is the standard directory created by Jupyter for checkpoint data. This entry correctly excludes it from version control.

examples/integrations/postgresql/postgresql-example.deepnote

examples/integrations/postgresql/README.md

examples/integrations/postgresql/sample_data.sql

Yggdrasill501 · 2025-11-10T14:48:39Z

Hello @NarayanaSabari thank you for the contribution, if you want this to get merged, please sort out the coderabbit review and than we can give you approve. If you are interested in more examples you can do example usage of MongoDB or Clickhouse.

Signed-off-by: narayanasabari <sabarinarayanakg@proton.me>

NarayanaSabari · 2025-11-12T05:11:28Z

Hello @Yggdrasill501 ,

I’ve resolved all the CodeRabbit review comments. ✅
I’ll now start working on adding more example usages, including MongoDB and ClickHouse.

Please let me know if there are any other issues or features you’d like me to focus on besides the examples.

Thanks for the guidance! 🙌

Artmann · 2025-11-17T10:01:41Z

Hi @NarayanaSabari!

This is an excellent start 💪

One of the core features of Deepnote is the SQL blocks and the integration system, so it would be great if we could highlight that here.

Let's structure the example and README in the following way:

Set up a PostgreSQL instance. (Your instructions here are great)
Configuring the integration. (Using "Manage integration")
How to query PostgreSQL using SQL blocks.
How to query PostgreSQL using Python.

For the Python part, you shouldn't need to install any dependencies, as we ship SQLAlchemy as part of the Deepnote kernel.

You can use that together with the integration system to set up a connection:

from sqlalchemy import create_engine
from sqlalchemy.engine.url import URL

import os

psql = create_engine(URL.create(
  "postgresql+psycopg2",
  username=os.environ["DEEPNOTE_STAGING___READONLY_USER"],
  password=os.environ["DEEPNOTE_STAGING___READONLY_PASSWORD"],
  host=os.environ["DEEPNOTE_STAGING___READONLY_HOST"],
  port=os.environ["DEEPNOTE_STAGING___READONLY_PORT"],
  database=os.environ["DEEPNOTE_STAGING___READONLY_DATABASE"],
)

Then you can use it to perform your query:

import pandas as pd

query = """
SELECT *
FROM users
"""

df = pd.io.sql.read_sql_query(query, psql)

df

added Postgresql example deepnote

cf47ea8

NarayanaSabari requested a review from a team as a code owner November 6, 2025 10:55

coderabbitai bot requested changes Nov 6, 2025

View reviewed changes

NarayanaSabari added 2 commits November 12, 2025 10:30

resolved coderabit reviews

fbbccbb

Signed-off-by: narayanasabari <sabarinarayanakg@proton.me>

Sortimngkey changed

1515322

coderabbitai bot approved these changes Nov 12, 2025

View reviewed changes

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Add PostgreSQL Integration Example #129

Add PostgreSQL Integration Example #129

Uh oh!

Uh oh!

Walkthrough

Sequence Diagram(s)

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Add PostgreSQL Integration Example #129

Are you sure you want to change the base?

Add PostgreSQL Integration Example #129

Uh oh!

Conversation

Uh oh!