Database Viewer SaaS

A modern web-based PostgreSQL database browser that allows you to connect to and browse any PostgreSQL database, either directly or through ngrok TCP tunnels.

Features

Connect to PostgreSQL databases via multiple methods:
- Direct connection with database URL
- ngrok TCP tunnel for accessing localhost databases remotely
Browse and explore database tables
View, edit, and delete table data
Examine table schema and column details
Docker-ready deployment
Modern, responsive user interface

Project Architecture

The application consists of two main components:

Backend Server (Go)
- RESTful API for database operations
- PostgreSQL connection management
- URL sanitization for ngrok connections
- Table data retrieval and manipulation
Frontend Client (Next.js)
- User-friendly web interface
- Connection management UI
- Table browser and data grid
- Responsive design with Tailwind CSS

System Design

┌───────────────┐           ┌───────────────┐           ┌───────────────┐
│               │           │               │           │               │
│  Next.js      │─────────▶│  Go Server    │─────────▶│  PostgreSQL   │
│  Client       │◀─────────│  API          │◀─────────│  Database     │
│               │           │               │           │               │
└───────────────┘           └───────────────┘           └───────────────┘
                                    │                           ▲
                                    │                           │
                                    ▼                           │
                             ┌───────────────┐                  │
                             │               │                  │
                             │  ngrok        │──────────────────┘
                             │  TCP Tunnel   │
                             │               │
                             └───────────────┘

Quick Start

Prerequisites

Docker and Docker Compose
ngrok account (for remote database access)
PostgreSQL database (local or remote)

Running with Docker Compose

Clone the repository:

   git clone https://github.com/yourusername/database-viewer-saas.git
   cd database-viewer-saas

Configure environment variables:

   cp server/.env.example server/.env
   cp client/.env.example client/.env

Start the application with Docker Compose:

   docker-compose up -d

Access the application at http://localhost:3000

Manual Installation

Server Setup

Navigate to the server directory:

   cd database-viewer-saas/server

Install dependencies:

   go mod download

Configure environment variables:

   cp .env.example .env

Start the server:

   go run main.go

Client Setup

Navigate to the client directory:

   cd database-viewer-saas/client

Install dependencies:

   npm install

Configure environment variables:

   cp .env.example .env

Start the development server:

   npm run dev

Access the application at http://localhost:3000

Setting Up ngrok for PostgreSQL Access

ngrok allows you to expose your local PostgreSQL database to the internet securely. Here’s how to set it up:

Step 1: Install ngrok

macOS

brew install ngrok

Linux

sudo snap install ngrok

Windows

choco install ngrok

Or download directly from ngrok.com

Create a free account at ngrok.com
Navigate to the Auth page
Copy your auth token

Step 3: Configure ngrok

ngrok config add-authtoken YOUR_AUTH_TOKEN

Step 4: Start a TCP Tunnel to PostgreSQL

ngrok tcp 5432

This will display output similar to:

Session Status                online
Account                       Your Account
Version                       3.3.1
Region                        United States (us)
Latency                       24ms
Web Interface                 http://127.0.0.1:4040
Forwarding                    tcp://0.tcp.in.ngrok.io:12345 -> localhost:5432

Step 5: Connect Using the Database Viewer SaaS

Open the Database Viewer SaaS application
Select “Connect via ngrok URL”
Enter the following details:
- URL: The forwarding URL (e.g., tcp://0.tcp.in.ngrok.io:12345)
- Username: Your PostgreSQL username
- Password: Your PostgreSQL password
- Database: Your database name
Click “Connect”

Important ngrok Notes

Free tier limits:
- 1 online ngrok agent
- 4 hours session length
- 40 connections/minute
The URL changes each time you restart ngrok
For persistent URLs, consider a paid ngrok plan

How It Works

Connection Flow

User Enters Connection Details
- Database URL/Host
- Credentials
- Database name
Server Processes Connection Request
- Sanitizes URL (removes protocol prefixes)
- Builds PostgreSQL connection string
- Attempts connection with timeout handling
Connection Established
- Server maintains connection pool
- Connection parameters are logged
- Success/failure response sent to client

URL Sanitization Process

The server automatically handles different URL formats:

// Example sanitizeHostURL function
func sanitizeHostURL(inputURL string) string {
    prefixes := []string{"http://", "https://", "tcp://", "postgres://", "postgresql://"}
    url := inputURL
    
    for _, prefix := range prefixes {
        if strings.HasPrefix(strings.ToLower(url), prefix) {
            url = strings.TrimPrefix(url, prefix)
            break
        }
    }
    
    return url
}

Table Data Retrieval

Server queries PostgreSQL for table list
Client displays tables in sidebar
When a table is selected:
- Schema is retrieved
- Pagination parameters are set
- Data query is executed with limits
- Results are formatted and sent to client

Troubleshooting

Common Connection Issues

Connection Refused
- Ensure PostgreSQL is running locally
- Check if firewall is blocking connections
Authentication Failed
- Verify username and password
- Check PostgreSQL authentication settings
ngrok URL Not Working
- Confirm ngrok is still running
- Check if the URL has changed (happens on restart)
- Verify the TCP tunnel is pointing to port 5432
Docker Network Issues
- Ensure containers can communicate
- Check if PostgreSQL is accessible from the server container

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

Fork the repository
Create your feature branch (git checkout -b feature/amazing-feature)
Commit your changes (git commit -m 'Add some amazing feature')
Push to the branch (git push origin feature/amazing-feature)
Open a Pull Request

License

This project is licensed under the MIT License - see the LICENSE file for details.

Acknowledgments

Go - The programming language used for the backend
Next.js - The React framework used for the frontend
ngrok - For secure tunneling capabilities
PostgreSQL - The database system supported