Convert RST to SQL

Why Convert RST to SQL?

Converting reStructuredText (RST) documents to SQL enables transforming documented data tables into executable database scripts. This is invaluable when documentation contains sample data, reference tables, or configuration values that need to be loaded into a database.

RST's grid tables provide a natural way to document database structures and sample data in human-readable format. Converting these tables to SQL INSERT statements automates the process of populating databases with documented data, ensuring documentation and actual data remain synchronized.

For teams that maintain data dictionaries or reference data in documentation, RST to SQL conversion creates a direct pipeline from documentation to database. This eliminates manual data entry, reduces errors, and ensures that documented specifications are accurately reflected in the database.

The conversion is particularly useful for generating seed data scripts, test fixtures, and migration scripts from documented specifications. When your RST documentation defines expected data formats and examples, converting to SQL creates ready-to-execute scripts for database population.

Key Benefits of Converting RST to SQL:

• Automated Data Loading: Convert documented tables directly to INSERT statements
• Schema Generation: Create table definitions from documented structures
• Test Data Creation: Generate database fixtures from documentation examples
• Documentation Sync: Keep database and documentation aligned
• Migration Scripts: Create data migration SQL from change documentation
• Seed Data: Populate development databases from documented defaults
• Multi-Database: Generate SQL for various database systems

Practical Examples

Example 1: Simple Table to INSERT Statements

Input RST file (users.rst):

User Accounts
=============

+----+----------+------------------+--------+
| id | username | email            | active |
+====+==========+==================+========+
| 1  | admin    | [email protected]    | true   |
+----+----------+------------------+--------+
| 2  | john_doe | [email protected] | true   |
+----+----------+------------------+--------+
| 3  | jane_doe | [email protected] | false  |
+----+----------+------------------+--------+

Output SQL file (users.sql):

-- User Accounts
CREATE TABLE user_accounts (
    id INTEGER PRIMARY KEY,
    username VARCHAR(255),
    email VARCHAR(255),
    active BOOLEAN
);

INSERT INTO user_accounts (id, username, email, active)
VALUES (1, 'admin', '[email protected]', TRUE);

INSERT INTO user_accounts (id, username, email, active)
VALUES (2, 'john_doe', '[email protected]', TRUE);

INSERT INTO user_accounts (id, username, email, active)
VALUES (3, 'jane_doe', '[email protected]', FALSE);

Example 2: Configuration Data Extraction

Input RST file (config.rst):

Application Settings
====================

+------------------+-----------------+------------------------+
| setting_key      | setting_value   | description            |
+==================+=================+========================+
| max_connections  | 100             | Maximum DB connections |
+------------------+-----------------+------------------------+
| timeout_seconds  | 30              | Request timeout        |
+------------------+-----------------+------------------------+
| debug_mode       | false           | Enable debug logging   |
+------------------+-----------------+------------------------+

Output SQL file (config.sql):

-- Application Settings
CREATE TABLE application_settings (
    setting_key VARCHAR(255) PRIMARY KEY,
    setting_value VARCHAR(255),
    description TEXT
);

INSERT INTO application_settings (setting_key, setting_value, description)
VALUES ('max_connections', '100', 'Maximum DB connections');

INSERT INTO application_settings (setting_key, setting_value, description)
VALUES ('timeout_seconds', '30', 'Request timeout');

INSERT INTO application_settings (setting_key, setting_value, description)
VALUES ('debug_mode', 'false', 'Enable debug logging');

Example 3: Product Catalog Migration

Input RST file (products.rst):

Product Catalog
===============

+---------+----------------+-------+----------+
| sku     | name           | price | category |
+=========+================+=======+==========+
| PRD-001 | Widget Pro     | 29.99 | Tools    |
+---------+----------------+-------+----------+
| PRD-002 | Gadget Plus    | 49.99 | Devices  |
+---------+----------------+-------+----------+
| PRD-003 | Super Gizmo    | 19.99 | Tools    |
+---------+----------------+-------+----------+

Output SQL file (products.sql):

-- Product Catalog
CREATE TABLE product_catalog (
    sku VARCHAR(50) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2),
    category VARCHAR(100)
);

INSERT INTO product_catalog (sku, name, price, category)
VALUES ('PRD-001', 'Widget Pro', 29.99, 'Tools');

INSERT INTO product_catalog (sku, name, price, category)
VALUES ('PRD-002', 'Gadget Plus', 49.99, 'Devices');

INSERT INTO product_catalog (sku, name, price, category)
VALUES ('PRD-003', 'Super Gizmo', 19.99, 'Tools');

Frequently Asked Questions (FAQ)

Q: How are RST tables converted to SQL?

A: RST grid tables are parsed to extract column headers (which become column names) and data rows (which become INSERT statements). The table title typically becomes the table name in the CREATE TABLE statement.

Q: What SQL dialect is generated?

A: The converter generates ANSI SQL that is compatible with most database systems including MySQL, PostgreSQL, SQLite, and SQL Server. Specific dialect options may be available depending on the converter settings.

Q: Are data types inferred automatically?

A: Yes, basic data types are inferred from the data values. Numbers become INTEGER or DECIMAL, true/false become BOOLEAN, and text values become VARCHAR. For precise control, you can add type hints in your RST documentation.

Q: Can I convert only INSERT statements without CREATE TABLE?

A: Yes, the converter can generate INSERT-only SQL for adding data to existing tables. This is useful when populating established database schemas with documented data.

Q: How are NULL values handled?

A: Empty cells in RST tables are converted to NULL values in SQL statements. If you need empty strings instead, use explicit empty string markers in your documentation.

Q: Can I specify primary keys in RST?

A: Columns named 'id' or ending with '_id' are typically treated as primary keys. You can also use RST field lists or comments to specify primary key columns explicitly in your documentation.

Q: What about special characters in data?

A: Special characters including quotes, backslashes, and SQL reserved words are properly escaped in the generated SQL to prevent injection issues and syntax errors.

Q: Can this be used for database migrations?

A: Yes! Document your migration data in RST format, then convert to SQL migration scripts. This provides both human-readable documentation and executable SQL in a single workflow.