SQL Essentials for Web Developers: Joins, Indexes, CTEs, Window Functions and N+1 Queries

·7 views

A DEV Community guide argues that web developers should understand raw SQL even though ORMs can generate queries for them, since SQL reveals what queries actually run and how to speed them up. It walks through core query patterns such as SELECT with filters, sorting, and pagination, plus INSERT, UPDATE, and upsert syntax for PostgreSQL and MySQL. The article then covers joins, subqueries, and common table expressions for structuring complex queries more readably. It also explains when indexes help or hurt performance, how to check index usage with EXPLAIN, and why cursor-based pagination is more stable than offset pagination. The piece additionally touches on window functions and N+1 query problems, though its excerpt ends before fully covering them.

Read the full story at DEV Community

This is an AI-generated summary. ShortSingh links to the original source for the complete article.

Discussion (0)

Does docker compose down Delete Your Data? Named Volumes Explained

Running docker compose down removes containers and project networks but keeps named volumes, so database files persist across restarts. Adding the --volumes flag also removes the project's managed volumes, which effectively resets stored data. Compose prefixes managed volume names with the project name, so renaming a project or moving its Compose file can make the application attach to a different, empty volume. The article advises checking existing volumes with docker volume ls and docker volume inspect before assuming data is lost. It recommends using stop or down for routine shutdowns, reserving --volumes for deliberate cleanup, and keeping backups of irreplaceable data.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

Voice Signatures Do Not Equal Consent, Experts Argue for Decision Records

A cryptographic signature can show that a specific key approved a specific payload, but it does not prove the voice owner agreed to a particular model, buyer, territory, or term. The article argues that voice platforms must separate technical provenance tools like hashes, watermarks, and signatures from a detailed authorization record that captures purpose, scope, compensation, and withdrawal. Recent cloning guidance increasingly distinguishes permission to create a clone from rights in source recordings and disclosure duties. The author describes a registry design requiring issuer and owner signatures with nonces to prevent replay, while stressing that old approvals should not silently cover new uses. The piece also highlights revocation as a key weakness, citing a Tokyo ruling that recognized publicity rights in an AI-cloned voice.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

DocuScan AI Offers Open-Source Document Parsing Built on Llama 3 and Groq

DocuScan AI is a document intelligence tool submitted to the Hacktoberfest Open-Source AI Challenge. It is built with FastAPI and LangChain for orchestration, using Llama 3 (llama3-70b-8192) served through the Groq Cloud API. The frontend is a responsive dashboard styled with Tailwind CSS, and the application is hosted on Render Web Services. The project is publicly available on GitHub and a live demo is accessible online.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

How to track Google AI Overviews: a manual method and what to measure

Google's AI Overviews are generated summaries shown above standard search results for some queries, and they are not ranked positions the way links are. Marketers can track them by choosing a fixed set of queries, checking them on a regular schedule, and recording whether an Overview appears, whether their brand is named, which sources are cited, and how the brand is described. Key metrics include the trigger rate, the mention rate, and share of voice across competitors. The guide notes that ranking well does not guarantee inclusion in an Overview, and that results can vary by location, wording, and time. A simple spreadsheet process can work for small query sets, though it becomes impractical as the list grows.

0 comments Read more at DEV Community