Back-end solution for pulling from CSV files

Question

I'm building a data visualization that displays COVID information for the United States, at the city, state, and county level.

The ultimate source of truth are three CSVs published by the New York Times on Github in this repo: https://github.com/nytimes/covid-19-data

The CSVs are updated once per day with new data from the previous day.

The front-end involves selecting a state, county, and type of statistic (number of deaths, number of cases, etc.). Three line charts are then displayed, showing the rate of change over time - at the national, state, and county level.

Right now, the app is purely front-end. It downloads the set of three CSVs (which are quite large), then does a series of calculations on the data, and when the Promise completes, the visualization is finally displayed in the browser. It takes good 5-10 seconds to complete on a good internet connection - hardly sustainable in production, and also requires the user to download the entirety of the data, even though they might be only looking for a few combinations of states / counties.

Is there a solution that could speed this up, without requiring a back-end? Or is a formal database / backend structure needed?

Here is my general idea of what the back-end solution (I would use a Node.js / Express REST API setup) would entail, but looking for suggestions:

1. Deploy a Node.js script that downloads the CSVs once per day and puts the data in a database. I could either download the entirety of the CSVs and rewrite the entire database, or download just the new data and add it to the database.

2. Do some additional calculations on the data (for example, calculate change from the previous day) and then send those to the database. These additional calculations could also be done client-side (this is how it is working currently in my front-end solution)

3. When the user loads the page, have the front-end query for a list of states and counties from the back-end, so front-end can load.

4. When the user selects a state / county combination, send just that information to the back-end via a REST API. Have the back-end query the database and return just the requested information to the front-end.

Miscellaneous concerns:

a. Obviously, a no-backend solution would be preferred, but I can't think of a way where I can query these CSVs with just the user-supplied information without downloading them in their entirety first.

b. From a database perspective, it is a big lift / cost to delete all the data and rewrite it entirely? Or would it be more cost-efficient (assuming this is a cloud-based solution) to only add the new data? (assuming the old data does not change, which is an assumption)

c. I've been looking at GraphQL as an alternative to REST, but I'm not sure it will solve the problem of having to download the CSVs in their entirety and "store" them somewhere. There are several open-source APIs online already that provide a more convenient way to query the data:

https://github.com/Li357/covid-nyt-api https://github.com/desholmes/covid-19-us-api

But these all seem to be pulling from the CSV, and they take a long time. Is this because they are accessing the data from a CSV instead of a database which I'm assuming has much faster access?

Answer 1

To allow a variety of queries on such data, which transfer only the requested amount of bits and bytes over the network, one needs to preprocess and optimize the data for this purpose, there is no way around this. That is exactly what databases are made for. Trying to make things simpler by "avoiding a database" will end up in building a database on its own, so reinventing the wheel.

Is there a solution that could speed this up, without requiring a back-end

In theory, one could setup a peer-to-peer solution where the database is build on one client and reused on others, but that is definitely not simpler and probably not what you want. So better go for the most simple and straightforward solution, which is using a database backend. If you have speed concerns, use a database with in-memory capabilities, like SQLite.

But if you don't have a backend, and no preprocessed data, only "GitHub" as a "poor man's backend", there will be no way around pulling all the CSV data to the browser first. And as you wrote, this does not give you a satisfying experience.

From a database perspective, it is a big lift / cost to delete all the data and rewrite it entirely? Or would it be more cost-efficient (assuming this is a cloud-based solution) to only add the new data?

This depends ultimately on the amount of old and new data, and it's ratio, but don't forget, the data is only updated once a day, and there is probably lots of time to preprocess it (= store it in the database). So go for the most simple solution you can think of, and optimize it afterwards when you note it becomes too slow. Don't overthink this.

I've been looking at GraphQL

GraphQL is a richer way to query the data. It does not change any of the former considerations.

Answer 2

Just keep it in memory

Even the full county level CSV data is relatively small (16 MByte) so it would be possible to fully cache it in backend memory without a database and keep a simplistic index on state and county in memory. The data could be held as copies of the lines of original CSV file, so your backend could deliver it with minimal effort in the format that your frontend is currently prepared to process.

The in-memory data could be backed by a local filesystem copy of the CSV file to avoid repeated querying of the source, and could be refreshed by some scheduled download process which uses the If-Modified-Since HTTP header to only download when new data is available. This is probably already available in some caching library.

Minimal API

Your backend could provide a very minimal API:

• One endpoint to request data, using query parameters for state and county (and probably date range if your app needs that), it will deliver the selected subset of data.

• One endpoint to request the list of state/county names available.

Minimal frontend changes

The frontend would require minimal changes to get the list of counties and to access data using query parameters. All data processing could be unmodified.

Answer 3

Since the source of truth is a git repo, you could forgo using a database if you can just clone it to your web server. Then have your backend just read the local copy instead of requesting it from Github every time. This makes keeping the data in sync easier too, since you can just make a cron task that does a git pull every once in a while.

If you don't want/can't install git on your web server, you could use something like git-ftp to keep it in sync.

Note: I haven't used git-ftp, it's just the first thing I found in a quick search. There's probably better ways to do that.

An even hackier solution would be to create your own git repo on github, download the csv file, split it out by county, and upload it to your repo with names like data-by-county/us-counties-Alabama-Autauga.csv. Then the front end code could retrieve just the data for the county the user's interested in. No backend needed. Just make sure to run your process once a day or whatever.