@pgkit/admin

@pgkit/admin

A no-config admin UI for running queries against PostgreSQL database, with autocomplete for tables, columns, views, functions etc.

Contents

• Contents
• Introduction
  • Features
  • What about pgAdmin
• Get started
• Use as a library
• Deployment
• Auth
• 👽 Future

Introduction

Features

• Schema-aware autocomplete:
  • tables, views, and functions from the database you're working with
  • columns from relevant tables/views/functions (e.g. with | as the cursor in select | from mytable, you will be suggested columns from mytable)
  • autocomplete fully-qualified names for tables not on the current session's search_path
  • suggest table-qualified columns for join statements (e.g. select i| from profile p join settings s on s.profile_id = p.id will suggest s.id and p.id rather than just id)
• results which can be copy-pasted directly into Excel or Google Sheets (using reactgrid)
• Migration management using @pgkit/migrator
• schemainspect explorer of the full schema
• Simple but informative error reporting
• Multi-statement handling - each query gets a collapsible result table
• Queries stored in localStorage so your work is saved between sessions
• Mermaid schema visualization

What about pgAdmin

pgAdmin is great, but when all you want is a query tool it can be a pain to configure. It (usually) requires a custom docker setup, and requires lots of laborious clicking and form-filling to create a connection to a server. It also requires login by default, even for localhost-only dev setups. Once all that's done, it still takes a fair amount of drilling-down and right clicking to just start writing a query.

It also became cleare that, after porting schemainspect, it would be possible to build a tool using fresh(er) UI components* which has more useful autocomplete.

It can also be more easily deployed as an internal admin tool against deployed databases, for existing stacks with a node.js backend (e.g. using express or similar). See the library usage section.

Finally, there is a lot that pgAdmin does that this library doesn't. Right now, @pgkit/admin doesn't run shell scripts, visualise query plans, have any special features around auto-vacuum, etc. (There is a a schema-diffing tool in the pgkit family though) Having said that, there are plenty more features that can be added to this. It's open source, and it's a simple react app. Take a look at the future section and feel free to open a pull request, or create an issue, if you have an idea of something you'd like added.

*codemirror and reactgrid do the heavy UI lifting.

Get started

You can install and run either globally or locally.

Globally:

npm install --global @pgkit/admin
pgkit-admin

Locally:

npm install @pgkit/admin
npx pgkit-admin

You can then set the connection-string header in the UI. When developing against a local database, this is all you'll need to do.

Use as a library

import {getExpressRouter} from '@pgkit/admin'
import express from 'express'

const app = express()

app.use(getExpressRouter())

app.listen(5050)

Deployment

If you would like to deploy the UI - for example, to an internal admin site - you can use it as a library from node.js. You can import a middleware for the API, and for the client static files, or there's a trpc router:

import {appRouter, clientMiddleware} from '@pgkit/admin'
import {createClient} from '@pgkit/client'
import {createExpressMiddleware} from '@trpc/server/adapters/express'

Auth

Auth isn't built in, but is easy to add using almost any auth solution for node.js. @pgkit/admin can run against a local database with no setup needed. If you want to use it against a production database, you are responsible for authenticating database calls.

The simplest usage for local development is to use the UI to set a connection-string header, which will be used by the local server to connect to the database. This is fine for a local db, where the value might be something you're not worried about storing in your browser's localStorage like connection-string: postgresql://. But you likely wouldn't want to (or couldn't) use this method for production. Instead, you can create a server middleware, perform whatever auth checks necessary in a middleware in the backend, and use trpc to create your own middleware, which doesn't get the connection string from headers.

import {appRouter, clientMiddleware} from '@pgkit/admin'
import {createClient} from '@pgkit/client'
import {createExpressMiddleware} from '@trpc/server/adapters/express'
import express from 'express'

const authMiddleware = getMyAuthMiddlewareSomehow() // e.g. https://authjs.dev/reference/express or https://clerk.com/docs/backend-requests/handling/nodejs

const client = createClient(process.env.PG_CONNECTION_STRING)

const apiMiddleware = createExpressMiddleware({
  router: appRouter,
  createContext: () => ({connection: client}),
})

const app = express()

app.use(clientMiddleware)
app.use(authMiddleware)
app.use(apiMiddleware)

app.listen(7003)

express is not a dependency. You can use adapters for any server framework (including a standalone node.js server) with the trpc router. See trpc docs on adapters for examples of how to use with fastify, Next.js, AWS lambda, and edge runtimes.

👽 Future

• SQL file browsing
• Table-based database editing. Add, edit, and remove rows from tables using a Google-sheets like UI
• [Maybe] a typescript script writer. Allow writing and runnning small scripts that use @pgkit/client that allow doing some manipulation in JavaScript.
• Auto-formatting of SQL queries
• Prepared statement support, with variables supplied separately, similar to GraphiQL