Connect Local Postgres DB to WP-Woocommerce

I hope I am in the right place to ask this question. I am wanting to connect my Local Postgres Database (Point of Sale POS0 system to Syncronize my Stock levels with my Woocommerce store. Can anyone give me some pointers on the best way to do this please?
Thanks heaps in advance.

Is anyone able to help me with this please? I know that I need to create an API for the Local Postgres DB and I have attempted to do that. But how would I then use this to connect to the Wordpress/Woocommerce API?

Are you using Dreamfactory for the API? @alisellou

That is what I’m thinking I need to do but not 100% sure on how to do this :frowning: @mattschaer


1 Like

@alisellou If I’m understanding correctly there are ways to do this but if the POS system reduces the stock level directly in your PostGreSQL database then another method of triggering the API would have to be done . You would need to connect both backend DBs as a service in Dreamfactory then write a script that gets data from one and synchronizes with the other.

You can use something like cron or windows task scheduler to make a call that triggers this event script. You can create a cURL script in windows and save it as a .bat file that runs at intervals of your choosing. Hope this helps!


So if I create a link to my woocommerce API in Dream Factory and a Link to my Postgres DB on my local machine, I should be able to write some code to do the updates, lookups etc inside dream factory? This could be a challenge for me as I am no PHP expert :frowning: Cheers

@alisellou No expertise required! Check out the custom scripting tutorial in V8js here. The same logic applies but should be applicable with a little bit of tuning you should be able to use the platform and api resources to POST to the Woocommerce API or Woocommerce backend. Please note that if you are POSTing directly to the Woocommerce API rather than the database as a REST end point you’ll need to specify the full URL in your script.

Is there anyone who can point to someone who can help set this up please? I am happy to pay for some help.

My Requirements are as follows.

When an order is created in Woocommerce, reduce the Qty of Stock in the Postgres DB by the qty of items sold.
When an order is created in the Postgres DB (POS System) reduce the Qty of Stock in Woocommerce.

No customer info is required to be updated here. I am just trying to keep my stock levels synchronised as it is currently a nightmare to do this on a daily basis and I run the risk of overselling an item online if is sells out in our store.

I can create the Postgres API in Dream Factory, Woocommerce already has and active API, I just really have no clue how to start building an interface to make these two systems communicate with each other.

Can anyone please help me with this? I am happy to pay for your time.