1 PostgreSQL Setup
gilgamesh edited this page 6 months ago

PostgreSQL Setup

Table of Contents

  1. Introduction
  2. Setup
    1. Windows
    2. Linux
      1. PostgreSQL
      2. Installing pgAdmin4 on Arch Linux
      3. Connecting to the database
  3. Connecting and working with PostgreSQL inside Godot
    1. Create a table
    2. Modify data
    3. Delete data

Introduction

This project uses GitHub - Marzin-bot/PostgreSQLClient at alpha. Note the alpha branch.

Setup

Windows

Install and enable WSL2, you can do so by following the documentation here Install WSL | Microsoft Docs

  1. Download and install Docker Desktop.

  2. Pull a PostreSQL image by running the following command in cmd.

    docker pull postgres
    
  3. Build and Run a container.

    docker run --name dev-postgres -e POSTGRES_PASSWORD=password -p 5432:5432 postgres
    
  4. Download pgAdmin4. This will be used to interact with the database outside of Godot.

  5. Run pgAdmin4 and check if your database is up and running.

  6. Clone the repository.

Linux

PostgreSQL

  1. Install Docker

  2. Pull a PostreSQL image by running the following command in cmd.

    docker pull postgres
    
  3. Build and Run a container.

    docker run --name dev-postgres -e POSTGRES_PASSWORD=password -p 5432:5432 postgres
    

    We’ll use the port later when connecting to the database.

Installing pgAdmin4 on Arch Linux

The pgadmin4 package seems to be broken, for now the alternative is to run pgAdmin4 using docker-compose, taken from here, use the following

  1. Make sure you installed Docker.

  2. Install docker-compose

  3. Create a directory named pgadmin.

  4. Inside the new directory, copy the configuration below into a file named docker-compose.yml

    version: "3.8"
    
    volumes:
      pgadmin-data:
    
    services:
      pgadmin:
        image: dpage/pgadmin4
        ports:
          - "9201:80"
        environment:
          PGADMIN_DEFAULT_EMAIL: root@localhost.localdomain
          PGADMIN_DEFAULT_PASSWORD: secret
          PGADMIN_DISABLE_POSTFIX: "true"
          PGADMIN_CONFIG_SERVER_MODE: "False"
        volumes:
          - pgadmin-data:/var/lib/pgadmin
    

    Sources: Container Deployment — pgAdmin 4 6.9 documentation, pgAdmin 4: Unable to start program - Arch Forums.

  5. Run docker-compose up -d in the same directory as the docker-compose.yml file.

  6. Use docker ps -a to see your running pgAdmin.

  7. Go to http://localhost:9201 to use pgAdmin.

  8. Use docker-compose down to shut it down.

  9. Network troubleshooting

    To connect you need to configure the network properly by using the network inspection capabilites of docker.

    1. docker network ls

      Some networks will be listed in the console, maybe you’ll find a [name]default it should be your network.

    2. docker network inspect [name]_default, for example mine is pgadmindefault

      From the shown information, the most important is IPv4Address for our use case.

      sudo docker network inspect [name]_default | grep IPv4Address will get you that, (e.g. 172.18.0.2/16)

    3. Use the IPv4 address (e.g. 172.18.0.2) when creating a server instance in pgAdmin4 to connect to our PostgreSQL server.

      • Example settings

        Setting Value Comment
        Hostname/address 172.18.0.2  
        Port 5432 From our PostgreSQL setup

        the rest depends on how you created your PostreSQL server.

    Source

Connecting to the database

Since the PostgreSQL database and the pgAdmin4 exist in different containers, we need to manually create the database instance.

  1. In the pgAdmin main page, right click on Servers, click on Register then choose Server and under the General tab enter the following settings

    Setting Value Comment
    Name dev-postgres  

    Under the Connection tab, enter the following

    Setting Value Comment
    Hostname/address 172.18.0.2  
    Port 5432 From our PostgreSQL setup.
    Maintenance database postgres  
    Username postgres  
    Password <your choice> You’ll use this in Godot to connect.

Connecting and working with PostgreSQL inside Godot

For a minimal setup:

  1. Create a 2D scene

  2. Attach a script to the 2D scene

  3. Create a script

  4. Create an instance of our database

    const USER := "postgres"
    const PASSWORD := "password"
    const HOST := "localhost"
    const PORT := 5432 # Default port
    const DATABASE := "postgres" # Database name
    
    var database: PostgreSQLClient = PostgreSQLClient.new()
    
    func _init():
    	var _error = database.connect("connection_established", Callable(self, "_connection_established"))
    	_error = database.connect("authentication_error", Callable(self, "_authentication_error"))
    	_error = database.connect("connection_closed", Callable(self, "_connection_close"))
    	_error = database.connect("data_received", Callable(self, "_data_received"))
    
    	#Connection to the database
    	_error = database.connect_to_host("postgresql://%s:%s@%s:%d/%s" % [USER, PASSWORD, HOST, PORT, DATABASE])
    
    
    func _physics_process(_delta: float) -> void:
    	database.poll()
    
    
    func _connection_established() -> void:
    	print(database.parameter_status)
    
    	var error := database.execute("""
    		BEGIN;
    		/*Helloworld*/
    		SELECT concat('Hello', 'World');
    		COMMIT;
    	""")
    
    	print(error)
    
    
    func _data_received(error_object: Dictionary, transaction_status: PostgreSQLClient.TransactionStatus, datas: Array) -> void:
    	match transaction_status:
    		database.TransactionStatus.NOT_IN_A_TRANSACTION_BLOCK:
    			print("NOT_IN_A_TRANSACTION_BLOCK")
    		database.TransactionStatus.IN_A_TRANSACTION_BLOCK:
    			print("IN_A_TRANSACTION_BLOCK")
    		database.TransactionStatus.IN_A_FAILED_TRANSACTION_BLOCK:
    			print("IN_A_FAILED_TRANSACTION_BLOCK")
    
    	# The datas variable contains an array of PostgreSQLQueryResult object.
    	for data in datas:
    		#Specifies the number of fields in a row (can be zero).
    		print(data.number_of_fields_in_a_row)
    
    		# This is usually a single word that identifies which SQL command was completed.
    		# note: the "BEGIN" and "COMMIT" commands return empty values
    		print(data.command_tag)
    
    		print(data.row_description)
    
    		print(data.data_row)
    
    		prints("Notice:", data.notice)
    
    	if not error_object.is_empty():
    		prints("Error:", error_object)
    
    	# database.close()
    	#
    	# I omit this command because otherwise we will not be able to
    	# communicate with the server because the connection will close
    	# as soon as it is opened.
    
    
    func _authentication_error(error_object: Dictionary) -> void:
    	prints("Error connection to database:", error_object["message"])
    
    
    func _connection_close(clean_closure := true) -> void:
    	prints("DB CLOSE,", "Clean closure:", clean_closure)
    
    
    func _exit_tree() -> void:
    	database.close()
    

Create a table

In addition to the code above

func create_table():
	var data = database.execute("""
	CREATE TABLE IF NOT EXISTS <table-name> (
	-- Schema
	);
	""")

Modify data

func update_table():
	var data = database.execute("""
	UPDATE <table-name> SET <column> = <value> WHERE <column> = <value>;
	UPDATE players SET score  = updated_score;
	""")

Delete data

To delete data from a table.

func delete_data():
	var data = database.execute("""
	DELETE FROM players WHERE <column> = <value>;
	""")