SQLYac

like httpyac but for sql files

What is SQLYac?

SQLYac lets you write multiple sql queries in a single file and execute them individually from the command line. Write your queries in an organized file, then pipe specific ones to your database tools of choice.

Why?

Ever find yourself with a bunch of sql snippets scattered across files, copying/pasting queries from your editor to the terminal or sql shell? SQLYac lets you:

• Organize related queries in one file
• Run specific queries by name
• Pipe results directly to mysql, sqlite3, psql, etc.
• Maintain your sql in version control with proper organization
• Add confirmation before running potentially destructive queries (see the config section below)

Installation

go install github.com/kalli/sqlyac
# or just `go run main.go` if you're feeling it

Usage

# list all available queries
sqlyac ls example.sql

# search by name, description, or SQL body (case-insensitive)
sqlyac search example.sql users

# run a specific query
sqlyac example.sql QueryName | mysql -u user -p database

# with flags (same thing)
sqlyac --file example.sql --name QueryName | sqlite3 db.sqlite

# print the installed version
sqlyac version

sqlyac <file> with no query name does the same thing as sqlyac ls <file> — both list every query in the file, one per line. If any query has a @db or @desc annotation, a tab-aligned table is shown instead.

File format

Use three dashes (---) as separators between queries, annotate your queries with @name. Example:

---
-- @name GetActiveUsers
SELECT user_id, username, last_login 
FROM Users 
WHERE active = 1
ORDER BY last_login DESC;
---

---
-- @name GetLargeOrders
SELECT order_id, customer_id, total_amount
FROM Orders 
WHERE total_amount > 1000
AND created_at > DATE_SUB(NOW(), INTERVAL 30 DAY);
---

You can also document each query with -- @desc <text>. Descriptions show up in sqlyac ls output and are matched by sqlyac search:

---
-- @name GetActiveUsers
-- @desc Find users with login in the last 30 days
SELECT * FROM users WHERE active = 1;
---

Variables

SQLYac supports variables for reusable values across queries. Define variables using SET @variable_name="value" syntax anywhere in your file, then reference them in queries using @variable_name. Here's an example:

-- @name QueryWithVariables
SELECT * FROM orders o, users u
WHERE u.id=@user_id 
AND u.active=@active
AND o.status=@status
LIMIT @lim;

SET @user_id=2;
SET @lim=10;
SET @active=true;
SET @status="completed";

When you run sqlyac file.sql QueryWithVariables, the output will be:

SELECT * FROM orders o, users u
WHERE u.id=2
AND u.active=true
AND o.status="completed"
LIMIT 10;

Examples

Explore what's available

$ sqlyac ls example.sql
CreateUsersTable
CreateOrdersTable
InsertSampleUsers
InsertSampleOrders
GetAllUsers
GetActiveUsers
GetLargeOrders
GetUserOrderSummary
GetRecentOrders
CountOrdersByStatus
CleanupTestData
QueryWithVariables

12 queries

Run a query:

$ sqlyac analytics.sql GetActiveUsers | mysql -u admin -p ecommerce_db --table
Enter password: 
+--------+----------+---------------------+
| user_id| username | last_login          |
+--------+----------+---------------------+
|   1234 | alice    | 2024-03-15 14:30:22 |
|   5678 | bob      | 2024-03-14 09:15:11 |
+--------+----------+---------------------+

Pipe to file:

$ sqlyac analytics.sql GetLargeOrders | mysql -u admin -p ecommerce_db > large_orders.csv

Workflow

1. Write your queries in .sql files, separate them by dashes (---) and annotate with @name
2. Version control your sql alongside your code
3. Run queries directly from terminal
4. Pipe results to any database tool or file

Running queries directly (optional)

If you don't want to type | mysql -u … -p … every time, you can define named connections and have sqlyac run the query for you. The pipe workflow above still works — this is purely opt-in.

1. Define your connections in ~/.sqlyac/config.json (personal) or .sqlyac.json in your project root (committable). Each connection is just a shell command — sqlyac pipes the rendered SQL into its stdin via sh -c.

{
  "connections": {
    "local":  { "command": "sqlite3 ./app.db" },
    "prod":   { "command": "mysql -u admin -p${DB_PASSWORD} -h prod.db mydb" }
  }
}

Use ${VAR} to pull secrets from your environment (unset vars expand to empty with a warning on stderr). Because the command runs through sh -c, you can include pipes, redirects, etc.

sqlyac walks up from the current directory looking for .sqlyac.json, then merges with ~/.sqlyac/config.json. Project connections win on name collisions; confirm flags come from user config.

2. Annotate your SQL file with -- @db <name>:

-- @db local

---
-- @name GetUsers
SELECT * FROM users;
---

---
-- @name GetUsersFromProd
-- @db prod
SELECT * FROM users WHERE active = 1;
---

File-level annotations (before the first ---) set the default for all queries. Query-level annotations override the file default for that one query.

3. Run with -x (or --exec):

# uses @db from the file
sqlyac example.sql GetUsers -x

# override at the CLI
sqlyac example.sql GetUsers -x --db prod

Without -x, sqlyac still just prints the SQL to stdout — exactly as before — so sqlyac file.sql Foo | mysql … keeps working.

Shell completion

sqlyac tab-completes query names by parsing the referenced .sql file. With completion installed, sqlyac example.sql Get<TAB> suggests GetActiveUsers, GetAllUsers, etc.

Install for your shell:

# zsh
sqlyac completion zsh > "$(brew --prefix)/share/zsh/site-functions/_sqlyac"

# bash
sqlyac completion bash > /usr/local/etc/bash_completion.d/sqlyac

# fish
sqlyac completion fish > ~/.config/fish/completions/sqlyac.fish

For a one-off (current shell only):

source <(sqlyac completion zsh)

See sqlyac completion <shell> --help for the exact install path on your system.

Config

You can save a configuration file in ~/.sqlyac/config.json with the following settings:

• confirm - Ask for confirmation on all queries.
• confirm_schema_changes - Ask for confirmation on any queries that change the database schema (i.e. drop table, alter table etc).
• confirm_updates boolean - Ask for confirmation on any queries that create, update or delete rows.
• connections - Named shell commands for running queries directly via -x (see Running queries directly above).

Here's an example that would ask for confirmation on all updates, inserts and schema changes:

{
    "confirm": false,
    "confirm_schema_changes": true,
    "confirm_updates": true
}

Running any commands with the --confirm toggle overrides your config and asks for confirmation every time.

Notes

• only parses .sql files
• ignores comment lines (except @name, @db, and @desc annotations)
• strips leading/trailing whitespace from queries
• pretty forgiving with whitespace in @name annotations

Tests

Run the unit tests like so:

go test ./...

Integration tests (docker)

There's a small integration suite that runs sqlyac end-to-end against real mysql, postgres, and sqlite3 clients in docker. You only need docker — no DB clients installed locally.

make integration-up    # start mysql, postgres, sqlite containers
make integration       # run `go test -tags integration -v ./...`
make integration-down  # tear down

The tests are guarded by the integration build tag, so they're skipped by go test unless that tag is passed. Each test skips with a helpful message if its container isn't running.

If you want to try -x manually against the same containers, copy .sqlyac.example.json to .sqlyac.json — it has mysql, postgres, and sqlite connections pointing at the docker-compose services.