alexdesousa / ayesql

Library for using raw SQL in Elixir
MIT License
138 stars 14 forks source link

:sparkles: Add duckdb runner #37

Closed alexdesousa closed 1 month ago

alexdesousa commented 1 month ago

This PR adds a Duckdbex runner to AyeSQL. This closes #36.

For many years, AyeSQL only supported Ecto and PostgreSQL, but DuckDB looks like a nice runner to have given its capabilities to query different types of files (CSV, XLS, etc.) with SQL.

Example

Given the following AyeSQL file:

-- name: get_player_by_last_name
-- docs: Get players by their last name
SELECT *
FROM atp
WHERE name_last = :last_name

And the following AyeSQL module:

defmodule ATP do
  use AyeSQL,
    runner: AyeSQL.Runner.Duckdbex

  defqueries "./atp.sql"
end

Then we can query a CSV file as follows:

# Open a DuckDB connection
{:ok, db} = Duckdbex.open()
{:ok, conn} = Duckdbex.connection(db)

# Fetch a remote CSV and copy its contents to the table `atp`
url = "https://raw.githubusercontent.com/duckdb-in-action/examples/refs/heads/main/ch05/atp/atp_players.csv"
{:ok, _res} = Duckdbex.query(conn, "INSTALL httpfs")
{:ok, _res} = Duckdbex.query(conn, "LOAD httpfs")
{:ok, _res} = Duckdbex.query(conn, "CREATE OR REPLACE TABLE atp AS FROM '#{url}'")

# Run our query to find a player by last name
ATP.get_player_by_last_name([last_name: "Federer"], conn: conn)

This should get the following result:

{:ok,
 [
   %{
     player_id: 103819,
     name_first: "Roger",
     name_last: "Federer",
     hand: "R",
     dob: "19810808",
     ioc: "SUI",
     height: 185,
     wikidata_id: "Q1426"
   }
 ]}