Sqlite3 Upgrade

1

I have an app with Sqlite but there are issues with foreign keys. If I can update the Sqlite version on Flyio this would resolve my issues. Is it possible to upgrade your Sqlite version?

Thanks!

2

Yes, you can control the SQLite version within your Dockerfile. What does your Dockerfile look like right now? Typically you need to install it through your OS’ package manager:

apt install sqlite3
3

Hi Ben, Thank you for that. For some more context I have a Rails app that I am using Sqlite on but without the latest version I am running into foreign key constraint issues. Hoping there is a way to install the latest version of Sqlite. My Dockerfile looks like:

# syntax = docker/dockerfile:1

# Make sure RUBY_VERSION matches the Ruby version in .ruby-version and Gemfile
ARG RUBY_VERSION=3.2.2
FROM ruby:$RUBY_VERSION-slim as base

LABEL fly_launch_runtime="rails"

# Rails app lives here
WORKDIR /rails

# Set production environment
ENV RAILS_ENV="production" \
    BUNDLE_WITHOUT="development:test" \
    BUNDLE_DEPLOYMENT="1"

# Update gems and bundler
RUN gem update --system --no-document && \
    gem install -N bundler:2.4.20


# Throw-away build stage to reduce size of final image
FROM base as build

# Install packages needed to build gems and node modules
RUN apt-get update -qq && \
    apt-get install --no-install-recommends -y build-essential curl libvips node-gyp pkg-config python-is-python3

# Install JavaScript dependencies
ARG NODE_VERSION=18.15.0
ARG YARN_VERSION=1.22.19
ENV PATH=/usr/local/node/bin:$PATH
RUN curl -sL https://github.com/nodenv/node-build/archive/master.tar.gz | tar xz -C /tmp/ && \
    /tmp/node-build-master/bin/node-build "${NODE_VERSION}" /usr/local/node && \
    npm install -g yarn@$YARN_VERSION && \
    rm -rf /tmp/node-build-master

# Install application gems
COPY --link Gemfile Gemfile.lock ./
RUN bundle install && \
    bundle exec bootsnap precompile --gemfile && \
    rm -rf ~/.bundle/ $BUNDLE_PATH/ruby/*/cache $BUNDLE_PATH/ruby/*/bundler/gems/*/.git

# Install node modules
COPY --link .yarnrc package.json yarn.lock ./
COPY --link .yarn/releases/* .yarn/releases/
RUN yarn install --frozen-lockfile

# Copy application code
COPY --link . .

# Precompile bootsnap code for faster boot times
RUN bundle exec bootsnap precompile app/ lib/

# Precompiling assets for production without requiring secret RAILS_MASTER_KEY
RUN SECRET_KEY_BASE=DUMMY ./bin/rails assets:precompile


# Final stage for app image
FROM base

# Install packages needed for deployment
RUN apt-get update -qq && \
    apt-get install --no-install-recommends -y curl imagemagick libsqlite3-0 libvips && \
    rm -rf /var/lib/apt/lists /var/cache/apt/archives

# Copy built artifacts: gems, application
COPY --from=build /usr/local/bundle /usr/local/bundle
COPY --from=build /rails /rails

# Run and own only the runtime files as a non-root user for security
RUN useradd rails --create-home --shell /bin/bash && \
    mkdir /data && \
    chown -R rails:rails db log storage tmp /data
USER rails:rails

# Deployment options
ENV DATABASE_URL="sqlite3:///data/production.sqlite3" \
    RAILS_LOG_TO_STDOUT="1" \
    RAILS_SERVE_STATIC_FILES="true"

# Entrypoint prepares the database.
ENTRYPOINT ["/rails/bin/docker-entrypoint"]

# Start the server by default, this can be overwritten at runtime
EXPOSE 3000
VOLUME /data
CMD ["./bin/rails", "server"]
4

That results in Debian 12.1 (“bookworm”), which by default contains sqlite3 version 3.40.1 2022-12-28 14:03:47.

If you would like to upgrade to 3.43.1 2023-09-11 12:01:27, replace two sections in your Dockerfile:

# Install packages needed to build gems and node modules
RUN apt-get update -qq && \
    apt-get install --no-install-recommends -y build-essential curl libvips node-gyp pkg-config python-is-python3 tcl unzip wget && \
    wget https://www.sqlite.org/2023/sqlite-src-3430100.zip && \
    unzip sqlite-src-3430100.zip && \
    cd sqlite-src-3430100 && \
    ./configure && \
    make && \
    make install

# Install packages needed for deployment
RUN apt-get update -qq && \
    apt-get install --no-install-recommends -y curl imagemagick libvips && \
    rm -f /usr/local/lib/x86_64-linux-gnu/libsqlite3.so.* && \
    rm -rf /var/lib/apt/lists /var/cache/apt/archives
COPY --from=build /usr/local/bin/sqlite3 /usr/local/bin
COPY --from=build /usr/local/lib/libsqlite3.so.* /usr/local/lib

I’ve tested this to the extent that it builds and I can run the sqlite3 executable. I haven’t tested it with Rails… if it causes any problems let me know and I’ll research further.

5

That worked which is amazing, thank you. I am still getting Foreign Key errors which leads me to believe the issue is actually with the Fly mount volume which is separate from the app itself.

6

What foreign key errors are you seeing? Are you importing data into SQLite from outside of Rails? SQLite doesn’t enforce foreign keys by default so you need to run this on your connection to enable it:

PRAGMA foreign_keys = ON
7

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.