31–40 of 87 posts
Earlier quoted context omitted.
> Better option is to just add a new column with the correct definition After that you won't be able to change column to NOT NULL. You would need migration to create new table with not null column, copy everything, drop old table and rename the new one. Edit: unless the table is empty.
Wrong, you can change NOT NULL since 3.53: https://sqlite.org/releaselog/3_53_3.html
- https://sqlite.org/whentouse.html
Earlier quoted context omitted.
> Better option is to just add a new column with the correct definition After that you won't be able to change column to NOT NULL. You would need migration to create new table with not null column, copy everything, drop old table and rename the new one. Edit: unless the table is empty.
How do you migrate in place data that doesn’t convert between types while maintaining a strict condition like NOT NULL? This is again a scenario I’ve never run into 20ish years of SQL.
I'm fairly confident this is AI generated, but it makes me think regardless: Whenever I see these kind of articles, I'm left wondering if they've actually used SQLite in production because I always see points about how to optimize performance, like using the WAL, but never about annoyances/issues you'd run into before even needing to worry about that. I guess it's the zeitgeist to use it in a production setting, and…
https://www.sqlite.org/lang_altertable.html
On Go you can embed your migrations in your binary:
https://oscarforner.com/blog/2023-10-10-go-embed-for-migrati...
I love SQLite, and I really want to run it in production, but my clients expect minimal data loss and downtime when one of my servers goes down. The answer to that being running it on top of LiteFS or LiteStream seems like starts to make the setup a lot less simple and lot less battle tested, which kind of starts to negate the advantages over just running Postgres.
I love SQLite, and I really want to run it in production, but my clients expect minimal data loss and downtime when one of my servers goes down. The answer to that being running it on top of LiteFS or LiteStream seems like starts to make the setup a lot less simple and lot less battle tested, which kind of starts to negate the advantages over just running Postgres.
all clients think they're google
If for no other reason, dealing with these at any scale often distracts from running your actual business.
As someone really tempted to use SQLite in production, the one thing I keep bumping against is how to have a nice GUI to interact with the running database. With our current prod databases, I can connect dbeaver and the like to them and nicely browse the data, query, or even do the occasional fix. Seems like this would be much more of a head scratcher if the database is just a file on the same VPS the app runs on.
Earlier quoted context omitted.
How do you migrate in place data that doesn’t convert between types while maintaining a strict condition like NOT NULL? This is again a scenario I’ve never run into 20ish years of SQL.
You create a nullable column and then change it to not null. Which wasn't possible in SQLite until recently.
Some data doesn’t convert is what I’m pointing out regardless of Postgres or SQLite.
I'm fairly confident this is AI generated, but it makes me think regardless: Whenever I see these kind of articles, I'm left wondering if they've actually used SQLite in production because I always see points about how to optimize performance, like using the WAL, but never about annoyances/issues you'd run into before even needing to worry about that. I guess it's the zeitgeist to use it in a production setting, and…
For alter table it offers a "transform" command which implements the pattern of creating a new table with your desired scheme, copying data to it from the old table, then renaming the tables (all in a transaction): https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...
sqlite-utils transform fixtures.db roadside_attractions \
--rename pk id \
--default name Untitled \
--column-order id \
--column-order longitude \
--column-order latitude \
--drop address
sqlite-utils migrate creatures.db path/to/migrations.py
from sqlite_utils import Migrations
migrations = Migrations("creatures")
@migrations()
def create_table(db):
db["creatures"].create(
{"id": int, "name": str, "species": str},
pk="id",
)
@migrations()
def add_weight(db):
db["creatures"].add_column("weight", float)