Update Postgres installation without docker instructions to use a script

[charlesBochet]

Scope & Context

In the documentation: https://docs.twenty.com/developer/local-setup#3-postgresql-database (Linux, WSL) we are giving instruction to create the mandatory schemas and database and users. This could be moved to a script and replace by a one-liner command

Technical inputs

We already have a script folder in server folder, let's add a new one to run this.

• If we go through node psql client, we will need to run these command as postgres user which is different as using twenty user (which is the case in the other scripts running on DB).
• Alternatively, maybe there is a way to make something clean using an init.sql file instead and using a psql command.

[b9aurav]

@charlesBochet I would be happy to work on this issue, can you please assign it to me?

[charlesBochet]

Sure, thank you @b9aurav!

[b9aurav]

@charlesBochet I've created a shell script based on instructions provided in the Documentation and organized all SQL statements into a new init.sql file.

Here's how the shell script is executed:

https://github.com/twentyhq/twenty/assets/79157299/73aa70a6-b70d-4708-be44-b716e8c58e95

Before I move forward, I'd like to confirm if I'm following the correct approach. Can you please confirm it? I'm open to any feedback, suggestions or improvements.

[charlesBochet]

looks good to me to create a shell script to run:

  sudo apt update
  sudo apt install postgresql postgresql-contrib

  sudo service postgresql start
  sudo -u postgres psql

then can we re-use the existing infra/dev/postgres/init.sql? It's the one we are using to setup the postgres docker db, would be great if we could avoid duplicating setup sql file

This might help: https://stackoverflow.com/questions/32041907/postgresql-initdb-database-initialization-on-linux

[b9aurav]

Thanks for feedback,

However, there are issues with reusing the existing infra/dev/postgres/init.sql

• infra/dev/postgres/init.sql uses "default" database which is already created in docker environment. You can find it here. It throws an error on fresh PostgreSQL install. (I couldn't find where this database is being created)
• infra/dev/postgres/init.sql uses GraphQL extension for docker environment, Local Setup (Yarn) is not using GraphQL as per documentation.

I couldn't find a relevant solution on the Stack Overflow link you provided. The question there is 8 years old, so it might be outdated.

[charlesBochet]

Interesting, regarding your points above:

• I think it's in our docker-compose.yml. We could justify either always creating a db with init.sql (in this case, we could create a "twenty" and a "test" db, so "twenty" does not conflict with the existing default db) OR we could maybe do a CREATE IF NOT EXIST. I think option 2 would be my favorite here
• that's correct, we haven't updated the doc yet (it has been merged on Friday and we were testing it). What is in the init.sql is correct, we need all this graphql setup ; this is the reason why I was pushing to have an install script instead of instruction in the docs, these are going to be more and more complex)

[b9aurav]

I see,

So, after bit research, I can think of two approaches here,

• Mannually create "default" database and "twenty" user with PostgreSQL and GraphQL setup in shell script and reusing existing init.sql, like this:

This is rough & working script (Without affecting existing init.sql)

#!/bin/bash

PG_MAIN_VERSION=14
PG_GRAPHQL_VERSION=1.3.0
TARGETARCH=$(dpkg --print-architecture)

USERNAME=twenty
PASSWORD=twenty
DATABASE=default

apt update -y
apt install -y postgresql-$PG_MAIN_VERSION postgresql-contrib
apt install -y curl

# Install precompiled pg_graphql extensions
curl -L https://github.com/supabase/pg_graphql/releases/download/v$PG_GRAPHQL_VERSION/pg_graphql-v$PG_GRAPHQL_VERSION-pg$PG_MAIN_VERSION-$TARGETARCH-linux-gnu.deb -o pg_graphql.deb
dpkg --install pg_graphql.deb

# Start PostgreSQL service
sudo service postgresql start

# Create "default" database and user
sudo -u postgres psql -c "CREATE DATABASE \"$DATABASE\";"
sudo -u postgres psql -c "CREATE USER $USERNAME PASSWORD '$PASSWORD';"

# Use existing init.sql file
sudo -u postgres psql -f ../../infra/dev/postgres/init.sql

No need to create "test" database as it is being created in existing init.sql file.

OR

• Update existing init.sql file which is being used in docker setup with CREATE IF NOT EXIST statement on database and user.

Let me know about your opinions and what approach do you recommend.

[charlesBochet]

I would go with CREATE IF NOT EXIST if it works! Looks good on the overall approach!

[b9aurav]

@charlesBochet Based on your suggestions, I have finalized script with CREATE IF NOT EXISTS approach. (Tested without docker)

Here's script execution:

https://github.com/twentyhq/twenty/assets/79157299/d21e3a70-0512-4882-b0e0-829e4197c757

Quick questions:

• Since script uses infra/dev/postgres/init.sql, what should be the location of this script?
• Should i use these fancy ASCII headers, or just plain straight-forward script?

I am still open for any suggestions/improvement :)

[charlesBochet]

that's cool ! Let's create a infra/dev/scripts folder and put into it That's awesome to see the logo in the script, let's use them, very nice!

[lucasbordeau]

Nice ASCII art !