search

kysely-org / kysely

A type-safe typescript SQL query builder
https://kysely.dev
MIT License
10.8k stars 275 forks source link

syntax error at or near "returning" with vercel postgres database #845

Closed Wizzel1 closed 10 months ago

Wizzel1 commented 10 months ago

I am using vercels postgres database and I am trying to insert a row into it. My goal is to return the id when the row has been created.

export type Episode = {
    id?: number;
};

type Database = {
    episodes: Episode;
};

const db = createKysely<Database>({
    connectionString: ENV.POSTGRES_URL
});
CREATE TABLE IF NOT EXISTS episodes (
    id SERIAL PRIMARY KEY
)
export async function createNewEpisodeAndGetId(): Promise<number | undefined> {
    try {
        const episode = await db.insertInto('episodes').returning('id').executeTakeFirst();
        console.log('result', episode);

        return episode?.id;
    } catch (error) {
        console.error(error);
    }
}

error: syntax error at or near "returning"
    at on.parseErrorMessage (C:\Users\Wizzel\Desktop\meme-channel-svelte\node_modules\.pnpm\@neondatabase+serverless@0.6.0\node_modules\@neondatabase\serverless\index.js:1174:6)
    at on.handlePacket (C:\Users\Wizzel\Desktop\meme-channel-svelte\node_modules\.pnpm\@neondatabase+serverless@0.6.0\node_modules\@neondatabase\serverless\index.js:1135:48)
    at on.parse (C:\Users\Wizzel\Desktop\meme-channel-svelte\node_modules\.pnpm\@neondatabase+serverless@0.6.0\node_modules\@neondatabase\serverless\index.js:1119:63)
    at x.<anonymous> (C:\Users\Wizzel\Desktop\meme-channel-svelte\node_modules\.pnpm\@neondatabase+serverless@0.6.0\node_modules\@neondatabase\serverless\index.js:1181:74)
    at x.emit (C:\Users\Wizzel\Desktop\meme-channel-svelte\node_modules\.pnpm\@neondatabase+serverless@0.6.0\node_modules\@neondatabase\serverless\index.js:395:63)
    at WebSocket.<anonymous> (C:\Users\Wizzel\Desktop\meme-channel-svelte\node_modules\.pnpm\@neondatabase+serverless@0.6.0\node_modules\@neondatabase\serverless\index.js:992:19)
    at callListener (C:\Users\Wizzel\Desktop\meme-channel-svelte\node_modules\.pnpm\ws@8.14.2_bufferutil@4.0.8_utf-8-validate@6.0.3\node_modules\ws\lib\event-target.js:290:14)
    at WebSocket.onMessage (C:\Users\Wizzel\Desktop\meme-channel-svelte\node_modules\.pnpm\ws@8.14.2_bufferutil@4.0.8_utf-8-validate@6.0.3\node_modules\ws\lib\event-target.js:209:9)
    at WebSocket.emit (node:events:514:28)
    at Receiver.receiverOnMessage (C:\Users\Wizzel\Desktop\meme-channel-svelte\node_modules\.pnpm\ws@8.14.2_bufferutil@4.0.8_utf-8-validate@6.0.3\node_modules\ws\lib\websocket.js:1192:20)
    at PostgresConnection.executeQuery (file:///C:/Users/Wizzel/Desktop/meme-channel-svelte/node_modules/.pnpm/kysely@0.26.3/node_modules/kysely/dist/esm/dialect/postgres/postgres-driver.js:88:41)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async file:///C:/Users/Wizzel/Desktop/meme-channel-svelte/node_modules/.pnpm/kysely@0.26.3/node_modules/kysely/dist/esm/query-executor/query-executor-base.js:35:28
    at async DefaultConnectionProvider.provideConnection (file:///C:/Users/Wizzel/Desktop/meme-channel-svelte/node_modules/.pnpm/kysely@0.26.3/node_modules/kysely/dist/esm/driver/default-connection-provider.js:10:20)
    at async DefaultQueryExecutor.executeQuery (file:///C:/Users/Wizzel/Desktop/meme-channel-svelte/node_modules/.pnpm/kysely@0.26.3/node_modules/kysely/dist/esm/query-executor/query-executor-base.js:34:16)
    at async InsertQueryBuilder.execute (file:///C:/Users/Wizzel/Desktop/meme-channel-svelte/node_modules/.pnpm/kysely@0.26.3/node_modules/kysely/dist/esm/query-builder/insert-query-builder.js:515:24)
    at async InsertQueryBuilder.executeTakeFirst (file:///C:/Users/Wizzel/Desktop/meme-channel-svelte/node_modules/.pnpm/kysely@0.26.3/node_modules/kysely/dist/esm/query-builder/insert-query-builder.js:530:26)       
    at async Module.createNewEpisodeAndGetId (C:/Users/Wizzel/Desktop/meme-channel-svelte/src/lib/server/postgresClient.ts:103:21)
    at async POST (C:/Users/Wizzel/Desktop/meme-channel-svelte/src/routes/api/testFolder/+server.ts:6:24)
    at async Module.render_endpoint (C:/Users/Wizzel/Desktop/meme-channel-svelte/node_modules/.pnpm/@sveltejs+kit@2.3.2_@sveltejs+vite-plugin-svelte@3.0.1_svelte@4.2.8_vite@5.0.11/node_modules/@sveltejs/kit/src/runtime/server/endpoint.js:49:18) {
  length: 98,
  severity: 'ERROR',
  code: '42601',
  detail: undefined,
  hint: undefined,
  position: '24',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'scan.l',
  line: '1188',
  routine: 'scanner_yyerror'
}

I also tried 

export async function createNewEpisodeAndGetId(): Promise<number | undefined> {
    try {
        const episode = await db.insertInto('episodes')
        .values({ id: undefined })
        .returning('id').executeTakeFirst();
        console.log('result', episode);

        return episode?.id;
    } catch (error) {
        console.error(error);
    }
}

but that results in another error:

error: syntax error at or near ")"
    at on.parseErrorMessage (C:\Users\Wizzel\Desktop\meme-channel-svelte\node_modules\.pnpm\@neondatabase+serverless@0.6.0\node_modules\@neondatabase\serverless\index.js:1174:6)
    at on.handlePacket (C:\Users\Wizzel\Desktop\meme-channel-svelte\node_modules\.pnpm\@neondatabase+serverless@0.6.0\node_modules\@neondatabase\serverless\index.js:1135:48)
    at on.parse (C:\Users\Wizzel\Desktop\meme-channel-svelte\node_modules\.pnpm\@neondatabase+serverless@0.6.0\node_modules\@neondatabase\serverless\index.js:1119:63)
    at x.<anonymous> (C:\Users\Wizzel\Desktop\meme-channel-svelte\node_modules\.pnpm\@neondatabase+serverless@0.6.0\node_modules\@neondatabase\serverless\index.js:1181:74)
    at x.emit (C:\Users\Wizzel\Desktop\meme-channel-svelte\node_modules\.pnpm\@neondatabase+serverless@0.6.0\node_modules\@neondatabase\serverless\index.js:395:63)
    at WebSocket.<anonymous> (C:\Users\Wizzel\Desktop\meme-channel-svelte\node_modules\.pnpm\@neondatabase+serverless@0.6.0\node_modules\@neondatabase\serverless\index.js:992:19)
    at callListener (C:\Users\Wizzel\Desktop\meme-channel-svelte\node_modules\.pnpm\ws@8.14.2_bufferutil@4.0.8_utf-8-validate@6.0.3\node_modules\ws\lib\event-target.js:290:14)
    at WebSocket.onMessage (C:\Users\Wizzel\Desktop\meme-channel-svelte\node_modules\.pnpm\ws@8.14.2_bufferutil@4.0.8_utf-8-validate@6.0.3\node_modules\ws\lib\event-target.js:209:9)       
    at WebSocket.emit (node:events:514:28)
    at Receiver.receiverOnMessage (C:\Users\Wizzel\Desktop\meme-channel-svelte\node_modules\.pnpm\ws@8.14.2_bufferutil@4.0.8_utf-8-validate@6.0.3\node_modules\ws\lib\websocket.js:1192:20) 
    at PostgresConnection.executeQuery (file:///C:/Users/Wizzel/Desktop/meme-channel-svelte/node_modules/.pnpm/kysely@0.26.3/node_modules/kysely/dist/esm/dialect/postgres/postgres-driver.js:88:41)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async file:///C:/Users/Wizzel/Desktop/meme-channel-svelte/node_modules/.pnpm/kysely@0.26.3/node_modules/kysely/dist/esm/query-executor/query-executor-base.js:35:28
    at async DefaultConnectionProvider.provideConnection (file:///C:/Users/Wizzel/Desktop/meme-channel-svelte/node_modules/.pnpm/kysely@0.26.3/node_modules/kysely/dist/esm/driver/default-connection-provider.js:10:20)
    at async DefaultQueryExecutor.executeQuery (file:///C:/Users/Wizzel/Desktop/meme-channel-svelte/node_modules/.pnpm/kysely@0.26.3/node_modules/kysely/dist/esm/query-executor/query-executor-base.js:34:16)
    at async InsertQueryBuilder.execute (file:///C:/Users/Wizzel/Desktop/meme-channel-svelte/node_modules/.pnpm/kysely@0.26.3/node_modules/kysely/dist/esm/query-builder/insert-query-builder.js:515:24)
    at async InsertQueryBuilder.executeTakeFirst (file:///C:/Users/Wizzel/Desktop/meme-channel-svelte/node_modules/.pnpm/kysely@0.26.3/node_modules/kysely/dist/esm/query-builder/insert-query-builder.js:530:26)
    at async Module.createNewEpisodeAndGetId (C:/Users/Wizzel/Desktop/meme-channel-svelte/src/lib/server/postgresClient.ts:103:21)
    at async POST (C:/Users/Wizzel/Desktop/meme-channel-svelte/src/routes/api/testFolder/+server.ts:6:24)
    at async Module.render_endpoint (C:/Users/Wizzel/Desktop/meme-channel-svelte/node_modules/.pnpm/@sveltejs+kit@2.3.2_@sveltejs+vite-plugin-svelte@3.0.1_svelte@4.2.8_vite@5.0.11/node_modules/@sveltejs/kit/src/runtime/server/endpoint.js:49:18) {
  length: 90,
  severity: 'ERROR',
  code: '42601',
  detail: undefined,
  hint: undefined,
  position: '25',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'scan.l',
  line: '1188',
  routine: 'scanner_yyerror'
}
koskimas commented 10 months ago

If you're not inserting any values, you need to add the default values specifier by calling defaultValues().

koskimas commented 10 months ago

You also should never make anything optional in your database interface. Please see the getting started documentation.

export type EpisodeTable = {
    id: Generated<number>;
};

type Database = {
    episodes: EpisodeTable;
};

export type Episode = Selectable<EpisodeTable>
export type NewEpisode = Insertable<EpisodeTable>
export type EpisodeUpdate = Updateable<EpisodeTable>
Wizzel1 commented 10 months ago

@koskimas Thanks that worked. However I have another problem where I could not find the correct documentation.

I have added a 

type VideoTable = {
  id: Generated<number>;
  in_episodes: number[],
}

export type Video = Selectable<VideoTable>;
export type NewVideo = Insertable<VideoTable>;
export type VideoUpdate = Updatable<VideoTable>;

and I am trying to query it like this:

const videosOfEpisodeOne = await db
.selectFrom('videos')
.where('in_episodes', '@>', [1])
.selectAll()
.execute();

console.log(videosOfEpisodeOne.length);

but that returns an error:

error: malformed array literal: "1"
...
length: 181,
severity: 'ERROR',
code: '22P02',
detail: 'Array value must start with "{" or dimension information.',
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: "unnamed portal parameter $1 = '...'",
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,

do I have to change the columntype of my video table here?