If you want to fiddle with SQLite and don't need all the power herein, I recommend DB Browser for SQLite.

https://sqlitebrowser.org/

I would recommend TablePlus, the free version has annoying limitations (how many tabs you can open at the same time) but it’s more than enough if you’re just getting started. One of the best developer tools that I’ve ever bought

https://tableplus.com/

I second table plus. Great tool

Another fantastic UI is IntelliJ (database support is built into the IDE) or Datagrip. It's a paid product, but it's more than worth the money if you use it daily.

Can you sell me on the IntelliJ suite of IDEs database UI? What makes it in your opinion fantastic?

I'm using it at the monent, it sure beats dealing with sqlite at the cli level but apart from that to me it feels as rudimentary as any other DB UI I've used in the past.

One thing I’ve done before is variable replacements in the queries (e.g. `WHERE user_id = ${user_id}`). Not sure if other tools do that easily, but it makes having a library of debugging queries more handy.

They also support a lot of drivers, including specialized ones for things like RDS.

I don’t have a ton of experience with other tools, so not sure if those a standout features versus the competition.

Consistency is what makes it for me.

I work with MySQL, Maria, Postgre, BigQuery, snowflake, SQLite

I can find UI’s for them, but having one tool means it’s the same set of shortcuts and tools. I also use their ides for development so having one set of global shortcuts that work across them is also great.

Usually someone chimes in that vscode, vim or whatever also can do this. My counter argument is that I don’t want one tool to do python and go. I don’t want my settings in one to affect the other. I want separate tools that I can configure but have the same feel and global shortcuts.

Julia Evans has a brief description of SQLite-Utils that sold me on it: https://jvns.ca/blog/2022/05/12/sqlite-utils--a-nice-way-to-...

Among other things that makes this tool awesome is how convenient it makes light weight data wrangling on the cli, e.g. to introspect some json data in your clipboard with sql

`$ pbpaste | sqlite-utils memory --table - "select * from stdin;"`

thank you @simonw for creating this wonderful tool! I reach for sqlite-utils for my adhoc data needs every now and then. For aggregation then I use duckdb to read the resulting sqlite file.

I'm absolutely recommend SQLite-utils for fast SQLite operations, converting to and from SQLite. I have piles of bash script that import different sources of data (json, csv) into SQLite so that I can run analysis on it using SQL. My scripts are expected to work for years to come because SQL in general are stable, unlike the dependencies hell that comes with Python or Node.js

Dude, what? Python has SQLite, JSON, and CSV support in its stdlib. It has been stable for years and years, and there is no indication this will change.

Oops. My bad. Me mostly working with DL/ML parts of Python so it’s absolutely madness to get a random DL project running

That, I fully agree with. It‘s a mess. Virtual envs help a bit, but are not perfect either.

One year into Python and I still don’t know what is the universal way to have a reproducible environment across machine. Poetry, Conda, Pip, ugh. All have problems that I don’t know how to deal with

That's very cool thanks for sharing. Reading the manual I wonder if it wouldn't be more work for me to use a library like this than to use the default sqlite3 Python biding and writing my SQL queries by hand. Still cool nonetheless, as I can totally see how it could be useful to some people.

Another sqlite-based high level library that positively surprised me: https://dataset.readthedocs.io/en/latest/