How do we store data in a structured, organized way that ensures data integrity, enables complex queries, and maintains relationships between different pieces of information? Flat files can’t handle this—SQL databases provide the solution.
SQL (Structured Query Language) databases store data in rigidly defined tables with rows and columns. Each table has a schema that defines what columns exist and what types they hold. Relationships between tables are defined through foreign keys, enabling joins and complex queries.
- Schema Definition: You define tables with columns and data types (e.g., users table has id, name, email)
- Data Insertion: Insert rows into tables following the schema
- Querying: Use SELECT statements to fetch data, with JOINs to combine tables
- Relationships: Foreign keys link tables (users.id = orders.user_id)
- Transactions: Group multiple operations that must all succeed or all fail together
Example: SELECT * FROM users WHERE name = 'ani' returns all rows from users table matching the condition.
- ACID compliant: Atomicity, Consistency, Isolation, Durability
- Predefined schema—all data must conform to table structure
- Powerful queries with JOINs, aggregations, subqueries
- Primary keys, foreign keys enforce relationships and data integrity
- Popular: PostgreSQL, MySQL, SQLite, Oracle
- Builds into: API — backends query databases to serve API responses
- Builds into: Backend as Program — backend programs interact with databases
- Contrasts with: NoSQL Database — different data model and trade-offs
- Related: SQL Query — the language used to interact with SQL databases
- Schema changes require migrations (adding columns to production tables is complex)
- Horizontal scaling is harder than NoSQL—sharding adds complexity
- Complex joins can be slow on large datasets
- Object-relational impedance mismatch—mapping objects to tables is work