BASIL with postgreSQL under Apache2 server

[StefanPofahl]

I have issues to login in the new version of BASIL (commit 198, 10-Sep-2025, PostgreSQL 15 as default database).
Node.js:
Installed nvm v10.9.3 plus node.js v22.19.0 according to the Node.js download instruction.
I do not know if this is relevant and therefore I share the message I receive during front-end installation:

chokidar@2.1.8: Chokidar 2 does not receive security updates since 2019.

attached you find the current version of my installation scripts.

build_basil_frontend.sh.txt

build_basil_api.sh.txt

And here a part of the Apache2 error.log:

Apache2-Error_BASIL_PostgreSQL15.log.txt

I hope that helps to clarifies :-)

[pellecchialuigi]

Hi,
thanks for creating this issue.
With last PR we changed the default database from sqlite3 to postgreSQL as the latter is better supporting concurrent requests. BASIL is aimed to be used in collaborative environments and we also want to run multiple tests from it at the same time.

With that said, you should have a postgreSQL database up and running to work with the last commit.

By default the new setup provided by run_demo.sh is expecting postgreSQL installed in way that API can see it at localhost:$BASIL_DB_PORT (it is working with containers)

So resuming what you should do:

Install postgresql locally on the API machine, it by default should run on port 5432.
Create a user name basil-admin and chose its password
Create a database named basil with this user

Change the WSGIDaemonProcess definition to add the environment variables:
BASIL_DB_PORT=5432 BASIL_DB_PASSWORD=your-password

If you want to have postgreSQL on separate server you should edit the db_orm.py to change localhost in the following code

DB_URL = f"postgresql+psycopg2://{DB_USER}:{DB_PASSWORD}@localhost:{DB_PORT}"

with your machine ip or propose a patch that change the implementation to read it from environment variable.

[StefanPofahl]

Thanks for the fast reply!
The Apache2-server is based on a postgreSQL.
The physical data base folder for the local databases on the server is:
/var/lib/postgresql/15/main

So what does it mean:

Install postgresql locally on the API machine

Can the BASIL database be located in the same folder?
The following command fails :-)

sudo -u postgres psql
postgres=# CREATE DATABASE basil OWNER basil-admin; 
postgres=# \q

The postgreSQL online-documentation is nothing for beginners ...
I guess I need more support ...

[StefanPofahl]

As the postgreSQL documentation is not convenient for me, I asked help from the AI, I open a new issue for the database installation, see #195

[StefanPofahl]

What does it mean:

Change the WSGIDaemonProcess definition to add the environment variables

Should I modify the content of one of the following files?

/Containerfile-api-fedora:24:       BASIL_DB_PASSWORD=${DB_PASSWORD}
/.github/workflows/build.yaml:58:   echo "BASIL_DB_PASSWORD=dbSecret123" >> "$GITHUB_ENV"
/.github/workflows/build.yaml:82:   -w $BASIL_DB_PASSWORD
/Containerfile-api-debian:28:       BASIL_DB_PASSWORD=${DB_PASSWORD}
/db/db_orm.py:17:                   DB_PASSWORD = os.environ.get("BASIL_DB_PASSWORD", "")

[pellecchialuigi]

If you are not using podman containers you don't need to edit the Containerfiles
the .github/workflows/build.yaml is only used by github actions. So it is the configuration of the repository CI.

You can avoid to create the database, you just need an user in postgresql named basil-admin.
You can set the DB_PASSWORD in db_orm.py as the password you chose for your basil-admin user.
If you can inject the BASIL_DB_PASSWORD into the website configuration you can avoid to edit the db_orm.py

Please let me know if that is solving the issue

[StefanPofahl]

I need more support, where should I add the environment variable BASIL_DB_PASSWORD?
I followed this advice
An added the following in my wsgi.py:

os.environ['BASIL_DB_PASSWORD'] = 'my_password'

but this does not seem to be correct :-(

[pellecchialuigi]

The API is interacting with the database as per the project architecture you can see into the documentation at https://basil-the-fusa-spice.readthedocs.io/en/latest/how_to_run_it.html#architecture

The environment variables usually are added to WSGIDaemonProcess but you can also add them to the wsgi script as you did.

Can you share the logs of postgres and api?

[StefanPofahl]

Hi,
it seems that something is missing in my two bash-scripts.
If I delete the database basil:

dropdb basil

And run my two bash scripts and try to login locally with "admin"&"admin"
I fail and if I list the available databases the database basil is missing.
Is a specific template recommended to create a new DB basil?
Could it be that the manual creation of the database is the reason for my
problems?

Regards,

Stefan

[pellecchialuigi]

Hi @StefanPofahl
when you init the API application, it will check if the database exists and if not it will create it.
You can take a look at db/models/init_db.py at initialization() is creating a db interface at db/db_orm.py at init() where we call ensure_database_exists(). This method will check if the database exists and it will create it if not.

I would suggest you to push a PR with your scripts so I can have a look to the setup and try to better understand the issue.

[StefanPofahl]

Hi @pellecchialuigi,

what folder in your repository do you propose to add my scripts?
Could be that you mentioned already a good place but I cannot remember ...
The scripts can be found in 192.

[pellecchialuigi]

Oh sorry, I completely missed the attachments of the first message.
Please push the scripts into a PR under deploy/apache

[pellecchialuigi]

No need to create a database folder we will figure out the flow in the CI

[pellecchialuigi]

You can add your name in a comment at the top of the files if you prefer

[StefanPofahl]

@pellecchialuig
Hi,
I managed to file a pull request.
The scripts offer two ways to go back in time, if wanted or needed ...
a) Specify number of HEAD in the past
b) Specify date (take the commit that is next in the past relative to that specified date)
The "build_basel_api.sh" is already prepared to differentiate
old version with "sqlite-DB" and new with "postreSQL-DB".