Skip to main content

PostgreSQL

Overview​

PostgreSQL is an open-source object-relational database system known for its reliability, feature robustness, and performance. With support for advanced data types, full ACID compliance for transactional integrity, and an extensible architecture, PostgreSQL provides a highly scalable environment for managing diverse datasets, ranging from small applications to large-scale data warehouses. Its extensive SQL compliance, support for various programming languages, and strong community backing make it a versatile choice for a wide range of business intelligence and analytical applications. You can connect to and read from PostgreSQL databases using either a supported connection string or connection URI syntax.

Authentication Methods​

To connect to PostgreSQL, you need to provide database connection credentials. Rill supports two approaches:

  1. Use Individual Parameters (recommended for clarity)
  2. Use Connection String/URI (alternative format)

When you add data from PostgreSQL through the Rill UI, the process follows two steps:

  1. Configure Authentication - Set up your PostgreSQL connector with database credentials
  2. Configure Data Model - Define which table or query to execute

This two-step flow ensures your credentials are securely stored in the connector configuration, while your data model references remain clean and portable.

Using individual parameters provides clear, readable configuration for your PostgreSQL connection.

Using the UI​

  1. Click Add Data in your Rill project
  2. Select PostgreSQL as the data source type
  3. In the authentication step:
    • Enter your host (e.g., localhost or your database server address)
    • Enter the port (default: 5432)
    • Enter your database name
    • Enter your username
    • Enter your password
  4. In the data model configuration step, enter your SQL query
  5. Click Create to finalize

After the model YAML is generated, you can add additional model settings directly to the file.

Manual Configuration​

If you prefer to configure manually:

Step 1: Create connector configuration

Create connectors/postgres.yaml:

type: connector
driver: postgres

host: "localhost"
port: "5432"
user: "postgres"
password: "{{ .env.POSTGRES_PASSWORD }}"
dbname: "my_database"

Step 2: Add credentials to .env

POSTGRES_PASSWORD=your_password_here
Did you know?

If this project has already been deployed to Rill Cloud and credentials have been set for this connector, you can use rill env pull to pull these cloud credentials locally (into your local .env file). Please note that this may override any credentials you have set locally for this source.

Then, create your first model.

Method 2: Connection String/URI​

You can also use a connection string or URI format to configure your PostgreSQL connection.

Connection String Format​

host=localhost port=5432 dbname=postgres_db user=postgres_user password=postgres_pass

Connection URI Format​

postgresql://postgres_user:postgres_pass@localhost:5432/postgres_db

Manual Configuration​

Step 1: Create connector configuration

Create connectors/postgres.yaml:

type: connector
driver: postgres

dsn: "{{ .env.POSTGRES_DSN }}"

Step 2: Add credentials to .env

POSTGRES_DSN=postgresql://postgres_user:postgres_pass@localhost:5432/postgres_db

Then, create your first model.

Create Your First Model​

Once your connector is configured using any method above, create a model to define what data to pull.

Create models/postgres_data.yaml:

type: model
connector: postgres
dev:
  sql: SELECT * FROM my_table limit 10000

sql: SELECT * FROM my_table

After creating the model, you can add additional model settings directly to the file.

Separating Dev and Prod Environments​

When ingesting data locally, consider setting parameters in your connector file to limit how much data is retrieved, since costs can scale with the data source. This also helps other developers clone the project and iterate quickly by reducing ingestion time.

For more details, see our Dev/Prod setup docs.

Deploy to Rill Cloud​

When deploying a project to Rill Cloud, Rill requires you to explicitly provide the PostgreSQL connection credentials with access to PostgreSQL used in your project. Please refer to our connector YAML reference docs for more information.

If you subsequently add sources that require new credentials (or if you simply entered the wrong credentials during the initial deploy), you can update the credentials by pushing the Deploy button to update your project or by running the following command in the CLI:

rill env push
Did you know?

If you've already configured credentials locally (in your <RILL_PROJECT_DIRECTORY>/.env file), you can use rill env push to push these credentials to your Rill Cloud project. This will allow other users to retrieve and reuse the same credentials automatically by running rill env pull.