Import an existing RDS or Aurora database

When deploying applications to your own cloud, the Encore platform can provision all necessary infrastructure, including the database server. However, if you already have an Amazon RDS for PostgreSQL DB instance or an Amazon Aurora PostgreSQL DB cluster, you can connect your Encore application directly to it instead.

Benefits

Using an existing database allows you to:

• Maintain data continuity with your existing systems
• Preserve specific database configurations
• Utilize familiar database setups without migration

Requirements

The database server you import must:

• Run PostgreSQL. It must be an RDS for PostgreSQL DB instance or an Aurora PostgreSQL DB cluster. Other RDS engines, such as MySQL, can't be imported.
• Have its master password managed by AWS Secrets Manager. Encore reads the master password from the secret RDS manages for it. If your database doesn't use this yet, modify it in the RDS console and enable Manage master credentials in AWS Secrets Manager. Doing so changes the master password, so update anything else that uses it.
• Be in a VPC. Encore reaches the database through its DB subnet group and security group. Aurora clusters created with express configuration aren't in a VPC and can't be moved into one. To import one, restore a snapshot of it as a cluster with full configuration in a VPC, with the master password managed by Secrets Manager, and import the restored cluster.
• Be in the AWS account and region of the environment. The Encore IAM role must be allowed to describe it (rds:DescribeDBInstances, and rds:DescribeDBClusters for an Aurora cluster) and read its master user secret.

For an Aurora cluster, import the cluster itself rather than one of its DB instances.

Importing a database

You import a database when you create an environment:

1. Navigate to Create Environment in the Encore platform dashboard
2. Select the AWS cloud provider
3. Pick the AWS Region your database resides in
4. In the Database section, choose Import Existing Database
5. Enter the identifier of your RDS DB instance or Aurora DB cluster, as shown in the RDS console
6. Click the Resolve button to validate it

If the database can't be imported, the dashboard tells you which requirement it doesn't meet, with a link to it in the RDS console. Once it's resolved, you can create the environment. When you deploy to this environment, the Encore platform connects your application to the imported database rather than provisioning a new one.

What Encore manages

An imported database server remains yours. Encore doesn't change its settings and never deletes it, nor its subnet group, security group, or parameter groups. For an Aurora cluster, Encore keeps the DB instances the cluster already has.

To connect your application to it, Encore:

• Adds an inbound rule to the database's security group allowing your services to connect on the database port, and the network ACL rules to go with it.
• Sets up VPC peering between the environment's VPC and the database's VPC, if they're different. The two VPCs' IP ranges must not overlap, so pick a free range for the environment's VPC when you create it if needed.
• Runs Emissary, a secure SOCKS proxy, in the environment's compute, and allows it to connect to the database the same way as your services. The Encore platform reaches the database through Emissary to create roles and databases and run your migrations, so the database doesn't have to be reachable from the internet.
• Creates the PostgreSQL roles it needs, using the master user, and a database for each database your application declares that doesn't exist yet.

When you delete the environment, Encore removes the roles, grants, and databases it created. Anything that was there before stays.

Mapping existing databases to your Encore app

To access an existing database in your Encore application, you need to specify the name of the existing database when you declare the database in your app. For example, if you have an existing database called mydb you can create a reference to it like so:

const db = new SQLDatabase("mydb");

sqldb.NewDatabase("mydb", sqldb.DatabaseConfig{
	Migrations: "./migrations",
})

Encore leaves databases, roles, role memberships, and grants that already exist on the server alone: it uses them, but never modifies, revokes, or deletes them.

Roles

Encore sets up the same role hierarchy on an imported server as on one it creates: encore_platform for itself, a reader, writer and admin role per database, a role per service, and a login role per container. It creates them through the master user, which ends up a member of encore_platform. The roles your application connects with only get access to the databases it declares, never to the master user or to other databases on the server.

If a role with one of these names already exists and can't log in, Encore uses it as it is and won't change or drop it. Your own roles are otherwise left alone, and none of them is granted to Encore's roles, except a database's owner as described below.

Table ownership

An existing database's tables are usually owned by the role that created them, often the master user, and not by Encore. So that your services can read and write them and your migrations can alter them, Encore runs your migrations as the database's owner, and grants your services access to the tables in every schema on each deploy:

• If the database is owned by the master user, Encore connects as the master user to run migrations and apply grants.
• If it's owned by another role, Encore grants that role to its own platform role, and revokes it again when the environment is deleted. Encore never grants the owner to the roles your application connects with.
• If it's owned by a superuser, Encore leaves it alone. Grant your services access yourself.

Owning a database doesn't by itself allow a role to alter tables, or grant access to them, if another role owns those tables. Only a table's owner, a member of the owner role, or a superuser can do that. So before you deploy, check who owns the tables your migrations alter and your services use (for example with \dt *.* in psql). If any are owned by a role other than the database's owner, make the database's owner a member of that role, or change the tables' owner to the database's owner. Otherwise those migrations and grants will fail.

Applying migrations to existing databases

Encore uses a table called schema_migrations in the public namespace to keep track of which migrations have been applied. If you import an existing database without that table, Encore will create it for you and apply your migrations in order. If the table already exists, Encore expects it to contain exactly two columns:

version bigint
dirty boolean

If the table exists but has a different schema, you will not be able to import it with Encore at this time. If the table exists with an existing entry, Encore will apply all higher versions in your migrations directory to the database.

Bootstrap migration for existing databases

If you have an existing production database that wasn't previously managed by Encore's migration system, you need to set up a bootstrap migration so that new environments (dev, staging, etc.) are created with the same schema, while your existing database is not modified.

Step 1: Dump your existing database schema:

$ pg_dump --schema-only --no-owner --no-privileges your_database > 001_bootstrap.up.sql

Review the output and clean it up as needed (remove any database-specific settings, comments, etc.), then place it in your service's migrations directory.

Step 2: In your existing imported database, manually create the schema_migrations table and mark the bootstrap migration as already applied:

CREATE TABLE IF NOT EXISTS schema_migrations (version bigint NOT NULL, dirty boolean NOT NULL);
INSERT INTO schema_migrations (version, dirty) VALUES (1, false);

This tells Encore that migration 1 (the bootstrap) has already been applied to this database. Encore will skip it and only run migrations with a higher version number.

Step 3: Any future schema changes should be added as new migration files (e.g. 002_add_column.up.sql). These will be applied to all environments, including the imported database.