senax-pgsql-parser

A comprehensive PostgreSQL database schema parser and DDL generator with PostGIS support.

⚠️ Important Notice

This code is AI-generated. The author does not fully understand all implementation details. Use at your own risk and verify the behavior thoroughly before using in production environments.

Features

• 🔍 Schema Analysis: Parse PostgreSQL database schemas with detailed information
• 📝 DDL Generation: Generate CREATE and DROP DDL statements from schema data
• 🗺️ PostGIS Support: Full support for PostGIS geometry and geography types
• 🔄 Roundtrip Testing: Verify schema integrity with comprehensive roundtrip tests
• 🔑 Constraint Support: Handle primary keys, foreign keys, indexes, and check constraints
• 📊 Serial Types: Proper handling of serial, bigserial, and smallserial types
• 💬 Column Comments: Parse and generate column comments
• 🎯 Type Safety: Strongly typed data structures with comprehensive enum support

Installation

Add to your Cargo.toml:

[dependencies]
senax-pgsql-parser = "0.1.0"

Library Usage

use senax_pgsql_parser::{connect_to_database, get_database_schema};
use std::error::Error;

#[tokio::main]
async fn main() -> Result<(), Box<dyn Error>> {
    let database_url = "postgresql://user:password@localhost/mydatabase";
    let pool = connect_to_database(database_url).await?;
    let schema = get_database_schema(&pool).await?;

    println!("Successfully connected and parsed schema.");
    println!("Found {} tables.", schema.tables.len());

    for table in schema.tables {
        println!("Table: {}.{}", table.table_schema, table.table_name);
        for column in table.columns {
            println!("  - {}: {}", column.column_name, column.data_type);
        }
    }
    Ok(())
}

Supported PostgreSQL Features

Data Types

• Basic Types: INTEGER, BIGINT, SMALLINT, VARCHAR, TEXT, BOOLEAN, etc.
• Numeric Types: DECIMAL, NUMERIC with precision and scale
• Serial Types: SERIAL, BIGSERIAL, SMALLSERIAL (with sequence detection)
• Date/Time Types: TIMESTAMP, DATE, TIME
• Array Types: Support for all array types
• Custom Types: ENUM types and user-defined types
• PostGIS Types: GEOMETRY, GEOGRAPHY with detailed spatial information

Constraints

• Primary Keys: Single and composite primary keys
• Foreign Keys: Single and composite foreign keys with CASCADE/RESTRICT actions
• Check Constraints: Custom validation constraints
• Unique Constraints: Unique indexes and constraints

Indexes

• Index Types: B-tree, Hash, GIN, GiST, SP-GiST, BRIN, Bloom
• Composite Indexes: Multi-column indexes
• Partial Indexes: Indexes with WHERE conditions
• Unique Indexes: Uniqueness constraints via indexes

PostGIS Support

• Spatial Types: Full support for GEOMETRY and GEOGRAPHY types
• Type Details: SRID, dimensions, and geometry type information
• Array Support: PostGIS array types
• Extension Detection: Automatic PostGIS extension requirement detection

Current Schema Support

The tool automatically detects and uses the current PostgreSQL schema using SELECT current_schema(), ensuring accurate schema analysis within the active database context.

Examples

Generate DDL from existing database

use senax_pgsql_parser::{connect_to_database, get_database_schema};

let pool = connect_to_database("postgresql://user:pass@localhost/db").await?;
let schema = get_database_schema(&pool).await?;

// Generate CREATE statements
let create_ddl = schema.to_create_ddl();
println!("{}", create_ddl);

// Generate DROP statements  
let drop_ddl = schema.to_drop_ddl();
println!("{}", drop_ddl);

Access detailed table information

for table in &schema.tables {
    println!("Table: {}", table.table_name);
    
    // Primary keys
    let pk_columns: Vec<_> = table.columns.iter()
        .filter(|col| col.is_primary_key)
        .collect();
    
    // Foreign keys
    for fk in &table.foreign_keys {
        println!("FK: {} -> {}.{}", 
            fk.columns.join(","), 
            fk.referenced_table, 
            fk.referenced_columns.join(",")
        );
    }
    
    // Indexes
    for index in &table.indexes {
        println!("Index: {} on {}", index.index_name, index.columns.join(","));
    }
}

License

This project is licensed under either of

• Apache License, Version 2.0, (LICENSE-APACHE or http://www.apache.org/licenses/LICENSE-2.0)
• MIT license (LICENSE-MIT or http://opensource.org/licenses/MIT)

at your option.