sqlpage configuration and docker

[flosigime]

Hello everyone,

I'm really new using docker, and i just succeeded in installing it and launching sqlpage with this command :

sudo docker run -it --name sqlpage -p 8080:8080 --volume "$(pwd):/var/www" --rm lovasoa/sqlpage

But, it's still a bit weird to me. I understood that (i think), even if you launch sqlpage from docker, the place you launch it from, eg /home/me/sqlpage, is important and that your index.sql file for example has to be put in this directory, ie /home/me/sqlpage.

But , i don't understand how to configure and where to place the sqlpage.json. It's not seen by sqlpage from docker...

Thanks for your help

[lovasoa]

Hello and thanks for the question !

I'll update the README to try and make it clearer. The important part was:

Optionally, you can also mount a directory containing sqlpage's configuration file, custom components, and migrations (see configuration.md) to /etc/sqlpage in the container.

Docker is a platform that allows deploying applications in a containerized environment. Docker containers are isolated from the host system, which makes them unable to access files on your machine by default. The container has a completely different file system with their own folder structure. Each folder you want to give access to has to be specified on the command line with the following syntax: --volume "/home/me/mywebsite:/var/www". This will make your website available in /var/www inside the container's file system.

For more security, SQLPage's container by default expects your website's files to be in /var/www and expects its configuration to be available in a separate directory: /etc/sqlpage.

In your case, the --volume flag mounts the current working directory ($(pwd)) to the /var/www directory inside the container. This means that any files in the current working directory will be accessible to the SQLPage application running inside the container. But the container expects its configuration in /etc/sqlpage, not /var/www/sqlpage

Regarding your question about the sqlpage.json configuration file, you need to mount the /etc/sqlpage directory inside the container to a directory on your host machine. This can be done by adding another --volume flag to the docker run command. You can modify the docker run command as follows:

docker run -it --name sqlpage -p 8080:8080 --volume "$(pwd):/var/www" --volume "$(pwd)/sqlpage:/etc/sqlpage:ro" --rm lovasoa/sqlpage

This command will mount the /home/me/mysite/sqlpage directory (if you launch the command from /home/me/mysite) on your host machine to the /etc/sqlpage directory inside the container. You can then place the sqlpage.json configuration file in the /home/me/mysite/sqlpage directory on your host machine, and it will be accessible to the SQLPage application running inside the container.

An even more secure setup would be to create separate folders for the files you serve and for the configuration. You can create /home/me/mysite/configuration with your sqlpage.json (and other configuration files such as custom components), and a separate /home/me/mysite/src with the .sql files you want to serve. Then you can run:

docker run -it --name sqlpage -p 8080:8080 --volume "/home/me/mysite/src:/var/www" --volume "/home/me/mysite/configuration:/etc/sqlpage:ro" --rm lovasoa/sqlpage

This will work whatever directory you launch the command from, since it does not use $(pwd).

[flosigime]

well, after some months, i'm back with docker, rasberry pi and sqlpage, and an sqlite database

I succeeded in making sqlpage work in the pi. I can access it, evything seems ok.
But (yes, there's a but)
i need to add spatialite to sqlpage.

I tried to write a dockerfile based on sqlpage like this :

FROM lovasoa/sqlpage:latest

# installer mod_spatialite
RUN apt-get update && apt-get install -y \
    libsqlite3-mod-spatialite \
    && rm -rf /var/lib/apt/lists/*

# démarrer le serveur SQLPage
CMD ["/usr/local/bin/sqlpage"]

First, i'm really not sure of the CMD,
Second, Docker said that he can't find apt-get, so he can't install spatialite.

So i copied mod_spatialite.so from my pi to the container, in /usr/lib. I'm really not sure of the results (it was late when i stopped...).

Could you guide me ?

Thanks in advance

[lovasoa]

The sqlpage image is optimized for size, and does not contain a full debian distribution with apt and other tools.

What you can do is follow these instructions from the readme: https://github.com/sqlpage/SQLPage?tab=readme-ov-file#with-docker .

FROM debian:stable-slim

COPY --from=lovasoa/sqlpage:latest /usr/local/bin/sqlpage /usr/local/bin/sqlpage

# install mod_spatialite
RUN apt-get update && apt-get install -y \
    libsqlite3-mod-spatialite \
    && rm -rf /var/lib/apt/lists/*

[flosigime]

Thanks for your answer. I didn't red all the readme about docker. Sorry for that. Le ven. 29 nov. 2024 à 17:26, Ophir LOJKINE ***@***.***> a écrit :

…

The sqlpage image is optimized for size, and does not contain a full debian distribution with apt and other tools. What you can do is follow these instructions from the readme: https://github.com/sqlpage/SQLPage?tab=readme-ov-file#with-docker . FROM debian:stable-slim COPY --from=lovasoa/sqlpage:main /usr/local/bin/sqlpage /usr/local/bin/sqlpage # install mod_spatialiteRUN apt-get update && apt-get install -y \ libsqlite3-mod-spatialite \ && rm -rf /var/lib/apt/lists/* — Reply to this email directly, view it on GitHub <#212 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AOYEXYZCLOOPOWRMWW3KU532DCIRTAVCNFSM6AAAAABSWWOLZWVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTCNBRG42DSNA> . You are receiving this because you authored the thread.Message ID: ***@***.***>

-- ------------------------------------------------------ *Florent Mercey * *- Responsable d'opération Protohistoire, Etudes céramique Bronze/Hallstattt* *- Référent SIG, Référent NAS, collectif ramen ***@***.***> (Recherches Archéologiques en Modélisation de l'Enregistrement Numérique)* Inrap - Orléans 525 avenue de la Pomme de Pin 45590 Saint Cyr en Val tel : 02 38 69 96 58 ; port : 06 08 90 00 62 http://www.inrap.fr

[flosigime]

well, i really don't understand how to configure sqlpage in docker.

First, i did this to create a new image, as you said above. I add the CMD line, but i'm not sure it's necessary

FROM debian:stable-slim

COPY --from=lovasoa/sqlpage:latest /usr/local/bin/sqlpage /usr/local/bin/sqlpage

# install mod_spatialite
RUN apt-get update && apt-get install -y \
    libsqlite3-mod-spatialite \
    && rm -rf /var/lib/apt/lists/*

CMD ["/usr/local/bin/sqlpage"]

then, i created in my Documents folder a folder Sqlpage. Inside i have my sql pages (index.sql, etc..), my database, and in a subfolder named sqlpage my json file. For the moment, it's this :

{
"port":8080,
"database_url": "sqlite:///etc/sqlpage/badass.sqlite"
}

i launch my docker from my documents/sqlpage folder with this :

docker run -it --name sqlpage -p 8080:8080 --volume "$(pwd):/var/www" --volume "$(pwd)/sqlpage:/etc/sqlpage:ro" --rm sqlpage_spatialite

response is :
2024-11-30T11:51:38.539Z INFO sqlpage::app_config] Configuration directory does not exist, creating it: "./sqlpage"
[2024-11-30T11:51:38.543Z INFO sqlpage::app_config] No DATABASE_URL provided, /sqlpage/sqlpage.db is writable, creating a new database file.
[2024-11-30T11:51:38.544Z INFO sqlpage::webserver::database::connect] Connecting to database: sqlite:///sqlpage/sqlpage.db?mode=rwc
[2024-11-30T11:51:38.545Z INFO sqlpage::webserver::database::migrations] Not applying database migrations because './sqlpage/migrations' does not exist
[2024-11-30T11:51:38.613Z INFO sqlpage::webserver::http] SQLPage v0.31.0 started successfully.

and no access to my index.sql and to the database.

Thanks for your answer

[lovasoa]

Oh, yes, I understand it's confusing: you are not using the default docker image where /var/www and /etc/sqlpage are configured, since you are now recreating your own image based on debian. You can reproduce the setup from the original image by setting the same environment variables:

SQLPage/Dockerfile

Line 50 in daec7b7

ENV SQLPAGE_WEB_ROOT=/var/www

[flosigime]

Ok, so you mean, i create a dockerfile like this :

FROM debian:stable-slim

COPY --from=lovasoa/sqlpage:latest /usr/local/bin/sqlpage /usr/local/bin/sqlpage

# install mod_spatialite
RUN apt-get update && apt-get install -y \
    libsqlite3-mod-spatialite \
    && rm -rf /var/lib/apt/lists/*

ENV SQLPAGE_WEB_ROOT=/var/www 

CMD ["/usr/local/bin/sqlpage"]

no need of the configuration directory ?

ENV SQLPAGE_CONFIGURATION_DIRECTORY=/etc/sqlpage

Thanks

[lovasoa]

No, if you want to reproduce the configuration of the official image, then set both SQLPAGE_WEB_ROOT=/var/www and SQLPAGE_CONFIGURATION_DIRECTORY=/etc/sqlpage (you need to create that folder).

You can do whatever you want, just put your sql files in the web root (. by default, configurable with SQLPAGE_WEB_ROOT) and your configuration files in the config directory (./sqlpage by default, configurable with SQLPAGE_CONFIGURATION_DIRECTORY). In debian:stable-slim, the current working directory (.) is / by default.

There is an example app that uses docker and spatialite in the examples, here: https://github.com/sqlpage/SQLPage/tree/main/examples/make%20a%20geographic%20data%20application%20using%20sqlite%20extensions

[flosigime]

ah yes, thanks for the example.

well.
So
If i create a spatialite database in my local folder, (my_database.db in a local folder /home/me/documents/mywebsite/ with all my pages)

I create a docker file :

FROM debian:stable-slim

COPY --from=lovasoa/sqlpage:latest /usr/local/bin/sqlpage /usr/local/bin/sqlpage

# install mod_spatialite
RUN apt-get update && apt-get install -y \
    libsqlite3-mod-spatialite \
    && rm -rf /var/lib/apt/lists/*

ENV SQLPAGE_WEB_ROOT=/var/www 

CMD ["/usr/local/bin/sqlpage"]

and i build it.

In /home/me/documents/mywebsite/sqlpage/, my json should be :

{
"database_url": "sqlite:///my_database.db"
"sqlite_extensions": ["mod_spatialite"]
}

my docker command, launched from /home/me/documents/mywebsite/ will be :

docker run -it --name sqlpage -p 8080:8080 --volume "$(pwd):/var/www" --rm sqlpage_spatialite