electric-sql / electric-sql/pglite

`useCopy` doesn't support `JSONB` or arrays

Open
#446 6 comments 0 reactions 0 assignees View on GitHub
Dominant language
TypeScript
Stars
16k
Forks
442
Avg merge
20h 19m
Merged PRs (30d)
7

Description

I am unable to build the project after trying for a couple of hours (wasm docker won't build with the scripts, at least on my machine) and it will require a few tests, but the following does what I need it to - it allows for arrays of simple types and `JSONB`. I spent far, far too long trying to get `JSONB[]` to import but in the end abandoned it. `JSONB` now probably has all the methods you might want that previously only had array versions, so maybe left as a challenge to the reader...

I tried to create a nix flake for building the project but wasn't able to get wasm building using the scripts.

```typescript
function toCopyable(value: any, surround = '"') {
// Escape double quotes and wrap in quotes if necessary
if (typeof value === "string" && (value.includes(",") || value.includes('"') || value.includes("\n"))) {
return `${surround}${value.replace(/"/g, '""')}${surround}`;
}
return value === null ? "\\N" : value;
}

function baseValue(value: any) {
return typeof value === "object" && value !== null && !Array.isArray(value) ? JSON.stringify(value) : value;
}

async function applyMessagesToTableWithCopy({
pg,
table,
schema = "public",
messages,
mapColumns,
debug,
}: ApplyMessagesToTableWithCopyOptions) {
if (debug) console.log("applying messages with COPY");

// Map the messages to the data to be inserted
const data: Record[] = messages.map((message) =>
mapColumns ? doMapColumns(mapColumns, message) : message.value,
);

// Get column names from the first message
const columns = Object.keys(data[0]);

// Create CSV data
const csvData = data
.map((message) => {
return columns
.map((column) => {
const value = baseValue(message[column]);
if (Array.isArray(value)) {
const vals: any[] = [];
let hasObjects = false;
for (const val of value) {
vals.push(toCopyable(baseValue(val), ""));
if (typeof val === "object") {
hasObjects = true;
}
}
return `"${hasObjects ? "[" : "{"}${vals.join(",")}${hasObjects ? "]" : "}"}"`;
}
return toCopyable(value);
})
.join(",");
})
.join("\n");
const csvBlob = new Blob([csvData], { type: "text/csv" });
const sql = `
COPY "${schema}"."${table}" (${columns.map((c) => `"${c}"`).join(", ")})
FROM '/dev/blob'
WITH (FORMAT csv, NULL '\\N')
`;
// Perform COPY FROM
await pg.query(sql, [], {
blob: csvBlob,
});

if (debug) console.log(`Inserted ${messages.length} rows using COPY`);
}

```

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.