Stripe SQLite data exports beta | Hacker News Reader

Stripe SQLite data exports beta

groups.google.com

68 pointsrscnt_11 comments

This is a great idea, I love it. Perhaps it would be a good idea to offer exports in the form of .sql files that can be loaded into any database, a la 'curl api.stripe.com/export... | psql'. I'm sure the export would be bigger than a binary sqlite file but it would remove the dependency on sqlite.

That's somewhat limiting though, you need to export in the subset of SQL (DDL and queries) compatible to all databases and it is very small indeed (I hope you don't have any textual content of more than 8000 bytes encoded).

But if what you want is to load the data into sqlite, chances are you can just export the sqlite content and load it into Postgres directly, since Richard Hipp (and the SQLite team) more or less consider Postgres to be the reference implementation of SQL: https://twitter.com/dchest/status/469597738431434752

Some of the SQLite utilities let you dump the tables out of the SQLite file as CSV or .sql if you want.

It would probably be easier if dB vendors just made a SQLite importer/exporter that turned any given SQLite file into a full database in their RBDM and visa-versa (or for some subset). It's not really that hard of a problem to do it and seems like a better way than shuffling around csv files or .sql files. At least then if you want to probe the files a little to see what's in them without loading them up you can just use one of the dozen or so SQLite utilities that are out there.

The hardest part might be type inference on schemas that don't define the attribute types (SQLite ignores those and so they're optional) and converting over the full-text indexing and other extensions. But it's not exactly rocket science.

I've tried this out and it is very neat. I can imagine a lot of little reporting tools being built with this.

I threw together a little reporting package for these data files, if anyone is interested.

https://github.com/peterkeen/stripe_reporter

The existing reports are pretty basic, but all of the data is in the schema to write really interesting reports.

Cool work with Mastering Modern Payments too. Just found out about your work and will check the repo for some customized reports.

Bravo! This is the kind of uniquely developer-oriented innovation I've come to expect from Stripe.

Following an export, it'd be nice to have access to a trickle-feed of incremental updates in the form of SQL statements which could be applied to the downloaded SQLite DB -- then we could effectively have an offline source of truth, and we'd only really need to use the Stripe API for update purposes. The existing web hook mechanism would work well to deliver these incremental update statements.

Josh Pigford and Baremetrics are going to go bonkers on this.

From where I can get sample db with dummy data, or details about tables and columns.

I need this to build custom reporting tool.

I was also hoping for some sample data to play around with. It would be awesome if Stripe could provide this.

is there a way to convert this sqlite database file to an API?

It would be really cool if you could just have a standalone, cross platform HTTP server that you could use locally deploy which would allow you to have an API directly to the data stored in sqlite3 and be able to run queries on it.

Reply on news.ycombinator.com