Giter Club home page Giter Club logo

Comments (3)

pringon avatar pringon commented on May 24, 2024 1

Can you also share information about the type definitions, migrations you run against the database, and db config, please? :)

For context, the following code worked for me:

import { CamelCasePlugin, Generated, Kysely, PostgresDialect } from "kysely";
import { jsonArrayFrom } from "kysely/helpers/postgres";
import { Pool } from "pg";

interface PersonTable {
  id: Generated<number>;
  first_name: string;
  last_name: string;
}

interface PetTable {
  id: Generated<number>;
  owner_id: number;
  name: string;
  species: "cat" | "dog";
}

interface Database {
  person: PersonTable;
  pet: PetTable;
}

const db = new Kysely<Database>({
  dialect: new PostgresDialect({
    pool: async () =>
      new Pool({
        database: "test_db",
        user: "test_user",
        password: "test_password",
        host: "localhost",
        port: 5433,
      }),
  }),
  plugins: [new CamelCasePlugin()],
  log: ["error"],
});

void (async () => {
  console.log(
    JSON.stringify(
      await db
        .selectFrom("person")
        .selectAll()
        .select((eb) =>
          jsonArrayFrom(
            eb
              .selectFrom("pet")
              .whereRef("pet.owner_id", "=", "person.id")
              .selectAll("pet")
              .orderBy("pet.id")
          ).as("b")
        )
        .where("person.id", "=", 1)
        .executeTakeFirst()
    )
  );
})();

Output:

{"id":1,"firstName":"John","lastName":"Doe","b":[{"id":1,"ownerId":1,"name":"Rex","species":"dog"}]}

Migration

CREATE TABLE person (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL
);

CREATE TABLE pet (
  id SERIAL PRIMARY KEY,
  owner_id INTEGER NOT NULL REFERENCES person(id),
  name VARCHAR(255) NOT NULL,
  species VARCHAR(50) CHECK(species IN ('cat', 'dog')) NOT NULL
);

-- Insert some initial data
INSERT INTO person (first_name, last_name) VALUES ('John', 'Doe'), ('Jane', 'Doe');
INSERT INTO pet (owner_id, name, species) VALUES (1, 'Rex', 'dog'), (2, 'Mittens', 'cat');

from kysely.

igalklebanov avatar igalklebanov commented on May 24, 2024 1

Hey 👋

Some dialects might simply return the data as a JSON string. In these cases you can use the built in ParseJSONResultsPlugin to parse the results.
https://kysely.dev/docs/examples/SELECT/nested-object

from kysely.

ekschro avatar ekschro commented on May 24, 2024

Hey @igalklebanov and @pringon - Thank you for the help!

For context, I am using the DataApiDialect. What @igalklebanov has mentioned makes perfect sense.

from kysely.

Related Issues (20)

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.