GitHub - dweinstein/db_sampler: quickly sample data from postgres db from elixir using postgrex, from either a table or sql (e.g., CTE)

DbSampler

Sample rows from a PostgreSQL database and export to NDJSON.

Installation

Add to your mix.exs:

def deps do
  [{:db_sampler, git: "https://github.com/dweinstein/db_sampler.git"}]
end

Set your database URL:

export DATABASE_URL="postgresql://user:password@localhost:5432/mydb"

Usage

Sample from a table

mix sample --table users --limit 100
mix sample -t orders -l 500 -o orders.ndjson
mix sample --table products --order "created_at DESC"

Execute SQL from a template file

mix sample --sql examples/queries/users_sample.sql.eex
mix sample --sql examples/queries/users_sample.sql.eex --var limit=100

Options

Flag	Short	Description
`--table`	`-t`	Table to sample (required in table mode)
`--sql`	`-s`	Path to `.sql.eex` file (SQL mode)
`--limit`	`-l`	Number of rows (default: 100)
`--output`	`-o`	Output file (default: sample.ndjson)
`--order`	`-r`	ORDER BY clause
`--timeout`		Query timeout in ms (default: 60000)
`--var`		Pass variable to template (repeatable)

SQL Templates

SQL files use EEx templating. Built-in variables:

@dev, @test, @prod - environment flags
@date - current UTC date
assigns[:var] - custom variables via --var

Example:

SELECT * FROM users
ORDER BY created_at DESC
<%= if @dev do %>
LIMIT <%= assigns[:limit] || 100 %>
<% end %>

Programmatic API

{:ok, rows} = DbSampler.sample_table("users", limit: 100)
{:ok, count} = DbSampler.export_table("users", "output.ndjson")
{:ok, rows} = DbSampler.query_file("queries/report.sql.eex", assigns: [limit: 50])

See mix docs for full API documentation.

Security

This tool executes SQL queries directly against your database. SQL template files (.sql.eex) can contain arbitrary Elixir code via EEx.

This tool is intended for use by trusted developers and operators who already have database access. Do not expose the --sql or --var flags to untrusted input.