Follow

Wrong: use csv
Correct: use sqlite

This is the tl;dr of an upcoming blog post. If you have any questions about this choice please ask them so I can address them.

I realize that very often the csv is an export from Excel, done by people who wouldn't be able to create a SQLite database. But when it gets exported from some other software as a "reporting feature" or "export feature" there's no excuse.

Show thread

@stchris Recently I've been exporting or collating data from APIs into sqlite and using metabase to help non technical users query the data from there. It's working very well so far and much better than exporting to CSV to load in Excel.

@Dignz exactly. And Metabase is really good! It was a pleasure setting it up and seeing non-devs pick it up and see its value.

@stchris if going for sqlite then why not go further with mysql which doesn't fall down so easily

@tomosaigon what do you mean with "fall down"?

One typical use case of a csv file is "Tom can you pass me the current state of blah". I'm advocating for a sqlite file instead of a csv file. MySQL is a whole different ball game in terms of sending data around

@tomosaigon @stchris A Sqlite can be accessed from Tcl, Python and other scripting languages with minimal ceremony. I wouldn't mind receiving one in the mail.

What are you doing to make them fall down? The locking may make them slow if you're trying to do concurrent access, that's about it.

@clacke @tomosaigon About being slow: nowadays you might want to enable the WAL which makes it perform adequately. See sqlite.org/wal.html

@stchris @clacke I remember it being really slow opening large files so this new change would have to be orders of magnitude better...

Another issue I had was occasionally the db would somehow get corrupt.

@clacke @stchris yes, mostly locking and related failures (connection) from rapidly hitting a db. Tried working around it, wrapping calls to expect failures, but in the end decided a real sql server would prevent future headaches instead of trying to build around sqlite.

@clacke re: Tcl - a long, long time ago I was a "professional" tcl developer. Never again! Stay away! @stchris

@stchris I'd like to see frictionlessdata.io discussed in your post since I rely on that for a data format standard proposal and any informed opinion is really useful

@steko hm, interesting. I'll check it out, thanks

Sign in to participate in the conversation
Fosstodon

Fosstodon is an English speaking Mastodon instance that is open to anyone who is interested in technology; particularly free & open source software.