"flask create_db" fails when db engine is set to sqlite

[oussjarrousse]

the "flask create-db" command is producing the following error message when SQLALCHEMY_DATABASE_URI = 'sqlite:////home/bayanat/bayanat/enferno.db'

(env) bayanat@bayanat:~/bayanat$ export FLASK_APP=run.py
(env) bayanat@bayanat:~/bayanat$ flask create-db
Traceback (most recent call last):
  File "/home/bayanat/bayanat/env/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/home/bayanat/bayanat/env/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: near "EXTENSION": syntax error

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/bayanat/bayanat/env/bin/flask", line 8, in <module>
    sys.exit(main())
  File "/home/bayanat/bayanat/env/lib/python3.8/site-packages/flask/cli.py", line 967, in main
    cli.main(args=sys.argv[1:], prog_name="python -m flask" if as_module else None)
  File "/home/bayanat/bayanat/env/lib/python3.8/site-packages/flask/cli.py", line 586, in main
    return super(FlaskGroup, self).main(*args, **kwargs)
  File "/home/bayanat/bayanat/env/lib/python3.8/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "/home/bayanat/bayanat/env/lib/python3.8/site-packages/click/core.py", line 1259, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/bayanat/bayanat/env/lib/python3.8/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/bayanat/bayanat/env/lib/python3.8/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "/home/bayanat/bayanat/env/lib/python3.8/site-packages/click/decorators.py", line 21, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/home/bayanat/bayanat/env/lib/python3.8/site-packages/flask/cli.py", line 426, in decorator
    return __ctx.invoke(f, *args, **kwargs)
  File "/home/bayanat/bayanat/env/lib/python3.8/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "/home/bayanat/bayanat/enferno/commands.py", line 18, in create_db
    db.engine.execute('CREATE EXTENSION if not exists pg_trgm ;')
  File "/home/bayanat/bayanat/env/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2235, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/home/bayanat/bayanat/env/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1003, in execute
    return self._execute_text(object_, multiparams, params)
  File "/home/bayanat/bayanat/env/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1172, in _execute_text
    ret = self._execute_context(
  File "/home/bayanat/bayanat/env/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "/home/bayanat/bayanat/env/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/home/bayanat/bayanat/env/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/home/bayanat/bayanat/env/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/home/bayanat/bayanat/env/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "EXTENSION": syntax error
[SQL: CREATE EXTENSION if not exists pg_trgm ;]
(Background on this error at: http://sqlalche.me/e/13/e3q8)

[oussjarrousse]

it seems that the line db.engine.execute('CREATE EXTENSION if not exists pg_trgm ;') is the cause of the issue as it does not check if the current engine is set to postgres

@click.command()
@with_appcontext
def create_db():
    """creates db tables - import your models within commands.py to create the models.
    """
    db.engine.execute('CREATE EXTENSION if not exists pg_trgm ;')
    print('Trigram extension installed successfully')
    db.create_all()
    print('Database structure created successfully')

    # possible optimization: SET enable_seqscan = off;

[oussjarrousse]

if Bayanat does not support sqlite3 then I suggest removing the following line from enferno/settings.py

# SQLALCHEMY_DATABASE_URI = 'sqlite:////home/bayanat/bayanat/enferno.db' 

as it suggests otherwise.

[sjacgit]

Bayanat is designed to work with postgres and doesn't support sqlite. We've removed this line from the settings file.