Using Postgres Extensions in GORM
Postgres extensions are add-on modules that extend the functionality of the database by providing new data types, operators, functions, procedural languages, and more.
This guide explains how to define a schema field that uses a data type provided by the PostGIS extension, and configure the schema migration to manage both Postgres extension installations and the GORM models as a single migration unit using Atlas.
Atlas support for Extensions is available exclusively to Pro users. To use this feature, run:
atlas login
Getting started with Atlas and GORM
Before we continue to extensions, ensure you have installed the Atlas GORM Provider on your GORM project.
To set up, follow along the getting started guide for GORM and Atlas.
Composite Schema
The GORM package is mostly used for defining tables (our Go types) and interacting with the database. Extensions like
postgis or hstore do not have representation in GORM. A Postgres extension can be installed once in your
Postgres database, and may be used multiple times in different schemas.
In order to extend our PostgreSQL schema migration to include both extensions and our GORM types, we configure Atlas to read the state of the schema from a Composite Schema data source. Follow the steps below to configure this for your project:
1. Create a schema.sql that defines the necessary extensions used by your database. In the same way, you can define
the extensions in Atlas Schema HCL language:
- Using SQL
- Using HCL
-- Install PostGIS extension.
CREATE EXTENSION postgis;
schema "public" {}
extension "postgis" {
schema = schema.public
version = "3.4.2"
comment = "PostGIS geometry and geography spatial types and functions"
}
2. In your GORM model, define a field that uses the data type provided by the extension.
package models
import (
"database/sql/driver"
"fmt"
"gorm.io/gorm"
)
// GeometryPoint is a custom GORM type for PostGIS point
type GeometryPoint struct {
X float64 `json:"x"`
Y float64 `json:"y"`
}
// Scan implements the Scanner interface for GeometryPoint
func (g *GeometryPoint) Scan(val interface{}) error {
var point string
switch v := val.(type) {
case []byte:
point = string(v)
case string:
point = v
default:
return fmt.Errorf("cannot convert %T to GeometryPoint", val)
}
_, err := fmt.Sscanf(point, "POINT(%f %f)", &g.X, &g.Y)
return err
}
// Value implements the driver Valuer interface for GeometryPoint
func (g GeometryPoint) Value() (driver.Value, error) {
return fmt.Sprintf("POINT(%f %f)", g.X, g.Y), nil
}
type User struct {
gorm.Model
Location GeometryPoint `gorm:"type:geometry(POINT, 4326)"` // Using PostGIS geometry type
}
3. In your atlas.hcl config file, add a composite_schema that includes both your extensions defined in schema.sql and your GORM model:
data "composite_schema" "app" {
# Load first custom types first.
schema {
url = "file://schema.sql"
}
schema "public" {
url = data.external_schema.gorm.url
}
}
env "local" {
src = data.composite_schema.app.url
dev = "docker://postgis/latest/dev"
}
Usage
After setting up our composite schema, we can get its representation using the atlas schema inspect command, generate
schema migrations for it, apply them to a database, and more. Below are a few commands to get you started with Atlas:
Inspect the Schema
The atlas schema inspect command is commonly used to inspect databases. However, we can also use it to inspect our
composite_schema and print the SQL representation of it:
atlas schema inspect \
--env local \
--url env://src \
--format '{{ sql . }}'
The command above prints the following SQL.
-- Add new schema named "public"
CREATE SCHEMA IF NOT EXISTS "public";
-- Set comment to schema: "public"
COMMENT ON SCHEMA "public" IS 'standard public schema';
-- Create extension "postgis"
CREATE EXTENSION "postgis" WITH SCHEMA "public" VERSION "3.4.2";
-- Create "users" table
CREATE TABLE "public"."users" ("id" bigserial NOT NULL, "created_at" timestamptz NULL, "updated_at" timestamptz NULL, "deleted_at" timestamptz NULL, "location" public.geometry(point,4326) NULL, PRIMARY KEY ("id"));
-- Create index "idx_users_deleted_at" to table: "users"
CREATE INDEX "idx_users_deleted_at" ON "public"."users" ("deleted_at");
Although the SCHEMA argument is supported by the CREATE EXTENSION command, it only indicates where the extension's
objects will be installed. The extension itself is installed at the database level and cannot be loaded multiple times
into different schemas.
Therefore, to avoid conflicts with other schemas, when working with extensions, the scope of the migration should be set
to the database, where objects are qualified with the schema name. Hence, the search_path is dropped from the dev-database
URL in the atlas.hcl file.
Generate Migrations For the Schema
To generate a migration for the schema, run the following command:
atlas migrate diff \
--env local
Note that a new migration file is created with the following content:
-- Create extension "postgis"
CREATE EXTENSION "postgis" WITH SCHEMA "public" VERSION "3.4.2";
-- Create "users" table
CREATE TABLE "public"."users" ("id" bigserial NOT NULL, "created_at" timestamptz NULL, "updated_at" timestamptz NULL, "deleted_at" timestamptz NULL, "location" public.geometry(point,4326) NULL, PRIMARY KEY ("id"));
-- Create index "idx_users_deleted_at" to table: "users"
CREATE INDEX "idx_users_deleted_at" ON "public"."users" ("deleted_at");
Apply the Migrations
To apply the migration generated above to a database, run the following command:
atlas migrate apply \
--env local \
--url "postgres://postgres:pass@localhost:5432/database?sslmode=disable"
Sometimes, there is a need to apply the schema directly to the database without generating a migration file. For example, when experimenting with schema changes, spinning up a database for testing, etc. In such cases, you can use the command below to apply the schema directly to the database:
atlas schema apply \
--env local \
--url "postgres://postgres:pass@localhost:5432/database?sslmode=disable"
Or, using the Atlas Go SDK:
ac, err := atlasexec.NewClient(".", "atlas")
if err != nil {
log.Fatalf("failed to initialize client: %w", err)
}
// Automatically update the database with the desired schema.
// Another option, is to use 'migrate apply' or 'schema apply' manually.
if _, err := ac.SchemaApply(ctx, &atlasexec.SchemaApplyParams{
Env: "local",
URL: "postgres://postgres:pass@localhost:5432/database?sslmode=disable",
}); err != nil {
log.Fatalf("failed to apply schema changes: %w", err)
}