#sql-query #sql #query #data-analysis

sql-insight

A utility for SQL query analysis, formatting, and transformation

3 unstable releases

0.2.0 Jul 4, 2024
0.1.1 Feb 12, 2024
0.1.0 Feb 12, 2024

#72 in #data-analysis


Used in sql-insight-cli

MIT license

81KB
2K SLoC

sql-insight

A utility for SQL query analysis, formatting, and transformation. Leveraging the comprehensive parsing capabilities of sqlparser-rs, it can handle various SQL dialects.

Crates.io Docs.rs Rust codecov License: MIT

Features

  • SQL Formatting: Format SQL queries to standardized form, improving readability and maintainability.
  • SQL Normalization: Convert SQL queries into a normalized form, making them easier to analyze and process.
  • Table Extraction: Extract tables referenced in SQL queries, clarifying the data sources involved.
  • CRUD Table Extraction: Identify the create, read, update, and delete operations, along with the tables involved in each operation within SQL queries.

Installation

Add sql_insight to your Cargo.toml file:

[dependencies]
sql-insight = { version = "0.2.0" }

Usage

SQL Formatting

Format SQL queries according to different dialects:

use sql_insight::sqlparser::dialect::GenericDialect;

let dialect = GenericDialect {};
let formatted_sql = sql_insight::format(&dialect, "SELECT * \n from users   WHERE id = 1").unwrap();
assert_eq!(formatted_sql, ["SELECT * FROM users WHERE id = 1"]);

SQL Normalization

Normalize SQL queries to abstract away literals:

use sql_insight::sqlparser::dialect::GenericDialect;

let dialect = GenericDialect {};
let normalized_sql = sql_insight::normalize(&dialect, "SELECT * \n from users   WHERE id = 1").unwrap();
assert_eq!(normalized_sql, ["SELECT * FROM users WHERE id = ?"]);

Table Extraction

Extract table references from SQL queries:

use sql_insight::sqlparser::dialect::GenericDialect;

let dialect = GenericDialect {};
let tables = sql_insight::extract_tables(&dialect, "SELECT * FROM catalog.schema.`users` as users_alias").unwrap();
println!("{:?}", tables);

This outputs:

[Ok(Tables([TableReference { catalog: Some(Ident { value: "catalog", quote_style: None }), schema: Some(Ident { value: "schema", quote_style: None }), name: Ident { value: "users", quote_style: Some('`') }, alias: Some(Ident { value: "users_alias", quote_style: None }) }]))]

CRUD Table Extraction

Identify CRUD operations and the tables involved in each operation within SQL queries:

use sql_insight::sqlparser::dialect::GenericDialect;

let dialect = GenericDialect {};
let crud_tables = sql_insight::extract_crud_tables(&dialect, "INSERT INTO users (name) SELECT name FROM employees").unwrap();
println!("{:?}", crud_tables);

This outputs:

[Ok(CrudTables { create_tables: [TableReference { catalog: None, schema: None, name: Ident { value: "users", quote_style: None }, alias: None }], read_tables: [TableReference { catalog: None, schema: None, name: Ident { value: "employees", quote_style: None }, alias: None }], update_tables: [], delete_tables: [] })]

Supported SQL Dialects

sql-insight supports a comprehensive range of SQL dialects through sqlparser-rs. For details on supported dialects, please refer to the sqlparser-rs documentation.

Contributing

Contributions to sql-insight are welcome! Whether it's adding new features, fixing bugs, or improving documentation, feel free to fork the repository and submit a pull request.

License

sql-insight is distributed under the MIT license.

Dependencies

~2MB
~46K SLoC