SQLite Support #62

New issue

Closed

opened 2025-07-12 17:44:19 -05:00 by notquitenothing · 3 comments

notquitenothing commented

2025-07-12 17:44:19 -05:00

(Migrated from github.com)

I think SQLite support should be added, possibly by adding environment variables for DB_ADAPTER and DB_PATH. There should also be a way to migrate the database between the two options, so you don’t lose your existing database.

👍 4

notquitenothing commented

2025-09-01 01:16:25 -05:00

(Migrated from github.com)

A summary of the issue: When modifying a table check constraint in a knex migration file, to support sqlite you must drop the entire table and recreate it instead of just dropping the constraint. This among other issues, such as lack of default value support, make the juice not worth the squeeze in my opinion.

It seems like SQLite has some pretty serious constraints around constraints that makes support with the current set of DB migrations very difficult. For this reason I am no longer planning on adding SQLite support until a workaround is found. A summary of the issue: When modifying a table check constraint in a knex migration file, to support sqlite you must drop the entire table and recreate it instead of just dropping the constraint. This among other issues, such as lack of default value support, make the juice not worth the squeeze in my opinion.

👎 1 👀 1

starfishpatkhoo commented

2025-09-23 23:57:50 -05:00

(Migrated from github.com)

In smaller environments, postgres is a little bit overkill. Additionally, we can even backup and move the sqlite DB file easily with cp .. Even Grist uses sqlite as a "document file format" .. LOL...

And while it is true, modifying a table check constraint will require a drop/create of the entire table in SQLite, how often does a table check constraint really need to be changed? I think if the table constraints are changing, one might consider that a major change/issue, regardless of whichever DB backend is being used, and needs to be handled with great care anyhow.

I think the biggest issue is maybe the much smaller set of types in SQLite vs postgres/maria/etc ... And in there, default values are kind of "basic" ... but I think that can be mitigated simply by reducing the structure to basic types, INTEGERs, TEXTs, etc, and/or setting clearly specified default values on INSERT..

In any case, I do hope you will please add SQLite support, it reduces the requirements and implementation difficulty for self-hosters in an impactful manner. Much appreciated! ^_^

EDIT: Just to add, I just checked, multi-domain support should work with SQLite also, as long as they share the same DB files across two different Voidauth instances..

In smaller environments, postgres is a little bit overkill. Additionally, we can even backup and move the sqlite DB file easily with `cp` .. Even Grist uses sqlite as a "document file format" .. LOL... And while it is true, modifying a table check constraint will require a drop/create of the entire table in SQLite, how often does a table check constraint really need to be changed? I think if the table constraints are changing, one might consider that a major change/issue, regardless of whichever DB backend is being used, and needs to be handled with great care anyhow. I think the biggest issue is maybe the much smaller set of types in SQLite vs postgres/maria/etc ... And in there, default values are kind of "basic" ... but I think that can be mitigated simply by reducing the structure to basic types, `INTEGER`s, `TEXT`s, etc, and/or setting clearly specified default values on `INSERT`.. In any case, I do hope you will please add SQLite support, it reduces the requirements and implementation difficulty for self-hosters in an impactful manner. Much appreciated! ^_^ EDIT: Just to add, I just checked, multi-domain support should work with SQLite also, as long as they share the same DB files across two different Voidauth instances..

👍 5

notquitenothing commented

2025-10-02 21:10:41 -05:00

(Migrated from github.com)

SQLite support is coming in the next release, if you want you can test it out on the :edge image tag voidauth/voidauth:edge by using the DB_ADAPTER=sqlite environment variable. There is currently no migration option, though a issue will be opened for it.

SQLite support is coming in the next release, if you want you can test it out on the :edge image tag `voidauth/voidauth:edge` by using the `DB_ADAPTER=sqlite` environment variable. There is currently no migration option, though a issue will be opened for it.