Chapter 11 — Databases

The Database section gives you a basic admin UI for the four most common engines and a generic Query Editor for ad-hoc work. It lives in the Preview → Database submenu (some features may be marked Beta).

Menu layout

• Database Manager — central registry; pick a connection and drill in.
• MySQL Management — per-engine tab with MySQL-specific tools.
• PostgreSQL Management — same, for Postgres.
• MongoDB Management — same, for Mongo.
• Redis Management — same, for Redis.
• Query Editor — multi-tab SQL workspace.
• DB Connection Manager — saved connection registry.

Database Manager

Open from Preview → Database → Database Manager.

The hub. Left panel lists saved connections by engine; click one to open it on the right with the engine-appropriate UI (table browser for SQL engines, key browser for Redis, collection browser for Mongo).

Buttons across the top:

• New Connection — adds a connection of any supported engine.
• Query — opens the Query Editor pointed at the selected connection.
• Disconnect / Reconnect — toggle the current connection.

Saving connections

Preview → Database → DB Connection Manager is the central registry.

Each connection has:

• Name — friendly label.
• Engine — MySQL, PostgreSQL, MongoDB, Redis.
• Host / Port — engine defaults (3306 / 5432 / 27017 / 6379) are pre-filled. Override as needed.
• Username / Password — saved in the vault.
• Default database — optional.
• TLS — enable for hosted DBs that require it.

Per-engine tabs

MySQL Management

Open from Preview → Database → MySQL Management or via Database Manager.

• Databases / Tables tree.
• Structure view — columns, types, indexes, foreign keys.
• Data view — paged table editor. Inline edits write back as UPDATE.
• SQL view — drop straight into the Query Editor scoped to this connection.
• Users & Privileges — list current users, grant/revoke. WRITE operations require confirmation.
• Status — SHOW STATUS, SHOW PROCESSLIST.

PostgreSQL Management

Equivalent to MySQL but with Postgres-specific extras:

• Schemas separate from databases.
• Roles in place of MySQL users.
• Extensions browser — install/remove with confirmation.
• EXPLAIN ANALYZE button on any SELECT in the data view.

MongoDB Management

NoSQL — different vocabulary:

• Databases → Collections → Documents tree.
• Document view in JSON. Edit inline; save with Ctrl+S.
• Indexes subtab — create/drop.
• Aggregation — pipeline builder with JSON stages.

Redis Management

Key-value:

• Key browser, scoped by pattern (user:*, session:*).
• Per-type editors: String, Hash, List, Set, ZSet, Stream.
• TTL editor on every key.
• CLI subtab — interactive Redis CLI in a pane.

Query Editor

Open from Preview → Database → Query Editor or from any per-engine tab.

A multi-tab SQL workspace:

• One tab = one query buffer.
• Top of each tab: Connection dropdown (pick which saved DB), Run (F5), Run Selection (F8), Explain.
• Results in a paged table at the bottom.
• Export Results… writes CSV / JSON / Excel.

Syntax highlighting honours the engine. Autocomplete suggests tables, columns, and keywords.

Keyboard shortcuts (when the editor is focused):

Saved queries land in ~/.aidaide/sql/ by default.

WRITE confirmations

The same READ/WRITE classification used in LCOT applies here:

• SELECT — runs immediately.
• INSERT / UPDATE / DELETE / DROP / TRUNCATE / ALTER / CREATE — confirmation dialog showing the SQL and an estimated affected-row count (when AidaIDE can derive one from EXPLAIN).

Run Selection (F8) runs only what is highlighted, useful when you have a destructive statement next to a SELECT and only want the SELECT.

Workflows

"I need to peek at production data without breaking anything"

1. Add a read-only DB user on the server.
2. DB Connection Manager → New with those credentials.
3. Open in Query Editor. The WRITE confirmations will fail anyway because of permissions, but the principle is "least privilege at the credential layer."

"Quickly find a column across all tables"

1. MySQL/PostgreSQL Management → connection → schema → Search… (top-right magnifier).
2. Search matches table names, column names, and column comments.

"Export a query result to my team"

1. Run the query in Query Editor.
2. Export Results… → Excel (or CSV).
3. The file lands wherever you point. UTF-8 encoded by default.

Terms & Setup

Connection vs database

A connection is an authenticated session to a database server. A database (or schema, or namespace) is a logical container inside that server. One connection → many databases/schemas/collections.

TLS

Transport Layer Security. Encrypts traffic between AidaIDE and the database server. Required by most cloud-hosted DBs (RDS, Cloud SQL, Atlas, Upstash). Enable in the connection's TLS tab; provide CA cert if the server uses a private CA.

"I do not have a database to play with"

• SQLite — bundled with Python; create a file with sqlite3 mydb.db. AidaIDE's Database Manager will pick it up if you set Engine = SQLite (Beta — limited features).
• Docker — docker run -e MYSQL_ROOT_PASSWORD=root -p 3306:3306 mysql:8 gives you MySQL in 30 seconds. Add postgres:16, mongo:7, or redis:7 for the others.
• Cloud free tiers — PlanetScale (MySQL), Supabase (Postgres), MongoDB Atlas, Upstash (Redis) all have generous free tiers.

"My password worked yesterday and not today"

• Server-side password change is the usual cause.
• Some hosted DBs rotate IPs and require allow-listing yours — check the provider's network policy.
• Re-enter the password in DB Connection Manager — if the vault entry got corrupted, this fixes it.

Performance tips

• Use Run Selection for large scripts so you can step through.
• The result grid pages 1,000 rows at a time by default — change in Settings → Database → Page Size.
• Closing the per-engine tab closes its connection. For long-running scripts, prefer Query Editor (its connections persist).

Privileges and safety

WRITE confirmation is in the UI; it is not a substitute for proper DB user permissions. For anything connected to a production DB:

• Use a read-only account in the connection if you only need to view.
• Use a read-write account only in a dedicated connection labelled clearly.
• Never paste production credentials into a connection named "dev."

"The MongoDB Document editor will not save"

• Documents are validated as JSON before save. A missing comma kills it.
• Numeric IDs (_id set as int) get coerced. If you have ObjectId(...), keep it in the $oid shorthand.
• If you replace a doc, Mongo replaces the whole doc (not just changed fields). Use the Update Fields button for a partial update instead.