Convert SQL to Org

Why Convert SQL to Org?

Converting SQL files to Org-mode format creates powerful, interactive database documentation for the Emacs ecosystem. Unlike static documentation formats, Org-mode's source code blocks (via Babel) can actually execute SQL queries directly within the document, making it possible to create "living documentation" where schema definitions and queries can be run, tested, and verified without leaving the editor.

Org-mode's hierarchical outline structure is perfectly suited for organizing database documentation. Each database becomes a top-level heading, tables and views become sub-headings, and individual columns and constraints are documented at deeper levels. The collapsible outline means you can expand or collapse any section instantly, navigating a complex database with hundreds of tables as easily as browsing a simple outline.

For developers who use Emacs as their primary editor, SQL-to-Org conversion integrates database knowledge directly into their workflow. Org tables provide spreadsheet-like functionality for documenting data examples, #+BEGIN_SRC sql blocks enable syntax highlighting and inline execution, and properties drawers can store metadata like table sizes, row counts, and last-modified timestamps. The result is a comprehensive knowledge base that combines documentation, executable queries, and project management.

Org-mode also excels as an export hub. Once your SQL documentation is in Org format, you can export it to HTML (for web publishing), PDF (via LaTeX for professional reports), ODT (for LibreOffice), DOCX, and many other formats using Org's built-in export dispatcher. This makes Org-mode a powerful single-source authoring tool for database documentation that needs to be published in multiple formats.

Key Benefits of Converting SQL to Org:

• Executable Queries: SQL blocks can run directly via Org Babel against live databases
• Hierarchical Navigation: Collapsible outline structure for complex schemas
• Org Tables: Schema information in formatted, aligned tables with calculations
• Multi-Format Export: Export to HTML, PDF, LaTeX, ODT, DOCX from one source
• Literate Programming: Mix documentation and executable SQL in one file
• Task Integration: Add TODO items and track database documentation tasks
• Plain Text: Version control friendly, grep-able, future-proof format

Practical Examples

Example 1: Interactive Schema Documentation

Input SQL file (schema.sql):

-- Project management database
CREATE TABLE projects (
    project_id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    owner_id INT NOT NULL,
    budget DECIMAL(12,2),
    start_date DATE,
    status VARCHAR(20) DEFAULT 'planning'
);

Output Org file (schema.org):

#+TITLE: Project Management Database
#+AUTHOR: Generated from SQL

* Projects Table
  :PROPERTIES:
  :TABLE_TYPE: base
  :PRIMARY_KEY: project_id
  :END:

| Column     | Type          | Constraints       |
|------------+---------------+-------------------|
| project_id | SERIAL        | PRIMARY KEY       |
| name       | VARCHAR(200)  | NOT NULL          |
| owner_id   | INT           | NOT NULL          |
| budget     | DECIMAL(12,2) |                   |
| start_date | DATE          |                   |
| status     | VARCHAR(20)   | DEFAULT 'planning'|

#+BEGIN_SRC sql
SELECT * FROM projects WHERE status = 'active';
#+END_SRC

Example 2: Query Notebook

Input SQL file (analysis.sql):

-- Active projects with budget utilization
SELECT p.name,
       p.budget,
       COALESCE(SUM(e.amount), 0) AS spent,
       p.budget - COALESCE(SUM(e.amount), 0) AS remaining
FROM projects p
LEFT JOIN expenses e ON p.project_id = e.project_id
WHERE p.status = 'active'
GROUP BY p.project_id, p.name, p.budget
ORDER BY remaining ASC;

Output Org file (analysis.org):

#+TITLE: Database Analysis Queries

* Active Projects with Budget Utilization

This query shows budget vs. actual spending
for all active projects.

#+BEGIN_SRC sql :exports both
SELECT p.name,
       p.budget,
       COALESCE(SUM(e.amount), 0) AS spent,
       p.budget - COALESCE(SUM(e.amount), 0) AS remaining
FROM projects p
LEFT JOIN expenses e ON p.project_id = e.project_id
WHERE p.status = 'active'
GROUP BY p.project_id, p.name, p.budget
ORDER BY remaining ASC;
#+END_SRC

*Tables:* projects, expenses
*Output:* Project name, budget, spent, remaining

Example 3: Database Runbook

Input SQL file (maintenance.sql):

-- Step 1: Check table sizes
SELECT relname AS table_name,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

-- Step 2: Vacuum analyze large tables
VACUUM ANALYZE projects;
VACUUM ANALYZE expenses;

-- Step 3: Reindex if needed
REINDEX TABLE projects;

Output Org file (maintenance.org):

#+TITLE: Database Maintenance Runbook

* TODO Step 1: Check Table Sizes

#+BEGIN_SRC sql
SELECT relname AS table_name,
       pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;
#+END_SRC

* TODO Step 2: Vacuum Analyze Large Tables

#+BEGIN_SRC sql
VACUUM ANALYZE projects;
VACUUM ANALYZE expenses;
#+END_SRC

* TODO Step 3: Reindex If Needed

#+BEGIN_SRC sql
REINDEX TABLE projects;
#+END_SRC

Frequently Asked Questions (FAQ)

Q: What is Org-mode?

A: Org-mode is a major mode for GNU Emacs created by Carsten Dominik. It provides a comprehensive system for note-taking, outlining, task management, literate programming, and document authoring — all in plain text. Org-mode files use the .org extension and feature hierarchical headings (* symbols), formatted tables, executable source code blocks, TODO tracking, and export to HTML, PDF, LaTeX, and many other formats.

Q: Can I execute SQL queries from the Org file?

A: Yes! This is one of Org-mode's most powerful features. Using Org Babel, you can execute #+BEGIN_SRC sql blocks directly against a configured database connection. Results appear inline in the Org document, creating a "literate SQL" notebook where documentation and live queries coexist. You need to configure ob-sql in your Emacs init file with database connection parameters.

Q: Do I need Emacs to use Org files?

A: While Emacs provides the best Org-mode experience, Org files are plain text and readable in any editor. Neovim has orgmode.nvim for partial support, VS Code has the Org Mode extension, and Logseq uses Org as one of its formats. Pandoc can convert Org files to and from many formats. GitHub renders basic Org formatting including headings and lists. For full features like code execution, Emacs is recommended.

Q: How are SQL tables represented in Org format?

A: SQL CREATE TABLE statements are converted to Org tables using pipe-delimited syntax with + alignment separators. Each column shows the field name, data type, and constraints. Org tables in Emacs support automatic alignment (Tab key), column calculations, and export to HTML/PDF tables. The tables are both human-readable in plain text and beautifully formatted when exported.

Q: Can I export the Org file to PDF or HTML?

A: Yes! Org-mode has a powerful built-in export dispatcher (C-c C-e in Emacs) that can export to HTML, PDF (via LaTeX), ODT, DOCX (via Pandoc), Markdown, plain text, and many other formats. SQL code blocks are syntax-highlighted in the exported output. This makes Org-mode an excellent single-source authoring format — write once, publish everywhere.

Q: How does the hierarchical structure work?

A: Org uses asterisks (*) for headings: * is level 1, ** is level 2, etc. In the converted output, databases are level 1 headings, tables and views are level 2, and details like column definitions are level 3 or deeper. In Emacs, you can collapse/expand any heading with Tab, cycle through visibility levels, and navigate the outline with keyboard shortcuts, making it easy to work with large database schemas.

Q: Can I add TODO tasks to the database documentation?

A: Absolutely! One of Org-mode's signature features is TODO tracking integrated into headings. The converter can mark maintenance tasks as TODO items (e.g., "* TODO Add index to users.email"). In Emacs, you can cycle TODO states, set deadlines, add priorities, and generate agenda views that pull tasks from your database documentation into your daily workflow.

Q: Is Org format suitable for version control?

A: Yes, Org is one of the best formats for version control. Since Org files are plain text, Git can diff and merge them efficiently. Changes to database schemas are clearly visible in commit diffs. You can track the evolution of your database documentation alongside your codebase, and Org's plain-text nature means there are never merge conflicts caused by binary format differences.