Skip to main content

PostgreSQL

Overview​

PostgreSQL is a free and open-source relational database management system. It can be accessed and manipulated using SQL to extract or load data.

Integration Use Cases​

PostgreSQL databases are great for those looking to configure their own database and can be used to feed into reports and analytics.

Example user stories:

  • As an NGO program manager, I would like to be able to automatically see cases registered in our case-management system in a Postgres database which is connected to a visualization dashboard.
  • As a fundraiser at an NGO, I would like to see all consumption data in Postgres so I can better monitor activities in real-time.

Integration Options​

OpenFn adaptors (see language-postgresql) provide direct database connections for accessing data and executing SQL and standard database operations.

Authentication:​

To create the direct DB connection, you'll need to specify the following credential inputs.

{
"configuration": {
"host": "some-host-url.compute-1.amazonaws.com",
"port": "5432",
"database": "testdatabase",
"user": "myusername",
"password": "testing123",
"ssl": true,
"allowSelfSignedCert": true
}
}

(This authentication step is handled in the OpenFn adaptor.)

Sample Job Expressions:​

This function creates a table users in a database from a given array of columns.

insertTable('users', state =>
state.data.map(column => ({
name: column.name,
type: column.type,
required: true, // optional
unique: false, // optional - set to true for unique constraint
}))
);

This function is used to insert a single record in a Postgres database.

insert(
'users',
{
email: 'antony@gmail.com',
first_name: 'Antony',
inserted_at: '2020-08-27 00:00:00',
updated_at: '2020-08-27 00:00:00',
},
{ setNull: ["''", "'undefined'"], writeSql: true, logValues: true }
);

See the Job Library for more sample jobs.

Integration tips​

  • Keys: Make sure the primary key and foreign keys for each table are known documented in the mapping specifications
  • Unique identifiers: To avoid the "ON CONFLICT DO UPDATE command cannot affect row a second time" error discussed below, spend time thinking through which field or combination of fields makes each record unique using the sample data recieved
  • Testing: Create a robust test suite which outlines which tables should be updated by each job run image

Common Errors​

  1. Connection TIMEOUT - This error usually means the destination system is offline or inaccessible to your user. Contact the destination system administrator.
  2. error: value too long for type character varying(16) & error: null value in column "household_id" violates not-null constraint - Both of these errors highlight a mismatch in the data you're attempting to add to the database and what it's configured to accept. In this case, you are sending more than 16 characters for a field that only accepts up to 16 characters AND sending "null" for a required field in that table.
  3. error: ON CONFLICT DO UPDATE command cannot affect row a second time- This error means that there are two rows with the same value for unique fields. Review the sample data and mapping specifications to decide what makes a record unique in that table.

OpenFn Adaptors​

OpenFn implementations can leverage the PostgreSQL adaptor.

Implementation Examples​

  1. Wildlife Conservation Society Kobo < > Database: https://github.com/OpenFn/consosci
  2. Cambodia Primero < > ONA Database: https://github.com/OpenFn/primero-ona-dashboard