Sqlglot

gem "sqlglot"

gem "sqlglot", github: "AccountAim/sql-glot-ruby"  # requires Rust
gem "sqlglot", path: "/path/to/this/repo"           # requires Rust

Sqlglot.transpile("SELECT NOW()", from: :postgres, to: :bigquery)
# => "SELECT CURRENT_TIMESTAMP()"

Sqlglot.transpile("SELECT * FROM t LIMIT 10", from: :mysql, to: :tsql)
# => "SELECT TOP 10 * FROM t"

Sqlglot.transpile("SELECT NVL(a, b) FROM t", from: :oracle, to: :tsql)
# => "SELECT ISNULL(a, b) FROM t"

Sqlglot.transpile(sql, from: :postgres, to: :bigquery)
Sqlglot.transpile(sql, from: "postgres", to: "bigquery")
Sqlglot.transpile(sql, from: Sqlglot::Dialect::POSTGRES, to: Sqlglot::Dialect::BIGQUERY)

ast = Sqlglot.parse("SELECT name, age FROM users WHERE active = true", dialect: :postgres)
# => {"Select" => {"columns" => [...], "from" => {...}, "where_clause" => {...}, ...}}

ast = Sqlglot.parse("SELECT name FROM users WHERE id = 1")

# Roundtrip back to SQL
Sqlglot.generate(ast)
# => "SELECT name FROM users WHERE id = 1"

# Generate for a different dialect
Sqlglot.generate(ast, dialect: :tsql)

ast = Sqlglot.parse("SELECT name FROM users WHERE id = 1")

# Change the WHERE value
ast["Select"]["where_clause"]["BinaryOp"]["right"] = { "Number" => "42" }

Sqlglot.generate(ast)
# => "SELECT name FROM users WHERE id = 42"

Sqlglot.version
# => "0.10.0"

q = Sqlglot::Query.new(<<~SQL, dialect: :postgres)
  SELECT u.name, COUNT(o.id) AS order_count
  FROM users AS u
  JOIN orders AS o ON u.id = o.user_id
  WHERE u.active = true
  ORDER BY order_count DESC
  LIMIT 10 OFFSET 20
SQL

q.query_type  # => :select
# Also: :insert, :update, :delete, :create_table, :merge, etc.

q.tables          # => ["users", "orders"]
q.tables_aliases  # => {"u" => "users", "o" => "orders"}

q = Sqlglot::Query.new("WITH cte AS (SELECT id FROM real_table) SELECT * FROM cte")
q.tables  # => ["real_table"]

q.columns
# => ["users.name", "orders.id", "users.id", "orders.user_id", "users.active"]

# Table aliases are resolved: u.name becomes users.name

q.columns_dict
# => {
#   select:   ["users.name", "orders.id"],
#   join:     ["users.id", "orders.user_id"],
#   where:    ["users.active"],
#   order_by: ["orders.id"]   # resolved from the "order_count" alias
# }

q.output_columns  # => ["name", "order_count"]

q = Sqlglot::Query.new("SELECT a, b + c AS total FROM t ORDER BY total")

q.columns_aliases        # => {"total" => ["b", "c"]}
q.columns_aliases_names  # => ["total"]
q.columns_aliases_dict   # => {select: ["total"], order_by: ["total"]}

q = Sqlglot::Query.new(<<~SQL)
  WITH active AS (SELECT * FROM users WHERE active = true)
  SELECT * FROM active
SQL

q.with_names    # => ["active"]
q.with_queries  # => {"active" => "SELECT * FROM users WHERE active = true"}

q = Sqlglot::Query.new(<<~SQL)
  SELECT * FROM (SELECT id FROM users) AS sub
  JOIN orders ON sub.id = orders.user_id
SQL

q.subqueries       # => {"sub" => "SELECT id FROM users"}
q.subqueries_names # => ["sub"]

q.limit_and_offset  # => [10, 20]

# Returns nil when no LIMIT is present
Sqlglot::Query.new("SELECT 1").limit_and_offset  # => nil

q = Sqlglot::Query.new("INSERT INTO users (name, age) VALUES ('Alice', 30)")

q.values       # => ["Alice", 30]
q.values_dict  # => {"name" => "Alice", "age" => 30}

# Auto-generates column names when not specified
q = Sqlglot::Query.new("INSERT INTO t VALUES (1, 'hello')")
q.values_dict  # => {"column_1" => 1, "column_2" => "hello"}

q = Sqlglot::Query.new("SELECT * FROM t WHERE id = 42 AND name = 'Alice'")
q.generalize
# => "SELECT * FROM t WHERE id = N AND name = 'X'"

q.ast  # => the full parsed Hash (same as Sqlglot.parse)

# config/application.rb (or an initializer)
config.sqlglot.default_dialect = :sqlite

Sqlglot.parse("SELECT 1")             # uses :sqlite
Sqlglot.transpile(sql, to: :bigquery) # from: defaults to :sqlite

Sqlglot.configure do |c|
  c.default_dialect = :sqlite
end