--- title: "Direct Database Access" canonical: "https://red-ant-documentation.refined.site/space/RDD/618332178/Direct%20Database%20Access" format: markdown --- > Macro (toc) ## Introduction This guide details how you can connect directly to a RetailOS database. This can be useful for integrations which require continuous replication of data held within RetailOS. RetailOS uses [PostgreSQL](https://www.postgresql.org/) for its primary database, this guide is written with the assumption that you have a working knowledge of Postgres databases. ## Determining Required Access Before you request access to a RetailOS database, you should consider the requirements needed for your integration. We can grant access to any table, though most integrations commonly use: - Orders - Users - Products - Variants Our recommendation is to have a conversation with Red Ant to discuss your needs, and we can suggest the best ways to fulfil them. ### Access to tables with Personal Identifiable Information (PII) If you require access to a sensitive table, such as customers, there are special considerations. Our preference is that we do not pass PII at all through our integrations, but there are options should you need it. - If your integration does not require PII we offer “safe” views of the sensitive tables, with PII columns omitted. - If your integration requires PII, then you must confirm to our DB credential rotation policy and build your integration in a way that can handle dynamic credentials. See the **Handling Credential Rotation **section below for more information. ### Handling credential rotation If we have flagged to you that the nature of your integration requires daily credential rotation, this guide will help you build an integration that can dynamically get credentials. #### Granting access to Heroku Heroku hosts RetailOS database, and being able to access the latest database credentials requires you to have a Heroku account. #### Using the Heroku CLI to access up-to-date credentials Heroku has a CLI that can be used for you to get up-to-date-credentials. Read the [Heroku CLI](https://devcenter.heroku.com/articles/heroku-cli) documentation to determine the best option for installing and authorising on your systems. The recommended command to get the latest credentials for your applications' database surfaced on the app using this command: ``` heroku config:get CLIENT_CONNECTION_URL --app {APP_NAME} ``` This will return the value of the environment variable set for the connection string which comes out in this format: ``` postgres://username:password@host:port/database ``` ## Connection Credentials When you request connection credentials from Red Ant, you will be provided with the following to access the Database. - Host - Username - Password - Database - Port Before you use the credentials you need to make sure that you have a Postgres SQL DB browser to access the DB. For Macs, you can use [Postico](https://eggerapps.at/postico2/) and for Windows, you can use [pgAdmin](https://www.pgadmin.org/download/pgadmin-4-windows/). Please install the browser depending on your machine type. Once you have your Postgres DB browser ready you can follow the below instructions depending on your Machine type. **MacOS** - Open your Postgres Browser (Postico) - Click on New Favourite - it will open the panel on the right side of the application. see the below screenshot. - The Nickname is what you name the DB, you can call it “Red Ant followers DB” - The Host, Port, User, Password and database name would've been provided to you in advance. Fill in the information accordingly. - Once you have input the relevant information. Click on connect and you are in and it will look something like the below screenshot. ![image-20240125-120034.png](media://10f43dad-c7eb-460b-92cb-37329f96f674) - You can now click on SQL Query to run any queries or even scroll through the tables on the left side. **Note: You will only be able to access tables that you have been given access to by Red Ant. If you need to access a certain table then please raise a request with the Red Ant Service Desk and we will review your request and let you know whether it’s safe for you to have access to the table or not.** **Windows** - Open your Postgres Browser (pgAdmin) - Set a password for yourself (you will need to use this every time you open the browser) - Once you have set up the password and logged in then right-click on the server on the left side, click on register and then click on register (see the below screenshot). - You will see the Register server page open up (as can be seen below). In the name column, you can name the DB anything, you can call it “Red Ant followers DB”. - Once you have named your DB then click on the connection bar to set up the connection to the database. The Host, Port, database, User, and Password name would've been provided to you in advance. Fill in the information accordingly. This is all you need for connection ignore the other fields and click save. - Once you are in you can see the DB that you have saved on the left. Open dropdown for the DB>databases>gb3565gs7880>schemas>public>tables to open and view tables and data on your end. - In order to run a query on the database, you can right-click on a table click on scripts and then select script to run the select statement. You should have a list of queries that you can use to retrieve the data provided by Red Ant already. - This is the query page that will open once you click on select script. Now you can paste your query and click on the play button to run the query. **Note: You will only be able to access tables that you have been given access to by Red Ant. If you need to access a certain table then please raise a request with the Red Ant Service Desk and we will review your request and let you know whether it’s safe for you to have access to the table or not.**