4 ms·

As a guy running a few Debian web servers for some small client projects and a few personal projects (some of which are medium sized, e.g. a website that gets 1

by wave84 3y ago

As a guy running a few Debian web servers for some small client projects and a few personal projects (some of which are medium sized, e.g. a website that gets 10 million pageviews / month) I actually tried to move from MariaDB to SQLite. The idea seemed great, at least in theory. Mainly to get away from mysqld, which actually crashed on me and corrupted some data once or twice in a timespan of 15 years.. think it was due to package updates every time. Anyway, query and app logic modifications aside, I quickly ran into two unsolvable issues for me. 1. The lack of a robust web based admin tool comparable to phpmyadmin (no, phpliteadmin doesn't even come close) 2. No alter table. This hit pretty hard. Plus lots of other idiosyncrasies - no enums, no type for dates, etc. In the end I decided it's not worth the extra work, trouble and risks and stayed on Maria.

simonw 3y ago
My main takeaway from this thread is that I need to do a much better job promoting my solution to the SQLite ALTER TABLE problem. https://simonwillison.net/2020/Sep/23/sqlite-advanced-alter-table/ https://simonwillison.net/2020/Sep/23/sqlite-advanced-alter-... My Datasette tool doesn't quite serve the same purpose of phpMyAdmin unless you're OK with read-only access, in which case it works great: https://datasette.io https://datasette.io
Sammi 3y ago
I've been having a good time with this vs code plugin for viewing and editing sqlite db files: https://marketplace.visualstudio.com/items?itemName=yy0931.vscode-sqlite3-editor https://marketplace.visualstudio.com/items?itemName=yy0931.v... I have also found the CHECK constraint on an INTEGER column to serve well as an ENUM: https://stackoverflow.com/questions/5299267/how-to-create-enum-type-in-sqlite https://stackoverflow.com/questions/5299267/how-to-create-en... For dates I happily use unix epoch integers or iso8601 date strings.

by wave84 3y ago

simonw 3y ago

My main takeaway from this thread is that I need to do a much better job promoting my solution to the SQLite ALTER TABLE problem. https://simonwillison.net/2020/Sep/23/sqlite-advanced-alter-table/ https://simonwillison.net/2020/Sep/23/sqlite-advanced-alter-... My Datasette tool doesn't quite serve the same purpose of phpMyAdmin unless you're OK with read-only access, in which case it works great: https://datasette.io https://datasette.io

Sammi 3y ago

I've been having a good time with this vs code plugin for viewing and editing sqlite db files: https://marketplace.visualstudio.com/items?itemName=yy0931.vscode-sqlite3-editor https://marketplace.visualstudio.com/items?itemName=yy0931.v... I have also found the CHECK constraint on an INTEGER column to serve well as an ENUM: https://stackoverflow.com/questions/5299267/how-to-create-enum-type-in-sqlite https://stackoverflow.com/questions/5299267/how-to-create-en... For dates I happily use unix epoch integers or iso8601 date strings.