Postgres

Iterapp supports Postgresql 14 out of the box. Opt in by adding an empty section tag in your iterapp.toml.

[postgres]

And Iterapp will create a fresh new database for your app and add a schema for your app on Iterapps Postgres cluster.

Warning

Any values beneath a section-tag will relate to the section. So move properties that do not relate to any tag to the top of the file.

For instance:

[postgres]

applings = ["app1", "app2"]

will not work as applings property is semantically under postgres-section, which is incorrect. The easiest solution is to move [postgres] to the end of the file. `

Connect to your database

The following environment variables are available when you would want to connect to the schema:

PGHOST
PGPORT
PGDATABASE
PGUSER
PGPASSWORD
DB_DATABASE
DB_PASSWORD
DB_HOST
DB_PORT
DB_USERNAME
DATABASE_URL

Graceful shutdown

When your application receives SIGTERM, database connections are given 10 seconds to deregister before being dropped.

Connect to your database locally

So to connect to your database locally, you can use the DATABASE_URL environment variable in your app. Just change user, password, host, port and database to what corresponds to your local database setup:

DATABASE_URL = postgres://user:password@host:port/database

Create tables and add data

Your database is initially empty. Your app is responsible for both creating tables and adding data into them. There are several ways of doing this. One approach would be to add migration-sqls when the app starts.

Environments

You will have one database per environment, which means that production and in test have their own database.

Postgres Extensions

You can enable postgresql extensions in your app by adding something like this to iterapp.toml

[postgres]
extensions=["uuid-ossp"]

We support all postgresql extensions supported by Google Cloud SQL

Note that when removing extensions from the list, they will not yet be removed from postgres if they are already deployed. This will probably be fixed at some later time.

Connect to your database on Google Cloud

In addition to the above, you can also connect to your own database on google cloud. To get started:

First you need to create your cloud SQL database in google-cloud. Notice your connection string (it looks something like app-iterate:europe-west1:app-iterate)

You need to give the service account cloud-sql-connect@app-iterate.iam.gserviceaccount.com access to connect to the database. To do that, add it to IAM with the Cloud SQL Client role.

Then add the following to iterapp.toml

[cloud_sql_postgres_instances]
5432="iterate-vake:europe-west1:vake-cloud-sql"

You will then be able to connect to your cloud sql instance on localhost:5433.

You also need credentials to login to the database, the above will only create the network-level connection. You should encrypt the credentials and add them as environment variables (see secrets).

Use both your own and iterapp db

If you want to connect to both to the db from iterapp and your own cloud sql instance, you can add both [postgres] and [cloud_sql_postgres_instances]. iterapp postgres will always listen on port 5432, so if you try to use that as a port number in cloud_sql_postgres_instances, you will get an error.