tableplus

# TablePlus TablePlus is a native app (Swift on Mac, C# on Windows) known for being incredibly **fast** and **lightweight**. ## When to Use - **Native Multi-Database Administration**: Connecting to PostgreSQL, MySQL, Redis, SQLite, SQL Server, and Cassandra natively. - **Secure Bastion & SSH Tunneling**: Querying internal production and staging databases through bastion jump-hosts securely. - **Inline Table Data Editing**: Modifying column values, adding rows, and previewing generated SQL before committing changes. - **Database Backup & Migration Inspection**: Dumping database schemas, restoring SQL snapshots, and reviewing pending migrations. ## Quick Start ### 1. Open Database via TablePlus CLI URL ```bash # Connect directly to Postgres instance via connection string tableplus "postgres://postgres:secret@localhost:5432/my_database" ``` ### 2. Essential Shortcuts ```text Cmd + T - Open New SQL Tab Cmd + Enter - Run Current Statement Cmd + Shift + E - Run All Statements Cmd + P - Quick Open Table Cmd + S - Commit Staged Changes ``` ## Core Concepts ### Bastion SSH Tunnel Connection Configuration Connecting securely to an Amazon RDS PostgreSQL database via a private VPC jump-host: ```text TablePlus Connection Settings: - Driver: PostgreSQL - Host: prod-db.internal.vpc (or 10.0.4.15) - Port: 5432 - User: db_admin - Database: production_v2 - Password: [SECURE_PASSWORD] SSH Tunnel Tab: - SSH Host: bastion.example.com - SSH Port: 22 - SSH User: ec2-user - Auth Method: SSH Key - Private Key: ~/.ssh/bastion-id_ed25519 ``` ### TablePlus Connection URI Scheme Opening database connections directly from terminal or scripts: ```bash # Connect to local PostgreSQL open "tableplus://postgres:password@localhost:5432/my_app_dev?statusColor=007AFF&env=local" # Connect via custom connection string with SSH Tunnel parameters open "postgresql://admin:secret@prod-db:5432/analytics?sslmode=require" ``` ### Inline Data Modification & Commit Preview - Double-click table in sidebar (e.g. `users`). - Double-click any cell to modify values in-place (turns yellow to indicate uncommitted state). - Press `Cmd + Shift + P` (or `Ctrl + Shift + P`) to **Preview SQL Changes**: ```sql UPDATE "users" SET "is_active" = TRUE WHERE "id" = '9b1deb4d-3b7d-4bad-9bdd-2b0d7b3dcb6d'; ``` - Press `Cmd + S` (`Ctrl + S`) to execute and commit transaction atomically. ## Common Patterns ### Atomic Staged Edits Review **Problem**: Review generated SQL statements before committing GUI cell modifications. **Solution**: Inspect the staged SQL script generated by TablePlus. ```sql -- Generated review script in TablePlus (Cmd + Shift + D) BEGIN; UPDATE "public"."users" SET "status" = 'active' WHERE "id" = '42'; UPDATE "public"."orders" SET "processed" = true WHERE "user_id" = '42'; COMMIT; ``` ### Redis Key Inspection & Scanning **Problem**: Inspect Redis cache keys without blocking single-threaded Redis process. **Solution**: Run safe cursor scanning from TablePlus query workspace. ```bash # TablePlus Redis Console execution SCAN 0 MATCH session:* COUNT 100 ``` ## Best Practices **Do**: - Assign distinct Environment Colors (Red for Production, Orange for Staging, Green for Local) in connection settings. - Enable **Safe Mode** on production database connections to enforce confirmation dialogs for `UPDATE` and `DELETE` queries. - Authenticate bastion hosts using modern Ed25519 SSH keys with passphrase protection. - Preview generated SQL (`Cmd+Shift+P`) before committing inline modifications to production tables. **Don't**: - Store plaintext production database passwords in unencrypted cloud backup profiles. - Run unbounded queries (`SELECT * FROM logs`) on billion-row tables without `LIMIT` clauses. - Share exported TablePlus connection files containing embedded private keys or passwords. ## Troubleshooting | Error | Cause | Solution | | ------------------------------------------------------------------ | ----------------------------------------------------------- | ---------------------------------------------------------------------------------------------- | | `SSH Tunnel: Connection refused` | Bastion host port 22 blocked or SSH key permissions invalid | Verify SSH key has `chmod 600` permissions and bastion security group permits inbound traffic. | | `pq: SSL is required` when connecting to AWS RDS / Heroku Postgres | SSL mode disabled in connection settings | In TablePlus connection settings, set **SSL Mode** to `REQUIRED` or `VERIFY-CA`. | | Query output truncated or slow on large tables | Default limit fetching thousands of heavy columns | Add explicit `LIMIT 100` to query or adjust row pagination limit in TablePlus preferences. | ## References - [TablePlus Documentation](https://tableplus.com/)