What is limited in SQLite support?

[dmi-dmi-dmi]

Database Support section in the docs states that SQLite support is limite in Piccolo.

Could someone please clarify what exactly is limited in comparison with Postgres?

I want to implement a basic desktop software in Python so using SQLite is somewhat natural in my case so I want to know if I still can use Piccolo.

Thanks

[dantownsend]

Hi @dmi-dmi-dmi

Almost all features are available for SQLite. The main limitation is Piccolo's migrations. SQLite has very limited ALTER TABLE support, which makes it hard for us to support it with migrations in Piccolo.

So when you're creating your database tables, you'll have to write a script to do it manually. Something like this:

from piccolo.table import create_db_tables_sync

create_db_tables_sync(TableA, TableB, TableC)

The docs are available here.

[Conobi]

Hey! I was tinkering with migrations with SQLite, and building a custom migration is quite painful compared to the awesome auto migration tool you built.
Just for fun I tried to disable the check of SQLite engine on --auto, and building, running forward/backward auto migrations on my SQLite setup (in 3.43.1 version) seemed to work well.
Precisely, what are the caveat that could go *really wrong* with doing this on a SQLite engine?
Since SQLite is gaining popularity (and Piccolo too), wouldn't it be possible to support auto migrations on SQLite?

Thanks!

[dantownsend]

It's a good point - I know that SQLite has been getting better over time with its support for ALTER TABLE statements.

I just checked, and Python 3.12 on my laptop now has SQLite 3.42.0.

It looks like the features now supported in SQLIte are:

• DROP COLUMN was added in 3.35.0
• RENAME COLUMN was added in 3.25.0
• ADD COLUMN was added in 3.2.0
• RENAME TABLE was added in 3.1.0

So in recent-ish versions, it's just altering columns which is the problem (e.g. making a column nullable).

The work around in SQLite has always been to do the following:

• Create a new table
• Copy the old data into the new table
• Drop the old table
• Rename the new table

It always felt like a hack, and I was hoping that at some point SQLite would gain full support for ALTER TABLE, but I don't think it will happen soon. So I think we'll just have to add support for the work around, and use that.

I agree that we should just remove this:

piccolo/piccolo/apps/migrations/commands/new.py

Lines 234 to 235 in 5f972d0

	if auto and isinstance(engine, SQLiteEngine):
	sys.exit("Auto migrations aren't currently supported by SQLite.")

At the very worst it should output a warning, or ask the user if they want to continue, because the auto migrations will work with SQLIte in most situations.

[Conobi]

Yes! A warning to tell to the user that it could break and to always test the migration is definitely a good idea. An explanation in the docs on what could break would be nice too, even if it is just with a link to the ALTER page of SQLite docs