Migrations with csv / json file

[MarkLee425]

Hey, just wondering if there is any plan to add the feature of csv / json file importing / migration to postgres in this package? Thanks.

[adrenalin]

CSV/text/binary is rather straightforward as long as you read it from STDIN and not trying to access a file, because the file has to be accessible from server, not from the client

I can implement this, since I also need this from time to time to populate tables that I use as flexible and externally accessible enums. All that is needed is to write this type of SQL, so it's really not overly difficult to do:

COPY target_table FROM STDIN CSV HEADER DELIMITER ';' QUOTE '"';
"id";"name";"date"
1;"foo";"2025-07-03"
2;"bar";"2025-07-03"
\.

Documentation gives immediately the usable options

With JSON pure SQL approach is rather difficult, but still theoretically possible, since JSON(B) can be parsed from a string by typecasting with ::JSONB in a CTE. Here is a working example (and surely there are other alternatives as well):

BEGIN;
    CREATE TEMPORARY TABLE target_table (
        id INT,
        v1 TEXT,
        v2 DATE
    ) ON COMMIT DROP;

    WITH source_data(data_as_json) AS (
        VALUES ('
            [
                {
                    "id": 1,
                    "v1": "foobar 1",
                    "v2": "2025-06-03T21:00:00.000+03:00"
                },
                {
                    "id": 2,
                    "v1": "foobar 2",
                    "v2": "2025-06-03T22:00:00.000+03:00"
                }
            ]
        '::JSONB)
    )
    INSERT INTO target_table (id, v1, v2)
    SELECT
        x.*
    FROM
        source_data, jsonb_to_recordset(source_data.data_as_json) AS x(id INT, v1 TEXT, v2 DATE);

    SELECT * FROM target_table;
END;

A very big issue is that the script has to know intended columns and their types, so it would mean either heuristics or then unnecessarily clumsy, so this would in practice be best done with separate or grouped INSERT queries.

[adrenalin]

Now I am actually not sure if you meant if it was possible to define the migrations as CSV/JSON rather than injecting data. Which was it? :)

[MarkLee425]

Yes, my bad for unclear explanation. I mean injecting data inside postgres.
My current solution is to do sth like this:

async function seedData(pgm: MigrationBuilder): Promise<void> {
	const sqlFiles = [
		{
			path: path.join(__dirname, '..', 'data', 'admin_user_init.sql'),
			name: 'admin_user_init.sql',
		},
		{
			path: path.join(__dirname, '..', 'data', 'blog_init.sql'),
			name: 'blog_init.sql',
		},
	];

	pgm.sql('BEGIN;');

	for (const file of sqlFiles) {
		try {
			const content = await fs.readFile(file.path, 'utf8');
			if (!content.trim()) {
				throw new Error(`SQL file ${file.name} is empty`);
			}
			pgm.sql(content);
		} catch (error) {
			console.error(`Failed to read or execute ${file.name}:`, error);
			throw error;
		}
	}

	pgm.sql('COMMIT;');
}

What I am thinking is that it can be a function that can be simply imported from node-pg-migrate and automatically injecting sql files inside postgres db.

[Shinigami92]

I'm not 💯% sure, but as far as I know node-pg-migrate also support .sql files directly

So you could do something like this:

migrations/
  1_schema.ts
  2_admin_user_init.sql
  3_blog_init.sql
  4_something_different.ts

I have not tested this yet, and I'm also not sure if we support mixed migration files (yet), but if this is not working, I'm happy to receive PRs (@brenoepics 👀)
see our discussion over in #1387 (reply in thread)