Copy To

Problem

In backend applications with Vapor, we often need to export large volumes of data from the database to files for different purposes: backups, offline analysis, integration with external systems, or data audits.

Using traditional queries with Fluent and then manually serializing the results presents several drawbacks:

High memory consumption: loading thousands of records into memory to process them one by one.
Slow processing: manual serialization to CSV requires iterating and formatting each record.
Lack of optimization: doesn’t leverage the native export capabilities of the database engine.
Unnecessary complexity: manual management of formats, character escaping, and null value handling.

For bulk export scenarios, we need a strategy that leverages PostgreSQL’s native capabilities to generate files efficiently.

Solution

We extend Database with a function that executes the COPY … TO command from PostgreSQL, allowing data export directly from the table schema to CSV files in the filesystem.

extension Database {
    func exportCSV(
        _ model: any Model.Type,
        file: PathEnum)
        async throws {
        let query = SQLQueryString(
            """
            COPY \"\(unsafeRaw: model.space ?? "public")\".
            \"\(unsafeRaw: model.schema)\"
            TO '\(unsafeRaw: file.rawValue)'
            WITH (FORMAT csv, HEADER true, DELIMITER ',',
            QUOTE '"', ESCAPE '"', NULL '')
            """
        )

        try await self.sqlDatabase
            .raw(query).run()
    }
}

Key points:

Uses PostgreSQL’s COPY … TO, the most efficient method for bulk exports.
The model.space parameter supports custom schemas (defaults to “public”).
model.schema automatically obtains the table name from the Fluent model.
Standard CSV configuration: headers included, delimiters, and proper null value handling.
Uses unsafeRaw for direct interpolation in the SQL query.

Result

func exportRegions() async throws {
    try await db.exportCSV(
        LocationRegionModel.self,
        file: file
    )
}

Benefits of this approach:

🚀 Optimal performance: COPY … TO is much faster than manual serialization.

💾 Resource efficiency: PostgreSQL writes directly to the file without loading data into application memory.

📦 Consistent format: the database engine guarantees a valid CSV with proper escaping.

🔧 Native integration: leverages optimized capabilities of the PostgreSQL engine.

📊 Scalability: allows exporting millions of records without impacting application performance.

This solution is the perfect complement to importCSV, forming a pair of functions that enables bidirectional data movement between PostgreSQL and the filesystem efficiently and reliably. If you haven’t seen the import side yet, I explain how to build it using PostgreSQL’s COPY FROM in Copy From.

Keep coding, keep running 🏃‍♂️

Copy To

Table of contents

Problem

Solution

Result