Unable to add a SQLite DB

[hc128168]

I tried to add a db located at "/home/hc/superset/db/test.db" to superset via "Sources->Databases->Add a new record". I set "sqlite:////home/hc/superset/db/test.db" as SQLAlchemy URI, and hit "Test Connection". And it says, "ERROR: SQLite database cannot be used as a data source for security reasons."

• superset version: 0.36.0
• python version: 2.7.5 (I have python 3.6 installed as well but not sure which one superset uses)
• node.js version: not installed as it is not listed as required here nor as optioal
• npm version: not installed.

Make sure these boxes are checked before submitting your issue - thank you!

• I have checked the superset logs for python stacktraces and included it here as text if there are any.
• I have reproduced the issue with at least the latest released version of superset.
• I have checked the issue tracker for the same issue and I haven't found one similar.

[dpgaspar]

Superset is going to drop support for SQLLite, for now you can override this by setting PREVENT_UNSAFE_DB_CONNECTIONS = False on the config

[hc128168]

@dpgaspar Thanks. It works!

While I looked up the setting, I found the pull request. It seems like it doesn't convert the examples to use another database tho.

[mjsabby]

Is there a reason to drop sqlite?

[hongvvu]

I installed via docker, any instruction on overwrite config.py with docker image?

[mistercrunch]

Happy to clarify some things here, first the project is dropping support for SQLLite as a metadata database, as it's simply not reliable to support a multi-web servers, multi-workers type of setup which is a fairly basic assumption for an application like Superset in production. As soon as you fire off multiple web workers or thread, you're likely to have issues with SQLLite locks. Given that and the fact that we had a fair amount of SQLLite-specific bugs and regressions (namely around database-migrations and DDL in general), we decided that we would not actively support it.

Now OP was asking about SQLLite as an analytics database, which may/could work in some capacity, assuming the file is mounted / visible from all the processes in the application. This can be done by flipping the feature flag that @dpgaspar pointed to, but may introduce security risk, where a user may be able to access some other SQLLite database living on the local filesystem (other applications or systems on the local machine could use a SQLLite database in a deterministic location, and a hacker could obtain access to it and potentially modify it if the OS rules allow the superset OS user to do this). There may be other Security-related issues as well that I'm not aware of here as well.

[doricardo-zz]

I installed via docker, any instruction on overwrite config.py with docker image?

Hi, Did you find how to do it?

[hongvvu]

I installed via docker, any instruction on overwrite config.py with docker image?

Hi, Did you find how to do it?

Wrote a blog post about it, and managed to overwrite config.py in my docker image.
https://abouthongwuxyz.typlog.io/2021/superset-docker-local-db-sqlite-configuration-dcf6876e

[harshhacks]

Hi, facing the same error in Windows but editing the config.py and including CSV doesn't remove the error.

Using Python 3.8 and Windows 10 for context and a SQLite database.
Changed the config file to have:
SQLALCHEMY_DATABASE_URI = 'sqlite:///C:\Users\harsh\.superset\superset.db'
PREVENT_UNSAFE_DB_CONNECTIONS = False

Does anyone have an idea of what could be causing this?

[jaanli]

It would be helpful to support sqlite databases. I work at a hospital and it would be very helpful to analyze health records data with sqlite rather than hosting my own mysql database.

Thank you!

[av-2024]

Well I was pretty excited about superset and spent a lot of time setting it up for a small scale self-hosted solution. Now I'm rather disappointed. Do you know how many of your users use SQLite?