roob Posts - Answer Overflow

roob

•Created by roob on 8/28/2023 in #help

JSON with PostgreSQL not serializing correctly

I have define my field like this:

  hostnames: jsonb("hostnames").$type<string[]>().default([]),

  hostnames: jsonb("hostnames").$type<string[]>().default([]),

Which looks fine when I look at the table structure. Now I have the following object I'm creating/updating, which has a string[] as hostnames:

const obj = {
  name: 'foo',
  hostnames: [ 'foo.bar.com' ],
}

const obj = {
  name: 'foo',
  hostnames: [ 'foo.bar.com' ],
}

After this is added, the value in the database is escaped like this:

"[\"foo.bar.com\"]"

"[\"foo.bar.com\"]"

This does not look correct, it looks like wrongly serialized. Or what am I doing wrong here? The following SQL should extract the first value, however because it's wrongly serialized, it gives me the JSON serialization as the first value:

# select hostnames->>0 from assets limit 5;
              ?column?
════════════════════════════════════
 ["foo.bar.com"]

# select hostnames->>0 from assets limit 5;
              ?column?
════════════════════════════════════
 ["foo.bar.com"]

3 replies

DTDrizzle Team

•Created by roob on 8/7/2023 in #help

Do I need to close the DB connection after running a script using drizzle?

I have a very simple script to import some CSV data:

import { System } from "@foo/core/system";
import { readFileSync } from "fs";

const [, , csvPath] = process.argv;
const csv = readFileSync(csvPath, "utf8");
const lines = csv.split("\n");

for (let line of lines) {
  line = line.trim();

  if (line.length === 0) {
    continue;
  }

  let [identifier, name] = line.split(";");
  console.log(`Importing ${identifier} (${name})`);
  await System.create({ identifier, name });
}

// system/index.ts
export async function create(input: { name: string; identifier: string }) {
  return db.insert(systems).values(input).execute();
}

import { System } from "@foo/core/system";
import { readFileSync } from "fs";

const [, , csvPath] = process.argv;
const csv = readFileSync(csvPath, "utf8");
const lines = csv.split("\n");

for (let line of lines) {
  line = line.trim();

  if (line.length === 0) {
    continue;
  }

  let [identifier, name] = line.split(";");
  console.log(`Importing ${identifier} (${name})`);
  await System.create({ identifier, name });
}

// system/index.ts
export async function create(input: { name: string; identifier: string }) {
  return db.insert(systems).values(input).execute();
}

The script runs (executed with tsx) and I can see the rows inserted, but the script is not exiting. It just 'hangs' after this loop. Do I need to manually close the db connection?

25 replies

DTDrizzle Team

•Created by roob on 3/16/2023 in #help

Example failed to run: `pg` does not provide an export named `Pool`

I'm trying to run the example from the README file. I made a minial version, installed pg, @types/pg, drizzle-orm and drizzle-kit and tried to run it via tsx. This is my stripped down example:

import { drizzle } from "drizzle-orm/node-postgres";
import {
  InferModel,
  pgTable,
  serial,
  text,
  timestamp,
} from "drizzle-orm/pg-core";
import { Pool } from "pg";

export const domains = pgTable("domains", {
  id: serial("id").primaryKey(),
  domain: text("domain").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

export type Domain = InferModel<typeof domains>;
export type NewDomain = InferModel<typeof domains, "insert">;

const pool = new Pool({
  connectionString: "postgres://robin@localhost/ts-drizzle",
});
const db = drizzle(pool);

async function main() {
  // Insert
  const d: NewDomain = {
    domain: "foo.com",
  };
  const inserted = await db.insert(domains).values(d).returning();
  console.log(inserted);
}

main();

import { drizzle } from "drizzle-orm/node-postgres";
import {
  InferModel,
  pgTable,
  serial,
  text,
  timestamp,
} from "drizzle-orm/pg-core";
import { Pool } from "pg";

export const domains = pgTable("domains", {
  id: serial("id").primaryKey(),
  domain: text("domain").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

export type Domain = InferModel<typeof domains>;
export type NewDomain = InferModel<typeof domains, "insert">;

const pool = new Pool({
  connectionString: "postgres://robin@localhost/ts-drizzle",
});
const db = drizzle(pool);

async function main() {
  // Insert
  const d: NewDomain = {
    domain: "foo.com",
  };
  const inserted = await db.insert(domains).values(d).returning();
  console.log(inserted);
}

main();

This is my tsconfig.json:

{
  "compilerOptions": {
    "target": "es2016",
    "module": "commonjs",
    "esModuleInterop": true,
    "strict": true
  }
}

{
  "compilerOptions": {
    "target": "es2016",
    "module": "commonjs",
    "esModuleInterop": true,
    "strict": true
  }
}

Running it:

$ npx tsx main.ts
SyntaxError: The requested module 'pg' does not provide an export named 'Pool'
    at ModuleJob._instantiate (node:internal/modules/esm/module_job:123:21)
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at async ModuleJob.run (node:internal/modules/esm/module_job:189:5)
    at async Promise.all (index 0)
    at async ESMLoader.import (node:internal/modules/esm/loader:533:24)
    at async loadESM (node:internal/process/esm_loader:91:5)
    at async handleMainPromise (node:internal/modules/run_main:65:12)

$ npx tsx main.ts
SyntaxError: The requested module 'pg' does not provide an export named 'Pool'
    at ModuleJob._instantiate (node:internal/modules/esm/module_job:123:21)
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at async ModuleJob.run (node:internal/modules/esm/module_job:189:5)
    at async Promise.all (index 0)
    at async ESMLoader.import (node:internal/modules/esm/loader:533:24)
    at async loadESM (node:internal/process/esm_loader:91:5)
    at async handleMainPromise (node:internal/modules/run_main:65:12)

9 replies

Gaming

Programming