[Feature Request]: Is sqlite supported as an alternative to mysql?

[Firstyear]

Is your feature request related to a problem? Please describe.

For self hosting, setting up mysql is more work over an embedded sqlite install. It is also greater in terms of footprint and resources for those smaller deployments. However, it's not clear if snipe supports sqlite as an alternative, and all the docs show mysql as the only option.

A benefit of supporting sqlite would be accessibility and ease of startup/setup for smaller deployments, and greatly simplified backups for those deployments (contrast to mysql). It would make the datavolume for snipe a "self contained" location for the state of an install.

Describe the solution you'd like

Clarification of if sqlite is supported, and if it could be possible to add support for. Saying this I also understand why you wouldn't want to support a second backend db since that adds another dimension to a testing matrix.

This in mind, I think the benefits of ease of use and administration are a valuable benefit for users.

As an alternative, if you don't wish to support sqlite or others, documenting this in the setup section would be good to avoid future requests like this :)

Describe alternatives you've considered

N/A

Additional context

No response

[marcusmoore]

Sqlite isn't officially supported but mostly (I believe) works. There are two migrations that sqlite doesn't like and #12721 addresses them.

Again, it's not officially supported but I would love to hear any issues that you run into while running it.

[Firstyear]

Thank you! I'll give it a go this afternoon and will let you know how it goes. :)

[Firstyear]

@marcusmoore As requested I gave this a go and found some errors. I'm trying to use docker here, so it's worth noting I'm building from your branch fixes/allow-migrating-sqlite found in your pr https://github.com/snipe/snipe-it/pull/12721.

The following is what I encountered: Some is docs, some is missing libs etc.

https://snipe-it.readme.io/docs/configuration - likely needs a bigger call out that key generate is different on docker.

https://snipe-it.readme.io/docs/configuration - config says gd or imagick - image library is always gd on docker.

For sqlite add to docker file:

php7.4-sqlite3 \ sqlite3 \

No env var for db path - forces /var/www/html/database/database.sqlite. Could read from DB_HOST (config/database.php line 59)

The sql test with sqlite checks if the db exists but doesn't create it by default. Requires a manual exec invocation (see below).

Contains/migrations fail with below errors due to inability to write to a long. Requires an extra log mount. See below.

The stream or file "/var/www/html/storage/logs/laravel.log" could not be opened in append mode: failed to open stream: Permission denied The exception occurred while attempting to log: UnexpectedValueException: The stream or file "/var/www/html/storage/logs/laravel.log" could not be opened in append mode:

Final docker commands:

docker run --rm -i -t -d -p 8080:80 --name="snipeit" --env-file=./env \
    --mount source=snipe-vol,dst=/var/lib/snipeit \
    --mount source=snipe-db,dst=/var/www/html/database/  \
    --mount source=snipe-logs,dst=/var/www/html/logs/   snipe-it:test

 docker exec -i -t snipeit bash
# sqlite3 /var/www/html/database/database.sqlite
# CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);
# .quit

touch /var/www/html/storage/logs/laravel.log
chown docker:root /var/www/html/storage/logs/laravel.log

Migration error

Migrating: 2022_08_25_213308_adds_ldap_default_group_to_settings_table

   Illuminate\Database\QueryException

  SQLSTATE[HY000]: General error: 1 Cannot add a NOT NULL column with default value NULL (SQL: alter table "settings" add column "ldap_default_group" integer not null)

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:712
    708▕         // If an exception occurs when attempting to run a query, we'll format the error
    709▕         // message to include the bindings with SQL, which will make this exception a
    710▕         // lot more helpful to the developer instead of just the database's errors.
    711▕         catch (Exception $e) {
  ➜ 712▕             throw new QueryException(
    713▕                 $query, $this->prepareBindings($bindings), $e
    714▕             );
    715▕         }
    716▕     }

      +9 vendor frames
  10  database/migrations/2022_08_25_213308_adds_ldap_default_group_to_settings_table.php:19
      Illuminate\Support\Facades\Facade::__callStatic()

      +22 vendor frames
  33  artisan:35
      Illuminate\Foundation\Console\Kernel::handle()

I hope this helps, but so far looks like sqlite is not an easy setup! I'm happy to test any updates you may have though :)

Snipe-IT Documentation

Configuration

This section is where you edit the Snipe-IT configuration file to reflect your own settings, such as your database credentials, mail server, preferred language, timezone, and so on. Some of the settings are optional, some are required.Don't be intimidated by the length of this page. The configuratio...

[marcusmoore]

@Firstyear thanks for all of the details and for dropping the final commands you needed to run to get it (semi-)working!

We can update documentation on this in the future but I would like to figure out why that migration is causing a problem first. I'm not using docker and "it works for me ™️" locally. My sqlite version is 3.38.3, which isn't the latest but shouldn't be much different.

The error being displayed makes sense (and a follow up migration fixes that issue 2022_12_20_171851_fix_nullable_migration_for_settings.php) but it is weird that it goes through without a problem for me.

This is going to take a little more digging...

[Firstyear]

Well if you need me to re-do or re-test anything let me know. I tried to use as much upstream and existing content as possible so if you want I can send you the docker files etc. I'm not really a PHP developer (outside of tweaking some lines here and there) so I can't do much code wise, but if you want me to test anything let me knoww :)

[marcusmoore]

I haven't had a chance to come back to this yet but I'm assigning it to myself so it stays on my radar.

[Firstyear]

@marcusmoore No stress at all mate. I'd help more but I'm not a PHP dev. Is there anything extra I can do to test or upload to help you reproduce?

[Firstyear]

Relates #12794

[lesinigo]

Having Sqlite support would be really great especially when deploying to container based solutions, like Kubernetes, since it would be more than enough for the workload and would allow to avoid running a MariaDB/MySQL container just for Snipe-IT

[nulano]

I've managed to get to the setup page, but I get an error trying to set up an initial user: production.ERROR: PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:501 I was getting a similar error during the migrations at first, but that was due to incorrect permissions on the DB file. Having fixed it, I now get the readonly database error when creating a user even with rw-rw-rw- permissions on the DB file.

My configuration (click to expand)

```yaml # docker-compose.yml version: '2.1' services: snipeit: # image: snipe/snipe-it:latest-alpine image: nulano/snipeit build: build restart: always volumes: - ./data:/var/lib/snipeit environment: DB_CONNECTION: sqlite DB_PATH: /var/lib/snipeit/db.sqlite3 # TODO MAIL APP_ENV: production APP_DEBUG: "true" APP_KEY: APP_URL: APP_TIMEZONE: APP_LOCALE: en PHP_UPLOAD_LIMIT: 100 ``` ```Dockerfile # build/Dockerfile FROM snipe/snipe-it:latest-alpine RUN apk add --no-cache php81-sqlite3 php81-pdo_sqlite sqlite RUN sed -i "s/database_path('database.sqlite')/env('DB_PATH', \0)/" /var/www/html/config/database.php COPY ./entrypoint_sqlite.sh /entrypoint_sqlite.sh CMD ["/entrypoint_sqlite.sh"] ``` ```bash # build/entrypoint_sqlite.sh #!/bin/sh if [ -z "$DB_PATH" ] then DB_PATH=/var/lib/snipeit/database/database.sqlite fi if [ ! -f $DB_PATH ] then sqlite3 $DB_PATH "CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z)" chown 100 $DB_PATH fi /entrypoint.sh ```

I've now given up and added MySQL to the compose file instead, but would like to migrate to SQLite in the future if support is improved.