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

Relational DB: The Next-Generation

Over the last decade, there has been a lot of excitement around databases that are decidedly NOT relational. We have seen a rise in popularity around Key-Value stores like Memcached and Redis due to their pubsub system, and a movement toward “NoSQL” databases that offer greater flexibility for storing objects of widely-varying shapes.

The great news is that hosted relational databases have caught up! Starting with PostgreSQL 9.4 and MySQL 5.7 support JSON as a column type, first-class pubs systems, full-text search and more!

  • Relational DB: The Next-GenerationStructured Data Types

    Starting with PostgreSQL 9.4 and MySQL 5.7, we can create JSON and array columns. The main benefit of storing these values as structured data (as opposed to “stringifying” them) is that we can query INTO the values via more sophisticated mechanisms than “does this string match”.

  • Relational DB: The Next-GenerationEXERCISE: Tagged Products

    Create a new database migration to add a tags array column to the Product table. This should allow us to do some non-hierarchical categorization on the product list (i.e., “Sauces”, “Bakery”, “Beverages”).

  • Relational DB: The Next-GenerationEXERCISE: Customer Preferences

    Create a new database migration to add a preferences column for json values to the Customer table. Present the information on the customer’s page.

  • Relational DB: The Next-GenerationPub/Sub

    A publish-subscribe (pubsub) system is a software architecture pattern where publishers push messages into “channels”, and subscribers who have an interest in particular channels receive them. Publishers and subscribers have no direct knowledge of each other.

  • Relational DB: The Next-GenerationEXERCISE: Auto-Refreshing Dashboard

    Whenever a new order is created, use the existing web socket mechanism with LISTEN and NOTIFY calls to trigger a page refresh (if users are viewing the dashboard).

  • Relational DB: The Next-GenerationFull Text Search

    When implementing a search-engine-like feature on a web application, typically the results must very closely match the search term. In the past, this limitation was countered by adding new system components like Apache Solr and Lucene. Setting these up is a daunting task, to say the least, and is absolutely overkill for many use cases.

    Thankfully, modern versions of PostgreSQL and MySQL feature simplified versions of this technology. We can perform a search against multiple fields, specifying how much “weight” should be given to each field.

  • Relational DB: The Next-GenerationEXERCISE: Global Search

    There’s currently a “global search” feature on our workshop app, which uses an overly simplistic and narrow mechanism to find relevant results. Upgrade this feature using our database’s full text search feature set.