A Ruby gem that generates SQL SELECT queries from natural language using AI providers (OpenAI, Claude, Gemini).

• 🤖 Integration with OpenAI, Claude (Anthropic), and Google Gemini
• 📊 Automatic database schema loading from files or database connections
• ⚙️ Configurable rules and conventions via YAML files
• 🚂 Rails integration with optional controller and routes
• 🔒 SQL validation to prevent dangerous operations
• 🎯 Customizable prompts and AI parameters

Add this line to your application's Gemfile:

gem 'magic_query'

And then execute:

$ bundle install

Or install it yourself as:

$ gem install magic_query

require 'magic_query' # Configure the gem MagicQuery.configure do |config| config.provider = :openai config.api_key = ENV['MAGIC_QUERY_API_KEY'] config.schema_path = 'config/schema.sql' config.rules_path = 'config/magic_query.yml' end # Generate a SQL query generator = MagicQuery::QueryGenerator.new sql = generator.generate("trova tutti gli utenti attivi") # => "SELECT * FROM users WHERE status = 'active'"

1. Run the generator to install configuration files:

rails generate magic_query:install

This will create:

• config/initializers/magic_query.rb - Configuration file
• config/magic_query.yml - Rules and conventions file

2. Configure your API key and settings in config/initializers/magic_query.rb:

MagicQuery.configure do |config| config.provider = :openai config.api_key = ENV['MAGIC_QUERY_API_KEY'] config.schema_path = Rails.root.join('config', 'schema.sql').to_s config.rules_path = Rails.root.join('config', 'magic_query.yml').to_s end

3. Use the controller endpoint (optional):

# POST /magic_query/generate # Body: { "query": "trova tutti gli utenti attivi" } # Response: { "sql": "SELECT * FROM users WHERE status = 'active'", "query": "..." }

For detailed information about the controller, customization options, and how to override methods, see CONTROLLER.md.

MagicQuery.configure do |config| config.provider = :openai config.api_key = ENV['MAGIC_QUERY_API_KEY'] config.model = 'gpt-4o-mini' # Optional, defaults to gpt-4o-mini end

MagicQuery.configure do |config| config.provider = :claude config.api_key = ENV['ANTHROPIC_API_KEY'] config.model = 'claude-3-5-sonnet-20241022' # Optional end

MagicQuery.configure do |config| config.provider = :gemini config.api_key = ENV['GEMINI_API_KEY'] config.model = 'gemini-1.5-flash' # Optional end

config.schema_path = 'config/schema.sql'

The schema file can be:

• SQL file with CREATE TABLE statements
• YAML file with structured schema definition

config.database_url = ENV['DATABASE_URL']

Create a YAML file (config/magic_query.yml) with your database rules:

naming_conventions: table_prefix: '' column_naming: 'snake_case' relationships: - 'users has_many posts' - 'posts belongs_to users' business_rules: - 'Active users have status = "active"' - 'Use soft deletes where applicable' tables: users: description: 'User accounts table' important_columns: - 'id (primary key)' - 'email (unique, required)'

config.temperature = 0.3 # Lower = more deterministic config.max_tokens = 1000 # Maximum response length config.base_prompt = 'Your custom prompt here' # Optional

Configure the gem globally:

MagicQuery.configure do |config| # Configuration options end

Main class for generating SQL queries:

generator = MagicQuery::QueryGenerator.new(config) sql = generator.generate("user input string")

This project includes Docker support for running tests and linting without requiring Ruby to be installed locally.

Build the Docker image:

docker-compose build

Run all tests:

docker-compose run --rm app bundle exec rspec

Run tests with coverage:

docker-compose run --rm app bundle exec rspec

Run RuboCop:

docker-compose run --rm app bundle exec rubocop

Auto-fix RuboCop offenses:

docker-compose run --rm app bundle exec rubocop -a

Run default task (tests + lint):

docker-compose run --rm app bundle exec rake

Run only tests:

docker-compose run --rm app bundle exec rake spec

Run only lint:

docker-compose run --rm app bundle exec rake rubocop

Open an interactive shell in the container:

docker-compose run --rm app bash

If you have Ruby installed locally, after checking out the repo, run:

bundle install

Run tests:

bundle exec rspec

Run linter:

bundle exec rubocop

Bug reports and pull requests are welcome on GitHub at https://github.com/gioggi/magic_query.

The gem is available as open source under the terms of the Apache License 2.0.

Copyright (c) 2026 Giovanni Esposito