Convert AsciiDoc to SQL

Why Convert AsciiDoc to SQL?

Converting AsciiDoc to SQL is valuable when you need to transform documented database schemas, data specifications, or tabular content from technical documentation into executable SQL statements. AsciiDoc is commonly used to document database designs, data dictionaries, and migration plans, and converting this documentation directly to SQL eliminates the manual step of translating documented specifications into database scripts.

AsciiDoc tables are particularly well-suited for describing database schemas -- columns typically represent field names, data types, constraints, and descriptions. The conversion process interprets these documented structures and generates corresponding CREATE TABLE statements, INSERT operations for sample data, and appropriate constraints. This approach ensures that the implemented database schema matches the documented specification exactly.

SQL, or Structured Query Language, has been the standard language for relational database management since its standardization by ANSI in 1986. Modern SQL supports complex data operations including transactions, stored procedures, views, and advanced querying. By converting documentation to SQL, teams can rapidly prototype database structures, generate seed data scripts, and create migration files that are immediately executable in database management systems like MySQL, PostgreSQL, SQLite, and Oracle.

This conversion is especially useful in agile development workflows where database documentation and implementation need to stay synchronized. Rather than maintaining separate documentation and SQL files that can drift apart, teams can author their schema documentation in AsciiDoc and generate SQL scripts automatically. This documentation-as-code approach reduces errors, speeds up development, and ensures that database changes are properly documented.

Key Benefits of Converting AsciiDoc to SQL:

• Schema Generation: Create database tables directly from documented specifications
• Data Migration: Generate INSERT statements from documented data tables
• Documentation Sync: Keep database schemas aligned with technical documentation
• Rapid Prototyping: Quickly set up databases from design documents
• Cross-Database: Generate standard SQL compatible with multiple database engines
• Seed Data: Convert sample data tables into executable INSERT scripts
• Reproducible: Create repeatable database setup scripts from documentation

Practical Examples

Example 1: Database Schema Documentation

Input AsciiDoc file (schema.adoc):

= E-Commerce Database Schema

== Products Table

|===
| Column | Type | Constraints

| product_id | SERIAL | PRIMARY KEY
| name | VARCHAR(200) | NOT NULL
| price | DECIMAL(10,2) | NOT NULL
| category | VARCHAR(100) | DEFAULT 'general'
| stock | INTEGER | DEFAULT 0
|===

== Orders Table

|===
| Column | Type | Constraints

| order_id | SERIAL | PRIMARY KEY
| user_id | INTEGER | REFERENCES users(id)
| total | DECIMAL(10,2) | NOT NULL
| status | VARCHAR(50) | DEFAULT 'pending'
|===

Output SQL file (schema.sql):

-- E-Commerce Database Schema

-- Products Table
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    category VARCHAR(100) DEFAULT 'general',
    stock INTEGER DEFAULT 0
);

-- Orders Table
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(50) DEFAULT 'pending'
);

Example 2: Sample Data Documentation

Input AsciiDoc file (seed-data.adoc):

= Test Data for Development

== Sample Users

|===
| name | email | role

| Alice Johnson | [email protected] | admin
| Bob Smith | [email protected] | editor
| Carol White | [email protected] | viewer
|===

== Sample Categories

|===
| name | description

| Electronics | Phones, laptops, tablets
| Books | Physical and digital books
| Clothing | Apparel and accessories
|===

Output SQL file (seed-data.sql):

-- Test Data for Development

-- Sample Users
INSERT INTO users (name, email, role) VALUES
('Alice Johnson', '[email protected]', 'admin'),
('Bob Smith', '[email protected]', 'editor'),
('Carol White', '[email protected]', 'viewer');

-- Sample Categories
INSERT INTO categories (name, description) VALUES
('Electronics', 'Phones, laptops, tablets'),
('Books', 'Physical and digital books'),
('Clothing', 'Apparel and accessories');

Example 3: Migration Plan Documentation

Input AsciiDoc file (migration.adoc):

= Database Migration v2.0

== New Columns

Add the following columns to the users table:

* `phone` VARCHAR(20) -- optional phone number
* `avatar_url` TEXT -- profile picture URL
* `last_login` TIMESTAMP -- last login time

== Index Requirements

Create indexes for frequently queried columns:

* users.email (unique)
* orders.user_id (non-unique)
* products.category (non-unique)

Output SQL file (migration.sql):

-- Database Migration v2.0

-- New Columns
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN avatar_url TEXT;
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;

-- Index Requirements
CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_products_category ON products(category);

Frequently Asked Questions (FAQ)

Q: What kind of SQL is generated from AsciiDoc?

A: The converter generates standard ANSI SQL that is compatible with most relational database systems. The output includes CREATE TABLE statements from schema tables, INSERT statements from data tables, and comments from document headings. The SQL follows standard syntax conventions and can be executed in MySQL, PostgreSQL, SQLite, SQL Server, and Oracle with minimal or no modifications.

Q: How does the converter handle AsciiDoc tables?

A: AsciiDoc tables are the primary source of structured data for SQL generation. Tables with column headers like "Column, Type, Constraints" are interpreted as schema definitions and generate CREATE TABLE statements. Tables with data-like content generate INSERT statements. The converter analyzes header row patterns to determine the appropriate SQL output type automatically.

Q: Can I specify the target database dialect?

A: The default output uses standard ANSI SQL for maximum compatibility. Database-specific features like SERIAL (PostgreSQL), AUTO_INCREMENT (MySQL), or AUTOINCREMENT (SQLite) depend on the data types documented in your AsciiDoc source. If your documentation uses vendor-specific syntax, those constructs are preserved in the SQL output. For cross-database compatibility, use standard SQL data types in your documentation.

Q: What happens to non-table content in the AsciiDoc file?

A: Section headings are converted to SQL comment blocks (-- Section Name) to maintain document structure. Descriptive paragraphs become multi-line SQL comments (/* ... */). Lists describing schema changes may be converted to ALTER TABLE or CREATE INDEX statements when the content follows recognizable patterns. Code blocks containing SQL examples are extracted and included directly.

Q: Is the generated SQL safe to run directly?

A: The generated SQL should be reviewed before execution on production databases. It is designed for development, testing, and prototyping purposes. Always test generated SQL in a development environment first. The converter does not add DROP TABLE or DELETE statements unless explicitly present in the source documentation. Use transactions (BEGIN/COMMIT) when running generated migration scripts.

Q: Can the converter handle complex AsciiDoc documents?

A: Yes. The converter processes multi-section documents with multiple tables, extracting SQL-relevant content from each section. Include directives are resolved, and nested document structures are flattened appropriately. Admonition blocks (NOTE, WARNING) are converted to SQL comments. Cross-references are resolved to their target content. The converter focuses on extracting actionable database operations from the documentation.

Q: How are AsciiDoc code blocks with SQL handled?

A: AsciiDoc source blocks marked with [source,sql] are extracted and included directly in the output without modification. This means if your documentation already contains SQL examples, those exact statements appear in the converted file. This is particularly useful for documentation that includes migration scripts, query examples, or stored procedure definitions alongside explanatory text.

Q: Can I convert SQL back to AsciiDoc?

A: Yes, our converter supports SQL to AsciiDoc conversion. The reverse process parses SQL statements and generates structured AsciiDoc documentation with tables describing schema definitions, formatted code blocks for complex queries, and organized sections based on SQL comment blocks. This is useful for generating database documentation from existing SQL scripts or database dumps.