PostgreSQL
Learn how to build an app in Noloco around your PostgreSQL database
Our PostgreSQL data source is now in beta! Want early access? Email [email protected]

In this guide we are going to walkthrough how easy it is to connect your PostgreSQL database(s) to your Noloco app. Once connected, this will allow your team, customers &/or third party stakeholders to read, update and create records in your PostgreSQL instance directly from your Noloco app.

  1. 1.
    Add your PostgreSQL data source Navigate to the data tab in your Noloco app and click to add a new data source. From the list, choose Postgres.
2. Name your data source It's best practice to call the data source the same name as your database to help you keep track.
3. Enter the server connection information The hostname is the URL that you access your server on and the port is the port you use to connect. By default PostgreSQL uses port 5432 so if you're not sure try that.
4. Enter the database information We need the name of the database on the server that you want us to connect to and the schema within the database that we should pull the tables from. By default the schema is probably called public, however the database will be something that you named.
5. Enter the login details You will need to provide a PostgreSQL user's login details for us to use. Specifically we need a the username and password for a user with SELECT, INSERT, UPDATE and DELETE permissions for tables within the schema you are importing to Noloco.

We support connecting to your database with SSL, just toggle it on in the last step of the new data source form.

If you restrict connections to your PostgreSQL database by IP, you can whitelist our three static IP addresses that we might connect from.
18.203.60.136
54.217.27.248
54.228.83.124

Noloco will import all tables from your PostgreSQL schema that have a primary key and whose name has some alpha-numeric or emoji characters. So for instance a table with just punctuation in the name would be ignored, as would a table with a valid name but no primary key.
We support tables with composite primary keys.
We only support regular tables and do not import views.

We categorise built-in data types of columns into three buckets; fully supported, partially supported and unsupported. When importing a column from a table, you will only see it in Noloco if its type is fully or partially supported.
We do not support arrays of any of the supported types.

Data types that Noloco fully supports will be imported into your Noloco project with full read-write capabilities to be updated as well as displayed.
Postgres Type
Noloco Type
bigint
INTEGER
bigserial
INTEGER
bool
BOOLEAN
boolean
BOOLEAN
char
TEXT
character
TEXT
character varying
TEXT
date
DATE
decimal
DECIMAL
double precision
DECIMAL
float4
DECIMAL
float8
DECIMAL
int
INTEGER
int2
INTEGER
int4
INTEGER
int8
INTEGER
integer
INTEGER
interval
DURATION
money
DECIMAL (CURRENCY)
numeric
DECIMAL
real
DECIMAL
serial
INTEGER
serial2
INTEGER
serial4
INTEGER
serial8
INTEGER
smallint
INTEGER
smallserial
INTEGER
text
TEXT
timestamp
DATE
timestamp with timezone
DATE
timestamp without timezone
DATE
varchar
TEXT

Other data types are partially supported which means that we will import them to your Noloco project and display them, but we do not support any write operations to them so you may not update them from Noloco.
Postgres Type
Noloco Type
cidr
TEXT
inet
TEXT
json
TEXT
jsonb
TEXT
macaddr
TEXT
macaddr8
TEXT
pg_lsn
TEXT
uuid
TEXT
xml
TEXT

All other column types are unsupported and columns with those types will not be imported to Noloco (although the rest of the table's columns with supported types will be imported).

We support a limited set of custom data types created via a CREATE TYPE query. Specifically we will sync enumerated types as Noloco SINGLE_OPTION types. We will only sync these if the enum created is not empty. Right now we do not support enum arrays or any other custom types.

We offer support for user-defined types created via a CREATE DOMAIN query. During schema syncing, domains will be mapped onto their underlying type and provided we offer full or partial support for that underlying type it will be synced to Noloco.

Any table with a foreign key constraint on one or more of its columns to another synced table will have those foreign key(s) interpreted as a Noloco relationship field(s) when synced.
We do not support composite foreign keys. Only foreign key constraints on single columns will be synced as relationships in Noloco.
The type of relationship that will be created in Noloco depends on other constraints on the foreign key constrained column. However because PostgreSQL requires all columns referenced by a foreign key to have a unique constraint, they will always be one of the two relationships below.
FK-Constrained Column is Unique
Noloco Relationship
Yes
ONE_TO_ONE
No
MANY_TO_ONE
These relationships are fully functional Noloco relationships and any updates to the relationship values in Noloco will be propagated to the foreign key on your PostgreSQL database.

As documented above, all PostgreSQL foreign keys must reference a column with a unique constraint. This means that from one foreign key alone you cannot create ONE_TO_MANY or MANY_TO_MANY relationships in PostgreSQL. The way that these are typically created are with join tables with multiple foreign keys that sit between two tables you want to relate. Noloco offers some limited support to interpret such join tables as multi-relationships.
Consider the following example:
Table A (id, value)
Table B (id, value)
Join Table (id, a_id, b_id)
The Join Table allows a MANY_TO_MANY relationship to be described between Table A and Table B by storing a normalised mapping of a_id and b_id.
If a table in your database matches all of these criteria, then we will classify it as a join table:
  • It has exactly two foreign keys (pointing to separate tables)
  • Either one or neither of the foreign keys has a unique constraint
  • All columns in the table are either in the primary key or in one of the foreign keys
Join tables are not synced as their own data type like the other tables. Instead they will be synced as a relationship field on each side of the relationship they point to. The type of Noloco relationship that is created for a join table depends on whether there are any unique constraints in the join table for the two foreign keys.
Number of FKs With Unique Constraints
Noloco Relationship
0
MANY_TO_MANY
1
ONE_TO_MANY
2
N/A - not classified as join table
These relationships are fully functional Noloco relationships and any updates to the relationship values in Noloco will be propagated to the join table on your PostgreSQL database.
Copy link
On this page
Overview
Connect your PostgreSQL database
Syncing tables
Built-in data types
Custom data types
Domain types
Foreign keys
Join tables