Project

magic_query

0.0
The project is in a healthy, maintained state
magic_querygioggi/magic_queryHomepageDocumentationSource CodeBug TrackerWiki
Magic Query integrates with OpenAI, Claude, and Gemini to generate SQL SELECT queries from natural language input, using database schema and configuration rules.
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
 Popularity
Downloads
201
Stars
4
Forks
0
Watchers
0
 Releases
Current version
0.1.2
Total releases
2
First release
2025-12-16
Latest release
2025-12-16
 Development
Primary Language
Ruby
Licenses
Apache-2.0
Average date of last 50 commits
2025-12-11
Reverse Dependencies
0

 Dependencies

Development

rspec
~> 3.12
rspec-rails
~> 6.0
rubocop
~> 1.81
rubocop-rspec
~> 2.25
simplecov
~> 0.22
webmock
~> 3.18

Runtime

activesupport
>= 6.0
faraday
~> 2.0
 Project Readme

Magic Query

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

Features

  • 🤖 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

Installation

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

Usage

Basic Usage

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'"

Rails Integration

  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
  1. 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
  1. 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.

Configuration

Providers

OpenAI

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

Claude (Anthropic)

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

Gemini (Google)

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

Schema Configuration

From File

config.schema_path = 'config/schema.sql'

The schema file can be:

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

From Database (Future)

config.database_url = ENV['DATABASE_URL']

Rules Configuration

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)'

AI Parameters

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

API Reference

MagicQuery.configure

Configure the gem globally:

MagicQuery.configure do |config|
  # Configuration options
end

MagicQuery::QueryGenerator

Main class for generating SQL queries:

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

Development

Using Docker (Recommended)

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

Setup

Build the Docker image:

docker-compose build

Running Tests

Run all tests:

docker-compose run --rm app bundle exec rspec

Run tests with coverage:

docker-compose run --rm app bundle exec rspec

Running Linter

Run RuboCop:

docker-compose run --rm app bundle exec rubocop

Auto-fix RuboCop offenses:

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

Running Rake Tasks

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

Interactive Shell

Open an interactive shell in the container:

docker-compose run --rm app bash

Local Development

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

bundle install

Run tests:

bundle exec rspec

Run linter:

bundle exec rubocop

Contributing

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

License

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

Copyright

Copyright (c) 2026 Giovanni Esposito