Docs › User › Database Tools

🗄 Database Tools

Explore, query, and redesign your database schemas without leaving Oryn.

Overview

Oryn includes two complementary database panels: the Database Explorer, which lets you manage connections, browse live table schemas, and run ad-hoc SQL queries against PostgreSQL, SQLite, or the built-in Oryn BaaS; and the DB Designer, a visual canvas that auto-detects connections from your project's configuration files and renders your entire schema as a draggable entity-relationship diagram. Together they give you a full picture of your data layer — from inspecting a single column to generating a Mermaid ER diagram of every table in your database.

How it works

The Database Explorer stores connection profiles in browser localStorage (passwords are deliberately stripped before saving) and uses Electron IPC (window.oryn.db) to test connections, load schemas, and execute queries via native CLI tools — sqlite3 for SQLite and psql for PostgreSQL, both of which must be on PATH. The DB Designer takes a different route: when opened it calls the .NET backend (POST /api/v1/db-designer/detect) to scan your workspace for connection strings in appsettings*.json and .env files, then introspects the live database through ADO.NET drivers (Npgsql for PostgreSQL, Microsoft.Data.Sqlite, Microsoft.Data.SqlClient for SQL Server) using information_schema queries and PRAGMA statements. Schema alterations are first previewed as safe, server-validated SQL — identifiers are checked against a strict allowlist and data types against an explicit allowlist before any ALTER TABLE is executed inside a transaction.

What you can do

Auto-detect connectionsScans appsettings*.json and .env in your workspace root to find database connection strings automatically.

Visual ER diagram canvasRenders every table as a draggable card with columns, data types, primary-key highlighting, and SVG lines connecting foreign keys.

Schema introspectionReads live table structure, column types, nullability, defaults, primary keys, and foreign key relationships from PostgreSQL, SQLite, and SQL Server.

ALTER TABLE previewRight-click any table card to generate and preview the exact ALTER TABLE SQL for adding, dropping, or altering a column — with server-side validation — before anything is applied.

Apply schema changesExecutes the previewed ALTER statement inside a transaction and rolls back automatically if it fails.

Export SQL DDLGenerates CREATE TABLE statements with all columns, types, nullability constraints, and ADD FOREIGN KEY statements for the current schema.

Export Mermaid ER diagramProduces a Mermaid erDiagram block for the current schema that can be pasted into any Markdown document or wiki.

Connection managerAdd, edit, save, and delete named connection profiles for PostgreSQL, SQLite, or Oryn BaaS from a persistent sidebar.

Connection testVerifies reachability and credentials before you attempt to load a schema or run a query.

Expandable schema browserInspect tables and their column names and types in a collapsible tree after clicking Connect.

Ad-hoc SQL query runnerWrite and execute arbitrary SQL in the Query tab; results are displayed in a scrollable table with row and timing counts.

Oryn BaaS query routingQueries against the built-in Oryn BaaS driver are routed through the Oryn API surface rather than a raw database connection.

How to use it

Open Database Explorer or DB Designer
Click the database icon in the Oryn sidebar to open the Database Explorer panel, or use the DB Designer entry to open the visual canvas. Both are available as full-screen modal panels.
Add or detect a connection
In the Database Explorer, click + in the Connections sidebar to create a new profile: choose a driver (PostgreSQL, SQLite, or Oryn BaaS), fill in the host, port, database, username, and password (or a file path for SQLite), then click Save. In the DB Designer, click Rescan — Oryn will automatically find connection strings in your workspace's appsettings*.json and .env files and populate the Connection dropdown.
Test the connection
Click the Test button. A green 'connected' badge confirms success; a red 'failed' badge shows an error message so you can fix credentials before proceeding.
Load and browse the schema
Click Connect to introspect the live database. The Schema tab populates with a collapsible tree of every table and its columns. In the DB Designer the canvas fills with draggable table cards; foreign key relationships are drawn as blue lines between cards.
Run an ad-hoc query
Switch to the Query tab in the Database Explorer, type any SQL (e.g. SELECT * FROM orders LIMIT 20;), and click Execute. Results appear in a table below with a row count and execution time.
Preview and apply a schema change (DB Designer)
Right-click a table card on the DB Designer canvas and choose Add column, Drop column, or Alter column. The ALTER preview pane on the right shows the exact validated SQL. Review it, then click Apply to execute it inside a transaction.
Export the schema
In the DB Designer toolbar, click Export SQL for a CREATE TABLE DDL script, or Export Mermaid for a Mermaid erDiagram block. The output appears in the Export pane on the right where you can copy it.

Example

You open the DB Designer while working on a Node/PostgreSQL project. Oryn scans your workspace and finds a connection string named DefaultConnection in appsettings.json pointing to your local Postgres instance. The canvas renders eight draggable table cards — users, orders, order_items, products, categories, addresses, sessions, and audit_log — with blue FK lines showing the relationships. You notice the products table is missing a description column. You right-click the products card, choose Add column, and the ALTER preview pane shows: ALTER TABLE "products" ADD COLUMN "new_col" TEXT; You refine the column name to description in the preview, then click Apply. The change executes inside a transaction and the canvas reloads to show the new column. You then click Export Mermaid and paste the resulting erDiagram block directly into your project's README.md.

Admin notes

The DB Designer backend resolves connection strings from the server's configuration under the key DbDesigner:ConnectionStrings:{name} (or a provider-scoped variant DbDesigner:ConnectionStrings:{provider}:{name}). Connections detected from workspace files are used directly by the frontend; for production deployments you may prefer to register connection strings in server configuration rather than relying on plaintext .env or appsettings.json files in the workspace. The Database Explorer stores connection profiles in browser localStorage and explicitly strips passwords before writing — users should be aware that for production databases a system keychain integration is preferable (the UI surfaces this note). The Database Explorer requires sqlite3 (for SQLite) and psql (for PostgreSQL) to be installed and available on PATH on the machine running the Oryn Electron app; if they are missing, Connect will fail with a schema error. SQL Server ALTER COLUMN is supported; SQLite ALTER COLUMN is not (the backend will return an error). The DB Designer applies a strict identifier allowlist (ASCII alphanumeric/underscore, up to 63 characters) and an explicit data-type allowlist on the server before executing any DDL, which prevents SQL injection via schema operations.