Reaching a Remote DB From Your Laptop: One Mental Model

Summary

• Scenario
  • At my company, all kinds of services lean on Postgres, ClickHouse, Spark, and MongoDB as data sources. I often need to drop a quick query to pull data across them. Normally I do this through DBeaver, a GUI.
  • But over the last year or two the CLI tools have gotten comfortable (k9s, lazygit, lazydocker…). I started wanting to not jump out to another app. I just wanted the result inside one terminal session, as fast as possible.

• Motivation

  • Remote DBs almost always sit inside a VPC or GKE and are not reachable from the public internet. So there is always a tunnel in the middle that “borrows” the remote DB onto a local port. I had been improvising this path and re-guessing the flags every time. What was missing was a mental model.
  • So I wanted to take the “laptop → remote DB” path apart one layer at a time. This way, the switch between DB CLIs becomes just the same model with a different spelling.

• Goal

  • Build one end-to-end mental model: understand what a connection is, open the tunnel, see how docker packages it, then connect and query.
  • Any database’s CLI is then just that same model with a different spelling.

Why there is an extra tunnel in the middle A remote DB usually is not exposed to the public internet (it lives inside a VPC or GKE). So you first use a Cloud SQL Proxy or kubectl port-forward to map it onto 127.0.0.1:PORT. Your client then connects to it as if it were a local DB. The tunnel is transparent. The client thinks it is talking to localhost.

Roadmap

1. What a connection really is: five coordinates. Any DB connection is host / port / user / password / database. A URI just strings them into one line. → a connection is five coordinates, the URI is canonical, flags decompose it
2. Open the tunnel: get a local port that reaches the remote DB. Managed Cloud SQL goes through the Auth Proxy, and a DB inside k8s goes through kubectl port-forward. Two auth paths, the same 127.0.0.1:PORT outcome. → two tunnels: Cloud SQL proxy vs port-forward
3. How docker packages the tunnel. These proxy tools run as docker-compose services, so where the image comes from and how files get into the container decide what actually runs. → docker packaging: image vs build, COPY bakes in, volumes mount live
4. After connecting: query. The only fork after connecting is whether you pass a query flag: with one it is one-shot, without one it is a REPL. → one-shot query vs interactive REPL
5. Worked example: MySQL over the proxy. String all of the above together and run it once. → MySQL remote connection and flags, MySQL: four ways to feed inline SQL

The spine: the five coordinates / URI name the DB you want, and the tunnel borrows the remote DB onto localhost:PORT. Docker packages that tunnel. After connecting, a query flag decides one-shot vs REPL, and the MySQL cards run it as one complete example.

Closing

By the end I noticed an annoying limitation: every database has its own CLI. The concept is identical, and only the spelling of the flags changes. Since the concept is the same, could I wrap a TUI over it and still run fast inside a terminal session? Turns out yes: I settled on sqlit, a TUI that follows the same “lazy” style as the k9s / lazygit I already use. I am migrating my workflow from DBeaver to sqlit, and so far so good. The full write-up of that payoff, with a before-and-after of my per-DB tooling, is here: sqlit, a keyboard-only DBeaver.

---

References

• PostgreSQL libpq connection: https://www.postgresql.org/docs/current/libpq-connect.html
• Cloud SQL Auth Proxy: https://cloud.google.com/sql/docs/postgres/sql-proxy
• Docker Compose build reference: https://docs.docker.com/reference/compose-file/build/
• MySQL command-line client: https://dev.mysql.com/doc/refman/8.0/en/connecting.html
• sqlit: https://github.com/Maxteabag/sqlit