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 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. Rill supports natively connecting to and reading from PostgreSQL as a source by using either a supported connection string or connection URI syntax.
As an example of a connection string:
host=localhost port=5432 dbname=postgres_db user=postgres_user password=postgres_pass
Using the same example, this would be an equivalent connection URI:
postgresql://postgres_user:postgres_pass@localhost:5432/postgres_db
Local credentials
When using Rill Developer on your local machine (i.e. rill start), you have the option to specify a connection string when running Rill using the --var flag.
An example of using this syntax in terminal:
rill start --var connector.postgres.database_url="postgresql://postgres:postgres@localhost:5432/postgres"
Alternatively, you can include the connection string directly in the source YAML definition by adding the database_url parameter.
An example of a source using this approach:
type: "postgres"
sql: "select * from my_table"
database_url: "postgresql://postgres:postgres@localhost:5432/postgres"
This second approach is generally not recommended outside of local development because it places the connection details (which may contain sensitive information like passwords!) in the source file, which is committed to Git.
For more information about available source properties / configurations, please refer to our reference documentation on Source YAML.
If this project has already been deployed to Rill Cloud and credentials have been set for this source, 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 that you have set locally for this source.
Cloud deployment
Once a project with a PostgreSQL source has been deployed using rill deploy, Rill requires you to explicitly provide the connection string using the following command:
rill env configure
Note that you must cd into the Git repository that your project was deployed from before running rill env configure.
If you've configured credentials locally already (in your <RILL_HOME>/.home file), you can use rill env push to push these credentials to your Rill Cloud project. This will allow other users to retrieve / reuse the same credentials automatically by running rill env pull.