Database Queries

Guide to writing efficient database queries with porsager/postgres

This guide covers how to write efficient database queries using the porsager/postgres library in the BrewHoard application.

Using the Postgres Tagged Template Literal

The postgres library uses tagged template literals for safe query execution:

JavaScript
import postgres from 'postgres';

const sql = postgres(connectionString);

// Simple query
const beers = await sql`SELECT * FROM beers`;

// Parameterized query
const beer = await sql`SELECT * FROM beers WHERE id = ${beerId}`;

Parameterized Queries (SQL Injection Prevention)

Always use parameterized queries to prevent SQL injection:

JavaScript
// Safe - parameters are automatically escaped
const userBeers = await sql`
  SELECT * FROM beers 
  WHERE user_id = ${userId} 
  AND name ILIKE ${`%${searchTerm}%`}
`;

// Unsafe - don't do this
const unsafeQuery = await sql(`SELECT * FROM beers WHERE user_id = ${userId}`);

Transactions

Use transactions for atomic operations:

JavaScript
const [result] = await sql.begin(async (sql) => {
  // Insert beer
  const [beer] = await sql`
    INSERT INTO beers (name, brewery) 
    VALUES (${name}, ${brewery}) 
    RETURNING *
  `;
  
  // Insert collection entry
  await sql`
    INSERT INTO collection (user_id, beer_id, quantity) 
    VALUES (${userId}, ${beer.id}, ${quantity})
  `;
  
  return beer;
});

JOINs and Relationships

Query related data using JOINs:

JavaScript
// Get beers with brewery information
const beersWithBreweries = await sql`
  SELECT 
    b.*,
    br.name as brewery_name,
    br.country as brewery_country
  FROM beers b
  JOIN breweries br ON b.brewery_id = br.id
  WHERE b.user_id = ${userId}
`;

// Get collection with beer details
const collection = await sql`
  SELECT 
    c.*,
    b.name,
    b.style,
    br.name as brewery_name
  FROM collection c
  JOIN beers b ON c.beer_id = b.id
  JOIN breweries br ON b.brewery_id = br.id
  WHERE c.user_id = ${userId}
`;

Aggregations and Grouping

Use aggregate functions for statistics:

JavaScript
// Count beers by style
const styleCounts = await sql`
  SELECT 
    style,
    COUNT(*) as count
  FROM beers
  WHERE user_id = ${userId}
  GROUP BY style
  ORDER BY count DESC
`;

// Get collection statistics
const stats = await sql`
  SELECT 
    COUNT(*) as total_beers,
    SUM(quantity) as total_quantity,
    AVG(rating) as avg_rating,
    MAX(acquisition_date) as last_acquisition
  FROM collection
  WHERE user_id = ${userId}
`;

Performance Tips

Indexes

Create indexes on frequently queried columns:

SQL
-- Index for user-specific queries
CREATE INDEX idx_beers_user_id ON beers(user_id);

-- Composite index for complex queries
CREATE INDEX idx_collection_user_beer ON collection(user_id, beer_id);

-- Partial index for active records
CREATE INDEX idx_beers_active ON beers(user_id) WHERE deleted_at IS NULL;

EXPLAIN Analysis

Use EXPLAIN to analyze query performance:

JavaScript
// Analyze a query
const analysis = await sql`
  EXPLAIN ANALYZE 
  SELECT * FROM beers 
  WHERE user_id = ${userId} 
  AND style = ${style}
`;

// Log the execution plan
console.log(analysis);

Query Optimization Tips

  • Use LIMIT for large result sets
  • Avoid SELECT * in production
  • Use appropriate JOIN types (INNER, LEFT, etc.)
  • Consider denormalization for read-heavy workloads
  • Use connection pooling for high traffic

Next Steps