Commit 49e68ce

@@ -44,11 +44,7 @@ fn format_query(query: &Query, indent_level: usize) -> String {
     if !query.order_by.is_empty() {
         result.push('\n');
         result.push_str(&format!("{}ORDER BY ", " ".repeat(indent_level)));
-        let order_items: Vec<String> = query
-            .order_by
-            .iter()
-            .map(|order| format_order_by_expr(order))
-            .collect();
+        let order_items: Vec<String> = query.order_by.iter().map(format_order_by_expr).collect();
         result.push_str(&order_items.join(", "));
     }
 
@@ -120,15 +116,13 @@ fn format_set_expr(set_expr: &SetExpr, indent_level: usize) -> String {
         }
         SetExpr::SetOperation {
             op,
-            set_quantifier,
+            set_quantifier: _,
             left,
             right,
         } => {
             let mut result = format_set_expr(left, indent_level);
             result.push('\n');
             result.push_str(&format!("{}{}", " ".repeat(indent_level), op));
-            result.push(' ');
-            result.push_str(&set_quantifier.to_string());
             result.push('\n');
             result.push_str(&format_set_expr(right, indent_level));
             result
@@ -187,14 +181,18 @@ fn format_select(select: &Select, indent_level: usize) -> String {
 
     if let Some(selection) = &select.selection {
         result.push('\n');
-        result.push_str(&format!("{}WHERE {}", base_indent, format_where_expr(selection, indent_level + 2)));
+        result.push_str(&format!(
+            "{}WHERE {}",
+            base_indent,
+            format_where_expr(selection, indent_level + 2)
+        ));
     }
 
     match &select.group_by {
         GroupByExpr::Expressions(exprs) if !exprs.is_empty() => {
             result.push('\n');
             result.push_str(&format!("{}GROUP BY ", base_indent));
-            let group_items: Vec<String> = exprs.iter().map(|expr| format_expr(expr)).collect();
+            let group_items: Vec<String> = exprs.iter().map(format_expr).collect();
             result.push_str(&group_items.join(", "));
         }
         _ => {}
@@ -661,8 +659,187 @@ fn format_where_expr(expr: &Expr, indent_level: usize) -> String {
         Expr::BinaryOp { left, op, right } if matches!(op, BinaryOperator::And) => {
             let left_str = format_where_expr(left, indent_level);
             let right_str = format_where_expr(right, indent_level);
-            format!("{}\n{}AND {}", left_str, " ".repeat(indent_level), right_str)
+            format!(
+                "{}\n{}AND {}",
+                left_str,
+                " ".repeat(indent_level),
+                right_str
+            )
         }
         _ => format_expr(expr),
     }
 }
+
+#[cfg(test)]
+mod tests {
+    use super::*;
+    use sqlparser::dialect::GenericDialect;
+    use sqlparser::parser::Parser;
+
+    fn parse_and_format(sql: &str) -> String {
+        let dialect = GenericDialect {};
+        let statements = Parser::parse_sql(&dialect, sql).unwrap();
+        format_statement(&statements[0], 0)
+    }
+
+    #[test]
+    fn test_simple_select_single_column() {
+        let input = "SELECT id FROM users";
+        let expected = "SELECT id\nFROM\n  users";
+        assert_eq!(parse_and_format(input), expected);
+    }
+
+    #[test]
+    fn test_simple_select_with_alias() {
+        let input = "SELECT 1 AS one FROM users";
+        let expected = "SELECT 1 AS one\nFROM\n  users";
+        assert_eq!(parse_and_format(input), expected);
+    }
+
+    #[test]
+    fn test_multi_column_select() {
+        let input = "SELECT id, name, email FROM users";
+        let expected = "SELECT\n  id,\n  name,\n  email\nFROM\n  users";
+        assert_eq!(parse_and_format(input), expected);
+    }
+
+    #[test]
+    fn test_select_with_where() {
+        let input = "SELECT * FROM users WHERE active = true";
+        let expected = "SELECT *\nFROM\n  users\nWHERE active = TRUE";
+        assert_eq!(parse_and_format(input), expected);
+    }
+
+    #[test]
+    fn test_select_with_multiple_and_conditions() {
+        let input = "SELECT * FROM users WHERE active = true AND age > 18 AND name LIKE 'A%'";
+        let expected =
+            "SELECT *\nFROM\n  users\nWHERE active = TRUE\n  AND age > 18\n  AND name LIKE 'A%'";
+        assert_eq!(parse_and_format(input), expected);
+    }
+
+    #[test]
+    fn test_select_with_join() {
+        let input = "SELECT u.name, p.title FROM users u INNER JOIN posts p ON u.id = p.user_id";
+        let expected = "SELECT\n  u.name,\n  p.title\nFROM\n  users AS u\n  INNER JOIN posts AS p ON u.id = p.user_id";
+        assert_eq!(parse_and_format(input), expected);
+    }
+
+    #[test]
+    fn test_select_with_subquery() {
+        let input =
+            "SELECT * FROM (SELECT id, name FROM users WHERE active = true) AS active_users";
+        let expected = "SELECT *\nFROM\n  (\n    SELECT\n      id,\n      name\n    FROM\n      users\n    WHERE active = TRUE\n) AS active_users";
+        assert_eq!(parse_and_format(input), expected);
+    }
+
+    #[test]
+    fn test_select_with_case_expression() {
+        let input =
+            "SELECT CASE WHEN age < 18 THEN 'minor' ELSE 'adult' END AS age_group FROM users";
+        let expected = "SELECT\n  CASE\n  WHEN age < 18 THEN 'minor'\n  ELSE 'adult'\nEND AS age_group\nFROM\n  users";
+        assert_eq!(parse_and_format(input), expected);
+    }
+
+    #[test]
+    fn test_select_with_cte() {
+        let input = "WITH active_users AS (SELECT * FROM users WHERE active = true) SELECT * FROM active_users";
+        let expected = "WITH\n  active_users AS (\n    SELECT *\n    FROM\n      users\n    WHERE active = TRUE\n  )\nSELECT *\nFROM\n  active_users";
+        assert_eq!(parse_and_format(input), expected);
+    }
+
+    #[test]
+    fn test_select_with_group_by_and_having() {
+        let input =
+            "SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5";
+        let expected = "SELECT\n  department,\n  COUNT(*)\nFROM\n  employees\nGROUP BY department\nHAVING COUNT(*) > 5";
+        assert_eq!(parse_and_format(input), expected);
+    }
+
+    #[test]
+    fn test_select_with_order_by_and_limit() {
+        let input = "SELECT * FROM users ORDER BY name ASC LIMIT 10";
+        let expected = "SELECT *\nFROM\n  users\nORDER BY name ASC\nLIMIT 10";
+        assert_eq!(parse_and_format(input), expected);
+    }
+
+    #[test]
+    fn test_union_query() {
+        let input = "SELECT id FROM users UNION SELECT id FROM customers";
+        let expected = "SELECT id\nFROM\n  users\nUNION\nSELECT id\nFROM\n  customers";
+        assert_eq!(parse_and_format(input), expected);
+    }
+
+    #[test]
+    fn test_function_calls() {
+        let input = "SELECT COUNT(*), MAX(age), UPPER(name) FROM users";
+        let expected = "SELECT\n  COUNT(*),\n  MAX(age),\n  UPPER(name)\nFROM\n  users";
+        assert_eq!(parse_and_format(input), expected);
+    }
+
+    #[test]
+    fn test_distinct_select() {
+        let input = "SELECT DISTINCT department FROM employees";
+        let expected = "SELECT DISTINCT department\nFROM\n  employees";
+        assert_eq!(parse_and_format(input), expected);
+    }
+
+    #[test]
+    fn test_between_expression() {
+        let input = "SELECT * FROM products WHERE price BETWEEN 10 AND 100";
+        let expected = "SELECT *\nFROM\n  products\nWHERE price BETWEEN 10 AND 100";
+        assert_eq!(parse_and_format(input), expected);
+    }
+
+    #[test]
+    fn test_in_subquery() {
+        let input = "SELECT * FROM users WHERE id IN (SELECT user_id FROM orders)";
+        let expected =
+            "SELECT *\nFROM\n  users\nWHERE id IN (\n    SELECT user_id\n    FROM\n      orders\n)";
+        assert_eq!(parse_and_format(input), expected);
+    }
+
+    #[test]
+    fn test_is_null_expression() {
+        let input = "SELECT * FROM users WHERE email IS NOT NULL";
+        let expected = "SELECT *\nFROM\n  users\nWHERE email IS NOT NULL";
+        assert_eq!(parse_and_format(input), expected);
+    }
+
+    #[test]
+    fn test_cast_expression() {
+        let input = "SELECT CAST(id AS VARCHAR) FROM users";
+        let expected = "SELECT\n  CAST(id AS VARCHAR)\nFROM\n  users";
+        assert_eq!(parse_and_format(input), expected);
+    }
+
+    #[test]
+    fn test_nested_expressions() {
+        let input = "SELECT * FROM users WHERE (active = true AND age > 18) OR role = 'admin'";
+        let expected =
+            "SELECT *\nFROM\n  users\nWHERE (active = TRUE AND age > 18) OR role = 'admin'";
+        assert_eq!(parse_and_format(input), expected);
+    }
+
+    #[test]
+    fn test_multiple_joins() {
+        let input = "SELECT u.name, p.title, c.content FROM users u LEFT JOIN posts p ON u.id = p.user_id RIGHT JOIN comments c ON p.id = c.post_id";
+        let expected = "SELECT\n  u.name,\n  p.title,\n  c.content\nFROM\n  users AS u\n  LEFT OUTER JOIN posts AS p ON u.id = p.user_id\n  RIGHT OUTER JOIN comments AS c ON p.id = c.post_id";
+        assert_eq!(parse_and_format(input), expected);
+    }
+
+    #[test]
+    fn test_wildcard_expressions() {
+        let input = "SELECT *, users.* FROM users";
+        let expected = "SELECT\n  *,\n  users.*\nFROM\n  users";
+        assert_eq!(parse_and_format(input), expected);
+    }
+
+    #[test]
+    fn test_complex_case_expression() {
+        let input =
+            "SELECT CASE status WHEN 'active' THEN 1 WHEN 'inactive' THEN 0 ELSE -1 END FROM users";
+        let expected = "SELECT\n  CASE status\n  WHEN 'active' THEN 1\n  WHEN 'inactive' THEN 0\n  ELSE - 1\nEND\nFROM\n  users";
+        assert_eq!(parse_and_format(input), expected);
+    }
+}

@@ -0,0 +1,3 @@
+pub mod formatter;
+
+pub use formatter::format_statement;

@@ -0,0 +1,1 @@
+SELECT id, name, email FROM users WHERE active = true
\ No newline at end of file

@@ -0,0 +1,7 @@
+SELECT
+  id,
+  name,
+  email
+FROM
+  users
+WHERE active = TRUE;
\ No newline at end of file

@@ -0,0 +1,1 @@
+SELECT CASE WHEN age < 18 THEN 'minor' WHEN age >= 65 THEN 'senior' ELSE 'adult' END AS age_group FROM users
\ No newline at end of file

@@ -0,0 +1,8 @@
+SELECT
+  CASE
+  WHEN age < 18 THEN 'minor'
+  WHEN age >= 65 THEN 'senior'
+  ELSE 'adult'
+END AS age_group
+FROM
+  users;
\ No newline at end of file

@@ -0,0 +1,1 @@
+WITH active_users AS (SELECT * FROM users WHERE active = true) SELECT * FROM active_users
\ No newline at end of file

@@ -0,0 +1,10 @@
+WITH
+  active_users AS (
+    SELECT *
+    FROM
+      users
+    WHERE active = TRUE
+  )
+SELECT *
+FROM
+  active_users;
\ No newline at end of file

@@ -0,0 +1,1 @@
+INVALID SQL SYNTAX HERE
\ No newline at end of file

@@ -0,0 +1,1 @@
+INVALID SQL SYNTAX HERE
\ No newline at end of file

@@ -0,0 +1,1 @@
+SELECT u.name, p.title FROM users u INNER JOIN posts p ON u.id = p.user_id
\ No newline at end of file

@@ -0,0 +1,6 @@
+SELECT
+  u.name,
+  p.title
+FROM
+  users AS u
+  INNER JOIN posts AS p ON u.id = p.user_id;
\ No newline at end of file

@@ -0,0 +1,1 @@
+SELECT * FROM users WHERE active = true AND age > 18 AND name LIKE 'A%'
\ No newline at end of file

@@ -0,0 +1,6 @@
+SELECT *
+FROM
+  users
+WHERE active = TRUE
+  AND age > 18
+  AND name LIKE 'A%';
\ No newline at end of file

@@ -0,0 +1,1 @@
+SELECT COUNT(*) as total, AVG(age) as avg_age FROM users GROUP BY department HAVING COUNT(*) > 5
\ No newline at end of file

@@ -0,0 +1,7 @@
+SELECT
+  COUNT(*) AS total,
+  AVG(age) AS avg_age
+FROM
+  users
+GROUP BY department
+HAVING COUNT(*) > 5;
\ No newline at end of file

@@ -0,0 +1,1 @@
+SELECT 1 AS one FROM users
\ No newline at end of file

@@ -0,0 +1,3 @@
+SELECT 1 AS one
+FROM
+  users;
\ No newline at end of file

@@ -0,0 +1,1 @@
+SELECT * FROM (SELECT id, name FROM users WHERE active = true) AS active_users WHERE name LIKE 'A%'
\ No newline at end of file

@@ -0,0 +1,11 @@
+SELECT *
+FROM
+  (
+    SELECT
+      id,
+      name
+    FROM
+      users
+    WHERE active = TRUE
+) AS active_users
+WHERE name LIKE 'A%';
\ No newline at end of file

@@ -0,0 +1,1 @@
+SELECT id FROM users UNION SELECT id FROM customers
\ No newline at end of file

@@ -0,0 +1,7 @@
+SELECT id
+FROM
+  users
+UNION
+SELECT id
+FROM
+  customers;
\ No newline at end of file

@@ -0,0 +1,43 @@
+# Test Fixtures
+
+This directory contains test fixtures for `xlg-sqlfmt`. Each fixture is a scenario that tests specific SQL formatting behavior.
+
+## Structure
+
+Each fixture is a directory containing:
+- `input.sql` - The SQL to be formatted (piped to stdin)
+- `output.sql` - The expected formatted output (compared with stdout)
+
+## Adding New Test Cases
+
+To add a new test case:
+
+1. Create a new directory with a descriptive name: `tests/fixtures/my_new_test/`
+2. Add `input.sql` with your test SQL
+3. Add `output.sql` with the expected formatted result
+4. Run tests - the new fixture will be automatically discovered!
+
+## Running Tests
+
+```bash
+# Run all fixture tests
+cargo test --test fixture_tests
+
+# Run a specific fixture test
+cargo test test_fixture_basic_select --test fixture_tests
+
+# See all discovered fixtures
+cargo test test_fixture_discovery --test fixture_tests -- --nocapture
+
+# Test all fixtures at once
+cargo test test_all_fixtures --test fixture_tests
+```
+
+## Special Cases
+
+- **invalid_sql**: For fixtures that should fail parsing, they will be tested with `.failure()` instead of `.success()`
+- The test automatically detects invalid SQL fixtures by the directory name "invalid_sql"
+
+## Current Fixtures
+
+Run `cargo test test_fixture_discovery --test fixture_tests -- --nocapture` to see all current fixtures.
\ No newline at end of file

@@ -0,0 +1,149 @@
+use assert_cmd::Command;
+use std::fs;
+use std::path::{Path, PathBuf};
+
+fn discover_fixtures() -> Vec<PathBuf> {
+    let fixtures_dir = Path::new("tests/fixtures");
+    let mut fixtures = Vec::new();
+
+    if let Ok(entries) = fs::read_dir(fixtures_dir) {
+        for entry in entries.flatten() {
+            let path = entry.path();
+            if path.is_dir() {
+                let input_file = path.join("input.sql");
+                let output_file = path.join("output.sql");
+
+                if input_file.exists() && output_file.exists() {
+                    fixtures.push(path);
+                }
+            }
+        }
+    }
+
+    fixtures.sort();
+    fixtures
+}
+
+fn run_fixture_test(fixture_path: &Path) {
+    let scenario_name = fixture_path.file_name().unwrap().to_str().unwrap();
+
+    let input_file = fixture_path.join("input.sql");
+    let output_file = fixture_path.join("output.sql");
+
+    let input_sql = fs::read_to_string(&input_file)
+        .unwrap_or_else(|_| panic!("Failed to read input file: {}", input_file.display()));
+
+    let expected_output = fs::read_to_string(&output_file)
+        .unwrap_or_else(|_| panic!("Failed to read output file: {}", output_file.display()));
+
+    let mut cmd = Command::cargo_bin("xlg-sqlfmt").unwrap();
+
+    // Handle the special case of invalid SQL (should fail and output original SQL)
+    if scenario_name == "invalid_sql" {
+        cmd.write_stdin(input_sql.as_str())
+            .assert()
+            .failure()
+            .stdout(format!("{}\n", expected_output));
+    } else {
+        cmd.write_stdin(input_sql.as_str())
+            .assert()
+            .success()
+            .stdout(format!("{}\n", expected_output));
+    }
+}
+
+#[test]
+fn test_all_fixtures() {
+    let fixtures = discover_fixtures();
+
+    assert!(
+        !fixtures.is_empty(),
+        "No fixtures found in tests/fixtures directory"
+    );
+
+    for fixture_path in fixtures {
+        let scenario_name = fixture_path.file_name().unwrap().to_str().unwrap();
+
+        println!("Running fixture test: {}", scenario_name);
+
+        run_fixture_test(&fixture_path);
+    }
+}
+
+#[test]
+fn test_fixtures_individually() {
+    let fixtures = discover_fixtures();
+
+    for fixture_path in fixtures {
+        let scenario_name = fixture_path.file_name().unwrap().to_str().unwrap();
+
+        // Create individual test cases that can be run separately
+        match scenario_name {
+            "basic_select" => run_fixture_test(&fixture_path),
+            "simple_projection" => run_fixture_test(&fixture_path),
+            "multiple_and_conditions" => run_fixture_test(&fixture_path),
+            "join_query" => run_fixture_test(&fixture_path),
+            "subquery" => run_fixture_test(&fixture_path),
+            "cte" => run_fixture_test(&fixture_path),
+            "case_expression" => run_fixture_test(&fixture_path),
+            "union_query" => run_fixture_test(&fixture_path),
+            "invalid_sql" => run_fixture_test(&fixture_path),
+            _ => {
+                // This ensures new fixtures are automatically tested
+                println!("Testing new fixture: {}", scenario_name);
+                run_fixture_test(&fixture_path);
+            }
+        }
+    }
+}
+
+// This test can be used to check which fixtures are available
+#[test]
+fn test_fixture_discovery() {
+    let fixtures = discover_fixtures();
+
+    println!("Discovered fixtures:");
+    for fixture in &fixtures {
+        let name = fixture.file_name().unwrap().to_str().unwrap();
+        println!("  - {}", name);
+    }
+
+    // This test always passes but shows what fixtures were found
+    assert!(
+        !fixtures.is_empty(),
+        "Should have found at least some fixtures"
+    );
+}
+
+#[cfg(test)]
+mod fixture_specific_tests {
+    use super::*;
+
+    // These are generated test functions that run individual fixtures
+    // You can run specific fixtures with: cargo test test_fixture_basic_select
+
+    macro_rules! generate_fixture_test {
+        ($test_name:ident, $fixture_name:literal) => {
+            #[test]
+            fn $test_name() {
+                let fixture_path = Path::new("tests/fixtures").join($fixture_name);
+                if fixture_path.exists() {
+                    run_fixture_test(&fixture_path);
+                }
+            }
+        };
+    }
+
+    generate_fixture_test!(test_fixture_basic_select, "basic_select");
+    generate_fixture_test!(test_fixture_simple_projection, "simple_projection");
+    generate_fixture_test!(
+        test_fixture_multiple_and_conditions,
+        "multiple_and_conditions"
+    );
+    generate_fixture_test!(test_fixture_join_query, "join_query");
+    generate_fixture_test!(test_fixture_subquery, "subquery");
+    generate_fixture_test!(test_fixture_cte, "cte");
+    generate_fixture_test!(test_fixture_case_expression, "case_expression");
+    generate_fixture_test!(test_fixture_union_query, "union_query");
+    generate_fixture_test!(test_fixture_invalid_sql, "invalid_sql");
+}

@@ -0,0 +1,227 @@
+use assert_cmd::Command;
+
+#[test]
+fn test_cli_basic_select() {
+    let mut cmd = Command::cargo_bin("xlg-sqlfmt").unwrap();
+    cmd.write_stdin("SELECT id, name FROM users WHERE active = true")
+        .assert()
+        .success()
+        .stdout("SELECT\n  id,\n  name\nFROM\n  users\nWHERE active = TRUE;\n");
+}
+
+#[test]
+fn test_cli_simple_select_single_line() {
+    let mut cmd = Command::cargo_bin("xlg-sqlfmt").unwrap();
+    cmd.write_stdin("SELECT 1 AS one FROM users")
+        .assert()
+        .success()
+        .stdout("SELECT 1 AS one\nFROM\n  users;\n");
+}
+
+#[test]
+fn test_cli_multiple_and_conditions() {
+    let mut cmd = Command::cargo_bin("xlg-sqlfmt").unwrap();
+    cmd.write_stdin("SELECT * FROM users WHERE active = true AND age > 18 AND name LIKE 'A%'")
+        .assert()
+        .success()
+        .stdout(
+            "SELECT *\nFROM\n  users\nWHERE active = TRUE\n  AND age > 18\n  AND name LIKE 'A%';\n",
+        );
+}
+
+#[test]
+fn test_cli_with_joins() {
+    let mut cmd = Command::cargo_bin("xlg-sqlfmt").unwrap();
+    cmd.write_stdin("SELECT u.name, p.title FROM users u INNER JOIN posts p ON u.id = p.user_id")
+        .assert()
+        .success()
+        .stdout("SELECT\n  u.name,\n  p.title\nFROM\n  users AS u\n  INNER JOIN posts AS p ON u.id = p.user_id;\n");
+}
+
+#[test]
+fn test_cli_with_subquery() {
+    let mut cmd = Command::cargo_bin("xlg-sqlfmt").unwrap();
+    cmd.write_stdin("SELECT * FROM (SELECT id, name FROM users WHERE active = true) AS active_users")
+        .assert()
+        .success()
+        .stdout("SELECT *\nFROM\n  (\n    SELECT\n      id,\n      name\n    FROM\n      users\n    WHERE active = TRUE\n) AS active_users;\n");
+}
+
+#[test]
+fn test_cli_with_cte() {
+    let mut cmd = Command::cargo_bin("xlg-sqlfmt").unwrap();
+    cmd.write_stdin("WITH active_users AS (SELECT * FROM users WHERE active = true) SELECT * FROM active_users")
+        .assert()
+        .success()
+        .stdout("WITH\n  active_users AS (\n    SELECT *\n    FROM\n      users\n    WHERE active = TRUE\n  )\nSELECT *\nFROM\n  active_users;\n");
+}
+
+#[test]
+fn test_cli_with_case_expression() {
+    let mut cmd = Command::cargo_bin("xlg-sqlfmt").unwrap();
+    cmd.write_stdin("SELECT CASE WHEN age < 18 THEN 'minor' ELSE 'adult' END AS age_group FROM users")
+        .assert()
+        .success()
+        .stdout("SELECT\n  CASE\n  WHEN age < 18 THEN 'minor'\n  ELSE 'adult'\nEND AS age_group\nFROM\n  users;\n");
+}
+
+#[test]
+fn test_cli_with_union() {
+    let mut cmd = Command::cargo_bin("xlg-sqlfmt").unwrap();
+    cmd.write_stdin("SELECT id FROM users UNION SELECT id FROM customers")
+        .assert()
+        .success()
+        .stdout("SELECT id\nFROM\n  users\nUNION\nSELECT id\nFROM\n  customers;\n");
+}
+
+#[test]
+fn test_cli_with_group_by_having() {
+    let mut cmd = Command::cargo_bin("xlg-sqlfmt").unwrap();
+    cmd.write_stdin("SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5")
+        .assert()
+        .success()
+        .stdout("SELECT\n  department,\n  COUNT(*)\nFROM\n  employees\nGROUP BY department\nHAVING COUNT(*) > 5;\n");
+}
+
+#[test]
+fn test_cli_with_order_by_limit() {
+    let mut cmd = Command::cargo_bin("xlg-sqlfmt").unwrap();
+    cmd.write_stdin("SELECT * FROM users ORDER BY name ASC LIMIT 10")
+        .assert()
+        .success()
+        .stdout("SELECT *\nFROM\n  users\nORDER BY name ASC\nLIMIT 10;\n");
+}
+
+#[test]
+fn test_cli_invalid_sql_passthrough() {
+    let mut cmd = Command::cargo_bin("xlg-sqlfmt").unwrap();
+    cmd.write_stdin("INVALID SQL SYNTAX")
+        .assert()
+        .failure()
+        .stdout("INVALID SQL SYNTAX\n");
+}
+
+#[test]
+fn test_cli_empty_input() {
+    let mut cmd = Command::cargo_bin("xlg-sqlfmt").unwrap();
+    cmd.write_stdin("").assert().success().stdout("");
+}
+
+#[test]
+fn test_cli_whitespace_only() {
+    let mut cmd = Command::cargo_bin("xlg-sqlfmt").unwrap();
+    cmd.write_stdin("   \n  \n   ")
+        .assert()
+        .success()
+        .stdout("");
+}
+
+#[test]
+fn test_cli_semicolon_addition() {
+    let mut cmd = Command::cargo_bin("xlg-sqlfmt").unwrap();
+    cmd.write_stdin("SELECT * FROM users")
+        .assert()
+        .success()
+        .stdout("SELECT *\nFROM\n  users;\n");
+}
+
+#[test]
+fn test_cli_idempotent_formatting() {
+    let formatted_sql = "SELECT\n  id,\n  name\nFROM\n  users\nWHERE active = TRUE;\n";
+
+    let mut cmd = Command::cargo_bin("xlg-sqlfmt").unwrap();
+    cmd.write_stdin(formatted_sql)
+        .assert()
+        .success()
+        .stdout(formatted_sql);
+}
+
+#[test]
+fn test_cli_function_calls() {
+    let mut cmd = Command::cargo_bin("xlg-sqlfmt").unwrap();
+    cmd.write_stdin("SELECT COUNT(*), MAX(age), UPPER(name) FROM users")
+        .assert()
+        .success()
+        .stdout("SELECT\n  COUNT(*),\n  MAX(age),\n  UPPER(name)\nFROM\n  users;\n");
+}
+
+#[test]
+fn test_cli_distinct() {
+    let mut cmd = Command::cargo_bin("xlg-sqlfmt").unwrap();
+    cmd.write_stdin("SELECT DISTINCT department FROM employees")
+        .assert()
+        .success()
+        .stdout("SELECT DISTINCT department\nFROM\n  employees;\n");
+}
+
+#[test]
+fn test_cli_between_expression() {
+    let mut cmd = Command::cargo_bin("xlg-sqlfmt").unwrap();
+    cmd.write_stdin("SELECT * FROM products WHERE price BETWEEN 10 AND 100")
+        .assert()
+        .success()
+        .stdout("SELECT *\nFROM\n  products\nWHERE price BETWEEN 10 AND 100;\n");
+}
+
+#[test]
+fn test_cli_in_subquery() {
+    let mut cmd = Command::cargo_bin("xlg-sqlfmt").unwrap();
+    cmd.write_stdin("SELECT * FROM users WHERE id IN (SELECT user_id FROM orders)")
+        .assert()
+        .success()
+        .stdout("SELECT *\nFROM\n  users\nWHERE id IN (\n    SELECT user_id\n    FROM\n      orders\n);\n");
+}
+
+#[test]
+fn test_cli_is_null() {
+    let mut cmd = Command::cargo_bin("xlg-sqlfmt").unwrap();
+    cmd.write_stdin("SELECT * FROM users WHERE email IS NOT NULL")
+        .assert()
+        .success()
+        .stdout("SELECT *\nFROM\n  users\nWHERE email IS NOT NULL;\n");
+}
+
+#[test]
+fn test_cli_cast_expression() {
+    let mut cmd = Command::cargo_bin("xlg-sqlfmt").unwrap();
+    cmd.write_stdin("SELECT CAST(id AS VARCHAR) FROM users")
+        .assert()
+        .success()
+        .stdout("SELECT\n  CAST(id AS VARCHAR)\nFROM\n  users;\n");
+}
+
+#[test]
+fn test_cli_nested_expressions() {
+    let mut cmd = Command::cargo_bin("xlg-sqlfmt").unwrap();
+    cmd.write_stdin("SELECT * FROM users WHERE (active = true AND age > 18) OR role = 'admin'")
+        .assert()
+        .success()
+        .stdout("SELECT *\nFROM\n  users\nWHERE (active = TRUE AND age > 18) OR role = 'admin';\n");
+}
+
+#[test]
+fn test_cli_multiple_statements() {
+    let mut cmd = Command::cargo_bin("xlg-sqlfmt").unwrap();
+    cmd.write_stdin("SELECT * FROM users; SELECT * FROM posts")
+        .assert()
+        .success()
+        .stdout("SELECT *\nFROM\n  users\nSELECT *\nFROM\n  posts;\n");
+}
+
+#[test]
+fn test_cli_quoted_identifiers() {
+    let mut cmd = Command::cargo_bin("xlg-sqlfmt").unwrap();
+    cmd.write_stdin("SELECT \"id\", \"user_name\" FROM \"admin_users\" WHERE \"active\" = true")
+        .assert()
+        .success()
+        .stdout("SELECT\n  id,\n  user_name\nFROM\n  \"admin_users\"\nWHERE active = TRUE;\n");
+}
+
+#[test]
+fn test_cli_your_original_example() {
+    let mut cmd = Command::cargo_bin("xlg-sqlfmt").unwrap();
+    cmd.write_stdin("SELECT 1 AS one FROM \"admin_roles\" WHERE \"admin_roles\".\"name\" = 'root' AND \"admin_roles\".\"organization_id\" = 1 LIMIT 1")
+        .assert()
+        .success()
+        .stdout("SELECT 1 AS one\nFROM\n  \"admin_roles\"\nWHERE admin_roles.name = 'root'\n  AND admin_roles.organization_id = 1\nLIMIT 1;\n");
+}

@@ -2,18 +2,220 @@
 # It is not intended for manual editing.
 version = 4
 
+[[package]]
+name = "aho-corasick"
+version = "1.1.3"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "8e60d3430d3a69478ad0993f19238d2df97c507009a52b3c10addcd7f6bcb916"
+dependencies = [
+ "memchr",
+]
+
+[[package]]
+name = "anstyle"
+version = "1.0.11"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "862ed96ca487e809f1c8e5a8447f6ee2cf102f846893800b20cebdf541fc6bbd"
+
 [[package]]
 name = "anyhow"
 version = "1.0.100"
 source = "registry+https://github.com/rust-lang/crates.io-index"
 checksum = "a23eb6b1614318a8071c9b2521f36b424b2c83db5eb3a0fead4a6c0809af6e61"
 
+[[package]]
+name = "assert_cmd"
+version = "2.0.17"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "2bd389a4b2970a01282ee455294913c0a43724daedcd1a24c3eb0ec1c1320b66"
+dependencies = [
+ "anstyle",
+ "bstr",
+ "doc-comment",
+ "libc",
+ "predicates",
+ "predicates-core",
+ "predicates-tree",
+ "wait-timeout",
+]
+
+[[package]]
+name = "autocfg"
+version = "1.5.0"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "c08606f8c3cbf4ce6ec8e28fb0014a2c086708fe954eaa885384a6165172e7e8"
+
+[[package]]
+name = "bstr"
+version = "1.12.0"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "234113d19d0d7d613b40e86fb654acf958910802bcceab913a4f9e7cda03b1a4"
+dependencies = [
+ "memchr",
+ "regex-automata",
+ "serde",
+]
+
+[[package]]
+name = "difflib"
+version = "0.4.0"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "6184e33543162437515c2e2b48714794e37845ec9851711914eec9d308f6ebe8"
+
+[[package]]
+name = "doc-comment"
+version = "0.3.3"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "fea41bba32d969b513997752735605054bc0dfa92b4c56bf1189f2e174be7a10"
+
+[[package]]
+name = "float-cmp"
+version = "0.10.0"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "b09cf3155332e944990140d967ff5eceb70df778b34f77d8075db46e4704e6d8"
+dependencies = [
+ "num-traits",
+]
+
+[[package]]
+name = "libc"
+version = "0.2.176"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "58f929b4d672ea937a23a1ab494143d968337a5f47e56d0815df1e0890ddf174"
+
 [[package]]
 name = "log"
 version = "0.4.28"
 source = "registry+https://github.com/rust-lang/crates.io-index"
 checksum = "34080505efa8e45a4b816c349525ebe327ceaa8559756f0356cba97ef3bf7432"
 
+[[package]]
+name = "memchr"
+version = "2.7.5"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "32a282da65faaf38286cf3be983213fcf1d2e2a58700e808f83f4ea9a4804bc0"
+
+[[package]]
+name = "normalize-line-endings"
+version = "0.3.0"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "61807f77802ff30975e01f4f071c8ba10c022052f98b3294119f3e615d13e5be"
+
+[[package]]
+name = "num-traits"
+version = "0.2.19"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "071dfc062690e90b734c0b2273ce72ad0ffa95f0c74596bc250dcfd960262841"
+dependencies = [
+ "autocfg",
+]
+
+[[package]]
+name = "predicates"
+version = "3.1.3"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "a5d19ee57562043d37e82899fade9a22ebab7be9cef5026b07fda9cdd4293573"
+dependencies = [
+ "anstyle",
+ "difflib",
+ "float-cmp",
+ "normalize-line-endings",
+ "predicates-core",
+ "regex",
+]
+
+[[package]]
+name = "predicates-core"
+version = "1.0.9"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "727e462b119fe9c93fd0eb1429a5f7647394014cf3c04ab2c0350eeb09095ffa"
+
+[[package]]
+name = "predicates-tree"
+version = "1.0.12"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "72dd2d6d381dfb73a193c7fca536518d7caee39fc8503f74e7dc0be0531b425c"
+dependencies = [
+ "predicates-core",
+ "termtree",
+]
+
+[[package]]
+name = "proc-macro2"
+version = "1.0.101"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "89ae43fd86e4158d6db51ad8e2b80f313af9cc74f5c0e03ccb87de09998732de"
+dependencies = [
+ "unicode-ident",
+]
+
+[[package]]
+name = "quote"
+version = "1.0.40"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "1885c039570dc00dcb4ff087a89e185fd56bae234ddc7f056a945bf36467248d"
+dependencies = [
+ "proc-macro2",
+]
+
+[[package]]
+name = "regex"
+version = "1.11.2"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "23d7fd106d8c02486a8d64e778353d1cffe08ce79ac2e82f540c86d0facf6912"
+dependencies = [
+ "aho-corasick",
+ "memchr",
+ "regex-automata",
+ "regex-syntax",
+]
+
+[[package]]
+name = "regex-automata"
+version = "0.4.10"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "6b9458fa0bfeeac22b5ca447c63aaf45f28439a709ccd244698632f9aa6394d6"
+dependencies = [
+ "aho-corasick",
+ "memchr",
+ "regex-syntax",
+]
+
+[[package]]
+name = "regex-syntax"
+version = "0.8.6"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "caf4aa5b0f434c91fe5c7f1ecb6a5ece2130b02ad2a590589dda5146df959001"
+
+[[package]]
+name = "serde"
+version = "1.0.226"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "0dca6411025b24b60bfa7ec1fe1f8e710ac09782dca409ee8237ba74b51295fd"
+dependencies = [
+ "serde_core",
+]
+
+[[package]]
+name = "serde_core"
+version = "1.0.226"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "ba2ba63999edb9dac981fb34b3e5c0d111a69b0924e253ed29d83f7c99e966a4"
+dependencies = [
+ "serde_derive",
+]
+
+[[package]]
+name = "serde_derive"
+version = "1.0.226"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "8db53ae22f34573731bafa1db20f04027b2d25e02d8205921b569171699cdb33"
+dependencies = [
+ "proc-macro2",
+ "quote",
+ "syn",
+]
+
 [[package]]
 name = "sqlparser"
 version = "0.45.0"
@@ -23,10 +225,44 @@ dependencies = [
  "log",
 ]
 
+[[package]]
+name = "syn"
+version = "2.0.106"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "ede7c438028d4436d71104916910f5bb611972c5cfd7f89b8300a8186e6fada6"
+dependencies = [
+ "proc-macro2",
+ "quote",
+ "unicode-ident",
+]
+
+[[package]]
+name = "termtree"
+version = "0.5.1"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "8f50febec83f5ee1df3015341d8bd429f2d1cc62bcba7ea2076759d315084683"
+
+[[package]]
+name = "unicode-ident"
+version = "1.0.19"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "f63a545481291138910575129486daeaf8ac54aee4387fe7906919f7830c7d9d"
+
+[[package]]
+name = "wait-timeout"
+version = "0.2.1"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "09ac3b126d3914f9849036f826e054cbabdc8519970b8998ddaf3b5bd3c65f11"
+dependencies = [
+ "libc",
+]
+
 [[package]]
 name = "xlg-sqlfmt"
 version = "0.1.1"
 dependencies = [
  "anyhow",
+ "assert_cmd",
+ "predicates",
  "sqlparser",
 ]

@@ -15,3 +15,7 @@ readme = "README.md"
 [dependencies]
 sqlparser = "0.45"
 anyhow = "1.0"
+
+[dev-dependencies]
+assert_cmd = "2.0"
+predicates = "3.0"