5 ms·
Sure, it is simple and that is why people use it. But I have also run into hard problems using it. Deleting and renaming columns is not supported natively. On
by cjfd 7y ago
Sure, it is simple and that is why people use it.
But I have also run into hard problems using it. Deleting and renaming columns is not supported natively. On the internet one can find procedures to do that but they generally do not take foreign key references into account. Getting this right is a hard problem. In the end we had a function that could be used to rename or delete a column even without switching off foreign key checking but it was not simple and it was not the first iteration of said function and earlier iterations caused lots of subtle problems, like a column having a default value or not depending on whether the database was a new database or was created using schema updates.
Use at your own peril.
- acdanger 7y agoI've never had an issue altering or deleting columns in SQLite. Grant it, I've used both Sequelize and SQLAlchemy as ORMs. But if you're using SQLite for simplicity there's a good chance you're not interested writing raw SQL statements either.
- The_Colonel 7y agoYeah, I'm using SQLite for simplicity and exactly because of that I want raw SQL instead of some leaky complex ORM abstractions.
- yread 7y agoI use EF core and instrument all my queries - if I see a slow one I hand optimize it. Best of both worlds I think - and I say that as someone who loves to fiddle with SQL.
- The_Colonel 7y agoAlso worst of both worlds - you need to know very well both the underlying database (SQL) and also the complex leaky abstraction (EF Core) and how are those abstraction translated into the SQL. I'm no ORM hater (use Hibernate daily) but claiming that using ORM will simplify working with the database is at least misleading.
- imtringued 7y agoFor some reason people think it's okay to be ignorant while using an ORM. What you are interacting with is still a database! Treat it like one and you will save a lot of time in the easy situations.
- oefrha 7y agoPretty sure SQLA the ORM has nothing to do with schema migration, which is usually handled with alembic, the sister project. Not sure about Sequelize. Now, there’s nothing magical about alembic’s handling of renaming or deleting columns for SQLite; it has to recreate the table just like what you would do if you’re hand rolling the SQL, so it has the same problems. Details: https://alembic.sqlalchemy.org/en/latest/batch.html https://alembic.sqlalchemy.org/en/latest/batch.html
- danso 7y agoNot disagreeing with your overall point about SQLite's feature set, but IIRC, `ALTER TABLE RENAME` was added in 2018, for version 3.25.0: https://www.sqlite.org/releaselog/3_25_0.html https://www.sqlite.org/releaselog/3_25_0.html (I chime in only to remark that I myself am constantly surprised that SQLite continues to add these kinds of features – e.g. WINDOW functions were also in 3.25 – long after I had accepted their absence as a fact of life)
- cjfd 7y agoAh, that is interesting. My experiences were before 2018 and I had not kept an eye on it, so I did not know this.
- cjfd 7y agoOw, but looking at the docs they still do not seem to support delete yet.
- dana321 7y agoYeah, its a pity it doesn't have the ability to do anything more than alter table statements that add to a schema, having to rename the table, create a new schema and copy its contents from the old table can be quite a tricky and troublesome thing to do if you've got a live system running with users on it.