Skip to main content

Using PostgreSQL Extensions with Atlas

Question​

How do I manage PostgreSQL extensions with Atlas?

Answer​

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

Atlas Pro Feature

Extensions are available only to Atlas Pro users. To use this feature, run:

atlas login

If using a SQL or HCL file for your schema definition, an extension can be created by adding the appropriate SQL or HCL command.

For example, to use the pgvector extension:

schema.sql
CREATE EXTENSION IF NOT EXISTS pgvector;

You must also ensure that your dev database is properly configured to support the extension. See this FAQ for more information.

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

Example​

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.