The PostgreSQL Database Connector (by ardoRic)

  
Hello everyone,

OutSystems Platform 9 brought a new Platform API that allows anyone to implement their own database integration connector. These are fully featured connectors, as powerful as the ones included with OutSystems Platform for SQL Server, Oracle, etc. All you need to build your own connector is database and native stack (Java/.NET) expertise.

This was, for me, the most interesting capability of the OutSystems Platform 9 (aside from the awesome incorporation of REST into the platform). So I set out to build something with it.

Using the Database Integration API, I’ve built a connector for the open source DBMS PostgreSQL. You know... because reasons.

Here you can find the latest release of the ardoPostgreSQL Database Connector for the OutSystems Platform (9.0.0.6+).

With this you can use the OutSystems Platform to connect to PostgreSQL databases in the same way you are already used to: by configuring a Database Connection in Service Center, Importing the tables you need from Integration Studio, and creating a fantastic application in Service Studio.





In order to get it running on your system, simply download the ardoPGSQL.zip file and follow the instructions for your selected stack (.NET or Java). At this time the instructions should work for IIS, JEE JBoss Community 7.1.1 and EAP 6. If you mean to install this on Weblogic, please get in touch with me.

This procedure currently requires the services  (including the application server) to be down, so you will likely have downtime. After this you get a new option in the Database Connections combo box for PostgreSQL and after that it's business as usual.

The Database Integration API SDK includes a battery of 200+ unit tests that cover most of the plugin capabilities. This plugin passed (almost) all the tests, but let me know if you run into any issues. The connector is not officially supported by OutSystems, as it was created by me. For help with any issue or suggestions, get in contact with me directly through the forums or by email (ricardo.silva@outsystems.com).

Please look into the known issues list (currently one, but it can grow) for known problems and potential workarounds/ fixes.

Best regards,
Ricardo "ardoRic" Silva
Nicely done, congrats :)
Solution
For more information on what the Database Integration API and SDK are, this video (18 minutes) provides a good introduction.

Currently, the Database Integration API SDK capability is under Limited Availability. We are doing a slow rollout in order to fine-tune the SDK for the general public. However, this doesn't mean the Database API is in beta. It is not. It can be used in Production.
If you would like to build your own database integration plugin, enroll in the Limited Availability Program by contacting us at product.management@outsystems.com.


Note: in order to use ardoRic's PostgreSQL database connector you do not need the SDK. A database plugin, created with the SDK can be installed standalone. You only need the SDK if you’re planning to build your own database plugin (or modify ardoRIc’s connector).
Solution
I'm just curious, why isn't PostgreSQL built into outsystems core platform?

There are many great companies that use PostgreSQL

  • Skype use PostgreSQL (over a billion registered users)
  • Apple use PostgreSQL
  • Sony use PostgreSQL
  • Fujitsu use PostgreSQL
  • Cisco use PostgreSQL
  • Telstra use PostgreSQL (Australia’s largest telecommunication company)
  • Optus use PostgreSQL (Australia’s 2nd largest telecommunication company)
  • plus more....and counting...

 

It seems like DB2 and MySQL have more people asking for it.

I'll let Arlindo answer the question more authoritively, but from what I remember, we wanted to have an Open Source database and we picked the most popular (MySQL) and we wanted a commercial database for which we picked DB2 as we had a lot of requests for it.

Since we were building the API, we figured most other databases would fit into the long tail and the community would take care of implementing them as needed.

I personally always suggested to go for PostgreSQL instead of MySQL, and would love to see how the OutSystems Platform fares with a PostgreSQL DB as a backend. Maybe one day, when there's an API we can use as there is this Integration API :)
It would be perfect if we could run PostgreSQL as a backend database! This way we can use outsystems data modeller!  

MySQL is more popular than PostgreSQL, easy to setup and manage, but it has limitations that you need to work around, whereas PostgreSQL is more advanced and more reliable than MySQL! Overall PostgreSQL is a better open sourced database.
Arlindo Lima wrote:
For more information on what the Database Integration API and SDK are, this video (18 minutes) provides a good introduction.

Currently, the Database Integration API SDK capability is under Limited Availability. We are doing a slow rollout in order to fine-tune the SDK for the general public. However, this doesn't mean the Database API is in beta. It is not. It can be used in Production.
If you would like to build your own database integration plugin, enroll in the Limited Availability Program by contacting us at product.management@outsystems.com.


Note: in order to use ardoRic's PostgreSQL database connector you don't need the SDK. A database plugin, created with the SDK can be installed standalone. You only need the SDK if you’re planning to build your own database plugin (or modify ardoRIc’s connector).
 Hi Arlindo & Ricardo,

If we want to connect to a PostgreSQL database, we are not sure how to proceed.

To connect to PostgreSQL, we need to use the Database Integration API.
  1. Is the API built-in in OutSystems, and if so, in which version? Or is it available on the Forge?
  2. As far as I can see, we do not need the Database Integration SDK. Is this correct?
  3. Do we need to enroll in the Limited Availability Program? (note that we do not plan to develop our own connector currently, we just want to connect to PostgreSQL).
  4. Can we just use the ardoPostgreSQL connector (https://github.com/ardoric/ardoPostgreSQL/releases) and use it in combination with the Database Integration API?
Thanks for the good work!
Regards, Jan-Hendrik
Hello Jan-Hendrik,

1. The API is built-in with OutSystems 9.0.0.6+. You'll be able to use the connector in any version higher than 9.0.0.6 
2. Correct. You only need to use the SDK if you're developing your own connector, or changing mine.
3. see above. Nope.
4. Yes. Just download the latest version, follow the installation instructions and you're good to go.
Thanks for the quick reply!
Hello all,

The latest version of the ardoPostgreSQL database connector now extends support for Redshift clusters.

Give it a spin and drop me any feedback from real usage.
Ricardo, your database connector should work just fine with Redshift, after all Redshift is based on postgresql 8.0.2 :) it is worth noting there are some unsupported postgresql features http://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-features.html
Actually its true that Redshift is based on postgresql 8.0.2, but that version is so old that about a year ago the developer of the drivers removed all "deprecated" code.
They recently put back some of the code to make it explicitly compatible with Redshift. So RIcardo had to update the driver to a compatible version


And from the list of things Ricardo "rage" dumped on my skype from the failing sdk tests I would say that they list of unsupported features is way incomplete. :)
Joao, thanks for the insight, you should dump interesting things on my Skype too ;)
Yeah, I had also thought that the connector should be able to directly connect, but when I actually got a chance to try it it didn't work.

As João said, it had to do with some assumptions the npgsql code made about what was supported on the other side (they only support supported versions of postgresql, and 8.0.2 has already been End-of-Life'd since 2010), which needed to be corrected in the driver itself.

Additionally there are some other constraints. PostgreSQL adds some constraints to introspection tables, and to some functions (namely date functions like to_timestamp).

There are also some differences in behavior which might require changes to the query generation and runtime when using Redshift databases, hence my decision to make a different connector ( which inherits from the PostgreSQL one ). 

Apparently, Amazon is now providing their own drivers to connect to redshift, so I might also look into that for better compatibility.
Ricardo, yes since red shift requires some extra work and not a simple postgresql drop in database, it would make sense to use Amazon official drivers for redshift.
I am actually curious about which data modeller tool would DB's use with RedShift database, currently use Postgresql 8.0.2 supported data modellers, this is fine for now, but what about the future? will things change? (Essentially, there is a need for a reliable RedShift data modeller tool, one that lets you create ER data models, sync your ER with your database, create triggers, indexes etc - there is no such tool!)

Alternatively, if you want the power and compatibility of postgresql but the speed of RedShift, use postgresql database with Periscope.io - you get the best of both worlds :)
This is going a hell of a lot off-topic here with all this Redshift discussion, but one should note that while Redshift is based off PostgreSQL, it is not designed as a relational database.

Redshift is a big data, Business Intelligence, database. This is overly evident by some of the design choices in Redshift. For example, integrity constraints (unique, foreign key, primary key) are NOT enforced. SELECT FOR UPDATE is not supported, which probably means there is no locking, indicating this data is mostly to be queried but not very prepared to be transactional.

Redshift *is not* good for everyday, relational database usage.
It looks like this does not work for 9.1.0.20.
I upgraded my server today from 9.0.1.9, things using postgres broke.

I even went so far as to follow the instructions over again, deleting the old files, and pasting them from a fresh zip, and reinstalling service center.


Redshift shows up on the dropdown list, but postgres no longer does.... And now I can no longer connect to postgres databases that are linked to my primary application....


Also, when I try to edit my previously configured database that used postgres I get the following error:

Does anyone have any suggestions?

Thanks,
Braxton
I tried swapping out the outsystems related DLLS with new ones from 9.1.0.20 and compiling from source... But this also throws errors....
Hello Braxton,

There is a breaking change with the OutSystems Platform Bali which renders dll's for Database Connectors built for 9.0 unusable in 9.1.

The Database Integration API has been changed due to the adding of the Long Integer type, and previously built connectors do not work.

I have a quickfix for this (which does not yet support Long Integers) which I'm attaching to this post. You can use it to get around your problem. All you need to do is bring down the services, replace the dll's and republish service center. You'll also need to republish any module that uses the connector.

I'm sorry I haven't yet had the free time to address this correctly.

Best regards,
Ricardo Silva


Thanks for the quick response!
Hi,

I´ve installed this connector on the Frontend and in the Controller.

I can see PostgreSQL available on the Frontend, but not on the Controller. What should I do?

I saw that the shell script installer has a different approach for the controller, not sure why though. Any tips?



Christian Reichel wrote:
Hi,

I´ve installed this connector on the Frontend and in the Controller.

I can see PostgreSQL available on the Frontend, but not on the Controller. What should I do?

I saw that the shell script installer has a different approach for the controller, not sure why though. Any tips?


 
 
" I can see PostgreSQL available on the Frontend, but not on the Controller."

Did you recompile servicecenter?