Salesforce, Python, SQL, & other ways to put your data where you need it -- a bilingual blog in English & French

Need event music? 🎸

Live and recorded jazz, pop, and meditative music for your virtual conference / Zoom wedding / yoga class / private party with quality sound and a smooth technical experience

Drupal vs. 11ty with kittens & puppies

05 Nov 2020 🔖 architecture databases jamstack tips web development
💬 EN

Table of Contents

On StackOverflow, Drupal user Alec asked how dynamic “list” generation worked in 11ty compared to Drupal views.

I know nothing about Drupal, but I thought perhaps I could shed some light on the impact differing content data models may have on the two web site building systems: relational (SQL) vs. object.

We’ll take a visual, low-code look at datasets of kittens and puppies in both systems, contrasting the approaches you might take to listing them by color.


Data storage

Drupal, like Wordpress, is a web site generation tool whose content is stored in a relational (SQL) database management system.

By contrast, 11ty is meant to generate web sites from content either:

  1. stored in ordinary computer files, or
  2. stored elsewhere on the internet, but that would make a perfectly good computer file, and that is retrievable by executing some code written in Node.js-style JavaScript

Stock photo of a cat begging

Data shape

The biggest difference, though, is not so much where the data is stored but the shape in which the site generation tool sees it.

Go have a look at my explanation of table-shaped vs. nested-list-shaped data to get some sense of the difference.

Not explained in that post, however, is the idea of combining multiple table-shaped data files by including identifying data from one table inside the details of another and indicating that it represents a link into a row of some other table – and having the computer automatically understand those links. (That’s what is meant by “relational” in “relational database.”)

Let’s look at data about some kittens and puppies waiting for adoption out of an animal shelter.

To let me be lazy in typing up sample data, my shelter is magical, and no two pets of the same name ever arrive at the same time, so I can use their names as unique record IDs.


Relational-database-shaped

In a fully relational database, we’d probably normalize our data – that is, we’d probably break it up into a lot of different tables.

Here’s an example data model with 4 tables: kittens, puppies, valid_colors, and pet_coloration:

kittens

slug name
oreo Oreo
hershey Hershey

puppies

slug name
fido Fido
rover Rover
snickers Snickers

valid_colors

row_id color
1 black
2 brown
3 white
4 gray
5 orange
6 tan
7 red

pet_coloration

pet_table slug color_id
kittens oreo 1
kittens oreo 3
kittens hershey 2
puppies fido 1
puppies fido 7
puppies rover 6
puppies snickers 1
puppies snickers 3

Nested-list-shaped

While there’s nothing stopping us from structuring our data into four tables that cross-reference each others’ row IDs in a file-based nested-list-shaped data store, in practice I’ve found that it’s not terribly common.

People have a tendency to store their data more like this, in 2 files called kittens and puppies, with coloration details buried inside each animal’s record:

kittens

  1. (kitten #1)
    • name: Oreo
    • colors:
      • black
      • white
    • slug: oreo
  2. (kitten #2)
    • name: Hershey
    • colors:
      • brown
    • slug: hershey

puppies

  1. (puppy #1)
    • name: Fido
    • colors:
      • white
      • red
    • slug: fido
  2. (puppy #2)
    • name: Rover
    • colors:
      • tan
    • slug: rover
  3. (puppy #3)
    • name: Snickers
    • colors:
      • black
      • white
    • slug: snickers

Listing pets by color

Relational

From what I can tell, it looks like relational-database-aware web site building systems like Drupal can make it relatively easy to build a URL like https://mysite.com/colors/black/ that lists all black pets available, no matter whether they’re a kitten or a puppy.

That’s largely because SQL, the declarative programming language built into the database management system storing the data, offers the opportunity to inspect the data from that perspective with very little code.

This code (note: did not execute; may have bugs, but hopefully gets the gist):

SELECT DISTINCT
 DECODE(pets.pet_table, "kittens," "kitten", "puppies," "puppy", pets.pet_table) as pet_type
 pets.slug, 
 pets.name
FROM valid_colors
INNER JOIN pet_coloration
 ON valid_colors.row_id = pet_coloration.color_id
INNER JOIN (
  SELECT "kittens" as pet_table, slug, name
  FROM kittens
  UNION ALL
  SELECT "puppies" as pet_table, slug, name
  FROM puppies
) pets
 ON pets.slug = pet_coloration.slug
 AND pets.pet_table = pet_coloration.pet_table
WHERE valid_colors.color = 'black'

… would produce this data:

pet_type slug name
kitten oreo Oreo
puppy snickers Snickers

In fact, I’m not going to type it all out here (the concept is a bit hard to doodle in 2 dimensions), but because “querying” the data in almost any pattern we can imagine is so easy, I can definitely see how a tool like Drupal would make an easy user interface by which you can simply say that you’d like it to manage building appropriate URLs under https://mysite.com/colors/black, tan, orange, etc.

Nested-list-shaped (objects)

Building a similar type of URL called https://mysite.com/colors/black/ using nested-list-shaped data that simply contained “kittens” and “puppies” would likely take a lot more manual coding.

Not only did we leave the “coloration” data about each pet buried inside individual pet listings, but web site building tools optimized to work with this kind of data, like 11ty, offer imperative programming languages that make us do all the hard work ourselves.

We have to hand-write code that loops over every single kitten or puppy record, inspecting it to see whether it’s black or not, and if so, setting aside the right details about that kitten or puppy.

The code might be something more like:

const pets = [
 ...kittens.map((kitten) => {
  kitten.pet_type = "kitten";
  return kitten;
 }),
 ...puppies.map((puppy) => {
  puppy.pet_type = "puppy";
  return puppy;
 }),
];

const pets_for_color = ({ color }) => {
 return pets.filter((pet) => {
  return pet.colors.includes(color);
 });
};

const black_pets = pets_for_color({ color: "black" });

module.exports = black_pets

Such code would produce data like this, out of which your web site builder could generate https://mysite.com/colors/black/:

  1. (pet #1)
    • name: Oreo
    • colors:
      • black
      • white
    • slug: oreo
    • pet_type: kitten
  2. (pet #2)
    • name: Snickers
    • colors:
      • black
      • white
    • slug: snickers
    • pet_type: puppy

Once you’ve broken your “relational data” thinking habits and gotten the hang of “object data” thinking habits, and once you’ve accustomed yourself to a new programming language, the new approach won’t be so bad.

I’m high-fiving myself right now for learning enough JavaScript over the last half-year to finally remember tricks like ..., .map(), and .filter(). Always celebrate your small wins!

In fact, as long as your dataset isn’t too big, once you know what you’re doing, it’s not too tough to write a few additional lines of code and generate a nested-list-shaped dataset that your web site builder could use for building all the https://mysite.com/colors/ URLs on your behalf:

// Code shown previously,
// except final lines pertaining to black_pets,
// goes here

const current_colors = [
 ...new Set(
  pets
   .map((pet) => {return pet.colors;})
   .flat()
 ),
];

const pets_by_color = current_colors.map((color) => {
 return { color, pets: pets_for_color({ color }) };
});

module.exports = pets_by_color;

Such code would produce data like this. Your web site builder could use the outermost level of listing to decide what pages to make, then pursue the details contained within each pets sub-listing to manage building appropriate URLs under https://mysite.com/colors/black, tan, red, etc.

  1. (color #1)
    • color: black
    • pets:
      • (...all the details about Oreo...)
      • (...all the details about Snickers...)
  2. (color #2)
    • color: white
    • pets:
      • (...all the details about Oreo...)
      • (...all the details about Fido...)
  3. (color #3)
    • color: brown
    • pets:
      • (...all the details about Hershey...)
  4. (color #4)
    • color: red
    • pets:
      • (...all the details about Fido...)
  5. (color #5)
    • color: tan
    • pets:
      • (...all the details about Rover...)

Writing such “data transformation” code and making the results available to Eleventy HTML templates is one of the big reasons 11ty treats JavaScript files stored in its _data folder as special.

Be sure to take advantage of data pre-processing in JavaScript on your next 11ty web site.

It should help you reach the “magic URLs” experience you’re accustomed to if you’re starting anew after using a database-based site building tool like Drupal or Wordpress.


--- ---