Gitpod: Adding SQL Databases

[patlecat]

Would be nice to have also a way to use DBs since they are often closely intertwined in applications and can't be simply switched on or off!

[svenefftinge]

You can have installed DBs through your Docker image. We are going to prepare some examples for mysql and postgresql within the next days/weeks.

[meysholdt]

here is a work-in-progress example for MySQL in Gitpod:
https://github.com/meysholdt/laravel-apache-mysql-php-in-gitpod-example

This blog post from @geropl shows how to use Postgres in Gitpod:
https://medium.com/gitpod/bring-your-own-docker-image-to-gitpod-52db1aa861de

[jankeromnes]

I'm creating an auto-building Docker image here that will give you a PostgreSQL environment by simply using this in your project's .gitpod.yml:

image: gitpod/workspace-postgres

It should even auto-start the server, and just work without you having to worry about starting it or connecting to it.

EDIT: This was merged and deployed, so now if you reference the above image in your .gitpod.yml, you'll get an auto-starting PostgreSQL server, as well as pg_start, pg_stop and pg_ctl status commands to control the server yourself.

[teirureddo]

https://github.com/teirureddo/envinits-gitpod-php

[alesanchezr]

But I cannot use the psql command line tool with this.

[jankeromnes]

@alesanchezr Hi! We now also have a gitpod/workspace-postgres Docker image.

You can use it in your project by adding this to your .gitpod.yml:

image: gitpod/workspace-postgres

And then you'll be able to use pg_start, pg_stop and pg_ctl status:

$ pg_ctl status
pg_ctl: no server running

$ pg_start
waiting for server to start.... done
server started

$ pg_ctl status
pg_ctl: server is running (PID: 1758)

Please remember to use pg_start and pg_stop instead of pg_ctl start and pg_ctl stop, as the latter won't work without a few extra arguments (needed because of our non-root psql setup).

(Also, in theory, PostgreSQL server should auto-start every time you open a terminal, so you shouldn't have to worry about that.)

(FYI, this image is used by the dev.to project.)

[hamza0867]

@jankeromnes Hi! so all we need to do is add a .gitpod.yml file to the repo with the content:
image: gitpod/workspace-postgres , and it will give us the pg_start ...etc commands ??

[jankeromnes]

Hi @hamza0867, yes that is correct! Gitpod will automatically detect the .gitpod.yml file at the root of your repo, so it will use the specified image (postgres), and then your workspace should have all the right commands. :)

Please let me know if can help you with that or if it doesn't work.

[hamza0867]

Hi @jankeromnes, If you try this repo for example (it is an empty repo with just a .gitpod.yml file) then it doesn't have the pg_start ... etc commands, only pg_config is available

[jankeromnes]

Hi @hamza0867, oh, that's unfortunate. 😐 I'm not quite sure what's going wrong, because when I try your repo, I do get a pg_start command:

gitpod /workspace/gitpod-yaml-config $ which pg_start
/home/gitpod/.pg_ctl/bin/pg_start

gitpod /workspace/gitpod-yaml-config $ pg_start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start.... stopped waiting
pg_ctl: could not start server
Examine the log output.

gitpod /workspace/gitpod-yaml-config $ pg_ctl status
pg_ctl: server is running (PID: 676)
/usr/lib/postgresql/10/bin/postgres "-D" "/home/gitpod/.pg_ctl/data" "-k" "/home/gitpod/.pg_ctl/sockets"

Could you please share a Snapshot of the workspace where you don't have a pg_start? (You can create one by clicking the top-right Avatar.)

[hamza0867]

Hi @jankeromnes , you are correct it is working now. I guess it just takes it some time to install all dependencies ?? However when I hit psql I get
psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
knowing that I already called pg_start (and it shows it is running on port 5432 ).
Am I doing something wrong ?

Thank you so much @jankeromnes for your help. I deeply appreciate it

[jankeromnes]

No problem. 😄 I'm very happy to help out, and I'd love to make sure that SQL databases work great in Gitpod.

It's good news that you now have a pg_start command! (Note: When you configure a repository to use a different Docker image, your Gitpod workspace will not "live-reload", so you have to create a new workspace for the changes to take effect. Maybe that's what caused the missing pg_start.)

However when I hit psql I get psql: could not connect to server

Oops, that issue I was able to reproduce. It seems that by default, psql will try to connect via a Unix socket, which doesn't work in our unprivileged PostgreSQL install.

The correct command to connect to the db is:

psql -h localhost -d postgres

I expect that many people will run into this issue. Maybe there is a way to make psql work out of the box in Gitpod? I'll investigate.

[jankeromnes]

Ok, I found these two environment variables that seem helpful:

$ export PGHOSTADDR="127.0.0.1"
$ export PGDATABASE="postgres"
$ psql
psql (10.8 (Ubuntu 10.8-0ubuntu0.18.10.1))
Type "help" for help.

postgres=#

I'll try to set them to the correct values by default in the gitpod/workspace-postgres image. Thanks a lot for the feedback! 😄

[hamza0867]

@jankeromnes What if I wanted to add monogodb to my workspace as well. Do I need to write a custom Dockerfile to support it or can I just put something in my .gitpod.yml to make it work ??

[jankeromnes]

@hamza0867 unfortunately, we don't yet have a custom image for MongoDB, so you'd have to write a custom Dockerfile implementing MongoDB's Ubuntu installation instructions.

For inspiration, here are all our custom Dockerfiles. I guess the most challenging part will be to make the MongoDB service run as the gitpod user without requiring sudo.

If you run into any trouble along the way, please let me know, I'm happy to collaborate on this. 😄

[hamza0867]

@jankeromnes Thanks man. You're awsome

[hamza0867]

Hi @jankeromnes ! I wrote a Dockerfile that installs mongodb to the system however when I launch the mongod command, I get the following error:
exception in initAndListen: IllegalOperation: Attempted to create a lock file on a read-only directory: /data/db, terminating
knowing that in my Dockerfile I already have RUN sudo chown mongodb:mongodb -R /data/db .
But it appears as it has no effect since running ls -al /data/db gives the following results:
drwxr-xr-x 1 mongodb mongodb 4096 Jun 5 17:23 . drwxr-xr-x 1 root root 4096 Jun 5 17:23 ..
How can I give the right privileges to make mongod work ??
You can test it on the same repo

[meysholdt]

Can you try to run mongodb as user gitpod and have RUN sudo chown gitpod:gitpod -R /data/db?

[jankeromnes]

@hamza0867 cool progress! Thank you for sharing. 😄

So, unfortunately, you can't use sudo or root privileges in Gitpod (well, you can in the Dockerfile, but no longer once a workspace is running). This also means that you can't run commands as another user than gitpod, i.e. the mongodb command will run as the gitpod user and not the mongodb user.

I think you have two options here:

1. Use RUN sudo chown gitpod:gitpod -R /data/db in your Dockerfile (EDIT: what @meysholdt just said 😅)

2. Or configure the mongodb command (e.g. with an env variable, or a command line argument, or a configuration file) to use a different data directory, e.g. under /home/gitpod/.mongodb

[hamza0867]

@meysholdt @jankeromnes Yes I can confirm that it is working now with changing ownership of /data/db to gitpod:gitpod maybe we can add a mongodb setup to the gitpod workspaces like the one with postgres ??

[jankeromnes]

Excellent news! Many thanks for powering through this! 💯

And actually yes, we'd love to have a MongoDB setup in gitpod/workspace-images. 😮 Would you like to make a pull request?

[hamza0867]

@jankeromnes I just created a pull request for that purpose. You can check it out

[meysholdt]

We now have MySQL, Postgres, Mongo...I think the original intention of this issue is satisfied. Closing.

Thank you @hamza0867 for your contribution!

[josuecarames]

Fantastic, thank you all for this great job!

[alesanchezr]

Yeah, it's all working.