Structured Query Language, the standard language for interacting with relational databases. It covers querying data with SELECT, modifying it with INSERT, UPDATE, and DELETE, defining schemas with CREATE and ALTER, and controlling access with GRANT and REVOKE. SQL is the universal interface to databases like PostgreSQL, MySQL, SQLite, and SQL Server.
Database security is built on a layered model: roles group privileges, privileges control access to objects, and additional mechanisms—row-level security, encryption, masking, and auditing—protect data beyond simple access control. The SQL standard defines GRANT and REVOKE as the verbs for assigning and removing privileges, with PUBLIC as the keyword for all users . Every major…
SQL injection remains one of the most dangerous and persistent web vulnerabilities, yet it is also one of the most completely solvable. The root cause is always the same: an application builds a SQL query by concatenating user input into a string, and the database cannot distinguish the developer’s intended query structure from the attacker’s…
Row-level security (RLS) and data masking are two distinct controls that are frequently mentioned together because they both restrict what a user can see. They operate at different granularities, however, and they solve different problems. RLS filters which rows a query returns—a user in one department sees only that department’s records, while a user in…
Relational databases were built for structured, tabular data. JSON is semi-structured, schema-flexible, and often nested. For years, the two existed in separate worlds: you either modeled everything relationally or you stored JSON as an opaque string in a text column and parsed it in application code. That changed when every major engine introduced native JSON…
Full-text search is the database feature that lets users find documents by their content rather than by exact column values. A LIKE ‘%term%’ query scans every row and matches character patterns, which is slow and linguistically naive. Full-text search instead builds an inverted index: a mapping from words to the rows that contain them, with…
Table partitioning is the practice of splitting one large logical table into smaller physical pieces called partitions, while keeping it accessible as a single table to the application. The motivation is not raw query speed—a well-indexed table can be fast without partitioning. The motivation is manageability at scale: a table with billions of rows becomes…
Locks are the mechanism that makes concurrent transactions safe. When one transaction modifies a row, the database places a lock on it so that another transaction cannot read or write the same row until the first one finishes. This is not a bug or a performance problem—it is the foundation of the ACID model, the…
An upsert is a single operation that inserts a row if it does not exist, or updates it if it does. The name is a contraction of “update” and “insert,” and the pattern exists because the alternative—a SELECT followed by an INSERT or UPDATE—has a race condition. Two concurrent transactions can both SELECT, both see…
Sharding and read replicas are the two primary strategies for scaling a database beyond a single server, and they solve completely different problems. Read replicas create copies of the entire database that serve read-only queries, reducing the load on the primary instance for read-heavy workloads . Sharding partitions the data itself across multiple independent database…
A database backup is not a single artifact. It is a strategy composed of different backup types, each solving a different part of the recovery problem. A full backup captures the entire database at a point in time. A differential backup captures only the pages that changed since the last full backup. A transaction log…