Postgres with Drizzle · Chapter 21 of 40

The view from above

By the end of this chapter you can save a query in the database under a name, read it like a table, store its answer for fast reads, refresh that answer, and remove both. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Advanced Queries
  • 5 tasks
  • PostgreSQL
  • In the all labs plan

The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.

Why you need this

Rohan's admin panel shows member statistics in four places, and each endpoint has its own copy of the same joins and counts. When Vikram asks for one change to how comments are counted, three copies get it and the fourth is forgotten. The heaviest page, post analytics, runs its joins on every load, although the numbers only need to be fresh once an hour.

A shared JavaScript function holding the SQL string helps only the code that imports it, not psql or the reporting tools. And caching the answer in the app means writing your own expiry. Postgres can keep the query itself under a name, and even keep its answer.

What you build: 5 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    createUserStatsView: a view of member statistics

    Write createUserStatsView(). It creates (or replaces) a view named user_stats_view with one row per member, every member included, with id, username, post_count (how many posts they wrote) and comment_count (how many comments they wrote), both 0 when there are none. Then it reads the whole view.

    Send raw SQL with getPool().query. Return the view's rows ordered by id: objects with at least id, username, post_count and comment_count, where both counts are numbers, not strings.

    Afterwards: user_stats_view exists. With the check's data, user1 has 3 posts, user2 has 2 comments and user3 has 0 posts.

  2. Task 2

    createPostAnalyticsMaterializedView: stored post analytics

    Write createPostAnalyticsMaterializedView(). It creates a materialized view named post_analytics_mv with one row per post: id, content, likes_count, author_id, author_name (the author's username) and comment_count (how many comments the post has, 0 for none, a number). Then it reads the whole view.

    Return the view's rows ordered by id. Afterwards: post_analytics_mv exists as a materialized view, not a plain one. With the check's data, user1's first post has 3 comments.

  3. Task 3

    refreshMaterializedView: catch up with new posts

    Write refreshMaterializedView(). It recomputes the stored rows of post_analytics_mv, then reads the whole view.

    Return the view's rows ordered by id. The check creates the view with your task 2 function while there is one post, adds two more posts, then calls this function: all 3 posts must come back.

  4. Task 4

    queryFromView: members with at least N posts

    Write queryFromView(minPosts). minPosts is a number. It reads user_stats_view (the check creates it with your task 1 function first) and returns the members whose post_count is at least minPosts (exactly minPosts counts), most posts first.

    Return the rows: objects with the view's columns, including username and post_count. Order: post_count, highest first. With the check's data (5, 2 and 0 posts), minPosts 3 gives user1 with 5, and minPosts 1 gives two members.

  5. Task 5

    dropViews: remove both views

    Write dropViews(). It removes the materialized view post_analytics_mv and the view user_stats_view, then returns true.

    Afterwards: neither view exists. Return the value true.

What you will learn

  • CREATE VIEW: a saved query you read like a table; it runs fresh every time.
  • CREATE MATERIALIZED VIEW: a saved query whose answer is stored, and REFRESH to recompute it.
  • Filtering a view: WHERE, ORDER BY and parameters, as on a table.
  • DROP: the right command for each kind.

More in Advanced Queries

Build The view from above yourself

MingleSphereQL grows up: its backend talks to Postgres through Drizzle. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.