codesnips

postgres

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
java 
@RestController
@RequestMapping("/api/customers")
public class CsvImportController {

    private final CsvImportService importService;

Streaming CSV Import in Spring Boot with MultipartFile and JDBC Batch Inserts

spring-boot csv jdbc
by codesnips 4 tabs
ruby 
class DailySignupRollup < ApplicationRecord
  validates :day, presence: true, uniqueness: true
  validates :total, :verified, numericality: { greater_than_or_equal_to: 0 }

  scope :for_range, ->(from, to) do
    where(day: from.to_date..to.to_date).order(:day)

Nightly Signup Rollups in Rails with a Scheduled Job and Upsert

rails postgres background-jobs
by codesnips 3 tabs
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
typescript 
import {
  Column,
  Entity,
  PrimaryGeneratedColumn,
  UpdateDateColumn,
  VersionColumn,

Optimistic Locking in NestJS with a TypeORM Version Column and Conflict Exception Filter

nestjs typeorm optimistic-locking
by codesnips 4 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 CreateWebhookEvents < ActiveRecord::Migration[7.1]
  def change
    create_table :webhook_events do |t|
      t.string :event_id, null: false
      t.string :source, null: false, default: "stripe"
      t.string :event_type, null: false

Idempotent Stripe Webhook Processing with a Unique Event Key in Rails

rails postgres webhooks
by codesnips 4 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