Forem: Thomas Lowry

How Functions and Composite Types Changed the Way I Use PostgreSQL

Thomas Lowry — Mon, 27 May 2019 20:56:56 +0000

Having learned learned just enough SQL to create some tables and perform the most simple CRUD operations during my time at a coding bootcamp, I was blown away when I learned about composite types in Postgres.

I originally learned that an SQL query always returns a list, so if you want to return a JSON-like structure from your database, you either have to perform multiple SQL queries and stitch the results together in Node (or whatever other language you use), or you have to use something like GraphQL, that automatically generates the SQL you need and structures it in the format you specify.

However, using composite types (a feature that only exists in Postgres, if I am not mistaken), you can not only return a JSON-like structure from a query, but you can also accept JSON-like data as an input to an operation.

Please note that I'm not talking about STORING data in a composite type (which has its own much more limited use-case), I'm rather talking about using composite types as INPUTS and OUTPUTS of a query.

Sample Schema

My go-to example-schema is one that represents classes in a school.

Here's a simple schema to capture classes, students, teachers, and enrollments:
(Note how students and teachers are both stored in the table "people")

Tables

CREATE TABLE people (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

CREATE TABLE classes (
    id SERIAL PRIMARY KEY,
    subject VARCHAR(50),
    teacher_id INTEGER REFERENCES people
);

CREATE TABLE student_class_enrollments (
    student_id INTEGER REFERENCES people,
    cid INTEGER REFERENCES classes,
    UNIQUE (student_id, cid)
);

Sample Data

Let's create a class just so we have something to query:

-- create a teacher and some students
INSERT INTO people (
    first_name,
    last_name
)
VALUES
('Tommy', 'Lowry'), -- id 1
('Student', 'One'), -- id 2
('Student', 'Two'), -- id 3
('Student', 'Three'); -- id 4

-- create a class
INSERT INTO classes (
    subject,
    teacher_id
)
VALUES
('Postgres Composite Types', 1);

-- enroll students in the class
INSERT INTO student_class_enrollments (
    student_id,
    cid
)
VALUES
(2, 1),
(3, 1),
(4, 1);

Querying: The Old Way

Now if I wanted to retreive all the information about a class, I formerly would have written a query something like this:

SELECT * FROM classes c
LEFT JOIN people t ON t.id = c.teacher_id
LEFT JOIN student_class_enrollments e ON e.cid = c.id
LEFT JOIN people s ON s.id = e.student_id

This would give me data something like:

class id	subject	teacher id	teacher first name	teacher last name	student id	student first name	student last name
1	Post...	1	Thomas	Lowry	1	Student	One
1	Post...	1	Thomas	Lowry	2	Student	Two
1	Post...	1	Thomas	Lowry	3	Student	Three

See how we have redundant, unstructured data? Not only are the class and teacher represented once for each student, but our list lacks any representation of our data's depth - class, teacher, and student are all lumped together in each node of the list instead of being organized into a heirarchy.

Composite Types

Let's see how composite types can help us to format this data into a more reasonable structure.

Note If you're not familiar with composite types, a composite type is a type that is 'composed' of other types. It allows you to have a single item that may contain other items - essentially it allows a nested structure. Composite types can be composed of built-in SQL types as well as other composite types.

You can read more about PostgreSQL composite types in the official documentation.

To show you how this works, let's create some composite types that mirror our data structure:

-- A `person` is composed of an `integer` and two `varchar`s titled 'id', 'first_name', and 'last_name', respectively.
CREATE TYPE person AS (
    id INTEGER,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

-- Likewise, a `class` is composed of an `integer`, a `varchar`, a `person`, and a `person[]` array, titled 'id', 'subject', 'teacher', and 'students', respectively.
CREATE TYPE class AS (
    id INTEGER,
    subject VARCHAR(50),
    teacher PERSON,
    students PERSON[]
);

Notice how this structure somewhat resembles our tables, with a few key differences:

In place of a one-to-many relationship (a single foreign key), we simply nest a teacher within a class.
In place of a many-to-many relationship (a join table), we simply nest an array of students within a class.

Note

Querying: The New Way

Now we're ready to create a function that will output an entire class in the structure that we've defined using our composite types.

Function Signature

There are a few steps to this, so let's start with a function signature that takes in a class id and returns a class:

CREATE OR REPLACE FUNCTION read_entire_class(cid INTEGER)
RETURNS class AS $$
DECLARE
BEGIN
END;
$$ LANGUAGE plpgsql;

Variables

Now we'll need to define the variables we will use in order to retreive each key/node to build our composite class:
(This'll make more sense once we actually use them)

CREATE OR REPLACE FUNCTION read_entire_class(cid INTEGER)
RETURNS class AS $$
----------
DECLARE
    subject TEXT;
    teacher PERSON;
    students PERSON[];
----------
BEGIN
END;
$$ LANGUAGE plpgsql;

Constructing a Composite Value / Explicit Type Casting

Next, before we get into the difficult stuff, let's add our return statement. Here we'll use the ROW() function, along with explicit type casting (::class) to put our class together and return it. This might seem rather abstract at first, but under the hood, that's all a composite type is: a single row of field names and corresponding data types.

CREATE OR REPLACE FUNCTION read_entire_class(cid INTEGER)
RETURNS class AS $$
DECLARE
    subject TEXT;
    teacher PERSON;
    students PERSON[];
BEGIN

----------
    RETURN ROW(     -- ROW() function to create composite
        id,         -- these must be in correct order
        subject,
        teacher,
        students
    )::class;       -- explicit type casting
----------
END;
$$ LANGUAGE plpgsql;

Note that the order of the values used is important and must always be the same as in the declaration of the type. If you put them in the wrong order and the types don't match up, it'll throw an error when you run the function. Worse, if you put them in the wrong order and the types are compatible, you'll be silently mismatching keys in your query until you recognize that something's off.

According to the docs, you actually can leave the ROW keyword out, as long as the composite type has more than one field. To do this, you remove ROW and leave the parentheses. You may also be able to leave out the explicit type-cast, since SQL will automatically cast the RETURN into the function's declared return type.

Now you should be able to create the function and run it - however you'll get an empty class every time.

Filling the Composite Value With Query Results

All that's left now is to fill the data structure with query results from the database.

We'll take this one step at a time, assuming minimal knowledge of the language.

Subject

First, let's get the subject:

CREATE OR REPLACE FUNCTION read_entire_class(cid INTEGER)
RETURNS class AS $$
DECLARE
    subject TEXT;
    teacher PERSON;
    students PERSON[];
BEGIN

----------
    SELECT c.subject FROM classes c   -- selects `subject` from classes
    INTO subject                      -- assigns query result to variable `subject`
    WHERE c.id = cid;
----------

    RETURN ROW(
        cid,
        subject,
        teacher,
        students
    )::class;
END;
$$ LANGUAGE plpgsql;

SELECT ... INTO will assign the result of the query to whatever variable is specified, so that we can reference the result later on.

Teacher

Now, let's get the teacher:

CREATE OR REPLACE FUNCTION read_entire_class(cid INTEGER)
RETURNS class AS $$
DECLARE
    subject TEXT;
----------
    tid INTEGER; -- add teacher id variable
----------
    teacher PERSON;
    students PERSON[];
BEGIN

    SELECT c.subject FROM classes c
    INTO subject
    WHERE c.id = cid;

----------
    -- EITHER --------
    SELECT t.id, t.first_name, t.last_name FROM people t
    INTO teacher
    WHERE t.id IN (                             -- subquery to select correct teacher
        SELECT c.teacher_id FROM classes c
        WHERE c.id = cid
    );
    -- OR --------
    SELECT c.teacher_id FROM classes c          -- extra query to get `teacher_id` instead of using subquery
    INTO tid
    WHERE c.id = cid;

    SELECT t.id, t.first_name, t.last_name FROM people t
    INTO teacher
    WHERE t.id = tid;
    -- END OR ----
----------

    RETURN ROW(
        cid,
        subject,
        teacher,
        students
    )::class;
END;
$$ LANGUAGE plpgsql;

There's not much new here, just a potential subquery to get the teacher of the class. I'll keep the subquery since it's more concise.

Students

Now we just need to get the students. This one is a bit more difficult, since we're dealing with a list of students. Here's how you do it:

CREATE OR REPLACE FUNCTION read_entire_class(cid INTEGER)
RETURNS class AS $$
DECLARE
    subject TEXT;
    teacher PERSON;
    students PERSON[];
BEGIN

    SELECT c.subject FROM classes c
    INTO subject
    WHERE c.id = cid;

    SELECT t.id, t.first_name, t.last_name FROM people t
    INTO teacher
    WHERE t.id IN (
        SELECT c.teacher_id FROM classes c
        WHERE c.id = cid
    );

----------
    SELECT ARRAY_AGG(ROW(s.id, s.first_name, s.last_name)::person) FROM people s
    INTO students
    WHERE s.id IN (
        SELECT e.student_id FROM student_class_enrollments e
        WHERE e.class_id = cid
    );
----------

    RETURN ROW(
        cid,
        subject,
        teacher,
        students
    )::class;
END;
$$ LANGUAGE plpgsql;

ARRAY_AGG() takes the results of the query and converts them into an array, so they are compatible with the array type of the students variable. ROW(...)::person converts each student into a composite person.

Done!

Now we have a function that requires nothing more than a class id to bundle up a class with its teacher and students into a nice, nested, JSON-like structure.

In order to invoke the function, run SELECT * FROM read_entire_class(1) and look at the beautifully nested results!

Things I've Learned as a Web Development Contractor

Thomas Lowry — Sat, 18 May 2019 21:23:54 +0000

A New Experience

Several months ago I started working as a software development contractor. I began working for a single client on a part-time basis while continuing to work part-time as a mentor at DevMountain.

In recent months, and since leaving my job at DevMountain, my working hours have evolved from a regular, enforced schedule, working mostly in person, into an almost entirely remote, autonomous agenda. This shift has presented me with some incredibly difficult challenges that have taught me some valuable lessons and altered my mindset about work.

Challenges of an Autonomous Schedule

One of the most difficult challenges that has come with this change is that I have had to constantly reevaluate my expectations of myself and of my work. For example, I went into this year expecting to clock 40 hours a week, but very frequently found myself too stressed and anxiety-ridden to make even 30. To make matters worse, I became stressed that I wasn't meeting my expectations, and thus my stress and anxiety compounded themselves against me.

When I stopped to really think about why I wasn't meeting my expectations for hourly work, I recognized quite a few things that I had failed to incorporate into my perspective.

First, I recognized that my expectation of clocking 40 hours a week failed to account for hours that I work on things that are not billable to my one client. Most prominently, until March this year I was still working 11 hours a week at DevMountain, and these were inconvenient hours on Tuesday/Thursday nights and Saturday mornings.

Additionally, I was still mostly working in Salt Lake, which provided only 6-1/2 working hours at the cost of a 1-1/2 hour commute two-ways. This, combined with a change in church hours on Sundays meant that I had 0 mornings a week to myself - something that is very important to my mental health. Also, since quitting DevMountain, I've begun working on some other endeavors which are not accounted for on the clock, such as starting a meetup here in Provo.

Based on all of these factors, I reevaluated my expectation and accepted that I'm likely not going to reach 40 hours a week, and even if I did, it would come at the cost of things that are much more important to me. I have a new goal to clock 30 hours a week, understanding that I am putting plenty of time into non-billable work, and that this is my intentional ideal. I am also considering increasing my hourly billing rate to account for this recognition - something that I am already discussing with my client.

Secondly, I recognized that my contract work involves several aspects that I hadn't incorporated into my work expectations. I realized that I was expecting myself to either be "programming" or collaborating anytime I'm on the clock, but that I also need to allow myself time for project management, planning, and research, among other things. I needed to expel the expectation that I can just sit down and write the application and recognize that this job doesn't work like all the other jobs I've had where I just sat down (or stood up) and "worked".

Now that I am doing creative work, I have to allow myself time for creativity and not stress out if I spend multiple hours at a time simply contemplating problems, researching technologies, and designing solutions. Also, since I am one of only two people working on the project, I need to allow adequate time for project management, so that I remain on track with my work. Until recently, I never really understood the role all these things play in a software development job.

This incomplete perspective caused me to expect much quicker results from my work than were realistic, which caused a great deal of stress and anxiety around my work, which led both to less effective time on the clock and less time on the clock altogether.

Reevaluating my expectations of what my work looks like has helped me to take it one step at a time and feel confident that I am providing the best value to my client even during the hours that I'm not hacking at my keyboard.

What I've Learned

I've learned from this that focusing solely on the completion of my goals is often not nearly as productive as focusing on discovering and removing the obstacles that are preventing me from accomplishing my goals.

The truth is, when I have something that I truly want to accomplish, I don't have to force myself to put time and effort toward that goal. In fact, when I have a goal that I'm truly passionate about, I more often than not I have to force myself NOT to work on that thing too much - I have to force myself to stop for a minute here and there so I can eat, sleep, shower, be social, and take care of myself.

So when I find myself in a situation where I am not putting as much time and effort into a goal as I would like to, then there is almost always one of two conditions at play: either I don't really care about the goal as much as I thought I did, or else there is something unseen holding me back.

Self-Evaluation

Before I stop to think about it, I tend to feel frustrated with myself and stressed as to why I am not progressing toward my goal. I tend to see myself as lazy, apathetic, and unproductive. In this situation it's important for me to recognize that my struggle comes not from a lack of virtue (i.e. diligence, work ethic, perseverance), but from a lack of perspective, and that I need to stop for a minute to engage in some introspection.

Three questions I need to ask myself are:

Is my goal, along with its associated expectations, in line with my values and capabilities?
What unseen challenges are holding me back from pursuing this goal and meeting my expectations?
What changes do I need to make to my goals and expectations in order to more effortlessly progress toward my goal?

These questions help me to evaluate whether I am expecting myself to work toward something that I truly value and whether I have accounted for all the steps necessary to accomplish the goal, and then to adjust my goals and expectations accordingly.

My anxiety is usually caused by a lack of clarity regarding steps that I need to take to move forward, and going through this process of reevalutating expectations helps me to ground myself once more in reality so that I can move forward one step at a time.

I hope my story and the things I've shared can help you to be more congruent in aligning your goals and expectations with your actions and your reality. Let me know if you relate to this story in any way and what you have learned from your experiences!

Does Brooks' Law Apply to a Project with Only a Single Developer?

Thomas Lowry — Tue, 16 Apr 2019 16:53:17 +0000

So here's the deal:

I'm working on this project that is pretty large, and extremely nuanced (it's a solution for automatically drafting glass in a building after the building has been architected, but before construction). I'm working on a team with just one other person. He is a designer who has been in the architecture industry for several years and has partnered with the business owner to create this software. So, I'm the one who understands how software works and he's the one who understands how the architecture works.

Anyways, I am the only developer and have therefore taken on a ton of responsibilities, including architecting the entire solution, designing a database, organizing the workflow into three data-entry applications and one end-user application, choosing which technologies to use, building the entire front-end of those applications, and so on.

There are some things I love about being a solo developer - the codebase is all mine; I know it inside and out; and so on.

But I often wish I had another developer to take on some of the tasks and to collaborate with, and I feel like adding another developer to the team could greatly increase our productivity if we do it right.

However, I keep running into the idea of Brooks' Law that states:

Adding human resources to a late software project makes it later.

My question is:

Does this law apply to small teams like mine?

Everything I read on the subject has to do with teams at least several times as large as mine, with project managers and senior developers and such, so it's hard to relate to those examples. Articles consistently refer to the fact that a newcomer to a project ALWAYS has less experience in the project and therefore can NEVER contribute as quickly or as well to a project as those already working on it. However, in my situation adding one person is equivalent to doubling the number of software developers on the project, so there is bound to be a point where the increased productivity of having two developers exceeds the initial cost of training the new developer.

What tips or experiences do you guys have that could give me insight into how to know when and how to add another developer to the project?

Thanks in advance!