Professional SQL

Most developers stick to performing the basic CRUD operations on their database, but modern projects like SQLite, PostgreSQL and MySQL can do so much more. In this course, we'll discuss a wide range of features that can serve to keep data layer speedy, scalable and consistent.

Professional SQL

Programming your database

There are often advantages to setting up a database so that common tasks can be performed easily and by name. This way, we can more simply refer to these operations in our application logic, and rely on always getting the consistently correct behavior.

  • Programming your database Welcome and Tech Check

    We’ll get to know each other and ensure everyone is set up for the workshop project.

  • Programming your database Views

    Views are just queries stored in our database. We can use them in queries by name, as if they’re another table.

  • Programming your database EXERCISE: Views for Dashboard Stats

    We’ll build a few queries for a “dashboard”, showing high-level statistics from our database. As we may have several applications that should retrieve the same result set, we’ll need to set our queries up as views - this may get a bit complicated.

  • Programming your database Prepared Statements

    Prepared statements allow us to create, parse and plan a parameterized database query. We'll pass values into a statement object later to evaluate it, just like a regular query. Depending on which database solution you are working with, prepared statements may be stored in the database itself (and shared across all clients), or created as an object in your application code.

  • Programming your database EXERCISE: Prepared Statements

    As the database connection is initially set up, build some prepared statements to power the “customer stats” feature.

  • Programming your database Triggers & Procedural SQL

    Triggers are pieces of procedural code that are automatically executed at a particular moment in time. There are many uses for triggers, and for the most part, this is a feature which works across SQLite, PostgreSQL and MySQL.

  • Programming your database EXERCISE: Order Totals

    Currently, it would be prohibitively expensive (in terms of CPU) to add an “Order Total” column onto the /orders/ page, due to the cost of aggregate function on a HUGE table (Order x OrderDetail). We can use another approach involving new OrderTotal column and a trigger. Whenever an OrderDetail row changes, update the OrderTotal value for the appropriate order.

  • Programming your database Materialized Views

    Materialized views can be used just like regular views. The key difference is that they exist as “refreshable” but nonetheless persisted tables in the database. To put it another way, materialized views need to be recalculated periodically, but certainly not on a per-query basis.

  • Programming your database EXERCISE: Better Dashboard Stats

    A dashboard is a great potential use case for materialized views, as it displays stats that are not changing from minute-to-minute. We can probably get away with running a few really intense queries once per hour, per day, etc… Once the work is done, the result set can be queried just as speedily as any other table.

  • Programming your database Lunch

    Break for lunch.