[ha470]

While I love SQLite as much as the next person (and the performance and reliability is really quite remarkable), I can’t understand all the effusive praise when you can’t do basic things like dropping columns. How do people get around this? Do you just leave columns in forever? Or go through the dance of recreating tables every time you need to drop a column?

[roenxi]

SQLite is for storing data in an environment where SQL is meaningful. Anyone wanting to do database admin tasks (like adjusting table schema) would be well advised to go with a Real Database.

SQLite really shines when you have a more-or-less final idea of what tables you want to build and don't have to worry about users inputting crazy data. Anything up to that and it is a model of simplicity. Anything beyond that and it is undercooked.

[cheez]

I use SQLite as a Real Database and alembic helps me do silly things like alter columns by copying tables for me.

[gwbas1c]

I just sucked the existing table into RAM and recreated the table. I did it on a transaction so there was no risk of data loss.

In my case the data was always 10s of MBs.

Remember, the point of SQLite is a replacement for generating a file format. Although it's a database, it lets us (developers) re-use our knowledge of databases when doing basic file I/O.

[72deluxe]

How does dropping the existing table and recreating it affect FKs pointing to the table that is being dropped??

Do the FK relationships get destroyed??

[virvar]

When do you need to drop a column in a production DB? Maybe my anecdotal bubble is about to burst, but I work in the public sector, and have for a while and on our 200 different production DBs behind around 300 systems we’ve never dropped a column.

[calpaterson]

Depends on the maturity of your schema - if it's all figured out based on your business domain it won't happen much. If you're still finding product-market fit (or equivalent) splitting a table into two happens sometimes.

[eli]

Don’t many MySQL backends also recreate the whole table when you drop a column? They just hide it from you better.

[faceplanted]

Pretty sure they must, row based storage on disk would practically require it just to not completely waste all of the space you've just gained from deleting the column by leaving a gap on every single row.

[gwbas1c]

And some of them have downtime during the schema update, too.

[mmsimanga]

I agree with you. SQLite drove me nuts when it came to changing your database. This is one of the reasons I just use DB Browser for SQLite (DB4S). It takes care of all the complexity.

[Carpetsmoker]

The general strategy is to create a new table, insert data from the old table, drop the old table, rename the new table, and re-create the indexes:

  create table foo2 (
   col1 int,
   col2 text
  );
  insert into foo2 sleect col1, col2 from foo;
  drop table foo;
  alter table foo2 rename to foo;
  create index on foo(col1);

See: https://www.sqlite.org/lang_altertable.html#making_other_kin...

As for the reason, see the next section on that link. It's not perfect, but it is what it is. SQLite wasn't designed to solve 100% of the use cases, and that's not a bad thing.

[dirtydroog]

We use sqlite as a smaller cache of a much larger postgres db. The cache is generated by a job and yes is regenerated every time before being pushed to machines that need it.

[barrkel]

Think of SQLite as a file format which happens to have a query interface, and not a database.

MySQL did DML for years with full table rewrites behind the scenes. It's not particularly hard to emulate, though not entirely pleasant.

(Although I really raise an eyebrow when people talk about performance. Every time I write anything server-like in sqlite I end up regretting it, and get enormous speed boost, not to mention actual concurrency, switching to PostgreSQL.)

[isoprophlex]

For data analysis workloads i just load in my raw source data and then develop a series of scripts that create new tables or views on top of those raw inputs.

For my use cases I've thusly never had to drop/alter a column... but I understand it could be very annoying.

[iagovar]

Yeah that's a problem, and I admit that I use some workarounds. I'd like them to implement easy drop, rename etc.

[nojito]

It's pretty fine to just create a new table and insert the data you want into it.