Chat with Database - Supabase / Vercel Storage

Use natural language to retrieve database values realtime and have ChatGPT analyze data from storage options from Supabase or Vercel - KV or Postgres DB. Host your own Next14 Admin API to securely browse your DB through ChatGPT

This is a Next.js project bootstrapped with create-next-app for storing database connection strings for various side projects.

This API using Next14 enables me to have a custom GPT in ChatGPT where I can query data from various side project. I can now ask ChatGPT how many users do I have and other SQL queries using natural language.

You could extend this API to build a frontend application to manage your side projects.

Get connection string for my project BuyMyRepo, then use it to understand my database. Once understood, show me a table of my users with their app profile details in a nice table.

Deploy on Vercel

Deploy this project in one click:

Check out our Next.js deployment documentation for more details.

Create a Custom GPT in ChatGPT

1. Deploy this project to Vercel with your .env values for a PostgresQL DATABASE_URL and your own API_KEY password
2. Click Actions
3. ***Choose API KEY option for Authorization and select the option "Custom" header
4. Set the custom header value to "api-key"*** & set your API_KEY value here
5. Copy & Paste the following YAML spec into the "Schema":

openapi: 3.1.0
info:
  title: PostgreSQL Database Viewer API
  description: API for interacting with PostgreSQL databases to retrieve schema, query tables dynamically, and manage project connection strings.
  version: "1.0.0"
servers:
  - url: https://postgre-sql-databse-viewer-action.vercel.app
components:
  securitySchemes:
    ApiKeyAuth:
      type: apiKey
      in: header
      name: api-key
  schemas:
    Project:
      type: object
      properties:
        projectName:
          type: string
          description: The name of the project.
        connectionString:
          type: string
          description: The connection string for the PostgreSQL database.
    ErrorResponse:
      type: object
      properties:
        error:
          type: string
        details:
          type: object
security:
  - ApiKeyAuth: []
paths:
  /api/get-db-schema:
    post:
      operationId: getDbSchema
      summary: Get Database Schema
      description: Retrieve the schema (table names and column details) from the specified PostgreSQL database.
      security:
        - ApiKeyAuth: []
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/Project'
      responses:
        '200':
          description: Schema information retrieved successfully.
          content:
            application/json:
              schema:
                type: array
                items:
                  type: object
                  properties:
                    table_name:
                      type: string
                    column_name:
                      type: string
                    data_type:
                      type: string
        '401':
          description: Unauthorized. API key is missing or invalid.
        '500':
          description: Error querying the database schema.
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ErrorResponse'
  /api/query-table:
    post:
      operationId: queryTable
      summary: Query Table
      description: Query a specified table in the PostgreSQL database and retrieve records based on optional conditions.
      security:
        - ApiKeyAuth: []
      requestBody:
        required: true
        content:
          application/json:
            schema:
              type: object
              properties:
                connectionString:
                  type: string
                  description: Connection string for the PostgreSQL database.
                table:
                  type: string
                  description: The name of the table to query.
                fields:
                  type: array
                  items:
                    type: string
                  description: Optional array of fields to retrieve.
                conditions:
                  type: object
                  description: Optional conditions for querying records.
      responses:
        '200':
          description: Records retrieved successfully.
          content:
            application/json:
              schema:
                type: array
                items:
                  type: object
                  additionalProperties:
                    type: string
        '401':
          description: Unauthorized. API key is missing or invalid.
        '500':
          description: Error querying the table.
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ErrorResponse'
  /api/projects:
    post:
      operationId: createProject
      summary: Create Project
      description: Add a new project with its corresponding connection string to the system.
      security:
        - ApiKeyAuth: []
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/Project'
      responses:
        '201':
          description: Project added successfully.
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Project'
        '401':
          description: Unauthorized. API key is missing or invalid.
        '500':
          description: Error adding project.
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ErrorResponse'
  /api/projects/{projectName}:
    get:
      operationId: getProjectConnectionString
      summary: Get Project Connection String
      description: Retrieve the connection string for a project by its name.
      security:
        - ApiKeyAuth: []
      parameters:
        - name: projectName
          in: path
          required: true
          schema:
            type: string
          description: The name of the project.
      responses:
        '200':
          description: Connection string retrieved successfully.
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Project'
        '401':
          description: Unauthorized. API key is missing or invalid.
        '404':
          description: Project not found.
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ErrorResponse'
        '500':
          description: Error retrieving project connection string.
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ErrorResponse'

API Endpoints

1. POST: Add Project and Connection String

curl -X POST http://localhost:3000/api/projects \
-H "api-key: MOCHA1233" \
-H "Content-Type: application/json" \
-d '{"projectName": "MyProject", "connectionString": "postgres://your_db_user:your_db_password@localhost:5432/your_db_name"}'

2. GET: Retrieve Connection String by Project Name

curl -X GET http://localhost:3000/api/projects/MyProject \
-H "api-key: API-KEY-HERE"

3. POST: Fetch Schema Information from a Database

curl -X POST http://localhost:3000/api/fetch-schema \
-H "api-key: API-KEY-HERE" \
-H "Content-Type: application/json" \
-d '{"connectionString": "postgres://your_db_user:your_db_password@localhost:5432/your_db_name"}'

4. POST: Query a Table with Fields and Conditions

curl -X POST http://localhost:3000/api/query-table \
-H "api-key: API-KEY-HERE" \
-H "Content-Type: application/json" \
-d '{"connectionString": "postgres://your_db_user:your_db_password@localhost:5432/your_db_name", "table": "your_table", "fields": ["field1", "field2"], "conditions": {"field1": "value1"}}'

Get started

First, run the development server:

npm run dev
# or
yarn dev
# or
pnpm dev
# or
bun dev

Open http://localhost:3000 with your browser to see the result.

You can start editing the page by modifying app/page.tsx. The page auto-updates as you edit the file.

This project uses next/font to automatically optimize and load Inter, a custom Google Font.

Learn More

To learn more about Next.js, take a look at the following resources:

• Next.js Documentation - learn about Next.js features and API.
• Learn Next.js - an interactive Next.js tutorial.

You can check out the Next.js GitHub repository - your feedback and contributions are welcome!