dockerfile for sqlpage to work with clickhouse through clickhouse-odbc

[oliverdding]

It took me such a long time to deal with it, so I would like to share thie piece of work to save others time:

FROM debian:stable-slim AS builder

RUN apt-get update && \
    apt-get install -y build-essential git cmake libpoco-dev libssl-dev libicu-dev unixodbc-dev patchelf

RUN git clone --recursive https://github.com/ClickHouse/clickhouse-odbc.git

RUN mkdir clickhouse-odbc/build && \
    cd clickhouse-odbc/build && \
    cmake -DCMAKE_BUILD_TYPE=RelWithDebInfo .. && \
    cmake --build . --config RelWithDebInfo -j$(nproc) && \
    cmake --install . --config RelWithDebInfo && \
    # permission problem here
    chmod 755 /usr/local/lib/libclickhouseodbc*.so && \
    # linking bugs in clickhouse-odbc, so we need to patchelf here
    patchelf --add-needed libgcc_s.so.1 /usr/local/lib/libclickhouseodbcw.so && \
    patchelf --add-needed libstdc++.so.6 /usr/local/lib/libclickhouseodbcw.so && \
    patchelf --add-needed libgcc_s.so.1 /usr/local/lib/libclickhouseodbc.so && \
    patchelf --add-needed libstdc++.so.6 /usr/local/lib/libclickhouseodbc.so && \
    ldconfig

FROM lovasoa/sqlpage:main AS binary

FROM debian:stable-slim

COPY --from=builder /usr/local/share/doc/clickhouse-odbc/ /usr/local/share/doc/clickhouse-odbc/
COPY --from=builder /usr/local/lib/ /usr/local/lib/

RUN apt-get update && \
    apt-get -y install openssl libicu76 unixodbc && \
    rm -rf /var/lib/apt/lists/*

RUN echo "[clickhouse-odbc-ansi]" > /etc/odbcinst.ini && \
    echo "Description = ODBC Driver (ANSI) for ClickHouse" >> /etc/odbcinst.ini && \
    echo "Driver      = /usr/local/lib/libclickhouseodbc.so" >> /etc/odbcinst.ini && \
    echo "Setup       = /usr/local/lib/libclickhouseodbc.so" >> /etc/odbcinst.ini && \
    echo "UsageCount  = 1" >> /etc/odbcinst.ini && \
    echo "" >> /etc/odbcinst.ini && \
    echo "[clickhouse-odbc-unicode]" >> /etc/odbcinst.ini && \
    echo "Description = ODBC Driver (Unicode) for ClickHouse" >> /etc/odbcinst.ini && \
    echo "Driver      = /usr/local/lib/libclickhouseodbcw.so" >> /etc/odbcinst.ini && \
    echo "Setup       = /usr/local/lib/libclickhouseodbcw.so" >> /etc/odbcinst.ini && \
    echo "UsageCount  = 1" >> /etc/odbcinst.ini && \
    echo "[ODBC Data Sources]" > /etc/odbc.ini && \
    echo "clickhouse-dsn-ansi = clickhouse-odbc-ansi" >> /etc/odbc.ini && \
    echo "clickhouse-dsn-unicode = clickhouse-odbc-unicode" >> /etc/odbc.ini && \
    echo "" >> /etc/odbc.ini && \
    echo "[clickhouse-dsn-ansi]" >> /etc/odbc.ini && \
    echo "Driver = clickhouse-odbc-ansi" >> /etc/odbc.ini && \
    echo "Description = DSN for clickhouse-odbc-ansi" >> /etc/odbc.ini && \
    echo "" >> /etc/odbc.ini && \
    echo "[clickhouse-dsn-unicode]" >> /etc/odbc.ini && \
    echo "Driver = clickhouse-odbc-unicode" >> /etc/odbc.ini && \
    echo "Description = DSN for clickhouse-odbc-unicode" >> /etc/odbc.ini

COPY --from=binary /usr/local/bin/sqlpage /usr/local/bin/sqlpage

RUN groupadd --gid 1000 --system sqlpage && \
    useradd --uid 1000 --system --no-create-home -g sqlpage sqlpage && \
    mkdir -p /etc/sqlpage

ENV SQLPAGE_WEB_ROOT=/var/www
ENV SQLPAGE_CONFIGURATION_DIRECTORY=/etc/sqlpage
WORKDIR /var/www

USER sqlpage

COPY --from=binary --chown=sqlpage:sqlpage /var/www/sqlpage/sqlpage.db /var/www/sqlpage/sqlpage.db

EXPOSE 8080

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

Just mount your sql file to /var/www, and configure environment variable DATABASE_URL, which is the same as lovasoa/sqlpage image. Enjoy ;)