@pgkit/admin


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

!demo

Contents

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

Introduction

$3

- 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

$3

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:

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

Locally:

`bash
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

`ts
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:


`ts
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.

`ts
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