GORM Studio System Design

Every developer needs to look at the data. Did the migration run, why does that row have a null in it, what did the importer actually write. The usual answers are a separate desktop client that each person installs and configures, or psql, which does not exist in a container, or an admin panel built for the business rather than for the rows.

None of them works where it is most needed. A staging deployment has no desktop on it. A container has no client installed. A customer-hosted instance cannot have a database port opened to a laptop. In exactly the situations where looking at the data would settle an argument in thirty seconds, the usual tools are the ones not there.

A browser mounted inside the application solves that, and immediately creates a worse problem than the one it solved. It is a route that can read every table, and if it runs SQL it is a route that can drop them. Mounted without thought it is a database console on the public internet. The interesting part of this design is not the grid, it is everything that constrains it.

There is also a schema problem that is specific to Go. The database knows the column types and the foreign keys. The GORM models know the relationship names, the Go types, and the many-to-many join tables. Neither alone describes the schema a developer is thinking about, so the tool has to read both and reconcile them.

The system has to be able to:

• Discover the schema from the database and from the model structs, and merge them.
• Browse, filter, sort and page any table without writing SQL.
• Follow a relationship from a row to the rows on the other side of it.
• Create, edit and delete records, when that has been allowed.
• Run a single read query, with the statements that cannot be made safe refused outright.
• Hide tables that must never be exposed, and make others readable but not writable.
• Export the schema and the data, and import both back.
• Generate Go model structs from an existing database.
• Run with no extra service, no installed client and no open database port.

Functional requirements

• Mount on a Gin router with a database handle and a list of models.
• Schema introspection, plus reflection over the registered model structs.
• A paginated grid with sorting, search and column filters.
• Relationship navigation for has one, has many, belongs to and many to many.
• Record create, edit and delete, and bulk delete.
• A raw SQL editor that accepts exactly one statement.
• A blocked keyword list that applies whether or not the instance is read-only.
• Per-table policy: hidden tables and read-only tables.
• An optional scope function, applied to every table query.
• An audit callback for every mutating action.
• Export to SQL, JSON, YAML, DBML, CSV, and an ERD as PNG or PDF.
• Import from SQL, JSON, CSV, Excel and Go struct files, bounded in size, rows and time.
• Per-IP rate limiting on the SQL and import endpoints.

Non-functional requirements

• Safe by refusal, not by intention. DROP, ALTER, TRUNCATE, CREATE, GRANT, ATTACH and the rest are refused by keyword before anything is parsed. A tool that relies on the operator not typing the dangerous thing is not a safe tool.
• One statement, always. the editor splits and counts. Two statements is an error, which is what closes the "harmless select, semicolon, delete" shape.
• Warns when it is dangerous. mounting with no authentication, with writes enabled and no authentication, or with the SQL editor and no authentication each log a warning at startup. The default is usable; the unsafe default is loud.
• Bounded imports. a size cap of 32 MiB, a row cap of 100,000 and a 30 second timeout, all applied before the body is read. An import endpoint without them is a decompression bomb away from an outage.
• Honest about what it cannot scope. a scope function filters table queries and cannot filter raw SQL. The documentation says to disable the editor when a scope is set rather than implying the boundary holds.
• No CGo, no extra service. a pure Go SQLite driver and an embedded frontend. One binary, which is the only reason it is present on the staging box at all.

Numbers for a mid-sized deployment. They are here to size the thing, not to predict your traffic: change an assumption and the sums below move with it.

Assumptions

One mount call. A schema reader that merges two sources, a query layer that every read passes through, and a set of guards that no path can go around.

Core components

• Mount. takes the router, the database handle, the model list and a config. Registers the routes under a prefix and logs a warning for each dangerous combination it was given.
• Schema reader. introspects the database for columns, types and keys, and reflects over the model structs for relationship names, Go types and join tables. Neither source describes the whole schema.
• Table policy. hidden tables are omitted from the schema, 404 on direct request, left out of exports, and refused by the SQL editor when a statement names one. Read-only tables can be browsed and never mutated.
• Scope function. applied to every table query, for an operator who should see only part of the data. It cannot apply to raw SQL, and that is stated rather than glossed.
• SQL classifier. strips comments, splits statements, refuses anything that is not exactly one, checks the leading keyword against the blocked list, and decides read or write.
• Import pipeline. size cap, row cap and timeout, with a parser per format. The caps come first.
• Audit callback. every mutating action: a row written, a bulk delete, a raw SQL write, an import. The record of what was done through the back door.
• Embedded frontend. the whole UI compiled into the binary, served with a content security policy that forbids framing and pins script origins.

Request flow

1. 1An operator opens the studio on a staging deployment and types a query. There is no database client on that machine and no port open to it, which is the whole reason this exists.
2. 2The auth middleware runs first, if one was configured. If none was, the startup log already said so three times, because a database browser with no authentication is the failure this design can warn about and cannot prevent.
3. 3The SQL and import endpoints are rate limited per client address, separately from anything the host application does. They are the two that are expensive enough to be worth limiting on their own.
4. 4The statement is classified: comments stripped, statements split, and anything that is not exactly one statement rejected. That single check closes the "innocent select, semicolon, delete from users" shape, which no amount of keyword matching on the first word would catch.
5. 5The leading keyword is checked against the blocked list, which applies whether or not the instance is read-only. VACUUM and REINDEX are on it alongside DROP and ALTER, because VACUUM INTO writes an arbitrary file and is therefore data exfiltration wearing a maintenance command.
6. 6A statement naming a hidden table is refused, and so is a write to a read-only one. Hidden means hidden everywhere: absent from the schema, 404 on a direct request, omitted from exports, and refused here.
7. 7What is left is one statement that is allowed to run. If a scope function is configured it does not apply here, which is why the configuration documentation says to disable this editor when a scope is set rather than letting an operator assume the boundary holds.
8. 8A write is reported to the audit callback. Changes made through a database browser are exactly the changes that have no application log line, so this is the only record there will be.

Data flow

• The schema comes from two places and neither is sufficient. The database has the columns and the foreign keys; the models have the relationship names, the Go types and the many-to-many join tables.
• Reads go through the same query layer, so the scope function and the table policy apply to the grid, the relationship view and the exports alike.
• Exports stream. Building a ten gigabyte file in memory before sending it is the obvious implementation and it is an out-of-memory kill.
• Imports are capped before the body is read, which is the difference between refusing a decompression bomb and absorbing one.

Core types

Design principles applied

• Refuse categories, do not sanitise strings. there is no safe escaping of DROP TABLE. The blocked list refuses whole classes of statement and the single-statement rule removes the way around it.
• Hidden has to mean hidden in every path. the schema, the row endpoint, the export and the SQL editor. A table omitted from three of them and present in the fourth is worse than one that was never hidden, because somebody is relying on it.
• Say what a guard does not cover. the scope function cannot filter raw SQL. Documenting that is the difference between a known limitation and a breach.
• Cap before reading. an import limit checked after buffering the upload has already lost. The byte cap is applied to the request before the body is consumed.
• Warn loudly about the unsafe default. it mounts with no authentication because that is right for a laptop, and it says so three times at startup because it is not right anywhere else.

Patterns

• This is an operator tool, so concurrency is a handful of people and throughput never matters.
• What does matter is that it shares the application’s connection pool. A sort on an unindexed column over ten million rows is a long query holding a connection that a request wanted.
• Exports stream, so memory stays flat while the database read does not. A large export during business hours is felt by users.
• Schema discovery is proportional to table count and worth caching, since it runs on every page load and the schema changes at deploy time.
• Imports are capped in three dimensions at once, because any one of them alone leaves a way to tie up the process.
• The honest scaling answer is that it belongs on staging and on a locked-down production route, not as a general-purpose query tool. Nothing in the design makes it a replacement for a read replica and a proper client.

What breaks first

• Mounted with no authentication. the single catastrophic failure. The library warns three times at startup and cannot do more, because refusing to mount would break the laptop case it is good at.
• Sharing the application connection pool. an expensive browse competes with live traffic, and it is run by someone who is investigating a problem, which is when the system is already under strain.
• The SQL editor defeats the scope. a scope function filters table queries and cannot filter raw SQL, so leaving both on gives an operator a way around the boundary.
• Count on a huge table. the pager needs a total, and a filtered count over tens of millions of rows is far more expensive than the page itself.
• Writes with no application context. a row edited here skips every validation, hook and audit the application would have applied. The audit callback records that it happened, not that it was correct.

What to do about it

• Always mount behind auth, and prefer read-only. basic auth at minimum, the application’s own admin check where there is one, and read-only plus no SQL editor unless a specific task needs otherwise.
• Give it its own database connection. a separate pool, with a low maximum and a statement timeout, so a careless browse cannot take connections from live traffic.
• Disable the editor whenever a scope is set. the two features are mutually exclusive in practice, and the configuration should be treated as if it enforced that.
• Hide the tables that hold secrets. encrypted columns, tokens, credentials. Hidden rather than read-only, because the risk is reading them.
• Treat the audit callback as required in production. wire it to the application’s audit log, not to stdout. A change made here is otherwise the one change with no record.