Skip to main content

Set up the database

Provision a database engine for Erde and point the server at it. Erde supports two engines — PostgreSQL with the PostGIS extension, or Microsoft SQL Server — and you supply the connection during the first-run setup wizard, not by hand-editing config files.

Before you start
  • Prerequisites: Erde must be installed, and a database server must be reachable from the Erde host. You provide the connection in the setup wizard, so have the host, port, and credentials ready.
  • Database names are fixed: Erde uses two databases, erde_app (domain data) and erde_platform (identity, settings, and tenancy). The setup wizard does not let you change these names.

Choose a provider​

Pick one engine per deployment and set the config key Database:Provider to its value. The setup wizard writes this key for you when you choose the provider.

EngineDatabase:Provider valueExtra requirement
PostgreSQLpostgresqlThe PostGIS extension (Erde creates it)
Microsoft SQL ServersqlserverA case-insensitive database collation

postgresql and sqlserver are the only accepted values, compared case-insensitively. Any other value stops the server at startup.

Engine versions

Erde's development environment is tested against PostgreSQL 17 with PostGIS and SQL Server 2022. No specific engine version is enforced by the server; use a version your organization supports.

Steps​

Follow the section for the engine you chose. In both cases you finish by entering the connection in the first-run setup wizard, which tests it, creates the two databases, runs the migrations, and seeds the initial data.

PostgreSQL​

  1. Install and start PostgreSQL on a host the Erde server can reach.

  2. Ensure the PostGIS extension is available to the server (install the PostGIS package for your PostgreSQL build). Erde stores spatial data — locations, site boundaries — and PostGIS is required.

  3. Create a login role Erde can connect with. The role must be able to create databases and create extensions, because Erde creates erde_app and erde_platform and enables the postgis and citext extensions on first run.

  4. In the setup wizard, select the PostgreSQL provider and enter the host, port, and credentials.

    FieldWhat to enter
    ProviderSelect PostgreSQL (Erde records postgresql)
    ServerThe PostgreSQL host name or address
    PortThe PostgreSQL port
    UsernameThe login role's username
    PasswordThe login role's password

  5. Select Test Connection, then complete the wizard. On apply, Erde runs CREATE EXTENSION IF NOT EXISTS citext; and CREATE EXTENSION IF NOT EXISTS postgis;, then migrates and seeds both databases.

SQL Server​

  1. Install and start SQL Server on a host the Erde server can reach.

  2. Make sure the database's collation is case-insensitive. Erde sets no collation of its own and relies on a case-insensitive database collation so that codes and names compare and stay unique regardless of case. Confirm the database has not been set to a case-sensitive (CS) collation.

  3. Provide a login Erde can connect with. The login must be able to create databases, because Erde creates erde_app and erde_platform on first run. Erde connects with either SQL authentication (username and password) or Windows Integrated Security.

  4. In the setup wizard, select the SQL Server provider and enter the connection details.

    FieldWhat to enter
    ProviderSelect SQL Server (Erde records sqlserver)
    ServerThe SQL Server host name or instance
    PortThe SQL Server port
    AuthenticationSelect SQL Authentication or Windows Authentication
    UsernameFor SQL authentication, the login username
    PasswordFor SQL authentication, the login password

  5. Select Test Connection, then complete the wizard. Erde sets TrustServerCertificate on the connection, so a self-signed server certificate does not block setup. On apply, Erde migrates and seeds both databases.

Which account Windows Authentication uses

When you pick Windows Authentication above, Erde connects to SQL Server as the identity its host process runs as — LocalSystem in both the Windows Service and IIS hosting shapes, which on a domain presents as the server's machine account (DOMAIN\SERVER$). Create a SQL Server login for that machine account and grant it permission to create databases. SQL Authentication instead uses the username and password you enter in the wizard.

Result​

Erde holds the connection in the production configuration and resolves the active application database per request. After setup completes, both databases exist and are migrated:

  • erde_platform — identity, system settings, and tenancy.
  • erde_app — your domain data (sites, samples, lab results, and so on).

On PostgreSQL, both the citext and postgis extensions are created in each database (erde_app and erde_platform) on first run. On both engines, text columns compare case-insensitively: PostgreSQL maps text columns to citext, and SQL Server relies on its case-insensitive collation.

Troubleshooting​

SymptomCauseResolution
Server stops at startup: Database:Provider not configured.The provider key is missing from configurationRe-run the setup wizard, or confirm Database:Provider is set to postgresql or sqlserver.
Server stops at startup: Unsupported database provider: <value>Database:Provider is set to a value other than postgresql or sqlserverUse one of the two accepted values exactly; nothing else is valid.
Setup fails with a PostGIS or postgis extension errorPostGIS is not installed on the PostgreSQL server, or the login role cannot create extensionsInstall the PostGIS package on the database host and grant the role permission to create extensions, then re-run setup.
Connection test failsWrong host, port, or credentials; the database server is unreachableRe-check the values against your database server, and confirm the Erde host can reach it over the network.
Codes or names that differ only by case are treated as duplicates or as distinct unexpectedly (SQL Server)The SQL Server database is using a case-sensitive (CS) collationUse a database with a case-insensitive default collation.