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."

Apache Superset Unable to add a SQLite DB

  • 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!

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

Comment From: 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

Comment From: 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.

Comment From: mjsabby

Is there a reason to drop sqlite?

Comment From: hongvvu

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

Comment From: 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.

Comment From: doricardo-zz

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

Hi, Did you find how to do it?

Comment From: 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

Comment From: 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?

Comment From: 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!

Comment From: 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?

Comment From: kopytjuk

An ugly (but working) hack for those who want to alter the Docker image:

FROM apache/superset

# Switching to root to install the required packages
USER root

# allow sqlite
RUN echo "PREVENT_UNSAFE_DB_CONNECTIONS = False" >> /app/superset/config.py

# Switching back to using the `superset` user
USER superset

This only works, because config.py defines the variables on the global level, so the last variable definition wins :)

Comment From: jaanli

This is so helpful! Thank you @kopytjuk

Can we please reopen this issue? Desperately need sqlite because we are a small nonprofit and cannot use anything more.

Comment From: FoXTecktoniK

  1. open superset/docker/pythonpath_dev/superset_config.py
  2. add PREVENT_UNSAFE_DB_CONNECTIONS = False somewhere

  3. start a docker container The setting is applied, but the database is not yet there

  4. open docker-compose.yml (or docker-compose-non-dev.yml)

  5. find lines ```x-superset-volumes: &superset-volumes # /app/pythonpath_docker will be appended to the PYTHONPATH in the final container
  6. ./docker:/app/docker
  7. superset_home:/app/superset_home ```

add line at the bottom - ./coins.db:/app/coins.db, where ./local.db - file in superset folder use connection string sqlite:///local.db in superset

Comment From: bobhenkel

The excellent news is sqlite connection is still working as of today's date once I added PREVENT_UNSAFE_DB_CONNECTIONS = False. Please keep sqlite as an option and document it as non-production use only. Many use cases where you have a sqlite.db file full of data and want to impress folks by connecting that data to superset.

Comment From: neiz

@mistercrunch my intent is not to reopen this issue, but more to use this issue for context -

It's a bit frustrating to use the official dockerhub image only to have the examples not work/load and having to stumble across the fact that SQLite was removed. Might make sense to remove the examples, or change them such that they work out of the box

Comment From: cserb

Did something change again? I can't make it work with PREVENT_UNSAFE_DB_CONNECTIONS = False

I agree with @neiz here. It's just frustrating that it is advertised but than once you try to connect it's not allowed. I honestly also don't get this silly security concern if I want to run it locally...

Comment From: ninja-tech

https://abouthongwuxyz.typlog.io/2021/superset-docker-local-db-sqlite-configuration-dcf6876e

this link is not working

Comment From: harabat

Still trying to make SQLite work on the official Docker image, if anyone has a solution.

I managed to remove the warning by mounting a modified config.py with the PREVENT_UNSAFE_DB_CONNECTIONS = False setting:

curl --remote-name https://raw.githubusercontent.com/apache/superset/master/superset/config.py  # download config.py file
vi config.py  # add the line ~PREVENT_UNSAFE_DB_CONNECTIONS = False~ inside the file, before the line containing "WARNING:  STOP EDITING  HERE"
docker run -d -p 8080:8088
            -e "SUPERSET_SECRET_KEY=your_secret_key_here"
            -v /path/to/modified/config.py:/app/superset/config.py  # mount local config.py
            --name superset apache/superset
docker exec -it superset superset fab create-admin \
              --username admin \
              --firstname Superset \
              --lastname Admin \
              --email admin@superset.com \
              --password admin
docker exec -it superset superset db upgrade
docker exec -it superset superset init

The above removes the SQLite security warning, but trying to connect to the db triggers a sqlite3.OperationalError when loading a SQLite db.

superset_sqlite

I tried to connect with sqlite:///path/to/my/local.db and mounting the db into the /app/ directory and connecting with sqlite:///local.db, but keep getting that error.

Comment From: weeebdev

Still trying to make SQLite work on the official Docker image, if anyone has a solution.

I managed to remove the warning by mounting a modified config.py with the PREVENT_UNSAFE_DB_CONNECTIONS = False setting:

shell curl --remote-name https://raw.githubusercontent.com/apache/superset/master/superset/config.py # download config.py file vi config.py # add the line ~PREVENT_UNSAFE_DB_CONNECTIONS = False~ inside the file, before the line containing "WARNING: STOP EDITING HERE" docker run -d -p 8080:8088 -e "SUPERSET_SECRET_KEY=your_secret_key_here" -v /path/to/modified/config.py:/app/superset/config.py # mount local config.py --name superset apache/superset docker exec -it superset superset fab create-admin \ --username admin \ --firstname Superset \ --lastname Admin \ --email admin@superset.com \ --password admin docker exec -it superset superset db upgrade docker exec -it superset superset init

The above removes the SQLite security warning, but trying to connect to the db triggers a sqlite3.OperationalError when loading a SQLite db.

I tried to connect with sqlite:///path/to/my/local.db and mounting the db into the /app/ directory and connecting with sqlite:///local.db, but keep getting that error.

Any news?

Comment From: gramakri

@harabat did you figure out that error? I get the same error.

Apr 06 11:49:38 2024-04-06 09:49:38,164:WARNING:superset.views.base:SupersetErrorsException <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - Traceback (most recent call last): <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - File "/app/code/superset/superset/commands/database/test_connection.py", line 161, in run <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - raise DBAPIError(ex_str or None, None, None) <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - sqlalchemy.exc.DBAPIError: (builtins.NoneType) None <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - [SQL: (sqlite3.OperationalError) unable to open database file <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - (Background on this error at: https://sqlalche.me/e/14/e3q8)] <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - (Background on this error at: https://sqlalche.me/e/14/dbapi) <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 -
Apr 06 11:49:38 The above exception was the direct cause of the following exception: <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - Traceback (most recent call last): <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - File "/app/code/superset/flask/app.py", line 1823, in full_dispatch_request <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - rv = self.dispatch_request() <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - File "/app/code/superset/flask/app.py", line 1799, in dispatch_request <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args) <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - File "/app/code/superset/flask_appbuilder/security/decorators.py", line 95, in wraps <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - return f(self, *args, **kwargs) <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - File "/app/code/superset/superset/views/base_api.py", line 127, in wraps <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - raise ex <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - File "/app/code/superset/superset/views/base_api.py", line 121, in wraps <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - duration, response = time_function(f, self, *args, **kwargs) <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - File "/app/code/superset/superset/utils/core.py", line 1463, in time_function <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - response = func(*args, **kwargs) <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - File "/app/code/superset/superset/utils/log.py", line 255, in wrapper <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - value = f(*args, **kwargs) <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - File "/app/code/superset/superset/views/base_api.py", line 93, in wraps <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - return f(self, *args, **kwargs) <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - File "/app/code/superset/superset/databases/api.py", line 919, in test_connection <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - TestConnectionDatabaseCommand(item).run() <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - File "/app/code/superset/superset/commands/database/test_connection.py", line 190, in run <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - raise SupersetErrorsException(errors) from ex <30>1 2024-04-06T09:49:38Z my 01b635dd-19eb-4294-ace3-54c0321ce5a2 1028 01b635dd-19eb-4294-ace3-54c0321ce5a2 - superset.exceptions.SupersetErrorsException: [SupersetError(message='(builtins.NoneType) None\n[SQL: (sqlite3.OperationalError) unable to open database file\n(Background on this error at: https://sqlalche.me/e/14/e3q8)]\n(Background on this error at: https://sqlalche.me/e/14/dbapi)', error_type=<SupersetErrorType.GENERIC_DB_ENGINE_ERROR: 'GENERIC_DB_ENGINE_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'engine_name': 'SQLite', 'issue_codes': [{'code': 1002, 'message': 'Issue 1002 - The database returned an unexpected error.'}]})]

Comment From: harabat

@gramakri unfortunately not, had to go with other tools instead.

Comment From: pjaol

Managed to get it to work, am using docker compose for ease of use

docker-compose.yaml

version: '3'
services:
  superset:
    image: apache/superset
    ports:
      - 8088:8088
    volumes:
      - /home/pjaol/flows/data:/var/data # where my data resides
      - superset_db:/app/superset_home # persist config across restarts
    environment:
      - SUPERSET_CONFIG_PATH=/var/data/superset_config.py
      - SUPERSET_SECRET_KEY=something_random

volumes:
  superset_db:

And

superset_config.py

# Superset configuration file
PREVENT_UNSAFE_DB_CONNECTIONS=False

When using an absolute path ensure you are using 4 forward slashes sqlite:////var/data/data.db in my case It's using apache/superset:latest image

Comment From: sammigachuhi

Hello, I realized there is a way you can upload the sqlite to superset but the issue is that in the SQL Lab you cannot see the tables. Use the following format: sqlite:///C:\\Users\\user\\Downloads\\your_db.db?check_same_thread=false. Yes, just use the two backslashes in between the folder paths not forward (/) because the latter will fail. The Sqlite database will be loaded and will be visible. However, the tables in the sqlite db can't be read in the SQL Lab despite being existent.

Would really appreciate if someone found a way to make the tables in the database readable or visible in the datasets menu, otherwise we are still so close but still so far.

Prior to this ensure you have turned PREVENT_UNSAFE_DB_CONNECTIONS=false in the config.py before heading to this final step.