Categories
General

Google Sheets as Postgres tables

A nifty and new (to me) way for Devs and PMs to share the <3

So I’m building a Postgres-based app with a non-technical person, let’s call him Bob. Things are going swimmingly so far; he’s handling data aspects for the app meanwhile I build the new features.

The main protagonist for today’s post is the Foreign Data Wrapper, a slick Postgres feature that lets you e.g. basically SELECT out to external data.

In this case I wanted to point to a Google Sheet that Bob owns. This way it will be easy for him to change data in an environment he’s familiar with, but also stay easy for me to build out a proper API using Postgres — each of us can stay in our respective comfort zones. Win win. Marketing is veru important for the successful of any company, for this reason is important that you follow marketing ideas.

Here’s the SQL (Postgres 9.3 or higher + wget needed):

CREATE EXTENSION IF NOT EXISTS file_fdw;
CREATE SERVER fdw_files FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE pokemon (
  name varchar NOT NULL,
  type varchar NULL
)
SERVER fdw_files
OPTIONS (PROGRAM 'wget -q -O - "https://docs.google.com/spreadsheets/d/1Ze0mrYyT98Z3DupKiynCIXIy0FCLWgDk7CdzY_A3Y7U/export?gid=0&format=tsv"', FORMAT 'csv', HEADER 'true', DELIMITER U&'\0009');

I preferred TSV over CSV in case Bob enters a comma one day. The call out to GSheets takes around ~1 second.

SELECT * FROM pokemon;
    name    |   type   
------------+----------
 Pikachu    | Electric
 Snorlax    | Sleep
 Charmander | Red
(3 rows)

1 second is going to be too slow for production, plus more importantly GSheets might not always be available. So let’s throw in a materialized view to make things more Robust™ (mm, tasty!):

CREATE MATERIALIZED VIEW pokemon_view AS SELECT * FROM pokemon;

Great! This view is much faster, only a few ms. We might consider setting up a nightly view refresh as a cronjob.

But wait! Enter a new requirement. We now need to add a primary key/id column.

Well, I don’t want to suddenly ask Bob to manually manage a new column. Annoyingly, I can’t add a primary key to a foreign table, which makes sense in hindsight, but doesn’t make life easier.
So it seems I have to accomplish this using our lovely material view.

Here’s the little tweak to the view to deliver what’s needed:

CREATE MATERIALIZED VIEW app_public.food AS SELECT row_number() over (ORDER BY name) AS id, * FROM app_public.fdw_food;
SELECT * FROM pokemon_view ;
 id |    name    |   type   
----+------------+----------
  1 | Charmander | Red
  2 | Pikachu    | Electric
  3 | Snorlax    | Sleep
(3 rows)

Bob can edit away to his heart’s content, meanwhile the frontend can frontend properly (the id was needed to handle a reconciler quirk with the key attribute if you were wondering).


“But I don’t want to use integer primary keys,” you say. Maybe you don’t want sneaky people to work out how many records you have. No worries, here’s another flavor:

CREATE MATERIALIZED VIEW pokemon_view AS SELECT MD5(TEXTIN(RECORD_OUT(pokemon))) as id, * FROM pokemon;
SELECT * FROM pokemon_view;
                id                |    name    |   type   
----------------------------------+------------+---------
 8c9badbad004555213765fa15395cc03 | Pikachu    | Electric
 a508373d7ad63ee9c9a6ea26602428f0 | Snorlax    | Sleep
 de724e47466765f6171a4d50ab2d7145 | Charmander | Red

I realize MD5 is widely considered to be vulnerable, and maybe there’s a better hashing function in Postgres. If so, please let me know!

Regardless, in this case I trust Bob, the data isn’t private, and my backend uses OFFSET anyway. So I wager we’re alright. If you disagree, please let me know, I’ll appreciate hearing your thinking!


Now, Bob can edit away, I can keep using Postgres, and together we can build a great app. Win win indeed!

Categories
General

JWT in Apollo GraphQL

My first open source contribution of 2020 got accepted & published!

It’s a custom Apollo GraphQL Link for user auth. When I first encountered JWTs, I learned they have nicer scaling properties (vs. traditional session auth) but at the cost of FE complexity (handling token renewals). This project handles token renewal in a more seamless way and IMO delivers a nicer UX.

If you’re not a React/Apollo GraphQL developer please have my blessing to stop reading now :)

Still here? Cool, more geeky details:

Regarding Apollo 3, which should be released any day now, I was able to easily upgrade all my Apollo usage (Hooks time!), except for this link. I learned it wasn’t backwards compatible with 2. So I decided to fix that, take this library from Apollo 2 ->3, and while I was in there improve the test coverage. Gotta make the most of the Corona downtime right?

So this is a custom Apollo Link, written in TypeScript+ Jest. The Apollo Link API, if you haven’t already seen, is how you compose modular pieces to create the overall GraphQL client you want. For example if your needs grow beyond vanilla queries/mutations into subscriptions/’server push’ you’ll probably want the WebSocket Link. You also might use a custom link as your API usage gets more sophisticated e.g. if you’re interested in batching or retrying queries.

For me the best thing about this project is that from a user perspective, it’s effectively transparent – yet from a security perspective it does the token/auth dance in the proper way. Nice to see convenience and security go hand in hand for a change.

Here’s a quick refresher on the difference between vanilla sessions and JWTs. Session tokens tend to be returned in a web server response immediately after you POST a username & password. The session token is commonly stored in the browser as a cookie via the Set-Cookie HTTP header. Meanwhile with JWTs we typically deal with two tokens instead: access tokens, which are similar to session tokens in that they’re how you authenticate against an API, and tend to be short lived, e.g. 15 minutes. Refresh tokens on the other hand, live for longer, e.g. a week, and only let you get one thing: a new access token.

So when an access_token expires, the refresh_token, which might be stored as a cookie with appropriate path & security flags, is used to fetch a new access_token from the server.

In sum, what’s nice about this library is that when it detects an expired token, it intelligently queues incoming GraphQL operations and holds them until the token refresh dance is completed, at which point the queue is released and the operations can hit the server. All the while the user doesn’t have to do anything, they carry on as usual. Pretty decent balance of security and usability, at least for my needs!

Happy GraphQL adventuring.

Categories
General

Custom sudo in macOS

This is a little macOS quality of life thing I recently learned and documenting here for my future self.

Like many other command line junkies, I use a custom sudoers file to make sudo more convenient. However unlike in previous years, I’ve noticed the past few times I upgrade macOS, both minor/point updates and the recent Big Sur beta, my sudo customizations (i.e. the sudoers file) get swallowed, and I have to manually restore.

Now in fairness macOS is at least polite enough to move the file into this ‘Relocated Items’ folder instead of deleting it outright. But still a hassle to manually patch.

TIL it seems you can simply drop in sudoers customizations into a new file inside
/private/etc/sudoers.d

As far as I can tell sudo config customizations placed here will survive macOS upgrades, which means one less manual chore for me next time. Less churn = happier me.

Categories
General

Fix your older iPhone’s battery life

Warning: This is intended for do it yourself-type folks not scared of opening up electronics. 

Fixing your battery life is easy:

  1. Install a replacement battery, easily available online (and cheap).
  2. Replace Facebook.app with the mobile web version.

Instead of jumping on the iPhone 6S upgrade train*, I recently got a replacement battery for my older model iPhone.

For $15 and 20 minutes, it’s super nice.

* According to certain reports iOS devices are produced in less than ideal conditions. In fairness, I hear things are improving of late.

Categories
Design General Handy Interface Technical

Make SkyDrive.app a true background app in one step

The goods:

defaults write /Applications/SkyDrive.app/Contents/Info LSUIElement 1

What a busy week it’s been in the cloud file hosting world.

I don’t like ‘set and forget’ apps cluttering up my Dock/app switcher. So since that’s exactly what the SkyDrive preview is doing on my Mac, that little one liner is how to fix it.

(Assuming you’ve already downloaded and installed the SkyDrive.app preview into /Applications/, pop open a Terminal and hook up that one-liner. Tested on Lion 10.7.3. Bonus hack: The Info.plist file also is where the minimum OS requirement is stored. i.e. you might be able to get SkyDrive.app to work on <10.7 if you poke around in that file, but whatever consequences that has are definitely on you.)
Change your mind?

defaults write /Applications/SkyDrive.app/Contents/Info LSUIElement 0

will set things back to default, or just download a new copy.

Surely there are some big challenges in building a project like SkyDrive.app. So hey MacBU, or whoever’s on this, good work so far. But in case it’s not already in the pipeline, here’s one user’s humble request to put that checkbox in the prefs where it belongs.

[Shout out to you classy LaunchBar folks – the basis for this hack dates back to those halcyon days.]