codesnips
go 48 lines · 1 tab

Safe dynamic SQL with squirrel (optional filters, stable ordering)

Leah Thompson Jan 2026
1 tab 
package store

import (
  "context"

  sq "github.com/Masterminds/squirrel"
  "github.com/jackc/pgx/v5/pgxpool"
)

type Filters struct {
  Status string
  Owner  string
  Limit  uint64
}

func Find(ctx context.Context, db *pgxpool.Pool, f Filters) ([]string, error) {
  q := sq.Select("id").From("tasks").PlaceholderFormat(sq.Dollar).OrderBy("created_at DESC")
  if f.Status != "" {
    q = q.Where(sq.Eq{"status": f.Status})
  }
  if f.Owner != "" {
    q = q.Where(sq.Eq{"owner_id": f.Owner})
  }
  if f.Limit == 0 || f.Limit > 200 {
    f.Limit = 50
  }
  q = q.Limit(f.Limit)

  sqlStr, args, err := q.ToSql()
  if err != nil {
    return nil, err
  }
  rows, err := db.Query(ctx, sqlStr, args...)
  if err != nil {
    return nil, err
  }
  defer rows.Close()

  ids := make([]string, 0, f.Limit)
  for rows.Next() {
    var id string
    if err := rows.Scan(&id); err != nil {
      return nil, err
    }
    ids = append(ids, id)
  }
  return ids, rows.Err()
}
1 file · go Explain with highlit

Endpoints with optional filters often devolve into messy SQL string concatenation. I prefer building queries with squirrel so I can conditionally add WHERE clauses while keeping the final query parameterized. The pattern also helps keep ordering stable and limits bounded, which matters for pagination and cacheability. I still rely on the database for correctness (constraints) and performance (indexes), but the builder keeps application code readable and less error-prone. Another benefit is testability: you can unit test the generated SQL for key conditions without mocking the DB. This is particularly useful for admin search endpoints where new filters are added frequently.

Related snips

go 
package api

import (
  "net/http"
  "runtime/debug"
)

Expose build metadata for debugging deploys

go observability build
by Leah Thompson 1 tab
go 
package dbutil

import (
  "context"

  "github.com/jackc/pgconn"

Retry Postgres serialization failures with bounded attempts

go postgres transactions
by Leah Thompson 1 tab
ruby 
require "csv"

class PeopleCsvStream
  include Enumerable

  HEADERS = %w[id full_name email signed_up_at plan].freeze

Resilient CSV Export as a Streamed Response

rails performance streaming
by codesnips 3 tabs
ruby 
# BAD: N+1 query problem
@users = User.all
@users.each do |user|
  puts user.posts.count  # Fires query for each user!
end

ActiveRecord query optimization and N+1 prevention

ruby rails activerecord
by Sarah Mitchell 3 tabs
ruby 
class AddSettingsToAccounts < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

  def change
    add_column :accounts, :settings, :jsonb, null: false, default: {}

Postgres JSONB Partial Index for Feature Flags

rails postgres jsonb
by codesnips 3 tabs
go 
package files

import (
  "context"
  "time"

Presigned S3 upload URLs (AWS SDK v2)

go aws s3
by Leah Thompson 1 tab

Share this code

Here's the card — post it anywhere.

Safe dynamic SQL with squirrel (optional filters, stable ordering) — share card
Link copied