# PostgreSQL Overview 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. ### Connect your PostgreSQL database {% hint style="warning" %} We recommend using PostgreSQL 13 or later to ensure compatibility with Noloco {% endhint %} 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 or simply visit this link: ![](https://319575345-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MifpIQWu0HCVYAt51oT%2Fuploads%2Fgit-blob-9db631b6b4d29bd2ce860ce681344ddf5b020e52%2Fnew%20source%20postgres.png?alt=media) 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. ![](https://319575345-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MifpIQWu0HCVYAt51oT%2Fuploads%2Fgit-blob-1b4975f1ccecef4cdb81109a74a8a33e9a09d82d%2Fpostgres%20name.png?alt=media) 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. ![](https://319575345-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MifpIQWu0HCVYAt51oT%2Fuploads%2Fgit-blob-91e59f2dcd09eee8d6c8ebfe06db6428c8a79623%2Fpostgress%20hostname%20%26%20port.png?alt=media) 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. ![](https://319575345-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MifpIQWu0HCVYAt51oT%2Fuploads%2Fgit-blob-516d7fafd1fa54f098d2be6fc348954d1a27a8a4%2Fpostgres%20db%20name%20%26%20schema.png?alt=media) 5\. **Enter the login details** You will need to provide a PostgreSQL user's login details for us to use. Specifically we need the username and password for a user with `SELECT`, `INSERT`, `UPDATE` and `DELETE` permissions for tables within the schema you are importing to Noloco. ![](https://319575345-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MifpIQWu0HCVYAt51oT%2Fuploads%2Fgit-blob-6c19c11c0ba43ce32e69d7c60c1bd842776433ac%2Fpostgres%20db%20user%20%26%20password.png?alt=media) #### Connecting as a read-only user We support connecting to your database with only `SELECT` permissions, however you need to toggle this setting on in the last step of the new data source form. #### Enabling SSL on connections We support connecting to your database with SSL, just toggle it on in the last step of the new data source form. #### Whitelisting Noloco's IP addresses 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 ``` ### Syncing tables 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. {% hint style="success" %} We support tables with composite primary keys. {% endhint %} We only support regular tables and do not import views. ### Built-in data types 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. {% hint style="warning" %} We do not support arrays of any of the supported types. {% endhint %} #### Fully supported column 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` | #### Partially supported column types 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` | #### Unsupported column types 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). ### Custom data types We support a limited set of custom data types created via a `CREATE TYPE` query. Specifically we will sync [enumerated types](https://www.postgresql.org/docs/current/datatype-enum.html) 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. ### Domain types We offer support for [user-defined types](https://www.postgresql.org/docs/current/domains.html) 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. ### Foreign keys 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. {% hint style="warning" %} We do not support composite foreign keys. Only foreign key constraints on single columns will be synced as relationships in Noloco. {% endhint %} 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. ### Join tables 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` along with its own primary key, `id` . If a table in your database matches *all* of these criteria, then we will classify it as a join table: * A primary key (`id` in the above example) * 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. ### Reserved field names The following (and any case variants) are reserved field names in Noloco: * `createdAt` * `id` * `updatedAt` * `uuid` If your PostgreSQL table has an `id` column we will still import it to Noloco, however it will be renamed to `[Table Name] Id` to avoid conflicting with our own `id`. If you have `createdAt` or `updatedAt` field(s) and they are of type `date`, `timestamp`, `timestamp without timezone` or `timestamp with timezone`, we will map your column(s) to our own field(s). Otherwise we will manage the created/updated times ourselves. If you have a `uuid` column it will not be imported. ### File fields We support syncing columns containing a URL as a Noloco `FILE` field. Just open up the corresponding table in your data table and click on the field then change its type to `FILE`. After the table next has a data sync you will see your files at those URLs appearing in Noloco.
### Create query tables In some cases importing the exact tables that exist on your PostgreSQL database might not be as useful to you as importing the results of a query that curates your data in a certain way. You can add a query table to your app, which creates a new table in your data source, from a custom SQL query. This table will sync with your database, **but will be read-only from Noloco.** Noloco supports creating a query table by opening up the menu for your data source: ![](https://319575345-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MifpIQWu0HCVYAt51oT%2Fuploads%2Fgit-blob-90d024395c245a9d41610e661378d3f76470bdec%2FScreenshot%202023-03-20%20at%2011.29.52.png?alt=media) This will take you to the query editor where you can set up the query that will build your table.
At the top of this page is an input for you to name your table. On the left you can see an overview of this data source from Noloco and on the right is a query editor for you to develop and test your query over time. The results of testing your query will appear in the section at the bottom of the page. To save your query you must have a successful test of it.
Any errors in your query will be surfaced in the section at the bottom of the page.
After saving the query you will be able to see and use it like any other data type in Noloco, **except it will be read-only**. It will also be kept in sync with the upstream database both in schema and in data. If you want to edit the query at any time you can do so by clicking on the edit button from the data table.
### FAQs
Does editing a relationship create a new, empty record? No, this will not happen if your foreign keys are of a similar type. Double check the types of the columns in your foreign key constraint - if one is a `bigint` or `int8` and another is an `int` or `int4` we will be treating the values differently and may not build the correct relationship in Noloco. We would recommend to keep any relationship fields the exact same type as the field they reference.
What is the minimum version of Postgres that you currently support? Noloco uses a number of features that were introduced in v10 so anything before this will not work. To ensure maximum compatibility we recommend using Postgres 13 or higher which is the oldest version currently being maintained by the Postgres team.