codesnips

postgres

go 
package store

import (
  "context"

  sq "github.com/Masterminds/squirrel"

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

go sql postgres
by Leah Thompson 1 tab
sql 
CREATE TABLE subscriptions (
    id                  BIGGENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id         BIGINT       NOT NULL,
    plan_code           TEXT         NOT NULL,
    status              TEXT         NOT NULL DEFAULT 'active',
    current_period_end  TIMESTAMPTZ  NOT NULL,

Partial index for “active” rows in Postgres

postgres performance sql
by codesnips 3 tabs
sql 
-- Step 1: add the column nullable, no default.
-- Catalog-only change in Postgres 11+, returns instantly.
ALTER TABLE orders
  ADD COLUMN currency text;

-- Optional: keep the lock attempt bounded so a long-running

SQL migration safety: add column nullable, backfill, then constrain

postgres migrations reliability
by codesnips 3 tabs
go 
package store

import (
  "context"
  "time"

pgxpool initialization with max connections and statement timeout

go postgres pgx
by Leah Thompson 1 tab
ruby 
class ReportsController < ApplicationController
  def monthly_sales
    year = params.fetch(:year, Date.current.year).to_i
    rows = Order.monthly_sales(year: year)
    @report = MonthlySalesReport.new(rows, year: year)

Aggregate Monthly Sales Reports in Rails with group_by SQL and a Presenter

rails postgres reporting
by codesnips 3 tabs
ruby 
module KeysetPaginatable
  extend ActiveSupport::Concern

  class_methods do
    def keyset_page(cursor: nil, limit: 20, direction: :desc)
      limit = limit.to_i.clamp(1, 100)

Keyset Cursor Pagination for ActiveRecord Instead of OFFSET

ruby rails activerecord
by codesnips 4 tabs
go 
package feed

import (
	"encoding/base64"
	"encoding/json"
	"time"

Cursor-Based Keyset Pagination for a Postgres Feed API in Go

go postgres pagination
by codesnips 3 tabs
ruby 
class CreateOutboxEvents < ActiveRecord::Migration[7.1]
  def change
    create_table :outbox_events do |t|
      t.string :event_type, null: false
      t.string :aggregate_type, null: false
      t.string :aggregate_id, null: false

Transactional Outbox for Reliable Event Publishing

rails background-jobs reliability
by codesnips 4 tabs
go 
package store

import (
  "context"
  "database/sql"
)

db/sql prepared statements with context and explicit Close

go sql postgres
by Leah Thompson 1 tab
typescript 
import { Prisma, PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

type TxClient = Prisma.TransactionClient;

Prisma transaction with retries for serialization errors

prisma postgres concurrency
by codesnips 3 tabs
python 
from alembic import op
import sqlalchemy as sa

revision = "20240612_add_status"
down_revision = "20240515_create_orders"
branch_labels = None

Zero-Downtime NOT NULL Column Backfill with Alembic and SQLAlchemy

alembic sqlalchemy migrations
by codesnips 2 tabs
ruby 
class LastSeenTracker
  THROTTLE = 5.minutes
  PENDING_KEY = "pending:last_seen".freeze

  class << self
    def touch(user_id, at: Time.current)

Database “Last Seen” without Hot Row Updates

rails performance redis
by codesnips 3 tabs