feat: support sqlite

[chamanbravo]

Sqlite support will make it faster and easier to setup. But don't replace postgres with sqlite. Some might want to add a external database. You can keep both like this.

https://github.com/create-go-app/fiber-go-template/blob/master/platform/database/open_db_connection.go

[jeyren95]

hi can i take this?

[chamanbravo]

hi can i take this?

Yeah sure. Please check out the link before adding. Try to keep both sqlite and postgres. Add a new env var db_type, keep sqlite default... And make sure migrations work properly for both. Thank you.

[jeyren95]

the current migration script requires creating the pgcrypto extension, but i dont think sqlite has that

are you looking to maintain only 1 migration script for both DB types?

[chamanbravo]

yeah, I think having only one migration script would be great.

[jeyren95]

hmmm ok i need to dig deeper to see if this is possible

[chamanbravo]

we can remove pgcrypto and use crypto library. So the script can work for both.

[jeyren95]

ok yeah that works, i'll implement the crypto library in this PR too

[jeyren95]

There is another issue with trying to support both postgres and sqlite with the same script:

• Postgres supports the "SERIAL" data type, but sqlite does not, making the id column difficult to synchronize between postgres and sqlite

Ways that i tried to resolve this but does not work:

1. Using integer primary key autoincrement - does not work because postgres does not support autoincrement
2. Using integer primary key generated always as identity - does not work because not supported by sqlite
3. UUID extension - does not work because the not supported by sqlite

[jeyren95]

not sure if you might have seen a solution for this somewhere, but we might have to end up having separate scripts if you do want to support both database types

[chamanbravo]

Hey thank you for trying, if having a single script for migration doesn't seem possible, we can create different scripts then...

[jeyren95]

opened a PR to resolve this issue #43