Joinery

Build PostgreSQL SELECTs and views by clicking. Joins are found for you, the SQL writes itself.

English · Русский

---

Joinery reads the schema of a PostgreSQL database, works out how the tables are related through foreign keys, and assembles a query while you tick tables and columns. The SQL and the rows it returns are both on screen the whole time, so you see what you are getting before you save anything. When the query looks right, one button turns it into a view.

-- two tables ticked, nothing typed:
SELECT e.last_name,
       e.first_name,
       (SELECT string_agg(DISTINCT p.title::text, ', ' ORDER BY p.title::text)
          FROM employee_position ep
          JOIN position p ON p.id = ep.position_id
         WHERE ep.employee_id = e.id) AS positions
FROM employee e
WHERE NOT e.archived
ORDER BY e.last_name

Features

• Finds the joins — relationships come from pg_constraint, i.e. from real foreign keys. Tick orders and customers and the ON clause is already there.
• Bridges through link tables — pick two tables with no direct relationship and Joinery inserts the table that connects them. The bridge carries the relationship only; its own columns stay out of the result.
• One-to-many in a single cell — when a join would duplicate rows (one employee, three positions), it writes a correlated string_agg subquery instead: one row, Fitter, Foreman, Inspector in the cell. A switch on the card gives you the duplicated form back.
• Conditions counted from today — up to today, overdue, in the next 60 days, this month compile to CURRENT_DATE, which PostgreSQL stores in the view definition. The view is still correct next month and nobody has to edit a date.
• Filters built from your data — values in the dropdown are read from the column itself, so you pick Warehouse 4 from a list instead of guessing the spelling. Boolean and date columns also get one-click suggestions.
• Rename and reorder columns — type a heading next to a column and it becomes the AS alias; drag the preview headings (or use ◀ ▶) to set the order of the SELECT list.
• Sample values everywhere — every column shows its type and a real value from the table, so you can tell status from state without opening anything.
• Live preview — the result table refreshes on every click, 15 rows by default and 200 on request.
• Saves the view two ways — CREATE OR REPLACE VIEW straight into the database, and/or appended to your migrations file so it survives a rebuild.
• Read-only by design — the only statements it can issue are SELECT, CREATE OR REPLACE VIEW and COMMENT ON VIEW. No INSERT, no DROP, not anywhere in the code.
• Bilingual — the whole interface is English and Russian, switched with one flag.
• Zero dependencies — one Python file, three files for the page. No pip, no virtualenv, no build step.

How it works

Start it and the browser opens on a welcome screen: it says which database is configured and whether it answers, then waits for you to press Open. Joinery never connects on its own.

Inside, the page is three columns.

1 · Tables	every table and view in the schema, with comments and a search box. Click to take one, click again to drop it, take as many as you like.
2 · Columns & filters	one card per table: columns with checkboxes, types, sample values, the rename field, and the as rows / comma-separated switch. Below, the filters and the sort order, all from dropdowns.
3 · Query & preview	the generated SQL, and under it real rows that recompute as you click.

Joins are discovered from single-column foreign keys. Composite keys are not followed, and a table with no declared constraints is invisible to the join finder — Joinery says there is no relationship rather than guessing one from column names.

Installation

git clone https://github.com/Yadek/joinery.git
cd joinery
python3 joinery.py

That is the whole installation. A browser opens at http://127.0.0.1:8765 with the connection form; fill it in once and the settings are written to joinery.config.json next to the script, mode 600 because it may hold a password. That file is gitignored.

Requirements: Python 3.7+ (the one shipped with macOS and most Linux distributions is fine), the psql client, and PostgreSQL 10+. If the database runs in a container, docker replaces psql and nothing needs to be installed on the host.

You can skip the form entirely:

python3 joinery.py --dsn postgresql://user:password@localhost:5432/mydb --schema public
python3 joinery.py --docker my_postgres --user postgres --database mydb

Command-line options

Option	Meaning
--dsn URL	postgresql://user:password@host:port/database
--host, --port-db, --user, --database	the same, field by field
--docker NAME	run psql inside this container instead of connecting over TCP
--schema NAME	schema to read (default public)
--views-file PATH	file that Append to file writes to
--config PATH	a different settings file — how you keep several databases apart
--port N	port of the local page (default 8765)
--lang en|ru	interface language
--no-browser	do not open a browser, for autostart

Command-line values override the config file for that run; --schema and --lang are also saved, so you do not repeat them.

Configuration file

See config.example.json. In short:

{
  "mode": "direct",
  "host": "localhost",
  "port": 5432,
  "user": "postgres",
  "database": "postgres",
  "password": "",
  "schema": "public",
  "views_file": "/path/to/views.sql",
  "lang": "en"
}

With "mode": "docker" the host/port pair is replaced by "container", and Joinery runs docker exec <container> psql instead of connecting over the network.

Generated SQL

Through a link table, many-side collapsed. employee and position have no direct relationship; employee_position connects them, and a plain join would turn one employee into three rows:

SELECT e.last_name,
       (SELECT string_agg(DISTINCT p.title::text, ', ' ORDER BY p.title::text)
          FROM employee_position ep
          JOIN position p ON p.id = ep.position_id
         WHERE ep.employee_id = e.id) AS positions
FROM employee e

Flip the switch to as rows and you get the ordinary shape instead:

SELECT e.last_name,
       p.title AS positions
FROM employee e
LEFT JOIN employee_position ep ON ep.employee_id = e.id
LEFT JOIN position p ON p.id = ep.position_id

Dates that stay current. Nine relative conditions compile against CURRENT_DATE:

WHERE c.valid_until <  CURRENT_DATE                                    -- overdue
WHERE c.valid_until <= CURRENT_DATE                                    -- up to today
WHERE (c.valid_until >= CURRENT_DATE AND c.valid_until < CURRENT_DATE + 61)   -- next 60 days
WHERE (d.day >= date_trunc('month', CURRENT_DATE)
   AND d.day <  date_trunc('month', CURRENT_DATE) + interval '1 month')       -- this month

PostgreSQL stores the expression, not its value — pg_get_viewdef confirms it afterwards. On timestamp columns the upper bound is written half-open (>= CURRENT_DATE AND < CURRENT_DATE + 1) so a row stamped 14:30 today is not silently dropped; plain date columns use the simpler form.

Security

• The HTTP server binds to 127.0.0.1 and serves only the files in web/. Attempts to walk out of that directory are rejected.
• Passwords go to psql through the environment (PGPASSWORD, or docker exec -e PGPASSWORD with no value so it is inherited). They never enter the argument list and never show up in ps.
• The config file is written with mode 600. It is still plaintext: leave the password empty and let psql use ~/.pgpass if you would rather not have it on disk.
• The password is never sent back to the browser — the status endpoint reports only whether one is stored.
• There is no authentication on the local page. Treat it like any other local dev server: anything that can reach port 8765 on your machine can query the database with your credentials. Connecting with a read-only role is a perfectly reasonable thing to do.

Autostart

Joinery is an ordinary foreground process (Ctrl+C stops it). To keep it always available at localhost:8765, start it at login.

macOS — LaunchAgent

Save as ~/Library/LaunchAgents/com.joinery.plist, then launchctl load ~/Library/LaunchAgents/com.joinery.plist:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0"><dict>
  <key>Label</key><string>com.joinery</string>
  <key>ProgramArguments</key>
  <array>
    <string>/usr/bin/python3</string>
    <string>/full/path/to/joinery/joinery.py</string>
    <string>--no-browser</string>
  </array>
  <key>RunAtLoad</key><true/>
  <key>KeepAlive</key><true/>
</dict></plist>

Linux — systemd user unit

~/.config/systemd/user/joinery.service, then systemctl --user enable --now joinery:

[Service]
ExecStart=/usr/bin/python3 /full/path/to/joinery/joinery.py --no-browser
Restart=always

[Install]
WantedBy=default.target

Limitations

• PostgreSQL only.
• Single-column foreign keys only, as described above.
• No INNER JOIN choice, no UNION, no GROUP BY … HAVING, no window functions, no CASE. The output is plain text — copy it out and finish the query in your editor.
• One schema per session.
• The preview is a sample, not the whole result (200 rows on request, 1000 hard cap).

Tech stack

Area	Choice
Language	Python 3.7+, standard library only
Server	http.server + ThreadingTCPServer, bound to 127.0.0.1
Database transport	the psql client, or docker exec … psql
Schema reading	one JSON blob from pg_class / pg_attribute / pg_constraint
Front end	plain HTML, CSS and JavaScript — no framework, no bundler
API	small JSON-over-POST: status, connect, schemas, schema, sample, values, rows, create-view, append-view
Localization	EN/RU tables at the top of web/app.js

Repository layout

joinery/
├─ joinery.py            # server: schema reading, psql transport, endpoints
├─ web/
│  ├─ index.html         # markup
│  ├─ app.js             # join discovery, SQL generation, interface
│  ├─ styles.css         # light and dark theme
│  └─ logo.svg           # favicon and in-app mark
├─ assets/joinery.svg    # logo for this page
└─ config.example.json   # copy to joinery.config.json and edit

No build step: edit a file, reload the page.

Contributing

Issues and pull requests are welcome. Two things to keep in mind: the code targets Python 3.7 and uses only the standard library, and every interface string exists in both languages, so a new one means a new entry in both tables at the top of web/app.js.

License

MIT