Add support for PostgreSQL

[acelaya]

No description provided.

[jasoniangreen]

This would be great, but I am not sure I understand, the actual shlink backend supports PostgreSQL but this docker image doesn't? Why is that? Does it just require some non standard setup?

[acelaya]

Well, technically it is supported if you provide the config using a config file through a volume.

However, the structure of the config is not documented, and it is not possible using env vars either.

I will soon add the docs needed to fully support it.

[jasoniangreen]

Cheers, in the meantime I can try and experiment with the config file / volume approach.

[acelaya]

@jasoniangreen maybe you can try with something like this.

{
    "entity_manager": {
        "connection": {
            "driver": "pdo_pgsql",
            "user": "my_username",
            "password": "my_password",
            "dbname": "shlink",
            "host": "localhost",
            "port": "5432"
        }
    }
}

Change params accordingly, save as database.config.json, and mount a volume in /etc/shlink/config/params/database.config.json

Let me know if that helps.

[jasoniangreen]

In AbstractPostgreSQLDriver.php line 79:
                                                          
  An exception occurred in driver: could not find driver  
                                                          

In PDOConnection.php line 31:
                         
  could not find driver  
                         

In PDOConnection.php line 27:
                         
  could not find driver  

I assume this means I would need to fork this image and add the driver?

[jasoniangreen]

Or actually, that initial DB code is run on my host machine, so I just need to install that PHP extension?

[jasoniangreen]

Nope, I tell a lie again, it's copied into the docker and run in there.

[jasoniangreen]

Looks like it worked, I'll submit a PR at least with the PG deps added.

[jasoniangreen]

See #34

[jasoniangreen]

Hello @acelaya ? All I need is this PR #34 and then everything else is working for me, it would be great to get it deployed.... oh actually I need this and for the image on docker to be build with the latest version of shlink 1.16.1 (maybe it already does that when it builds).

[acelaya]

Yes, the image is missing the postgre PHP extension. I knew there was some other reason making it not Postgre compatible. My bad.

Regarding your pull request, I remember now that I wanted to carefully check the best approach.

If you are not using mysql in production, does not make much sense to bloat the image with mysql dependencies. Same for postgre.

For this reason I was considering on releasing two diferent images, one for postgres and one for mysql, but I need some time to work on it.

However, this could be a good first approach to support both, allowing to later split the image.

I'll need one or two days to get some time to carefully think it.

[acelaya]

@jasoniangreen Both latest and v1.16.1 tags are being rebuilt with postgres support. I'll leave a message here when it's finished.

[acelaya]

@jasoniangreen build finished.

[jasoniangreen]

No problem @acelaya. By the way, can you select the DB schema with the environment variables, or is there another way to do that?

[acelaya]

You should only need to define the database name with the DB_NAME env var

[jasoniangreen]

Our setup is one main Postgres database with each system getting it's own schema. It was as easy as adding schema property into the database config json file you pointed me to in another message.

[acelaya]

I see.

Then for now I'd suggest using the config file.

I will add support for this new env var for future versions.

[acelaya]

Hey @jasoniangreen, can I ask how are you providing the schema in the config file?

According to the docs of the ORM used by shlink, that's in theory not possible, and my tests are not very satisfactory so far.

Would you mind sharing the entity_manager.connection part of your config file? (without credentials, of course. I just need to see the structure).

[jasoniangreen]

Hey @acelaya sure. This is the file I save to /etc/shlink/config/params/database.config.json.

{
  "entity_manager": {
    "connection": {
      "driver": "pdo_pgsql",
      "user": "$user",
      "password": "$password",
      "dbname": "$dbname",
      "host": "$host",
      "port": "$port",
      "schema": "$schema"
    }
  }
}

[acelaya]

Thanks @jasoniangreen!

I must be missing something else, because that's what I tried. I will investigate.