Migrating from FaunaDB to Neon Postgres

Neon, like Fauna, offers a serverless architecture—but it’s built on Postgres. That means you get the scalability of serverless along with the reliability and familiarity of a proven SQL database.

This guide is designed to help FaunaDB users understand how to transition to Neon Postgres.

FaunaDB vs. Neon (Postgres)

Before diving into the migration process, it's important to understand the fundamental differences between FaunaDB and Neon (Postgres). While both are databases, they operate with distinct paradigms:

Migration steps

The migration process from FaunaDB to Neon Postgres involves several key steps, each essential for a successful transition. These steps include exporting your data and schema from FaunaDB, translating your schema to Postgres DDL, importing your data into Neon, and converting your queries from FQL to SQL. Let's break down these steps in detail:

Step 1: Exporting data from FaunaDB

If you are on a paid FaunaDB plan, you can utilize the database's export functionality to save data in JSON format directly to an Amazon S3 bucket. Fauna CLI can be used to export data to S3

fauna export create s3 \
  --database <database_name> \
  --collection <collection_name> \
  --bucket <s3_bucket_name> \
  --path <s3_bucket_path> \
  --format simple

For smaller datasets, you can export data directly to your local filesystem using FQL. The following script demonstrates exporting data from FaunaDB collections as JSON files. For example, here's a Node.js script that exports data from specific collections (e.g., Product, Category) to JSON files (e.g., Product.json, Category.json):

import { Client, fql, FaunaError } from "fauna";
import fs from "fs";

// Route queries to a specific database
// using the authentication secret in
// the `FAUNA_SECRET` environment variable.
const client = new Client();

// Specify the collections to export.
// You can retrieve a list of user-defined collections
// using a `Collection.all()` query.
const collectionsToExport = ["Product", "Category"];

// Loop through the collections.
for (const collectionName of collectionsToExport) {
  try {
    // Compose a query using an FQL template string.
    // The query returns a Set containint all documents
    // in the collection.
    const query = fql`
      let collection = Collection(${collectionName})
      collection.all()`;

    // Run the query.
    const pages = client.paginate(query);

    // Iterate through the resulting document Set.
    const documents = [];
    for await (const page of pages.flatten()) {
      documents.push(page);
    }

    // Convert the 'documents' array to a JSON string.
    const jsonData = JSON.stringify(documents, null, 2);

    // Write the JSON string to a file named `<collectionName>.json`.
    fs.writeFileSync(`${collectionName}.json`, jsonData, "utf-8");

    console.log(
      `${collectionName} collection data written to ${collectionName}.json`
    );
  } catch (error) {
    if (error instanceof FaunaError) {
      console.error(`Error exporting ${collectionName}:`, error);
    } else {
      console.error(
        `An unexpected error occurred for ${collectionName}:`,
        error
      );
    }
  }
}

client.close();

You can learn more about exporting data from FaunaDB in the official documentation.

For example, here's a sample JSON file for the exported Product collection:

[
  {
    "data": [
      {
        "id": "426122714129891392",
        "coll": "Product",
        "ts": "2025-03-22T10:58:58.290Z",
        "name": "cups",
        "description": "Translucent 9 Oz, 100 ct",
        "price": 698,
        "stock": 100,
        "category": {
          "id": "426122714117308480",
          "coll": "Category"
        }
      },
      .... other records
  }
]

Step 2: Exporting FaunaDB schema (FSL schema pull)

In addition to your data, you'll need to export your FaunaDB schema, defined in Fauna Schema Language (FSL).

Using fauna schema pull command:

The fauna schema pull command in the Fauna CLI is used to export your database schema to a local directory.

Fauna CLI command:

fauna schema pull \
  --database <your_faunadb_database_name> \
  --dir <local_schema_directory> \
  --active

For example, here's a sample FSL schema file collections.fsl exported from FaunaDB:

collection Product {
  name: String
  description: String
  // Use an Integer to represent cents.
  // This avoids floating-point precision issues.
  price: Int
  category: Ref<Category>
  stock: Int

  // Use a unique constraint to ensure no two products have the same name.
  unique [.name]
  check stockIsValid (product => product.stock >= 0)
  check priceIsValid (product => product.price > 0)

  index byCategory {
    terms [.category]
  }

  index sortedByCategory {
    values [.category]
  }

  index byName {
    terms [.name]
  }

  index sortedByPriceLowToHigh {
    values [.price, .name, .description, .stock]
  }
}

Step 3: Schema translation - FSL to Postgres DDL

Begin by thoroughly examining the exported Fauna Schema Language (FSL) files. This step is crucial for gaining a comprehensive understanding of your FaunaDB schema structure. Pay close attention to the definitions of collections, their associated fields, indexes, and constraints.

For instance, the Product collection, as shown in the above example collections.fsl file, includes fields like name, description, price, category, and stock. The schema also specifies unique and check constraints for data integrity, along with indexes to optimize query performance.

Once you have a clear grasp of your exported FSL schema, the next step involves translating it into Postgres Data Definition Language (DDL). This translation process is necessary to create equivalent tables and indexes within your Postgres database. By accurately converting your FaunaDB schema into DDL, you ensure a smooth transition and maintain the structural integrity of your data during migration.

If you need a refresher on Postgres, you can refer to Neon's PostgreSQL Tutorial.

Key translation considerations:

• Collections to tables: Each FaunaDB collection in your FSL schema could become a Neon Postgres table.
• Field definitions to columns: FaunaDB field definitions will guide your Neon Postgres column definitions. Pay attention to data types like String, Number, Time, Ref, and optionality (? for nullable).
• Unique constraints: Translate FaunaDB unique constraints in FSL to UNIQUE constraints in your Postgres CREATE TABLE statements.
• Indexes: Translate FaunaDB index definitions in FSL to CREATE INDEX statements in Postgres. Consider the terms and values of FaunaDB indexes to create effective Postgres indexes.
• Computed fields/functions: FaunaDB's more advanced schema features like compute, functions will require careful consideration for translation. Computed fields might translate to Postgres views or computed columns. UDFs will likely need to be rewritten as stored procedures or application logic.

Example FSL to Postgres DDL translation

Let's consider the Category collection from the FSL schema and translate it to a categories table in Neon Postgres. Here's the FSL schema for the Category collection:

collection Category {
  name: String
  description: String
  compute products: Set<Product> = (category => Product.byCategory(category))

  unique [.name]

  index byName {
    terms [.name]
  }
}

Neon Postgres DDL (Translated):

Here's how you can translate the Category collection to a categories table with the necessary constraints and indexes:

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  -- Constraints
  CONSTRAINT unique_category_name UNIQUE (name)
);

-- Indexes (create indexes after data migration if possible for speeding up data import)
CREATE INDEX idx_categories_name ON categories(name);

Now let's consider the Product collection from the FSL schema and translate it to a products table in Neon Postgres. Here's the FSL schema for the Product collection:

collection Product {
  name: String
  description: String
  price: Int
  category: Ref<Category>
  stock: Int

  unique [.name]
  check stockIsValid (product => product.stock >= 0)
  check priceIsValid (product => product.price > 0)

  index byCategory {
    terms [.category]
  }

  index sortedByCategory {
    values [.category]
  }

  index byName {
    terms [.name]
  }

  index sortedByPriceLowToHigh {
    values [.price, .name, .description, .stock]
  }
}

Neon Postgres DDL (Translated):

Now that you have a categories table created in Neon Postgres, here's how you can translate the Product collection to a products table with the necessary constraints, references and indexes:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price INT NOT NULL,
  category_id INT NOT NULL,
  stock INT NOT NULL,
  -- Constraints
  CONSTRAINT unique_product_name UNIQUE (name),
  CONSTRAINT stock_valid CHECK (stock >= 0),
  CONSTRAINT price_valid CHECK (price > 0),
  -- Foreign key
  CONSTRAINT fk_category
    FOREIGN KEY (category_id)
    REFERENCES categories(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

-- Indexes (create indexes after data migration if possible for speeding up data import)
CREATE INDEX idx_products_category ON products(category_id); 
CREATE INDEX idx_products_price_asc ON products(price) INCLUDE (name, description, stock);

Here we are adding a foreign key constraint fk_category to ensure that the category_id in the products table references the id column in the categories table. This constraint enforces referential integrity between the two tables.

Step 4: Data import to Neon Postgres

With your Neon Postgres database ready and your data exported from FaunaDB, the next step is to import this data into your newly created tables.

For this guide, we'll demonstrate importing data from the product.json file (exported from FaunaDB) into the products table in Neon Postgres.

This example Node.js script reads the Product.json file, parses the JSON data, and then generates and executes INSERT statements to populate your products table in Neon Postgres.

You can get NEON_CONNECTION_STRING from your Neon dashboard. Learn more about Connecting Neon to your stack

import pg from 'pg';
import fs from 'fs';

const { Client } = pg;

async function importProducts() {
    const neonConnectionString = process.env.NEON_CONNECTION_STRING;
    const client = new Client({ connectionString: neonConnectionString });

    try {
        await client.connect();
        const rawData = fs.readFileSync('Product.json');
        const productData = JSON.parse(rawData);

        // Start transaction
        await client.query('BEGIN');

        // Build a bulk insert query for a batch of records
        const insertValues = [];
        const placeholders = [];

        productData[0].data.forEach((product, index) => {
            const { name, description, price, stock, category } = product;
            const categoryId = Number(category.id);

            const offset = index * 5;
            placeholders.push(`($${offset + 1}, $${offset + 2}, $${offset + 3}, $${offset + 4}, $${offset + 5})`);
            insertValues.push(name, description, price, stock, categoryId);
        });

        const insertQuery = `
          INSERT INTO products (name, description, price, stock, category_id)
          VALUES ${placeholders.join(', ')}
        `;

        await client.query(insertQuery, insertValues);
        await client.query('COMMIT');
        console.log('Products imported successfully!');

    } catch (error) {
        console.error('Error during product import:', error);
        await client.query('ROLLBACK');
    } finally {
        await client.end();
    }
}

importProducts();

You can adapt this script to import data from other collections by adjusting the file paths, table names, and data transformations as needed.

Step 5: Query conversion - FQL to SQL

This is a critical step in the migration process, as it involves converting your application's FaunaDB queries (written in Fauna Query Language - FQL) to equivalent SQL queries.

Here are some key translation patterns to consider when converting Fauna's FQL to Postgres SQL:

Retrieving all documents from a collection

For example, to retrieve all documents from the Product collection using FQL:

let collection = Collection("Product")
collection.all()

Assuming you have ported 'Product' collection to 'products' table in Neon Postgres, the equivalent SQL query would be:

SELECT * FROM products;

Filtering data - Simple WHERE clause

For example, to filter products by name in FQL:

Product.where(.name == 'avocados');

The equivalent SQL query would be:

SELECT * FROM products WHERE name = 'avocados';

Filtering data - Nested field WHERE clause (Joins equivalent)

For example, to filter products by category name in FQL:

Product.where(.category?.name == "party")

The equivalent SQL query would involve a join between the products and categories tables:

SELECT p.*
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE c.name = 'party';

Ordering data

For example, to order products by price in ascending order:

Product.all().order(.price)

The equivalent SQL query would be:

SELECT * FROM products ORDER BY price ASC;

Counting documents

For example, to count all documents in the Product collection in FQL:

Product.all().count();

The equivalent SQL query would be:

SELECT COUNT(*) FROM products;

Aggregations

For example, to calculate the total stock count of all products in FQL:

let stockCounts = Product.all().map(doc => doc.stock )
stockCounts.aggregate(0, (a, b) => a + b)

The equivalent SQL query would be:

SELECT SUM(stock) FROM products;

Filtering data - AND and OR conditions

For example, to filter products that are both priced above $10 AND have less than 50 units in stock in FQL:

Product.where(.price > 10 && .stock < 50)

The equivalent SQL query to achieve the same filtering would be:

SELECT * FROM products WHERE price > 10 AND stock < 50;

For example, to filter products that are either in the "party" category OR priced below 20 in FQL:

Product.where(.category?.name == "party" || .price < 20)

The equivalent SQL query, involving a join to access the category name, would be:

SELECT p.*
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE c.name = 'party' OR p.price < 20;

Updating a document

For example, to update the description of a product named "avocados" in FQL:

let productRef = Product.where(.name == 'avocados').first()
Product.byId(productRef!.id)!.update({ description: "Fresh, ripe avocados from California" })

The equivalent SQL query to update the same product description would be:

UPDATE products
SET description = 'Fresh, ripe avocados from California'
WHERE name = 'avocados'
LIMIT 1;

Deleting a document

For example, to delete a product named "pizza" in FQL:

let productRef = Product.where(.name == 'pizza').first()
Product.byId(productRef!.id)!.delete()

The equivalent SQL query to delete the same product would be:

DELETE FROM products
WHERE name = 'pizza'
LIMIT 1;

Inserting a new document

For example, to insert a new product "Organic Strawberries" in FQL, linking it to the "produce" category:

Product.create({
  name: "Organic Strawberries",
  price: 699,
  stock: 150,
  description: "Fresh strawberries",
  category: Category.where(.name == "produce").first()
})

The equivalent SQL query to insert the same product and link it to the "produce" category would be:

INSERT INTO products (name, price, stock, description, category_id)
VALUES ('Organic Strawberries', 699, 150, 'Fresh strawberries', (SELECT id FROM categories WHERE name = 'produce'))
RETURNING *;

Actionable steps for query conversion:

1. Review application queries: Identify the key queries in your application that interact with FaunaDB.
2. Translate FQL to SQL (focus on key queries): Translate these key FQL queries into equivalent SQL queries, focusing on the patterns shown in the examples above.
3. Test SQL queries: Test your translated SQL queries against your Neon Postgres database to ensure they function correctly, return the expected data, and are performant. You might need to use EXPLAIN ANALYZE in Postgres to analyze query performance and optimize indexes if needed.

Resources

• The Future of Fauna
• Migrate off Fauna
• Modernizing from PostgreSQL to Serverless with Fauna: Part 1 Part 2 Part 3