Skip to main content

Using PostgreSQL Extensions with Atlas

To include a PostgreSQL extension in your schema, there are a few configurations to be made to ensure the extension is properly applied.

If using a SQL or HCL file for your schema definition, an extension can be created by adding the appropriate SQL or HCL command. To use the pgvector extension as an example:

schema.sql
CREATE EXTENSION IF NOT EXISTS pgvector;

Additionally, extensions are database-level objects, so they are installed at the database level. Therefore, when working with extensions, the scope of migrations will be set to the database and objects such as tables are qualified with the schema name.

Let's use the following SQL schema of a PostgreSQL database as an example for putting this all together:

In this schema, we use the uuid_generate_v4() function to create random unique ID numbers. This is not a core PostgreSQL function, so the uuid-ossp extension must be added.

schema.sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE SCHEMA IF NOT EXISTS "public";

-- Table to store customers, qualified with schema name
CREATE TABLE public.customers (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
);

-- Table to store products, qualified with schema name
CREATE TABLE public.products (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
);

-- Table to store orders, qualified with schema name
CREATE TABLE public.orders (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    customer_id UUID REFERENCES customers(id) ON DELETE CASCADE,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(50) DEFAULT 'Pending'
);
Using an ORM

If you are using an ORM with your extension, follow our guide for using extensions and SQLAlchemy for further guidance.

If you are still running into issues, feel free to reach out on our Discord server.