senax-pgsql-parser 0.1.2

PostgreSQL database schema parser and DDL generator with PostGIS support
Documentation

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

at your option.