Make Sqlite the default database for development

[sdc50]

I propose that we make sqlite3 the default database engine for Tethys. This will make a simpler development environment and eliminate any confusion with whether or not the database server is running, or if there multiple databases that use the same port.

I think this will also give Tethys Portal managers more flexibility in deciding what database to set up in production.

To prevent a breaking change I would add code that detects if a postgres database is configured and preserve the postgres ENGINE configuration with a warning that it will change in the future.

One concern is that there could be possible differences with how SQLAlchemy interacts with a sqlite database vs PostgreSQL or other databases. I'll need to research this to ensure that there will not be potential pitfalls for app developers.

[sdc50]

@swainn Here is the message that would be displayed if a postgres database configuration is detected:

[sdc50]

If no database configuration exists, but the default psql database dir exists in TETHYS_HOME then the message would be slightly different:

[swainn]

I have a few additional suggestions:

1. Provide a data migration command for tethys db that would allow users to migrate data from one type of DB to another. Potential use cases include:
   • Someone wanting to move their development environment from an existing PostgreSQL database to the new default SQLite database.
   • Someone who has outgrown the SQLite database for development to move to a new engine.
2. Update the documentation for database settings:
   • Tethys Portal Configuration documentation to include the DATABASES.default.ENGINE setting.
   • Manual Production Deployment documentation to include warnings about using SQLite in production.
   • Update the Manual Production Deployment documentation to include instructions for configuring various DB backends in production (PostgreSQL, MySQL, Microsoft SQL, etc.). Include instructions for installing additional dependencies such as database drivers.

[swainn]

What do you think about changing the CI to run the tests multiple times with different db backends to help us get some measure of assurance that it works for each of them? I'm thinking sqlite, postgresql, and mysql at least. If it's possible to get Microsoft SQL instances in GitHub actions without needing to license it, then we could do that one too.

[sdc50]

What do you think about changing the CI to run the tests multiple times with different db backends to help us get some measure of assurance that it works for each of them? I'm thinking sqlite, postgresql, and mysql at least. If it's possible to get Microsoft SQL instances in GitHub actions without needing to license it, then we could do that one too.

I've wondered about this. I'm running tests locally on both postgresql and sqlite and they work for both. In theory Django is abstracting the database engine, so it shouldn't matter what is used, and testing multiple DBs seems like we're testing Django functionality rather than our own code. However, theory and practice are only the same in theory, so in practice it might be worth it.

[sdc50]

I have yet to test actually installing and running apps with a SQLite backend. The one thing that may be a hiccup is that I think the persistent store settings require a host and port, so SQLite might not (currently) work with apps that require persistent stores.

[sdc50]

I have yet to test actually installing and running apps with a SQLite backend. The one thing that may be a hiccup is that I think the persistent store settings require a host and port, so SQLite might not (currently) work with apps that require persistent stores.

The host and port are indeed currently required when creating a PersistentStoreService. Additionally, the PersistentStoreDatabaseSetting class has several hard-coded SQL statements that are PostgreSQL specific.

I think SQLite could still be the default database for Tethys in development, but a Postgres database would need to be created for apps that require a persistent store until further work is done to expand the PersistentStoreService and PersistentStoreDatabaseSetting capabilities.

see #926