Forem: Grouparoo

Data cleaning null values -- SQL or Code?

Teal Larson — Thu, 29 Jul 2021 22:36:48 +0000

When preparing your data set for analysis, it is crucial to ensure that your data set is both complete and accurate. One step in this process is deciding how to handle null values. Depending on how your data is going to be used, you may not want null values at all!

Let's clean some data

We're going to take a look at calculating Lifetime Value (LTV) of a customer. We want a customer with no purchase history to have an LTV of 0. What happens if our aggregated column returns no rows, and thus is null? Should we clean the data at the query level or afterwards using a script?

We’re going to walk through this LTV example from beginning to end looking at different options -- some that work well, some that work poorly, and some that won’t work at all.

To start, we have a users table and a purchases table.

users:

user_id
1
2
3
4
5

purchases:

user_id	price
1	79.36
3	42.97
1	33.14
2	16.48
4	96.41

As you can see, the users table has 5 unique users and the purchases table has 5 purchases from users 1, 2, 3, and 4. User 5 is not present in the purchases table. Now that we have our data, it’s time to create our calculated column. Our query is being called by a function that passes it a list of user_ids that can be used in the query.

Option 1: With a SQL Query

Using `COALESCE`

COALESCE is a keyword used by Postgres and other SQL dialects to return the first non-null value in a set. Because of this, it’s a common keyword for data cleaning. Let’s look closer at the following query.

SELECT
    COALESCE(SUM(purchases.price), 0) as LTV,
    user_id
FROM purchases
WHERE purchases.user_id
    IN (1, 2, 3, 4, 5)
GROUP BY user_id
ORDER BY user_id;

When we look at the results from that query we end up with:

user_id	LTV
1	112.50
2	16.48
3	42.97
4	96.41

Yikes! No user 5. That means wherever you’re using this data, when you try and access the row for user 5, you’ll get null, which may throw off calculations. So why did COALESCE do this?

A SQL query is like checking a post office box -- you might find a bunch of envelopes containing various amounts of mail, or even an empty envelope I suppose. But if there’s no mail at that post office box, you leave empty handed. If SQL can’t find any rows, it comes back empty handed as well. In fact, getting back a null vs a 0 vs an undefined may seem similar, but they tell us different things:

value	meaning
0	We know something is there and its value is zero.
null	We know something is there and its value is unspecified
empty set	We know nothing is there

So the issue with our query isn’t with the COALESCE but with the order of execution - there are no rows to return with a user_id of 5 in purchases. Let's think about the logical order of how we get there:

The order that query gets executed in is:

Find rows in purchases that match the given user ids (5 rows, matching 4 of the given user ids)
On each of the rows returned, return either the sum of the price column or 0 (same 5 rows, with aggregated data for LTV)
Group by user_id and order by user_id (4 rows returned, in order by user_id, with aggregated data for LTV)

After the first line, there isn’t even a row for user 5 anymore. We can expect the same behavior if we try a CASE switch for our LTV because, again, there is no row, no data for it to act on.

Had our purchases table contained a row for user_id 5 with null for price, it would have returned as expected. Knowing that, a second option is to break out a right outer join.

`RIGHT OUTER JOIN`

Knowing that our issue was with missing rows, we could go the route of a RIGHT OUTER JOIN with our users table to achieve that. This way, we’ll get back a row for every user from users, and, if it’s there, the sum of their data from the “price” column in purchases, otherwise a 0.

SELECT
users.id as user_id,
    COALESCE(SUM("price"), 0) as LTV
FROM purchases
    RIGHT OUTER JOIN users
    ON demo.purchases.user_id = users.id
GROUP BY user_id
ORDER BY user_id;

user_id	LTV
1	112.50
2	16.48
3	42.97
4	96.41
5	0

It works! Fantastic.

💡 If all of your foreign keys are coming from a single table, the above might be the right solution for you. However, in more complex cases, you may not have something like a generic “users” table.

Your primary data may be spread across multiple tables -- for instance, you may have a setup like:

location_a_customers

Column	Type	Nullable
user_id	string	not null
email	string	not null
last_name	string	not null
user_id	string	not null

location_b_customers

Column	Type	Nullable
user_id	string	not null
email	string	not null
last_name	string	not null
user_id	string	not null

that you want to use with a purchases table:

Column	Type	Nullable
user_id	string	not null
purchase_amount	decimal	not null
item_sku	string	not null
item_category	string	not null

Fear not, we can still make it happen. Though, for everyone’s sake, at this point I would recommend using a function over using SQL! Just for giggles, let's prove it's possible first.

`UNION` with a list

⚠️ Overkill query ahead. You should really try a function instead.

One way around the issue of having multiple users tables would be to create a list of all the user_ids for the query with a sublist of (user_id, 0), then UNION the original query with that list. If it sounds overkill, that’s because it is:

SELECT
  DISTINCT on (user_id) user_id,
  purchases FROM( SELECT user_id, COUNT(*) as LTV
FROM purchases
WHERE purchases.user_id in (1, 2, 3, 4, 5)
GROUP BY user_id

UNION

SELECT * FROM (
  values (1, 0), (2, 0), (3, 0), (4, 0), (5, 0))
  AS t (user_id, LTV)
  ORDER BY user_id, LTV DESC
) AS data;

Which returns our beloved 0:

user_id	LTV
1	112.50
2	16.48
3	42.97
4	96.41
5	0

The query isn’t too out of hand for 5 users, but imagine having to write that out (or have a function write that out) for 500 users! What about 10,000! Just because something works, doesn’t make it necessarily the right idea. So let’s try it from the programming sde.

Option 2: After the SQL Query 🥇

We’re really asking a lot of SQL there. While it was possible, it was not very pretty, very concise, or very readable. It also muddies things up as it is technically returning data from our database that isn’t there. Thankfully, transforming the data we do get back using a function is a much more straightforward task!

I worked this one out in both TypeScript and Python. I love that we use TypeScript for building out our data pipeline because dynamic typing helps keep the guard rails on and make sure I’m paying attention to what I’m working with -- especially as our data structures grow in complexity, it helps to be completely sure what you’re working with. And Python for all the Python-loving data folks as well.

Here’s how we can use a function to transform our data to include 0s. We start by setting the LTV of every id as [0], then iterate through our results and replace the [0]s as we go:

TypeScript:

interface Profile {
  id: string;
}

interface DataResonseRow {
  id: string;
  data: number[];
}

function calculateLTV(
  queryResults: DataResponseRow[],
  aggregationMethod: string,
  profiles: Profile[]
) {
  const responsesById: { [id: string]: any[] } = {};

  for (const i in profiles) {
    if (aggregationMethod === "count" || aggregationMethod === "sum") {
      //default all entries for counted or summed aggregations to 0 to start... will be replaced later if there is a response
      responsesById[profiles[i].id] = [0];
    }
  }

  for (const row in queryResults) {
    responsesById[queryResults[row].id] = queryResults[row].data;
  }

  return responsesById;
}

Python:

🐍 Note: There are many Python libraries that help with data cleaning and analytics. Two popular choices are pandas and matplotlib. However, this is a super straightforward solution using plain-vanilla Python.

def calculate_ltv (query_results, aggregation_method, profiles):
  responses_by_id = {}

  for  i in profiles:
    if aggregation_method == "count" or aggregation_method == "sum":
        # default all entries for counted or summed aggregations to 0 to start... will be replaced later if there is a response
        responses_by_id[i["id"]] = [0]


  for row in query_results:
    responses_by_id[row["id"]] = row["data"]

  return responses_by_id


mock_query_results = [{"id":1, "data":[112.50]}, {"id":2, "data":[16.48]}, {"id":3, "data":[42.97]}, {"id":4, "data":[96.41]}]
mock_aggregation_method = "sum"
mock_profiles = [{"id": 1}, {"id": 2}, {"id": 3}, {"id": 4}, {"id": 5}]


print(calculate_ltv(mock_query_results, mock_aggregation_method, mock_profiles));

To see them in action, here’s a jsfiddle for the TypeScript solution and an IDEOne instance for the Python solution.

Learn More:

To learn more about handling conditionals and aggregations in Postgresql, check out their docs

Generate Dynamic JSON Pages with Next.js

Sean C Davis — Fri, 11 Jun 2021 10:28:15 +0000

Next.js is a super powerful tool for building scalable websites and web applications. Building dynamic web pages is no big thing with Next.

I had a scenario pop up in which I wanted to generate and deliver JSON pages. I wanted to retrieve the data from elsewhere and then output it to a file that didn't have to change between builds.

Limitations of Pages in Next.js

Part of the reason Next is equal parts powerful and easy to use is a result of the opinions it brings along. One such opinion is the way in which pages are delivered.

While there are options to fetch data prior to rendering a page, pages are rendered as React components. And they are wrapped in application-level components.

That means there isn't an easy way for me to follow the Next.js pages pattern to generate statically dynamic JSON pages.

Fortunately, I found two ways in which I could still accomplish what I wanted in two other ways.

The Setup

Before we walk through these two examples, I'm assuming you have a Next.js project ready to go. If you don't you can use create-next-app to start with the default template.

$ npx create-next-app

We're going to install a single dependency for this example, axios:

$ npm install axios

Once it seems you're ready to go, boot that development server. With the default template, that command is:

$ npm run dev

And the server runs at localhost:3000 in your browser.

If you had an existing Next project, you may have a different command to start the dev server and a different port on which the front end runs.

Method #1: API Routes

Now that you're up and running, let's look at our first option for generating JSON pages: API routes.

Okay, I lied. A little. Pages in next don't have to be React components. Next also supports what they call API routes. These are methods that run on the server side and return data back to the user. That feels like a really good use case for our scenario.

Let's pretend that we want to return a single random dad joke.

Starting Simple

As a quick introduction to API routes, let's first create a page at pages/api/joke.js with the following content:

// pages/api/joke.js

export default (req, res) => {
  res.status(200).json({ hello: "World" });
};

Now visit http://localhost:3000/api/joke in your browser, or make a GET request to that same URL through an API client. You'll see on screen (or in your client) the object we sent:

{ "hello": "world" }

Great!

Making it Dynamic

Now let's make it dynamic by adding axios and querying the icanhazdadjoke.com API:

// pages/api/joke.js

import axios from "axios";

export default async (req, res) => {
  const { data } = await axios.get("https://icanhazdadjoke.com/", {
    headers: { Accept: "application/json" },
  });

  res.status(200).json(data);
};

There's not much to that, really. We're asking the icanhazdadjoke.com API for a response and passing that response on to the user.

Now refresh your browser or make a request through your API client again and you'll be sent something classically witty like this:

{
  "id": "xXg3LZLZDd",
  "joke": "*Reversing the car* \"Ah, this takes me back\"",
  "status": 200
}

Note that if you were going to take this into production, you'd want to put some checks in place to guard against the icanhazdadjoke.com API being down or giving you something you didn't expect.

Method #2: Static File

The first method is powerful and all, but it's also forcing you into a solution in which you have to run that method (i.e. do some work, like hit another API) every time you want the data in this file.

Recall in the intro that I mentioned a nuance of not needing the file to change in between builds. Thus, the JSON file itself should be generated dynamically, but could be delivered statically. (This has all the makings of a static API.)

While we know we can't make pages as static JSON files, we could generate a static JSON file prior to building the site and serve it as a static asset.

Generate Script

To do that, let's put together a little script at scripts/getJoke.js which retrieves the dad joke and then writes it to a file at public/joke.json.

Note: We're putting it in the public directory because these files get copied over directly. A file at public/joke.json would be available at /joke.json on our website.

// scripts/getJoke.js

const path = require("path");
const fs = require("fs");
const axios = require("axios");

const filePath = path.join(__dirname, "../public/joke.json");

const main = async () => {
  const { data } = await axios.get("https://icanhazdadjoke.com/", {
    headers: { Accept: "application/json" },
  });

  fs.writeFileSync(filePath, JSON.stringify(data, null, 2));
};

main().then(() => console.log("Done."));

Notice this is very similar to our API function. But instead of returning the response, we write it to file.

To see it in action, you can run the script like this from the root of your project:

$ node ./scripts/getJoke

Check your public directory for a joke.json file.

{
  "id": "xXg3LZLZDd",
  "joke": "*Reversing the car* \"Ah, this takes me back\"",
  "status": 200
}

You can verify that it will be available on your website by visiting localhost:3000/joke.

Automate It!

To automate this process, we can add a pre script to hook into the appropriate script in our package.json file.

For example, let's say we want this to run before we run the npm run dev script. To do that, add a predev script to your package.json file:

// package.json

{
  // ...
  "scripts": {
    "predev": "node ./scripts/getJoke",
    "dev": "next dev"
    // ...
  }
}

Now, whenever you run npm run dev, your getJoke script will run, producing a new static file.

Try it out. Stop your server (if it's still running) and start it back up. Then visit localhost:3000/joke to see new content!

There are two methods for taking dynamic data and rendering it as JSON in your Next.js application. One updates itself on every request, the other on every build. Choose the best path for you and keep building cool things!

Bundling and Distributing Next.js Sites via NPM

Evan Tahler — Fri, 04 Jun 2021 19:54:16 +0000

Grouparoo uses Next.js to build our web frontend(s), and we distribute these frontend User Interfaces (UIs) via NPM as packages, e.g. @grouparoo/ui-community. This allows Grouparoo users to choose which UI they want to use (or none) by changing their package.json:

Example package.json for a Grouparoo project:

{
  "author": "Your Name <email@example.com>",
  "name": "grouparoo-application",
  "description": "A Grouparoo Deployment",
  "version": "0.0.1",
  "dependencies": {
    "@grouparoo/core": "0.3.3",
    "@grouparoo/postgres": "0.3.3",
    "@grouparoo/mailchimp": "0.3.3",
    "@grouparoo/ui-community": "0.3.3" // <-- Choose UI Package to install
  },
  "scripts": {
    "start": "cd node_modules/@grouparoo/core && ./bin/start"
  },
  "grouparoo": {
    "plugins": [
      "@grouparoo/postgres",
      "@grouparoo/mailchimp",
      "@grouparoo/ui-community" // <-- Choose UI Package to load
    ]
  }
}

Here is how we bundle up our Next.js applications so that our customers can use them out of the box.

`next build` and `npm run prepare`

The first step in “compiling” your Next.js projects is to use the next build command. We alias this to the “prepare” npm lifecyle command so that this command will be run automatically before npm publish. In this way we can ensure that we always have a freshly built bundle to use when we publish our packages.

This is different from Next’s recommendation to alias next build to npm build because we are not “deploying” our sites - we are publishing them. Many hosting providers look for a build script in your pacakge.json to run when the deploy, hence Next.js’ recommendation.

`.npmignore` vs `.gitignore`

The next step in bundling up a Next.js application for deployment via NPM is to include the build files. In all Next.js projects, you want to ignore the .next folder in your .gitignore. The .next folder is where Next.js keeps all the build artifacts it creates — minified javascript, css chunks, etc. Assuming your “source code” is Typescript and SCSS, everything in the .next folder should be ignored, and rebuilt as needed from the source.

BUT… the content of .next is actually what the visitors to your site really load - that’s the HTML, CSS, and Javascript that ends up in the browser. Since we are trying to package up a usable site, we need to bundle the contents of .next into our NPM bundles. However, we still want to exclude these rapidly changing files from git’s history.

The solution is a .npmignore file! By default, NPM will use a .gitignore file to determine which files it packs up into your packages, and which files it ignores. But, you can override this behavior by placing a .npmignore in your project. For example:

.gitignore

.DS_Store
node_modules
.next

.npmignore

.DS_Store
node_modules
# .next is included

Skip the `.pack` files

The final thing we learned is that while the contents of the .next directory are needed for your visitors, not everything is needed. We saw that we were shipping 300mb packages to NPM for our Next.js UIs. We dug into the .next folder and learned that if you opt-into Webpack v5 for your Next.js site, large .next/cache/*.pack files will be created to speed up how Webpack works. This is normal behavior, but we were inadvertently publishing these large files to NPM! We added the .next/cache/* directory to our .npmignore and our build sizes went down to a more reasonable 20mb.

Our final .npmignore looks like this:

.npmignore

.DS_Store
node_modules
.next/cache/*

Data Makes Your Tools Smarter

Brian Leonard — Wed, 26 May 2021 17:47:03 +0000

When I was in charge of Product/Engineering at TaskRabbit, it was always challenging to prioritize integrations being requested by our Marketing, Sales, and Customer Success teams.

First and foremost, most engineers just do not like working on this sort of thing. Often, this is the deciding factor in organizations. The work that gets done is the work that energizes people and no one was excited to brainstorm how we could most effectively sync data to Marketo.

What they do want to work on is the core product. That is why they joined the company and critically, they know how to be successful there. The requirements for the Intercom integration are much fuzzier. It is hard to know what success looks like. The ambiguity prevents the momentum often necessary to get the work started, much less to the finish line.

Of course, we also had our own goals. There were features to build, bugs to fix, metrics to move. When combined the the other factors, the integrations were at the bottom of the list despite the outsized opportunity to impact the business.

Customer Success

One practice we had at TaskRabbit was periodically shadowing coworkers on other teams. It is enlightening and helps build the empathy that makes the business much stronger. If you have ever done this, particularly with a customer success representative, you will know what I am talking about when I say that it is very difficult to watch.

The primary job is working tickets in a tool like Zendesk. As an engineer, the problems themselves made me sad because they were often bugs or fundamental misunderstandings on how the product worked. However, I had much more anxiety over the process of solving the ticket itself. Specifically, the data to help close those tickets is everywhere. Each ticket leads to multiple tabs just to get the right context and few more to solve the problem.

When this data inefficiency is multiplied for all the customers and all the tickets and all the coworkers, it is a huge waste of time that negatively affects everyone: the customer, the coworker, the business.

The key concept of integrations is leverage.

An data integration makes it possible to route the tickets better and surface key data right there in Zendesk. I've seen this result in 10x efficiency increase which as a huge impact on customer and coworker satisfaction, leading to a much stronger business.

The leverage here is tremendous because of the volume, scale, and how manual things were to begin with.

Marketing and Sales

In Product organizations, we thing in product terms. What can we build that will attract more users? What feature will make them stay around longer? However, it often turns out that's the wrong solution space altogether.

After the minimum viable product stage, the difference in many businesses is channels.

Let's agree that if nobody knows about the product, it doesn't matter what features you have. The solve for that isn't always a better landing page. It might be a Facebook ads integration to allow the Marketing team to create lookalike audiences.

If someone has signed up or used the product, what's going to bring them back? Not your social proof on the funnel. And probably not the generic newsletter each month. The most effective tool here is personalized communication about their needs where they are in the journey, accomplished by syncing their data to tools like Mailchimp and leveraging that to be more relevant.

B2B cases are all about timing. How can you reach the right people at the right time with the right message? It's not going to be a feature and it's not going to be blindly emailing all of the accounts. When Salesforce knows about recent activity in the product, you can use that data to trigger the right communication to better qualified leads.

A Common Language

Even with these strong business cases, integrations were always still hard to prioritize because of the unfamiliarity. Eventually, they had to happen for the company to scale. At that company and 100 more I've spoken with, it was always done later than it should have been.

The cause of this delay is the lack of a common language between engineers and operational teams. When looking to solve this organizational problem, we set out to fill this gap with Grouparoo. By defining patterns through an open source framework, both sides can engage on consistent footing. This approach makes data enablement easier and adds confidence, the core challenges for the engineers.

I don't have any illusions about how exciting we can make data sync. It's unlikely product engineers will wake up in the morning thinking about Marketo's rate limits. My bar is pretty low.

I just want to make it possible for an engineer to respond with "Oh yeah, what do they need?" instead of "No, thanks." to an integration ask.

This response is a signal that integration work is on an equal footing with other features. At that point, everyone starts to realize the leverage and power of these integrations. They are low-hanging fruit that make a huge difference. Then, they get done, the tools get smarter, everyone has a better experience, and the whole business wins.

Your smart frontend is doing too much

Pedro S. Lopez — Wed, 26 May 2021 12:37:58 +0000

The rise in popularity of frontend libraries and frameworks like React, Vue and Angular make it easier than ever before to build rich and interactive web apps. Pair these powerful libraries with a nice API to pull some data, and you can pretty quickly build out complex use cases. However, the ability to do so many things on the client side doesn't always mean you should.

In this article we'll reflect on API design, separation of concerns, and how to build APIs that better serve the clients they're intended to be used by.

Taking it back to RPC

In the days of RPC APIs, clients would call functions on the backend that did exactly what they needed to do. This resulted in a somewhat "client-first" approach, where these functions could be tailor-made to support the client's functionality.

For example, if a client needed to get a list of blog posts from a certain author, a route /getAllPostsByAuthor could be made. If they instead needed to get the most recent posts, /getMostRecentPosts could be added. If an action to delete a post was necessary, /deletePostById would do the job.

Implementation on the client side would be pretty simple, since clients could just call the functions on the server that accomplished what they needed to do. However, this also meant that the client and server end up being tightly coupled, which introduces a whole other set of issues. It could also be easy to end up with tons of similar functions (and therefore API routes) to support slightly different use cases, which can be hard to maintain.

Hello REST

Somewhere along the way, we got tired of having all these functions and routes that were hard to discover and maintain, so we started moving over to REST. The more common RESTful approach used today leads to building more general APIs to support a wide range of use cases with the same interface. This means that your frontend and backend can evolve independently as long as that contract is maintained.

It works by exposing "resources" instead of functions, and leveraging standard HTTP methods like GET, PUT, POST and DELETE to define consistent APIs that can easily and neatly cover most use cases.

All three example endpoints we saw for managing posts with an RPC approach could be boiled down into a single /posts endpoint. Then, we could request GET /posts?authorId=1 to get all posts by a certain author, GET /posts?limit=5 to get the five latest posts and DELETE /posts/12 to delete a certain post.

With this approach, if the client needed data for a post and its comments, they could issue multiple requests to get the data they need. For example, GET /posts/20 and GET /posts/20/comments.

While this works great for simple CRUD operations, things can get messy when clients need to execute actions or make multiple requests to different resources to accomplish a given task.

When clients are too smart

As a tool for keeping important data synchronized between systems, Grouparoo's various React apps need to support some pretty complex flows. They're used to manage and configure what exactly happens when profiles are being synced, which has real effects on your data.

For example, in the page used to configure a given destination, you are asked to select which group of profiles the destination should track. This determines who gets synced to the destination and who does not.

This may seem simple at first, but it means that when doing a simple action like changing the group, a number of things need to happen. If the tracked group has changed, we may need to kick off a run to get the latest data for all the affected profiles and export them to the destination. Similarly, if the destination is no longer tracking a group, we need to go to the destination and remove all the profiles that had been sent over in previous exports.

We were leaning more on the REST / reusability side, so to support this our API had exposed three endpoints:

One for updating the destination's data, setting options and configuring which properties would be sent over.
Another for setting the newly-tracked group and kicking off an import/export of profiles, if needed.
Another for removing the previously-tracked group from the destination and kicking off an export to remove the profiles for the destination, if needed.

Our simple button at the bottom of the page to save destination data now had to determine if the group had been changed and execute a number of API requests depending on what had been done. This architecture later caused some weird bugs that were hard to track down.

What could possibly go wrong?

Having a nice API that could support so many use cases and a smart frontend that could decide what things to do now led to executing multiple, sequential API requests from a single button click. At first glance this may not seem so bad since we're bringing more flexibility to how the API can be used. But as always, the devil is in the details.

⏳ Network speeds

A somewhat obvious issue that comes with having to do more requests is that you end up having to do multiple roundtrips to the server. If the client has a spotty connection, this could lead to longer response times and increases the amount of things that can go wrong.

❌ Partial failures

Something could always go wrong when issuing any of those requests. If your first request fails, then maybe everything's fine. But what if the first one succeeds but the second one doesn't? Do we need to rollback the first one? What if that fails? Nasty.

↔️ State changes between requests

Another problem that comes with issuing multiple requests is that there's no guarantee that the state will be the same when that second request arrives to the backend. What if another user issued a request to update the same resource and it happened to be processed before yours? The state of that resource could be different at this point, meaning that your second request could produce unintended results.

A very similar issue was happening at Grouparoo, where the group that the client thought the destination was tracking was actually some other group. This meant that we could potentially miss triggering an export in certain scenarios.

Building better APIs

Fine, maybe it's not such a good idea to have the client make these decisions. But then how could we represent these actions if we want to build a RESTful API? Ultimately, the solution lies in good API design.

Though there are things that can and should be controlled in the frontend, others should really be decided directly in the backend. Actions that are dependent on the current state, sensitive to interactions between multiple clients or those that involve complex business logic could be good candidates for things that shouldn't be decided on the client. However, this can vary depending on what you're building. The most important thing is to keep the clients and consumers in mind while designing the API that they'll end up using.

Make it part of the resource

Your API resources don't have to match 1:1 to your internal models. Well-designed APIs should abstract away internal details and expose a simple interface that can be used to talk to your service. If that means adding an additional field to make things easier on the clients, that's totally fine.

This is actually what we ended up doing to resolve our bug. Instead of having a separate route to track and un-track a group, we added a trackedGroupId property to the destination's PUT request that we could use to execute the necessary side effects that had to take place.

Or, maybe RPC isn't all bad

Fun fact: It's actually pretty common for so-called "RESTful" APIs to expose actions as additional endpoints for a resource. While REST purists may frown upon it, there are some cases where actions that need to be performed just can't be represented as a resource or as part of one. In these cases, it's fine to take some inspiration from RPC.

For example, in the context of syncing data, you could imagine calling POST on an endpoint /destination/hubspot/export to trigger an action that exports all profiles to the destination, even though this may not map to a resource. Of course, it's important to keep in mind how many of these actions you're creating and if they really can't be better expressed as resources. If all you're doing is creating actions, maybe REST isn't an appropriate choice for your API at all and you're better off using RPC.

At Grouparoo we deal with lots of APIs to integrate with third-party systems, and we've seen our fair share of excellent and not-so-great APIs. It's always great when integrating with a platform that has thought through these use cases and exposes an API that's easy to work with.

Using Typescript to create a Robust API between your frontend and backend

Evan Tahler — Wed, 19 May 2021 17:48:32 +0000

Two of the major components of the @grouparoo/core application are a Node.js API server and a React frontend. We use Actionhero as the API server, and Next.JS for our React site generator. As we develop the Grouparoo application, we are constantly adding new API endpoints and changing existing ones.

One of the great features of Typescript is that it can help not only to share type definitions within a codebase, but also across multiple codebases or services. We share the Typescript types of our API responses with our React Frontend to be sure that we always know what kind of data we are getting back. This helps us ensure that there is a tight coupling between the frontend and backend, and that we will get compile-time warnings if there’s something wrong.

Getting the type of an API Response

In Actionhero, all API responses are defined by Actions, which are classes. The run() method of the Action class is what is finally returned to the API consumer. Here’s a prototypical example of an action that lets us know what time it is:

import { Action } from "actionhero";

export class GetTime extends Action {
  constructor() {
    super();
    this.name = "getTime";
    this.description = "I let you know what time it is";
    this.inputs = {};
    this.outputExample = {};
  }

  async run() {
    const now = new Date();
    return { time: now.getTime() };
  }
}

This action takes no input, and returns the current time as a number (the unix epoch in ms). The action is also listed in our config/routes.ts file as responding to GET /time.

The next step is to extract the run() method’s return type to get the type of the API response

We can use a helper like type-fest’s PromiseValue to get the return value, or we can do it ourselves:

// from https://www.jpwilliams.dev/how-to-unpack-the-return-type-of-a-promise-in-typescript

export type UnwrapPromise<T> = T extends Promise<infer U>
  ? U
  : T extends (...args: any) => Promise<infer U>
  ? U
  : T extends (...args: any) => infer U
  ? U
  : T;

So, the type of the Action’s response is:

type ActionResponse = UnwrapPromise<typeof GetTime.prototype.run>; // = { time: number; }

And in our IDE:

This is excellent because now any changes to our action will result in the type being automatically updated!

Consuming the API Response Type in React

The Grouparoo Application is stored in a monorepo, which means that the frontend and backend code always exist side-by-side. This means that we can reference the API code from our Frontend code, and make a helper to check our response types. We don't need our API code at run-time, but we can import the types from it as we develop and compile the app to Javascript.

The first thing to do is make a utility file which imports our Actions and extracts their types. Grouparoo does this in web/utils/apiData.ts

import { UnwrapPromise } from "./UnwrapPromise";
import { GetTime } from "../../api/src/actions/getTime";

export namespace Actions {
  export type GetTime = UnwrapPromise<typeof GetTime.prototype.run>;
}

This apiData.ts will allow us to more concisely reference Actions.GetTime in the rest of our react application.

Now, to use the Action’s response type, all we have to do is assign it to the response of an API request:

import { useState, useEffect } from "react";
import { Actions } from "../utils/apiData";

export default function TimeComponent() {
  const [time, setTime] = useState(0);

  useEffect(() => {
    load();
  }, []);

  async function load() {
    const response: Actions.GetTime = await fetch("/api/time");
    setTime(response.time);
  }

  if (time === 0) return <div>loading...</div>;

  const formattedTime = new Date(time).toLocaleString();
  return <div>The time is: {formattedTime}</div>;
}

Now we have enforced that the type of response in the load() method above will match the Action, being { time: number; }. We will now get help from Typescript if we don’t use that response value properly as a number. Foe example, assigning it to a string variable creates an error.

Summary

Since Typescript is used at “compile time”, it can be used across application boundaries in surprisingly useful ways. It’s a great way to help your team keep your frontend and backend in sync. You won’t incur any runtime overhead using Typescript like this, and it provides extra certainty in your test suite that your frontend will use the data it gets from your API correctly.

If this type of work is interesting to you, Grouparoo is hiring!

Debugging image dimensions with Next.js

Brian Leonard — Wed, 19 May 2021 17:31:57 +0000

I was writing a blog post. In grand engineer tradition, I got distracted while blogging and spent a few hours writing tools to increase blogging efficiency.

Specifically, I was having trouble knowing the correct width and height props to put on the screenshots I was making for the blog post. I would take the screenshot and then use image tools and even a spreadsheet to figure out the right ratio/dimensions for how I wanted it to show up in the UI.

Now, if I get the dimensions wrong (or just want to see the correct ones), it will show it as a little red overlay in development. The banner uses the passed in dimensions to suggest changes that would match either the given height or width.

With the correct sizing, it goes away.

Migration

Next.js has a fancy new image component that requires you pass in these width and height props. These are likely necessary to prevent the page shifting as images load, but if they do not match the ratio of the actual image, it will become distorted as above.

When I was implementing the next/image component the first time, we had to add these props for all of our website images. I made this script that went through all of them and output the true widths, making this spreadsheet.

Then, I went through all our images and filled in the dimension (maxWidth or maxHeight) that I wanted to set. It then gave me the other dimension.

This worked pretty well for the migration.

However, the spreadsheet wasn't working for me as I was writing blog posts, though. I was figuring out the size from the MacOS "Get Info" menu item and adding them to the sheet manually. It took me out of the flow of writing.

Implementation

For example, the code for the logo above was the following:

<Image src="/images/logos/nextjs.png" height={300} width={600} />

The banner is accomplished with a new DebugImage component that adds an an API request after the page loads. The server-side code seems to be needed to actually inspect the actual image on disk.

The component checks with the API:

GET /api/imgdim?w=600&h=300&url=%2Fimages%2Flogos%2Fnextjs.png

{
  "optimized": false,
  "message": "W x H: 600 x 539, 334 x 300",
  "dimensions": [
    { "w": 600, "h": 539 },
    { "w": 334, "h": 300 }
  ],
  "ratio": 1.1136363636363635
}

When it gets fixed (by changing the width to 334):

GET /api/imgdim?w=334&h=300&url=%2Fimages%2Flogos%2Fnextjs.png

{
  "optimized": true,
  "message": "Optimized",
  "dimensions": [
    { "w": 334, "h": 300 },
    { "w": 334, "h": 300 }
  ],
  "ratio": 1.1136363636363635
}

The code in the DebugImage component takes the following steps:

Doesn't do anything unless the NODE_ENV is "development"
Otherwise, wraps the component in a div with no border, padding, etc
If the image is not optimized shows the message in the top left corner

Conclusion

I guess now, I have some work to do...

Maybe there's also a way to catch this in our test suite. There seems to be a few options there:

Use selenium and look for this banner, but that would be pretty slow
Compile the site, but with a stubbed Image component that records what's being used. Compare those dimensions to the ones on disk.

Anyway, I made my original blog post, will make future ones easier, and even got an extra one out of it!

Here is the pull request with all the code.

Testing sessionStorage and localStorage with Selenium (Node)

Sean C Davis — Mon, 17 May 2021 18:05:56 +0000

I recently added a feature to the Grouparoo site that is using sessionStorage to send analytics data. Being that it's an important feature, we should write test(s) to cover the use case(s), right?

Okay, fine. Let's do it!

This website is a Next.js application that uses Jest as the test runner and Selenium WebDriver for integration test help.

What I wanted to do with Jest and Selenium was to read from sessionStorage after visiting a series of pages. After a bit of perusing, I finally uncovered a (goofy) way to achieve what I wanted.

We can use the executeScript method to run a JavaScript expression and capture the result. Our test looks like this:

declare var browser: any;

async function getSessionItem(key) {
  return await browser.executeScript(
    `return window.sessionStorage.getItem("${key}");`
  );
}

test("stores page history in the session data", async () => {
  await browser.get(url + `/docs/config`);
  expect(await getSessionItem("prevPath")).toBe("null");
  expect(await getSessionItem("currentPath")).toBe("/docs/config");
  await browser.get(url + `/meet`);
  expect(await getSessionItem("prevPath")).toBe("/docs/config");
  expect(await getSessionItem("currentPath")).toBe("/meet");
});

Here are a few of the key items to note:

You must return the JavaScript expression or you'll end up with undefined.
It's a much cleaner approach to run tests as async functions so you can use await to retrieve the result of the script, rather than ending up in a nightmarish Promise chain.
browser is often referred to as driver in other documentation and implementations. This comes from the library we're using to connect Jest and Selenium.

This now works like a charm! You could take a similar approach if you wanted to read from any other JavaScript object, including localStorage.

Deferring Side-Effects in Node.js until the End of a Transaction

Evan Tahler — Mon, 17 May 2021 18:05:04 +0000

At Grouparoo, we use Actionhero as our Node.js API server and Sequelize for our Object Relational Mapping (ORM) tool - making it easy to work with complex records from our database. Within our Actions and Tasks, we often want to treat the whole execution as a single database transaction - either all the modifications to the database will succeed or fail as a unit. This is really helpful when a single activity may create or modify many database rows.

Why do we need Transactions?

Take the following example from a prototypical blogging site. When a user is created (POST /api/v1/user), we also create their first post and send them a welcome email. All examples in this post are written in Typescript, but the concepts work the same for Javascript.

import { action } from "actionhero";
import { User, Post } from "../models";

export class UserCreate extends Action {
  constructor() {
    super();
    this.name = "user:create";
    this.description = "create a user and their first post";
    this.inputs = {
      firstName: { required: true },
      lastName: { required: true },
      password: { required: true },
      email: { required: true },
    };
  }

  async run({ params }) {
    const user = await User.create(params);
    await user.updatePassword(params.password);
    await user.sendWelcomeEmail();

    const post = await Post.create({
      userId: user.id,
      title: "My First Post",
      published: false,
    });

    return { userId: user.id, postId: post.id };
  }
}

In this example, we:

Create the user record
Update the user’s password
Send the welcome email
Create the first post for the new user
Return the IDs of the new records created

This works as long as nothing fails mid-action. What if we couldn’t update the user’s password? The new user record would still be in our database, and we would need a try/catch to clean up the data. If not, when the user tries to sign up again, they would have trouble as there would already be a record in the database for their email address.

To solve this cleanup problem, you could use transactions. Using Sequelize’s Managed Transactions, the run method of the Action could be:

async run({ params }) {
  return sequelize.transaction(async (t) => {
    const user = await User.create(params, {transaction: t});
    await user.updatePassword(params.password, {transaction: t} );
    await user.sendWelcomeEmail();

    const post = await Post.create({
      userId: user.id,
      title: 'My First Post',
      published: false,
    }, {transaction: t})

    return { userId: user.id, postId: post.id };
  })
}

Managed Transactions in Sequelize are very helpful - you don’t need to worry about rolling back the transaction if something goes wrong! If there’s an error throw-n, it will rollback the whole transaction automatically.

While this is safer than the first attempt, there are still some problems:

We have to remember to pass the transaction object to every Sequelize call
We need to ensure that every method we call which could read or write to the database needs to use the transaction as well, and take it as an argument (like user.updatePassword()... that probably needs to write to the database, right?)
Sending the welcome email is not transaction safe.

Sending the email as-written will happen even if we roll back the transaction because of an error when creating the new post… which isn’t great if the user record wasn’t committed! So what do we do?

Automatically Pass Transactions to all Queries: CLS-Hooked

The solution to our problem comes from a wonderful package called cls-hooked. Using the magic of AsyncHooks, this package can tell when certain code is within a callback chain or promise. In this way, you can say: "for all methods invoked within this async function, I want to keep this variable in scope". This is pretty wild! If you opt into using Sequelize with CLS-Hooked, every SQL statement will check to see if there is already a transaction in scope... You don't need to manually supply it as an argument!

From the cls-hooked readme:

CLS: "Continuation-Local Storage"

Continuation-local storage works like thread-local storage in threaded programming, but is based on chains of Node-style callbacks instead of threads.

There is a performance penalty for using cls-hooked, but in our testing, it isn’t meaningful when compared to await-ing SQL results from a remote database.

Using cls-hooked, our Action's run method now can look like this:

// Elsewhere in the Project

const cls = require('cls-hooked');
const namespace = cls.createNamespace('actionhero')
const Sequelize = require('sequelize');
Sequelize.useCLS(namespace);
new Sequelize(....);

// Our Run Method

async run({ params }) {
  return sequelize.transaction(async (t) => {
    const user = await User.create(params);
    await user.updatePassword(params.password);
    await user.sendWelcomeEmail();

    const post = await Post.create({
      userId: user.id,
      title: 'My First Post',
      published: false,
    })

    return { userId: user.id, postId: post.id };
  })
}

Ok! We have removed the need to pass transaction to all queries and sub-methods! All that remains now is the user.sendWelcomeEmail() side-effect. How can we delay this method until the end of the transaction?

CLS and Deferred Execution

Looking deeper into how cls-hooked works, we can see that it is possible to tell if you are currently in a namespace, and to set and get values from the namespace. Think of this like a session... but for the callback or promise your code is within! With this in mind, we can write our run method to be transaction-aware. This means that we can use a pattern that knows to run a function in-line if we aren’t within a transaction, but if we are, defer it until the end. We’ve wrapped utilities to do this within Grouparoo’s CLS module.

With the CLS module you can write code like this:

// from the Grouparoo Test Suite: Within Transaction

test("in a transaction, deferred jobs will be run afterwords", async () => {
  const results = [];
  const runner = async () => {
    await CLS.afterCommit(() => results.push("side-effect-1"));
    await CLS.afterCommit(() => results.push("side-effect-2"));
    results.push("in-line");
  };

  await CLS.wrap(() => runner());
  expect(results).toEqual(["in-line", "side-effect-1", "side-effect-2"]);
});

You can see here that once you CLS.wrap() an async function, you can defer the execution of anything wrapped with CLS.afterCommit() until the transaction is complete. The order of the afterCommit side-effects is deterministic, and in-line happens first.

You can also take the same code and choose not apply CLS.wrap() to it to see that it still works, but the order of the side-effects has changed:

// from the Grouparoo Test Suite: Without Transaction

test("without a transaction, deferred jobs will be run in-line", async () => {
  const results = [];
  const runner = async () => {
    await CLS.afterCommit(() => results.push("side-effect-1"));
    await CLS.afterCommit(() => results.push("side-effect-2"));
    results.push("in-line");
  };

  await runner();
  expect(results).toEqual(["side-effect-1", "side-effect-2", "in-line"]);
});

CLSAction and CLSTask

Now that it is possible to take arbitrary functions and delay their execution until the transaction is complete, we can use these techniques to make a new type of Action and Task that has this functionality built in. We call these CLSAction and CLSTask. These new classes extend the regular Actionhero Action and Task classes, but provide a new runWithinTransaction method to replace run, which helpfully already uses CLS.wrap(). This makes it very easy for us to opt-into an Action in which automatically runs within a Sequelize transaction, and can defer it's own side-effects!

Putting everything together, our new transaction-safe Action looks like this:

// *** Define the CLSAction Class ***

import { Action } from "actionhero";
import { CLS } from "../modules/cls";

export abstract class CLSAction extends Action {
  constructor() {
    super();
  }

  async run(data) {
    return CLS.wrap(async () => await this.runWithinTransaction(data));
  }

  abstract runWithinTransaction(data): Promise<any>;
}

// *** Use the CLSAction Class ***

import { CLSAction } from "../classes";
import { User, Post } from "../models";

export class UserCreate extends CLSAction {
  constructor() {
    super();
    this.name = "user:create";
    this.description = "create a user and their first post";
    this.inputs = {
      firstName: { required: true },
      lastName: { required: true },
      password: { required: true },
      email: { required: true },
    };
  }

  async runWithinTransaction({ params }) {
    const user = await User.create(params);
    await user.updatePassword(params.password);
    await CLS.afterCommit(user.sendWelcomeEmail);

    const post = await Post.create({
      userId: user.id,
      title: "My First Post",
      published: false,
    });

    return { userId: user.id, postId: post.id };
  }
}

If the transaction fails, the email won’t be sent, and all the models will rolled back. There won't be any mess to clean up 🧹!

Summary

The cls-hooked module is a very powerful tool. If applied globally, it unlocks the ability to produce side-effects throughout your application worry-free. Perhaps your models need to enqueue a Task every time they are created... now you can if you cls.wrap() it! You can be sure that task won’t be enqueued unless the model was really saved and committed. This unlocks powerful tools that you can use with confidence.

Why do database columns have a character length of 191?

Evan Tahler — Mon, 17 May 2021 18:00:46 +0000

Sometimes, when you are looking at a database’s schema, you see that there are text fields defined like this:

email_address varchar(191) NOT NULL

This means that the column supports strings with a maximum length of 191 characters, and can’t be null. 191 is such an odd number - where did it come from? In this post, we’ll look at the historical reasons for the 191 character limit as a default in most relational databases.

Why `varchar` and not `text`?

The first question you might ask is why limit the length of the strings you can store in a database at all? All modern popular relational database support (almost) unlimited sized strings with a text or blob-type column, so why not use that? The reason is indexes.

If you are going to search by a column, say email_address, you probably want to add an index to it to speed things up when you do the following:

select id from users where email = 'foo@example.com';

As your table gets bigger, searches get slower because your database has to check every row to find a match. However, if you add a search index, you are telling your database to essentially "pre-compute" popular search patterns with a tree so the next search is much faster. In essence, indexes spend computation time (and a little bit of disk space) making writes to the database slower, to speed up reads later. For most applications this is a great tradeoff, since they are "read heavy" and "write lite".

So, why use varchar? Indexes can be made to perform better when assumptions can be made about the type of data they store. Knowing how long the strings in the index are is one of the best ways speed things up. For some databases, you aren’t allowed to add a search index to columns of type text because this optimization can't be done, while in others, the index just won’t perform as well. In fact, historically, databases were constructed with limits on how big an index could be to optimize search and how they stored data on disk.

It’s MySQL’s fault

Ok, so indexes are good. But, generically, it seems that an index of any size should work, and while that’s true today, it wasn't always possible. The next stop on our journey is to look at what the default column size was far in the past, and that was 255 characters, e.g.:

email_address varchar(255) NOT NULL

MySQL, the most popular open source database of the early 2000s had a limit of 255 characters in indexed fields. The history is fuzzy as to why MySQL chose a 255 character limit (see the articles linked below), but the most popular theories include:

256 is the largest number you can represent with an 8-bit integer. MySQL being very concerned with speed and memory usage, wanted to store things with the smallest possible data types.
MySQL was itself trying to be compatible with even older databases (sybase/SAP), and they had a 255 character limit.
MySQL wanted to ensure that its index files could fit within a single page block on older file systems.

With a 256 character limit in mind, the MySQL developers felt comfortable further optimizing many parts of the database against that 255-character limit (more on this later). Since many popular open source application frameworks launched in that time period (Wordpress, Django, and Rails to name a few), they all followed MySQL’s defaults, even when they could run on multiple database types, like postgres. This formed a common default for most ORMs (Object–relational mapping - Wikipedia) to use varchar(255), regardless of the database in use.

It’s 🐟’s fault

255 makes a lot more sense than 191. How did we get to 191? I’m going to blame emoji 😜. Seriously. Well, utf8mb4 at least, the charter set that allows for "international" ¹ characters, and included the first emoji. MySQL in the early 2000s was happy supporting 255 characters in varchar columns and indexing them. However, the the most popular text encoding (Latin1 or utf8 ) on the most popular MySQL database engine (innodb) assumed that 3 bytes was enough to store every character ², and once utf8mb4 came along with characters like 𠼭³ and 🐟, 4 bytes were needed to store each character. There were more character to choose from, so referencing them took more bytes.

The way innodb MySQL databases worked was that you can only have 767 bytes for an index - enough to store 255 3-byte characters (767/3 = 255). This is an extreme example of index optimization based on knowing the size of the data you are indexing! So if the characters took more space to store, then the number of characters you could index had to get smaller. Specifically, 767/4 = 191 characters! As more software supported an international audience, varchar(191) replaced varchar(255) as the default. For those software applications that didn't need to support international users, they also needed to upgrade once users started expecting emoji support (often linked to the rise of smartphones) in the early 2010s.

Today

These days, with modern databases, character encodings like utf8mb4 and others which can support "all" characters are the default, and the fixed-length index is a thing of the past. However, we still have these 191-character defaults in many applications to ensure compatibility. Regardless, indexes still work best when they know the size of the the strings they are comparing, so we still want to have some limit on our column length for speed reasons, and thanks to history and inertia, the 191 limit is still with us.

Thank you

Thanks to all the reference articles I checked when putting together this history, specifically:

"International" is an odd way to talk about the languages that most of the world uses, specifically those using non-Latin characters. However, operating systems and databases had thoroughly english-centric origins, and the legacy of those early choices is still rippling though our code today. ↩
Throughout this post, I've use the word "character" rather than "letter", and this is why - depending on your language, each character in a string might be a letter, a whole word, or even a pictogram like an emoji. ↩
"To Honk" (like from a car) - https://words.hk/zidin/%F0%A0%BC%AD ↩

Build a Responsive Mega Menu with React Bootstrap

Teal Larson — Thu, 13 May 2021 22:48:25 +0000

Having clear and accessible navigation is huge for website conversions. Sites with poor navigation are frustrating to use. Nested navigation menus are a common way to help keep top-level navigation to a minimum, but they can have major usability issues. A better way to handle a large number of links in a dropdown is to create a mega menu. Recently, we gave our site navigation a face lift using mega menus.

Since both our core product and our website are open source here at Grouparoo, let’s dive in and take a look at how we built out a mega menu using our stack.

First, What’s a Mega Menu?

This!

A mega menu is a way to have multiple columns (or more!) of items within your dropdown menu. Some key things to consider:

Make sure you have enough items within your dropdown to warrant a mega menu
Figure out how you want to arrange the items within your menu

Recently, we’ve been adding a lot of content to our site. We wanted to link more pages in our navigation, but still keep the top level navigation bar minimal. So we went with a mega menu.

Let’s Build

For this tutorial, we'll be building this copycat mega menu:

We'll be working off a boilerplate project
with NextJS, React Bootstrap,
SASS, and Fort Awesome
already imported and ready to go. Coffee themed, because of course.

React Bootstrap, and Bootstrap in general are great. The built-in components, classes, and utilities save a lot of time building responsive layouts, and they're accessible by default. They’re also super flexible. For those less comfortable with front end, Bootstrap's documentation is full of copy-and-pasteable examples to get started making modern, accessible sites. And once you've been working with Bootstrap for a while, it becomes almost second nature to Frankenstein pieces together like we're about to do.

💡 To get started, fork this
repo and npm install!

Here is our Navbar element starting out:

When developing our mega menu for Grouparoo, I started with this Figma mockup. From there, I looked at what React Bootstrap components to use. We did end up making a few design changes further into the process, but it all started with:

We're going to build something similar where the "events" link currently lives on our template.

Let's start by adding the following into /components/navigation.js in place of the current Nav.Link for events:

<NavDropdown
  className="pr-2 py-2 align-text-top"
  title="Events"
  id="basic-nav-dropdown"
>
  <Container className="eventsNav pt-0 mt-0">
    <Row>
      <Col xs="12" md="6" className="text-left">
        <Dropdown.Header>
          <FontAwesomeIcon
            color="black"
            icon={"concierge-bell"}
            size="1x"
            className="pr-1"
          />
          {"  "}
          Catering
        </Dropdown.Header>
        <Dropdown.Item>
          <Link href="/">
            <a className="nav-link" role="button">
              Corporate
            </a>
          </Link>
        </Dropdown.Item>
        <Dropdown.Item>
          <Link href="/">
            <a className="nav-link" role="button">
              Private
            </a>
          </Link>
        </Dropdown.Item>

        <Dropdown.Divider />
        <Dropdown.Header>
          <FontAwesomeIcon
            color="black"
            icon={"chalkboard-teacher"}
            size="1x"
            className="pr-1"
          />
          {"  "}
          Classes
        </Dropdown.Header>
        <Dropdown.Item>
          <Link href="/">
            <a className="nav-link" role="button">
              Barista 101
            </a>
          </Link>
        </Dropdown.Item>
        <Dropdown.Item>
          <Link href="/">
            <a className="nav-link" role="button">
              History of Coffee
            </a>
          </Link>
        </Dropdown.Item>
        <Dropdown.Item>
          <Link href="/">
            <a className="nav-link" role="button">
              Intro to Cafe Snobbery
            </a>
          </Link>
        </Dropdown.Item>
        <Dropdown.Divider className="d-md-none" />
      </Col>

      <Col xs="12" md="6" className="text-left">
        <Dropdown.Header>
          <FontAwesomeIcon
            color="black"
            icon={"building"}
            size="1x"
            className="pr-1"
          />
          {"  "}
          Rentals
        </Dropdown.Header>
        <Dropdown.Item>
          <Link href="/">
            <a className="nav-link" role="button">
              Fireside Room
            </a>
          </Link>
        </Dropdown.Item>
        <Dropdown.Item>
          <Link href="/">
            <a className="nav-link" role="button">
              Roasting Room
            </a>
          </Link>
        </Dropdown.Item>
        <Dropdown.Divider />
        <Dropdown.Header>
          <FontAwesomeIcon
            color="black"
            icon={"sun"}
            size="1x"
            className="pr-1"
          />
          {"  "}
          Seasonal
        </Dropdown.Header>
        <Dropdown.Item>
          <Link href="/">
            <a className="nav-link" role="button">
              Coldbrew Night
            </a>
          </Link>
        </Dropdown.Item>
        <Dropdown.Item>
          <Link href="/">
            <a className="nav-link text-wrap" role="button">
              Campfire Coffee Class
            </a>
          </Link>
        </Dropdown.Item>
      </Col>
    </Row>
  </Container>
</NavDropdown>

Here's where we're at:

It's all there... but obviously in need of some help. I applied a custom className
of eventsNav to the dropdown container knowing I'd need to add some SASS. It is
wise to avoid custom styles (inline or on a stylesheet) if possible when working
with Bootstrap as it can make debugging more difficult. However, sometimes, to strictly override the Bootstrap styles, it's necessary. Add the following to /styles/styles.scss
:

.nav-link {
  font-size: 0.95rem;
}

#basic-nav-dropdown {
  font-size: 0.95rem;
}

#seeMoreLink {
  color: #607d8b;
}

#seeMoreLink:hover {
  color: black;
  font-weight: 400;
}
.dropdown-menu {
  padding: 1em;
}

.dropdown-item:hover {
  outline: none;
  box-shadow: none;
  background: none;
}
.dropdown-item a:hover {
  color: black;
}

.dropdown-item {
  height: 2em;
  padding-left: 0em;
}

.dropdown-header {
  font-weight: 600;
  padding-left: 0em;
}

@media only screen and (min-width: 600px) {
  .eventsNav {
    width: 22em;
    padding-bottom: 1em;
  }
}

Most of those rules are just visual adjustments to match fonts, remove text decorations, etc. The ultra important rule there is the final @media query that sets the width of .eventsNav. That rule is how we tell Bootstrap we want our dropdown menu's container a specific size.

Et voila! A mega menu showcasing multiple categories worth of pages (all the links currently point to home, but they work!):

And on mobile additional divider divs appear and the columns expand to full container width. Again, Bootsrap's utilities for working with screen size breakpoints are 👌:

Check out the original version live here or star our site repo or core product to see more. Or better yet, we're open source! Drop us a PR!

Change the Primary Key Data Type with Sequelize

Sean C Davis — Wed, 12 May 2021 15:45:43 +0000

We recently adjusted how we handle primary keys. Previously they were UUIDs with a max length of 40 characters. With our Declarative Sync feature, we allow developers to set primary key values from their configuration files. Thus, we needed to lengthen the maximum number of characters allowed on primary keys in our database.

Seems simple, right?

I thought so, too. We're using Sequelize as our ORM tool, and I found a handy changeColumn method.

So that's what I did. It looked like this:

await migration.changeColumn(tableName, columnName, {
  type: DataTypes.STRING(191),
});

Note: In these examples, I'm accessing Sequelize methods through an object called migration. This is because we use Actionhero to run our database migrations. Your objects will look different, but the methods on them should be the same.

I first tested with SQLite and voila! It did exactly as I expected. All the primary keys were changed and working just lovely.

Changing PostgreSQL Primary Keys

Since we support both Postgres and SQLite as our application database, I moved on to test in Postgres, and that's when, instead of the database being properly migrated, I was presented with this lovely message:

column "id" is in a primary key

I thought: Yes, true. That is correct. And ... ?

It turns out Sequelize doesn't handle this action well with Postgres. After going down a rabbit hole in playing around with constraints, I ended up just writing the SQL statement directly. It looked something like this:

const query = `ALTER TABLE "${tableName}" ALTER COLUMN "${columnName}" SET DATA TYPE varchar(${maxIdLength}); `;
await migration.sequelize.query(query);

That worked!

Consistency is Awesome! (SQLite is Weird.)

It made sense to me to try to use the same approach with both databases. So I tried my Postgres solution with SQLite.

It didn't work. (Sensing a theme yet?)

That seemed odd. But, of course, we already know that SQLite is weird. And it turns out SQLite's ALTER TABLE methods are extremely (and intentionally) limited.

Which meant I was stuck with two solutions. And when that happens, we tend to look at the current dialect and execute the appropriate code. And that's why this is the weird function that alters the primary key column in both Postgres and SQLite:

const changeColumn = async (tableName, columnName) => {
  if (config.sequelize.dialect === "postgres") {
    const query = `ALTER TABLE "${tableName}" ALTER COLUMN "${columnName}" SET DATA TYPE varchar(${maxIdLength}); `;
    await migration.sequelize.query(query);
  } else {
    await migration.changeColumn(tableName, columnName, {
      type: DataTypes.STRING(191),
    });
  }
};

You can see the complete set of changes that came along with this code in this pull request.

Forem: Grouparoo

Data cleaning null values -- SQL or Code?

Let's clean some data

Option 1: With a SQL Query

Using COALESCE

RIGHT OUTER JOIN

UNION with a list

Option 2: After the SQL Query 🥇

TypeScript:

Python:

Learn More:

Generate Dynamic JSON Pages with Next.js

Limitations of Pages in Next.js

The Setup

Method #1: API Routes

Starting Simple

Making it Dynamic

Method #2: Static File

Generate Script

Automate It!

Bundling and Distributing Next.js Sites via NPM

next build and npm run prepare

.npmignore vs .gitignore

Skip the .pack files

Data Makes Your Tools Smarter

Customer Success

Marketing and Sales

A Common Language

Your smart frontend is doing too much

Taking it back to RPC

Hello REST

When clients are too smart

What could possibly go wrong?

Building better APIs

Using Typescript to create a Robust API between your frontend and backend

Getting the type of an API Response

Consuming the API Response Type in React

Summary

Debugging image dimensions with Next.js

Migration

Implementation

Conclusion

Testing sessionStorage and localStorage with Selenium (Node)

Deferring Side-Effects in Node.js until the End of a Transaction

Why do we need Transactions?

Automatically Pass Transactions to all Queries: CLS-Hooked

CLS and Deferred Execution

CLSAction and CLSTask

Summary

Why do database columns have a character length of 191?

Why varchar and not text?

It’s MySQL’s fault

It’s 🐟’s fault

Today

Thank you

Build a Responsive Mega Menu with React Bootstrap

First, What’s a Mega Menu?

Let’s Build

Change the Primary Key Data Type with Sequelize

Changing PostgreSQL Primary Keys

Consistency is Awesome! (SQLite is Weird.)

Using `COALESCE`

`RIGHT OUTER JOIN`

`UNION` with a list

`next build` and `npm run prepare`

`.npmignore` vs `.gitignore`

Skip the `.pack` files

Why `varchar` and not `text`?