Add SQLite support

[brtwrst]

Hi,

allowing to use SQLite would simplify the hosting for the app greatly. SQLite should be more than capable of handling the load if the instance is not extremely big. see: https://sqlite.org/whentouse.html

Important: a) enable WAL mode b) use a single writer Process (works with multiple, but processes should retry if they receive "database is locked" and not error out) see: https://news.ycombinator.com/item?id=33975635

[plutov]

Yes, that would make sense. We can probably still keep Postgres and let user decide what to use to store data.

[plutov]

Done in new release!

[brtwrst]

Awesome, nice job.

What motivated the change from alpine to debian:bookworm for the base image?

[plutov]

Good observation, I think it's needed to work properly with sqlite as it needs cgo. At least for build, but let me test the final stage.

[plutov]

ok, for some reason this command doesn't work with alpine:

COPY --from=builder /root/api .

and then it says:

api-1  | exec ./api: no such file or directory

[brtwrst]

I tried it and it worked fine for me the build step at least. The resulting image with alpine is quite a bit smaller I did not test any functionality yet though, just pulled the repo, changed the line to

FROM alpine:latest
RUN apk --no-cache add ca-certificates tzdata bash sqlite

(sqlite is probably not needed as a package) and built it

[brtwrst]

yeah nevermind the file can somehow not be run

[brtwrst]

I was able to make it work with this

FROM golang:1.22-alpine AS builder

RUN apk add build-base

WORKDIR /root

COPY go.mod .
COPY go.sum .

RUN go mod download

COPY ./cmd ./cmd
COPY ./pkg ./pkg
COPY ./migrations ./migrations
COPY ./surveys ./surveys-examples
RUN CGO_ENABLED=1 GOOS=linux go build -o api -tags enablecgo cmd/console-api/api.go

FROM alpine:latest
RUN apk --no-cache add ca-certificates tzdata bash
WORKDIR /root
COPY --from=builder /root/api ./api
COPY --from=builder /root/migrations ./migrations
COPY --from=builder /root/surveys-examples ./surveys-examples

CMD ["./api"]

[plutov]

Awesome, it works perfectly. Just pushed it. Thanks a lot!

[brtwrst]

I think you can also shrink the UI image a bit by using the following:

FROM node:20-alpine AS base

FROM base AS deps
RUN apk add --no-cache libc6-compat
WORKDIR /app

COPY package.json package-lock.json ./
RUN npm ci

FROM base AS builder
WORKDIR /app
COPY --from=deps /app/node_modules ./node_modules
COPY . .

ENV NODE_ENV=production
ARG NEXT_PUBLIC_CONSOLE_API_ADDR
ENV NEXT_PUBLIC_CONSOLE_API_ADDR=$NEXT_PUBLIC_CONSOLE_API_ADDR

RUN npm run build

# Use stock alpine as base image and add nodejs
FROM alpine:latest AS runner
WORKDIR /app

RUN apk add nodejs

ENV NODE_ENV=production

RUN addgroup --system --gid 1001 nodejs
RUN adduser --system --uid 1001 nextjs

COPY --from=builder /app/public ./public

RUN mkdir .next
RUN chown nextjs:nodejs .next

COPY --from=builder --chown=nextjs:nodejs /app/.next/standalone ./
COPY --from=builder --chown=nextjs:nodejs /app/.next/static ./.next/static

USER nextjs

EXPOSE 3000

ENV PORT=3000

CMD ["node", "server.js"]

This also means that both images can run on base alpine which should make combining them quite simple

[plutov]

That looks good, can you submit it as a PR please? I am thinking to keep 2 separate Dockerfiles still, as people still may want to deploy them separately. But also in root folder create a combined Dockerfile.