database

# Vulnerable: user input is concatenated directly into SQL.
email = params[:email]
password = params[:password]

sql = "SELECT * FROM users WHERE email = '#{email}' AND password_hash = '#{password}'"
user = ActiveRecord::Base.connection.execute(sql).first

-- EXPLAIN ANALYZE (actual execution statistics)
EXPLAIN ANALYZE
SELECT u.username, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'

from django.db.models import Count, Avg, Sum, Q, F
from django.views.generic import TemplateView
from products.models import Product, Order, OrderItem


class DashboardView(TemplateView):

class ReportQuery
  SQL = <<~SQL.freeze
    SELECT date_trunc('day', events.created_at) AS day,
           count(*) AS total,
           count(*) FILTER (WHERE events.kind = 'purchase') AS purchases
    FROM events

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

-- Logical backup with pg_dump
-- Single database
-- pg_dump -h localhost -U postgres -d mydb -F c -f mydb_backup.dump

-- All databases
-- pg_dumpall -h localhost -U postgres -f all_databases.sql

class AddDeletedAtToPosts < ActiveRecord::Migration[6.1]
  def change
    add_column :posts, :deleted_at, :datetime
    add_index :posts, :deleted_at
  end
end

from django.db import connection
from blog.models import Post


def get_top_authors(limit=10):
    """Get authors with most published posts using raw SQL."""

from django.db.models import F, Window
from django.db.models.functions import RowNumber, Rank, DenseRank
from products.models import Sale


def get_sales_with_ranking():

import { Pool, PoolClient, Client, QueryResult, QueryResultRow } from 'pg';

const MAX_LIFETIME_MS = 30 * 60 * 1000;

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,

module Middleware
  class DatabasePinning
    PIN_WINDOW = 5.seconds

    def initialize(app)
      @app = app

# Create table migration
class CreateUsers < ActiveRecord::Migration[7.0]
  def change
    create_table :users do |t|
      t.string :email, null: false, index: { unique: true }
      t.string :name, null: false