SQL Fundamentals

Most web applications rely on storing their data in a relational database, consisting of tables which are comprised of columns and rows. PostgreSQL, MySQL and SQLite are the most popular and established relational databases, and luckily, they have a LOT in common.

SQL Fundamentals

The Schema Evolves

Over time, you will often need to update the schema or "shape" of your data to meet your application's needs. In this unit, we will learn about using migrations to manage these changes. We can apply database-level constraints via the schema to ensure that even if our application logic misbehaves, our data is always consistent.

  • The Schema EvolvesMigrations

    As a database-driven system evolves, we often need to make changes to its schema. We’ll discuss best practices for treating a database as a semi-free-standing system component and learn how to commit schema changes to a git repository along with our source code, to provide ourselves with a reliable way to maintain multiple environments across a team.

  • The Schema EvolvesIndices

    When we create an index in a database, we are telling it to do some bookkeeping as records are added and updated. When the time comes to search in a particular way, results can be retrieved quickly and directly using indices.

  • The Schema EvolvesEXERCISE: Boost JOIN performance via INDEXes

    You may have noticed that our database query times increased over the last few exercises. One contributor to this problem has to do with the JOINs we added in exercise 4. Adding an index will tell the database to keep track of particular slices of data at all times, and should dramatically improve these JOINed queries

  • The Schema EvolvesConstraints

    To ensure data integrity, sometimes we have the option to put constraints directly on a database. For example, if one record refers to another, we can require that the other record actually exists. We’ll look at several different database-level constraints we can put in place, including NOT NULL, UNIQUE indices and foreign keys.

  • The Schema EvolvesEXERCISE: Adding DB constraints

    We’ll add a few constraints to our database to ensure that even if our business logic runs into problems, only consistent and valid records can be stored.

  • The Schema EvolvesWrap up

    We'll recap everything we have learned, and provide some resources for further learning.