2021-06-10 htmx + Mypy in version 0.900 + forga + postgres 14 (multirange) + python pep

tuto htmx

L’architecture moite-moite, ou quand tu en mets des 2 côtés de la tartine

Python Type Hints - How to Narrow Types with TypeGuard

I previously covered type narrowing using isinstance(), assert, and Literal.

In today’s post we’ll cover TypeGuard, a new special type that allows us to create custom type narrowing functions.

TypeGuard was defined in PEP 647, and is available on Python 3.10+, or on older versions from typing-extensions.

Guido van Rossum added support to Mypy in version 0.900, which was released yesterday

https://gitlab.com/forga

Better Range Types in Postgres 14: Turning 100 Lines of SQL Into 3

I can talk about the benefits of PostgreSQL for application development and operations all day. But there two enduring topics that are close to my heart: SCRAM (you need to update your passwords to use SCRAM) and range types .

I’ve been stoked about range types since they were released in PostgreSQL 9.2.

Before I joined Crunchy Data, I deployed them heavily in production to help manage a mature scheduling and space booking application. Performance wise, range types provided a fast way to perform sophisticated l ookups over temporal data sets!

With all the power and flexibility of range types, there was an unfortunate limitation: working with non overlapping (or noncontiguous) ranges.

Now don’t get me wrong, it afforded me the opportunity to write some very advanced recursive SQL and build some cool real-time data management systems.

However, this was at the cost of convenience and maintainability.

Almost a decade after range types were first introduced, PostgreSQL 14 now adds the ability to write some « boring SQL » to further unlock the power of working with range data. Meet the multirange data type.

A Real World Example: Availability

Let’s build a similar example to what we had in my previous blog post on searching availability using range types.

We’ll change up the example a bit to days with appointments: 

CREATE TABLE appointments (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    appointment_dates daterange NOT NULL,
    EXCLUDE USING gist (appointment_dates WITH &&)
);

Let’s create a set of appointment dates similar to what we had in the previous example: 

INSERT INTO appointments (appointment_dates)
VALUES
    (daterange('2021-06-02', '2021-06-02', '[]')),
    (daterange('2021-06-06', '2021-06-09', '[]')),
    (daterange('2021-06-11', '2021-06-12', '[]')),
    (daterange('2021-06-16', '2021-06-17', '[]')),
    (daterange('2021-06-25', '2021-06-27', '[]'));

In other words, the appointment dates are: 

June 2
June 6 - June 9
June 11 - June 12
June 16 - June 17
June 25 - June 27

Now, let’s say we want to return all the dates that we are booked for an appointment in the month of June.

We can accomplish this with a single query: 

SELECT appointment_dates
FROM appointments
WHERE appointment_dates && daterange('2021-06-01', '2021-06-30', '[]');

which yields: 

    appointment_dates
-------------------------
 [2021-06-02,2021-06-03)
 [2021-06-06,2021-06-10)
 [2021-06-11,2021-06-13)
 [2021-06-16,2021-06-18)
 [2021-06-25,2021-06-28)

If we wanted to aggregate all of these dates into a single multirange, we can do so with range_agg: 

SELECT range_agg(appointment_dates)
FROM appointments
WHERE appointment_dates && daterange('2021-06-01', '2021-06-30', '[]');

which yields: 

                                                         range_agg
---------------------------------------------------------------------------------------------------------------------------
 {[2021-06-02,2021-06-03),[2021-06-06,2021-06-10),[2021-06-11,2021-06-13),[2021-06-16,2021-06-18),[2021-06-25,2021-06-28)}

Recall that the problem that the previous example wanted to solve was « finding what dates I’m available for an appointment within a given month ».

To solve that before, we wrote a fairly involved recursive query.

With multi range types, we can simplify this to: 

SELECT datemultirange(daterange('2021-06-01', '2021-06-30', '[]')) -
  range_agg(appointment_dates) AS availability
FROM appointments
WHERE appointment_dates && daterange('2021-06-01', '2021-06-30', '[]');

which yields: 

                                                                    availability
---------------------------------------------------------------------------------------------------------------------------------------------------
 {[2021-06-01,2021-06-02),[2021-06-03,2021-06-06),[2021-06-10,2021-06-11),[2021-06-13,2021-06-16),[2021-06-18,2021-06-25),[2021-06-28,2021-07-01)}

Wow! That’s way simpler. While the previous method I showed works, this is way simpler, less error prone and provides many advantages, including:

  • Easier to understand: This is only 3 lines of straightforward SQL, vs. a complex recursive query.

  • Easier to maintain: With less SQL comes less maintenance, say if there is an update to your schema.

  • Less storage: due to the cost of the query, the previous methods requires you to « precompute » the availability on larger systems.

    With the efficiency of the range aggregate (range_agg) and multi data type difference (-) operator, this lessens, if not eliminates the need to precompute your availability.

Next Steps

Postgres 14 is now in beta. I strongly suggest downloading it, kicking the tires, and seeing what other interesting ways you can deploy some of its new features.

If you’re already using range types in non trivial ways, I suggest you play around with the new multi range types: you may be able to simplify your queries without sacrificing performance!

This PEP adds Template Literals to Python

Abstract

This PEP adds Template Literals to Python.

To avoid code injection like XSS or SQL-injection Template Literals can help you to write save Python code.

Template Literals provide an easy way to access the local and global variables (like f-strings), so that passing a dictionary to the Template is not necessary.

Motivation

In the context of web development Python can do more than providing REST APIs via http.

With the trend to Server-Side-Rendering, we face a fundamental question:

How to create HTML with Python ?

If you use the FrOW pattern (HTML fragments over the wire) [1], then you will be writing small methods returning small HTML fragments.

As a developer I want to pass escaped data into template literals to be as simple as possible.