How to run it through Docker with SQLite?

[the-lazy-fox]

Hi there,
I see in the documentation some references about SQLite being covered nevertheless in the config sample and in Docker images list, it's never mentioned.
Could you please clarify which images should I use and which configuration to have PrivateBin running with SQLite?
Thank you!

[rugk]

AFAIK you should use the PDO one, as that is the PHP adapter for SQL* in any way: https://hub.docker.com/r/privatebin/pdo

So if you wanna use that, just provide a custom configuration: https://github.com/PrivateBin/docker-nginx-fpm-alpine#custom-configuration

And then configure it to use your DB backend: https://github.com/PrivateBin/PrivateBin/wiki/Configuration#model

Warning: Note that our installation guide advises against SQLite for production.

[elrido]

Some additional details on the reasoning for not recommending SQLite, while still supporting it:

• SQLite lets us run the unit tests using an in-memory database configuration, which is ideal for us to mock the PDO and data access through it in a throw away, repeatable fashion.
  

  PrivateBin/tst/Data/DatabaseTest.php

  Lines 14 to 19 in ac06627

  	private $_options = array(
  	'dsn' => 'sqlite::memory:',
  	'usr' => null,
  	'pwd' => null,
  	'opt' => array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION),
  	);

• SQLite can only have one writing thread, so if several pastes get created around the same time they would get stored in a serialized fashion and that would introduce delays to some users in a busy instance - that is not really a big concern for a smaller instance, but reason enough for us not to recommend it for production uses.
  • other RDBMS do support multiple writes at the same time (or do at least hide the serialization better)
  • the filesystem storage does also handle parallel writes gracefully, simply because each paste creates a unique filename and path based on it's unique ID
• SQLite requires local file storage, so in a scale out environment you'd need to provide a shared, writable file backend like NFS, GlusterFS, OCFS2 or a similar solution.
  • other RDBMS won't have that limitation and can be hosted independently (or just use one that is provided as a managed solution by your cloud or hosting environment)
  • the filesystem storage has the same limitation as SQLite

So from the multiple writes and shared filesystem backend point of view, SQLite doesn't offer any benefits over the filesystem storage, where as the other RDBMS can be useful when there is no shared storage available or one wants to use an existing managed solution for convenience.

[the-lazy-fox]

Hi @rugk
Thank you for answering, I've done that but it seems that the container is not creating the database by itself?

[model]
; example of DB configuration for SQLite
class = Database
[model_options]
dsn = "sqlite:" PATH "/srv/data/db.sq3"
;usr = null
;pwd = null
opt[12] = true  ; PDO::ATTR_PERSISTENT

I've tried to touch db.sq3 but it doesn't change anything.

I'm facing this error:

privatebin  | 2022/09/02 18:17:41 [error] 21#21: *4 FastCGI sent in stderr: "PHP message: PHP Fatal error:  Uncaught PDOException: could not find driver in /srv/lib/Data/Database.php:110
privatebin  | Stack trace:
privatebin  | #0 /srv/lib/Data/Database.php(110): PDO->__construct()
privatebin  | #1 [internal function]: PrivateBin\Data\Database::getInstance()
privatebin  | #2 /srv/lib/Model.php(86): forward_static_call()
privatebin  | #3 /srv/lib/Controller.php(199): PrivateBin\Model->getStore()
privatebin  | #4 /srv/lib/Controller.php(125): PrivateBin\Controller->_create()
privatebin  | #5 /var/www/index.php(18): PrivateBin\Controller->__construct()
privatebin  | #6 {main}
privatebin  |   thrown in /srv/lib/Data/Database.php on line 110" while reading response header from upstream, client: 172.18.39.1, server: , request: "POST / HTTP/1.1", upstream: "fastcgi://unix:/run/php-fpm.sock:"

Any ideas?

[r4sas]

could not find driver

You must install php sqllite extension.

[the-lazy-fox]

Into your own Docker image?

[elrido]

Yes, the current PDO docker image doesn't include the SQLite driver:
https://github.com/PrivateBin/docker-nginx-fpm-alpine/blob/master/buildx.sh#L59-L60

Correct, you'd need to create your own image that includes it. I'd imagine your Dockerfile would need to like something like this:

# I'd suggest using the "fs" image, as it is the most lightweight one:
FROM docker pull privatebin/fs:1.4.0

# switch to the root user, required to install packages
USER 0:0

# install the required PDO driver for SQLite
RUN apk add --no-cache php8-pdo_sqlite

# switch back to the user nobody, group www-data, which the services will run as
USER 65534:82