🐘

Connect to PostgreSQL

Prerequisites for Connecting Graphweaver with PostgreSQL

In this guide, we are going to cover how to get Graphweaver and PostgreSQL connected.

Before we dive in, let’s make sure that you have PostgreSQL, Node.js version 18 or greater, and pnpm version 8 or greater installed on your local machine.

Once they are installed, we can get started.

Step 1: Installing and Configuring PostgreSQL

The first step is to make sure that we have the configuration settings correct in PostgreSQL. For this guide, we are going to assume that you have PostgreSQL running locally on port 5432.

If you have it running on another port, then remember to change this in the Graphweaver configuration below. Next, you are going to need a user in PostgreSQL that we can use when connecting from Graphweaver. For this guide, we are going to assume that you have a user postgres with an empty password. Here are the settings that we will need:

  • user: postgres
  • password: ''
  • port: 5432

Next, let’s setup a database to use as an example.

Step 2: Creating a Database

For this guide, we are going to create a database with a single table. We will be using a number of the PostgreSQL command line tools to create the DB, create a user table and seed it with some data. Don’t worry if you prefer to use a GUI when setting up the DB - as long as the names match you can use any tool you need.

Let’s start by creating the database:

createdb gw

Next, let’s create the user table:

psql -U postgres -d gw -c "CREATE TABLE \"user\" (id BIGSERIAL PRIMARY KEY, username VARCHAR(255) UNIQUE NOT NULL, password VARCHAR(255) NOT NULL, first_name VARCHAR(255), last_name VARCHAR(255));"

Lastly, let’s create some dummy users in the table:

psql -U postgres -d gw -c "
  INSERT INTO \"user\" (username, password, first_name, last_name) VALUES
    ('user1', 'password1', 'John', 'Doe'),
    ('user2', 'password2', 'Jane', 'Smith'),
    ('user3', 'password3', 'Alice', 'Johnson'),
    ('user4', 'password4', 'Bob', 'Brown');
"

💡
We are just using plain text password here in this guide, you should never do this in a production system!

Now that we have a database and a users table, let’s create the Graphweaver project.

Step 3: Setting up the Graphweaver Project

Now we are ready to install Graphweaver on your local machine. To do that, run the following command:

npx graphweaver@latest init

This will start the creation wizard that will guide you through creating your Graphweaver app.

Give your app a name. Here, we are calling it postgres-example :

? What would your like to call your new project? postgres-example

Next, select that you would like to use PostgreSQL:

? Which Graphweaver backends will you need? (Press <space> to select, <a> to toggle all, <i> to invert selection, and <enter> to proceed)
❯◉ MikroORM - PostgreSQL Backend

You will be asked if you are happy with a new directory being created for your new app. Hit enter.

Step 4: Installing the Required Packages

Now that we have created the project, make sure that you change directory and install the dependencies:

cd postgres-example
pnpm i

Then run pnpm start - you should now be able to see the Admin UI interface at http://127.0.0.1:9000/:

image

There are currently no data sources attached to Graphweaver, so let’s go ahead and do that next.

Step 5: Configuring the Connection to PostgreSQL in Graphweaver

In this next step we are going to create the connection from PostgreSQL to Graphweaver. For this, we can use the CLI tool to import the database. To start the import, run the following command: 

npx graphweaver@latest import postgresql

This will then ask you a number of questions to make the connection to the database: 

? What is the database server's hostname? 127.0.0.1
? What is the database name? gw
? What is the username to access the database server? postgres
? What is the password for this user? 
? What is the port? 5432

Once you have answered the connection details, introspection will create the files in your project to model the data:

⠙ Introspecting...Fetching database schema...
⠹ Introspecting...Building metadata...
Closing database connection...
Database connection closed.
Import complete.

If you look at the folder structure now you can see that you have new files that define the data source:

image

We now have everything we need to start the server and make our first GraphQL request.

Let’s do that next.

Step 6: Making our first GraphQL Query

Back on the command line, start the server with pnpm start. This will start the server at http://localhost:9000 - you should now see yout data source listed like this:

image

Select it and then select the User entity to browse the data:

image

Click on the Open Playground button to open the playground, where you can now send a query to the GraphQL server: 

query {
  result: users(pagination: {offset: 0, limit: 50, orderBy: {id: ASC}}) {
    id
    username
    firstName
    lastName
    __typename
  }
}

And get the results:

image

Congratulations! You have now connected PostgreSQL to Graphweaver. You can keep creating your tables and data models.

The great thing about Graphweaver is that you can connect multiple data sources together. For more information on what data sources you can connect, take a look at the Introduction to Data Providers page.

Exogee