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.

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.