Nil Posts - Answer Overflow

Nil

•Created by Nil on 11/30/2023 in #help

Creating a table within a schema using a column from a table in another schema

I'm using two schemas and I want to create a table in one of them with a column referencing a column that comes from another schema, how would I do this in migrations i'm using Postgresql

  await db.schema
    .createSchema('schemaNameOne')
    .execute()

  await db.schema
    .withSchema("schemaNameOne")
    .createTable("apiClient")
    .addColumn("id", "bigserial", (col) => col.primaryKey())
    .addColumn("username", "varchar", (col) => col.notNull())
    .addColumn("type", "varchar", (col) => col.notNull())
    .addColumn("apiClientId", "integer", (col) => col.notNull())
    .addColumn("webhookUrl", "varchar")
    .addColumn("createdAt", "timestamp", (col) => col.defaultTo(sql`now()`).notNull())
    .addColumn("updatedAt", "timestamp", (col) => col.defaultTo(sql`now()`).notNull())
    .execute();
  
  await db.schema
    .withSchema("schemaNameOne")
    .createTable("apiClientCustomer")
    .addColumn("id", "bigserial", (col) => col.primaryKey())
    .addColumn("customerId", "integer", (col) => col.references('schemaNameTwo.customer.id').onDelete('cascade').notNull())
    .addColumn("apiClientUsername", "varchar", (col) => col.references('apiClient.username').onDelete('cascade').notNull())
    .addColumn("createdAt", "timestamp", (col) => col.defaultTo(sql`now()`).notNull())
    .addColumn("updatedAt", "timestamp", (col) => col.defaultTo(sql`now()`).notNull())
    .execute();

  await db.schema
    .createSchema('schemaNameOne')
    .execute()

  await db.schema
    .withSchema("schemaNameOne")
    .createTable("apiClient")
    .addColumn("id", "bigserial", (col) => col.primaryKey())
    .addColumn("username", "varchar", (col) => col.notNull())
    .addColumn("type", "varchar", (col) => col.notNull())
    .addColumn("apiClientId", "integer", (col) => col.notNull())
    .addColumn("webhookUrl", "varchar")
    .addColumn("createdAt", "timestamp", (col) => col.defaultTo(sql`now()`).notNull())
    .addColumn("updatedAt", "timestamp", (col) => col.defaultTo(sql`now()`).notNull())
    .execute();
  
  await db.schema
    .withSchema("schemaNameOne")
    .createTable("apiClientCustomer")
    .addColumn("id", "bigserial", (col) => col.primaryKey())
    .addColumn("customerId", "integer", (col) => col.references('schemaNameTwo.customer.id').onDelete('cascade').notNull())
    .addColumn("apiClientUsername", "varchar", (col) => col.references('apiClient.username').onDelete('cascade').notNull())
    .addColumn("createdAt", "timestamp", (col) => col.defaultTo(sql`now()`).notNull())
    .addColumn("updatedAt", "timestamp", (col) => col.defaultTo(sql`now()`).notNull())
    .execute();

3 replies

KKysely

•Created by Nil on 8/31/2023 in #help

hi, how do i make a select with a where that searches within a json? I'm using postgres

inside the json there is an id that I want to use to do the search, but I didn't understand how to do it, could you help me?

8 replies

KKysely

•Created by Nil on 8/8/2023 in #help

how to insert an array of strings

hey guys, I wanted to know how to store an array of strings with kysely, I created my table using migrations:

  await db.schema
    .withSchema("schemaName")
    .createTable("tableName")
    .addColumn("id", "bigserial", (col) => col.primaryKey())
    .addColumn("columnName", sql`text[]`)

  await db.schema
    .withSchema("schemaName")
    .createTable("tableName")
    .addColumn("id", "bigserial", (col) => col.primaryKey())
    .addColumn("columnName", sql`text[]`)

I ran it this way and it created the table, but the problem now is inserting the data:

  const insertedProductId: any = await db
     .insertInto("teleclinica.product")
     .values({
       columnName: ["data1", "data2"]
     })
     .returning(['id'])
     .executeTakeFirstOrThrow()

  const insertedProductId: any = await db
     .insertInto("teleclinica.product")
     .values({
       columnName: ["data1", "data2"]
     })
     .returning(['id'])
     .executeTakeFirstOrThrow()

I'm using postgresql, could you help me please

6 replies

KKysely

•Created by Nil on 6/27/2023 in #help

how to search inside a json type column, I'm using postgresql

I looked in the documentation, and it talks about using the jsonObjectFrom function that is in the kysely lib, but I didn't find it, should I have done the function?

5 replies

KKysely

•Created by Nil on 6/12/2023 in #help

insert data into a table that has id of type bigserial

I created a table that has id(bigserial), cognitoId(varchar), subaccountInfo(json), I'm trying like this:

    const obj = [{
      nome: "nil", 
      sobrenome: "kuro"
    }]
    const json = JSON.stringify(obj)
    console.log(json)
    const responseDb = await db
    .withSchema("schemaName")
    .insertInto('administrator')
    .values({
      "cognitoId": "nil", 
      "subaccountInfo": json
    })
    .execute()

    const obj = [{
      nome: "nil", 
      sobrenome: "kuro"
    }]
    const json = JSON.stringify(obj)
    console.log(json)
    const responseDb = await db
    .withSchema("schemaName")
    .insertInto('administrator')
    .values({
      "cognitoId": "nil", 
      "subaccountInfo": json
    })
    .execute()

But I can't record anything, I don't know where I'm going wrong...

6 replies

KKysely

•Created by Nil on 6/7/2023 in #help

i am trying to select my id from type uuid

when i select all id from administrator table, it returns me status 200 and returns all id

    const responseDb = await db
      .withSchema("schemaName")
      .selectFrom("administrator")
      .select("id")
      .execute()

    const responseDb = await db
      .withSchema("schemaName")
      .selectFrom("administrator")
      .select("id")
      .execute()

but when i try to select a single id, it returns me status 400 badrequest

    const responseDb = await db
      .withSchema("schemaName")
      .selectFrom("administrator")
      .select("id")
      .where("id", "=", "a0ac162b-9f03-44f3-b3b1-a0b31538e10c")
      .execute()

    const responseDb = await db
      .withSchema("schemaName")
      .selectFrom("administrator")
      .select("id")
      .where("id", "=", "a0ac162b-9f03-44f3-b3b1-a0b31538e10c")
      .execute()

what am i doing wrong?

4 replies

KKysely

•Created by Nil on 6/7/2023 in #help

I am getting an error when entering an id with a uuid value

const uuid = uuidv4() await db .withSchema("schema_name") .insertInto("table_name") .values({ id: uuid }) .execute(); Error: ERROR: column "id" is of type uuid but expression is of type character varying Hint: You will need to rewrite or cast the expression.

7 replies

Gaming

Programming