sqlite-utils

> After identifying about 7 commits (with pretty basic/useless messages, and no PR link!), I then had to find the corresponding PRs based on timestamps, and search the PR history for PRs merged around those timestamps. Not sure if this would save any time, but it is possible to search PRs by commit. For example, say git blame led me to this commit: https://github.com/simonw/sqlite-utils/commit/129141572f249e... I could have found PR #373 via this search: https://github.com/simonw/sqlite-utils/pulls?q=bb16f52681b6d... > I thus treat PRs as ephemeral I think I see what you're saying but as others have pointed out, sometimes you want to add screenshots etc to the context, and you can't capture this kind of info in commit messages. So then you have two choices: issues or PRs. > Then any review comments are preferably not addressed directly i

Is this example too large or about the right size for you? https://github.com/simonw/sqlite-utils/commit/ab8d4aad0c42f9...

The vast majority of breaking changes should have been down to the Python 2 to Python 3 upgrade - since then I've seen very few breaking changes affect my projects. The trick I use to protect against them is to run my test suite against multiple Python versions using GitHub Actions, e.g. here: https://github.com/simonw/sqlite-utils/blob/main/.github/wor...

This is fantastic, thanks. My solution is a lot less smart - I loop through every record and keep track of which potential types I've seen for each column: https://sqlite-utils.datasette.io/en/latest/python-api.html#... Implementation here: https://github.com/simonw/sqlite-utils/blob/3fbe8a784cc2f3fa...

You don't need to create a new table to add foreign keys - you can do it by setting PRAGMA writable_schema = 1, then updating the table definition directly in the sqlite_master table to include the foreign key constraints. This sounds terrifyingly dangerous, so you should absolutely take a backup first - but I've found it to work just fine in practice. Here's where I implement that in my sqlite-utils Python library and CLI tool: https://github.com/simonw/sqlite-utils/blob/2d84577202e22767...

> * Which dialect for querying worked the best for you? If this is SQL dialects, I am partial to both the Postgres JSON/JSONB operators, SQLite has a solid JSON implementation too. > * Which tools do you enjoy the most? This could be for any reason: convenience of install/availability, expressivity, raw speed, other niche features, etc. "Datasette" (from Django co-creator) can take tabular data (SQLite, CSV, JSON, etc) and generate a REST/GraphQL API with visualization tools from it: https://github.com/simonw/datasette From the same author, "sqlite-utils" generate SQLite table definitions and rows from similar: https://github.com/simonw/sqlite-utils I find this useful outside of SQLite because the syntax is similar across SQL databases. Great way to bootstrap a DB definition from existing datasets. "Pipe JSON (or CSV or TSV) directly

I would add VisiData to that list. It is more suited for tabular data, but it can be used to explore large nested data. I find it is a good starting place to explore json data I'm not familiar with. I will also sometimes flatten the data with some general jq scripts or gron. If I'm trying to understand some new json data, I start with visidata, and then I poke around to find the data I want. If it is data I want to extract again, I use jq to get the interesting bits. Sometimes I use that in combination with sqlite-utils to store that data so I can query it. (I haven't tried some of the other tools that will create a sqlite database for you.) - https://github.com/saulpw/visidata - https://github.com/simonw/sqlite-utils

Sqlite-utils has this: https://github.com/simonw/sqlite-utils