Nicolas Posts - Answer Overflow

Nicolas

Explore posts from servers

CDCloudflare Developers SSolidJS RRefine TtRPC WWindmill HHono DTDrizzle Team

•Created by Nicolas on 8/11/2024 in #help

SQL string could not be parsed: non-terminated block comment at ...

Hi all, I am using Turso and I added two properties to my users table created_at and updated_at. I successfully generated a migration file, which looks like this:

ALTER TABLE `users` ADD `created_at` text DEFAULT (current_timestamp) NOT NULL;--> statement-breakpoint
ALTER TABLE `users` ADD `updated_at` text DEFAULT (current_timestamp) NOT NULL;

ALTER TABLE `users` ADD `created_at` text DEFAULT (current_timestamp) NOT NULL;--> statement-breakpoint
ALTER TABLE `users` ADD `updated_at` text DEFAULT (current_timestamp) NOT NULL;

This is my schema:

import { sql } from 'drizzle-orm';
import { boolean } from 'drizzle-orm/mysql-core';
import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core';
import { createInsertSchema, createSelectSchema } from 'drizzle-zod';

export const users = sqliteTable('users', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),  
  email: text('email').unique().notNull(),      
  subscribed: integer('subscribed', { mode: "boolean"}).notNull(), // Store subscription type
  google_id: text('google_id').unique().notNull(), // Store Google ID for authentication
  created_at: text('created_at').notNull().default(sql`(current_timestamp)`), <--- NEW
  updated_at: text('updated_at').notNull().default(sql`(current_timestamp)`), <--- NEW
});

export const sessions = sqliteTable('sessions', {
  id: text('id').primaryKey(),
  expires_at: integer('expires_at').notNull(),
  user_id: text('user_id').notNull().references(() => users.id),
});

export type InsertUser = typeof users.$inferInsert;
export const zInsertUser = createInsertSchema(users);
export type SelectUser = typeof users.$inferSelect;

import { sql } from 'drizzle-orm';
import { boolean } from 'drizzle-orm/mysql-core';
import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core';
import { createInsertSchema, createSelectSchema } from 'drizzle-zod';

export const users = sqliteTable('users', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),  
  email: text('email').unique().notNull(),      
  subscribed: integer('subscribed', { mode: "boolean"}).notNull(), // Store subscription type
  google_id: text('google_id').unique().notNull(), // Store Google ID for authentication
  created_at: text('created_at').notNull().default(sql`(current_timestamp)`), <--- NEW
  updated_at: text('updated_at').notNull().default(sql`(current_timestamp)`), <--- NEW
});

export const sessions = sqliteTable('sessions', {
  id: text('id').primaryKey(),
  expires_at: integer('expires_at').notNull(),
  user_id: text('user_id').notNull().references(() => users.id),
});

export type InsertUser = typeof users.$inferInsert;
export const zInsertUser = createInsertSchema(users);
export type SelectUser = typeof users.$inferSelect;

When running the migration, i get the following error:

[⣯] applying migrations...LibsqlError: SQL_PARSE_ERROR: SQL string could not be parsed: non-terminated block comment at (3, 1)
    at mapHranaError (file:///home/ubuntu/reap-checker/node_modules/@libsql/client/lib-esm/hrana.js:257:16)
    at HttpClient.batch (file:///home/ubuntu/reap-checker/node_modules/@libsql/client/lib-esm/http.js:82:19)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at LibSQLSession.batch (/home/ubuntu/reap-checker/node_modules/src/libsql/session.ts:75:24)
    at migrate (/home/ubuntu/reap-checker/node_modules/src/libsql/migrator.ts:50:2) {
  code: 'SQL_PARSE_ERROR',
  rawCode: undefined,
  [cause]: [ResponseError: SQL string could not be parsed: non-terminated block comment at (3, 1)] {
    code: 'SQL_PARSE_ERROR',
    proto: {
      message: 'SQL string could not be parsed: non-terminated block comment at (3, 1)',
      code: 'SQL_PARSE_ERROR'
    }
  }
}

[⣯] applying migrations...LibsqlError: SQL_PARSE_ERROR: SQL string could not be parsed: non-terminated block comment at (3, 1)
    at mapHranaError (file:///home/ubuntu/reap-checker/node_modules/@libsql/client/lib-esm/hrana.js:257:16)
    at HttpClient.batch (file:///home/ubuntu/reap-checker/node_modules/@libsql/client/lib-esm/http.js:82:19)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at LibSQLSession.batch (/home/ubuntu/reap-checker/node_modules/src/libsql/session.ts:75:24)
    at migrate (/home/ubuntu/reap-checker/node_modules/src/libsql/migrator.ts:50:2) {
  code: 'SQL_PARSE_ERROR',
  rawCode: undefined,
  [cause]: [ResponseError: SQL string could not be parsed: non-terminated block comment at (3, 1)] {
    code: 'SQL_PARSE_ERROR',
    proto: {
      message: 'SQL string could not be parsed: non-terminated block comment at (3, 1)',
      code: 'SQL_PARSE_ERROR'
    }
  }
}

Any idea what's going on? This is a super simple migration, just adding 2 columns, so not sure what to do... Thanks

6 replies

DTDrizzle Team

•Created by Nicolas on 2/14/2024 in #help

Getting error NeonDbError when pushing migration file

Hi all, So i have a schema that defines custom type JSON objects for my NeonDB database, like so:

type Address = {
  street: string;
  city: string;
  state: string;
  zip: string;
};

type Address = {
  street: string;
  city: string;
  state: string;
  zip: string;
};

And I am trying to use it like so:

export const Organizations = vendorPortalSchema.table('organizations', {
  id: serial("id").primaryKey(),
  name: text("name").unique().notNull(),
  description: text("description"),
  location: json("location").$type<Address>(),  
  logo_picture_file_id: serial("logo_picture_file_id").references(
    (): AnyPgColumn  => Files.id
  ),
});

export const Organizations = vendorPortalSchema.table('organizations', {
  id: serial("id").primaryKey(),
  name: text("name").unique().notNull(),
  description: text("description"),
  location: json("location").$type<Address>(),  
  logo_picture_file_id: serial("logo_picture_file_id").references(
    (): AnyPgColumn  => Files.id
  ),
});

However, I am getting an error NeonDbError: data type json has no default operator class for access method "btree" So perhaps I am not specifying the custom type of json correctly, but I could not find any examples on the drizzle website. I also tried like (commads instead of semi-colons):

type Address = {
  street: string,
  city: string,
  state: string,
  zip: string,
};

type Address = {
  street: string,
  city: string,
  state: string,
  zip: string,
};

But it still lead to the same error. Any thoughts on how to do this properly? Thank you!

4 replies

DTDrizzle Team

•Created by Nicolas on 10/11/2023 in #help

Infer Types from Partial Select Prepared Statement

5 replies

DTDrizzle Team

•Created by Nicolas on 10/9/2023 in #help

Null fields not working as expected

Hi all, I am using Drizzle ORM with Zod validation for useForm, and I am running into issues with fields that are allowed to be undefined / null, but I get Zod errors stating that the value of the optional fields (when left empty) are invalid_type and in required. I was able to fix this by refining the field as follows:

export const selectDeploymentSchema = createSelectSchema(deployments, {
  city: (deployments) => deployments.city.nullish(),
});

export const selectDeploymentSchema = createSelectSchema(deployments, {
  city: (deployments) => deployments.city.nullish(),
});

This allows the city field to be left blank, but by default, even though i didn't specify notNull() on that field, Zod would error out saying that the field is required. I am using Turso DB, so this is my schema:

export const deployments = sqliteTable("deployments", {
  id: integer("id").primaryKey(),
  organizationId: integer("organization_id")
    .notNull()
    .references(() => organizations.id),
  name: text("name").notNull().unique(),
  description: text("description"),
  streetAddress: text("street_address"),
  city: text("city"),
  state: text("state"),
  zip: text("zip"),
});

export const deployments = sqliteTable("deployments", {
  id: integer("id").primaryKey(),
  organizationId: integer("organization_id")
    .notNull()
    .references(() => organizations.id),
  name: text("name").notNull().unique(),
  description: text("description"),
  streetAddress: text("street_address"),
  city: text("city"),
  state: text("state"),
  zip: text("zip"),
});

Notice for all fields by the id, organizationId, and name, they are optional; however, Zod treats them as ZodNullable but they should in fact be treated as ZodOptional<ZodNullable<ZodString>> to allow my fields to be empty (as is the intended affect).

2 replies

DTDrizzle Team

•Created by Nicolas on 9/30/2023 in #help

notNull() is still letting field be optional

I am performing the following for SQLite:

export const products = sqliteTable("products", {
  id: integer("id").primaryKey({ autoIncrement: false }).notNull(),
  name: text("name").notNull(),
  description: text("description"),
});

export const products = sqliteTable("products", {
  id: integer("id").primaryKey({ autoIncrement: false }).notNull(),
  name: text("name").notNull(),
  description: text("description"),
});

However, when I perform: export type NewProduct = typeof products.$inferInsert; It says that id is optional (this is what it shows when i hover over NewProduct)

type NewProduct = {
    name: string;
    id?: number | undefined; <---- SHOULD JUST BE NUMBER
    description?: string | null | undefined;
}

type NewProduct = {
    name: string;
    id?: number | undefined; <---- SHOULD JUST BE NUMBER
    description?: string | null | undefined;
}

How can I fix this?

8 replies

DTDrizzle Team

•Created by Nicolas on 9/30/2023 in #help

Explanation on execute() vs all() vs get() vs run()

I am using prepare() to make prepared statements for Turso (so SQLite), and the docs use prepare.all() for SQLite instead of prepare.execute() as is used for PostgreSQL and MySQL, why is that? Same goes for prepare.get() for SQLite instead of prepare.execute() When do I know when to run those different queries for SQLite? Thank you!

1 replies

DTDrizzle Team

•Created by Nicolas on 9/29/2023 in #help

Issue Pushing Schema to Turso

Hi All, I made a schema.ts with sqliteTable statements, and I created a drizzle.config.ts as follows:

import { Config } from "drizzle-kit";

export default {
  driver: "turso",
  dbCredentials: {
    url: "libsql://[TABLE]-[GITHUB].turso.io",
    authToken:
      "[TOKEN]",
  },
  schema: "./schema.ts",
  out: "./out",
} as Config;

import { Config } from "drizzle-kit";

export default {
  driver: "turso",
  dbCredentials: {
    url: "libsql://[TABLE]-[GITHUB].turso.io",
    authToken:
      "[TOKEN]",
  },
  schema: "./schema.ts",
  out: "./out",
} as Config;

And when I try running pnpm drizzle-kit push:sqlite, I get the following error regarding that it cannot find module better-sqlite3:

drizzle-kit: v0.19.13
drizzle-orm: v0.28.6

node:internal/modules/cjs/loader:1048
  const err = new Error(message);
              ^

Error: Cannot find module 'better-sqlite3'
Require stack:
- /workspaces/reserve-power/node_modules/.pnpm/drizzle-kit@0.19.13/node_modules/drizzle-kit/index.cjs
    at Module._resolveFilename (node:internal/modules/cjs/loader:1048:15)
    at Module._load (node:internal/modules/cjs/loader:901:27)
    at Module.require (node:internal/modules/cjs/loader:1115:19)
    at require (node:internal/modules/helpers:130:18)
    at node_modules/.pnpm/@libsql+client@0.1.6/node_modules/@libsql/client/lib-esm/sqlite3.js (/workspaces/reserve-power/node_modules/.pnpm/drizzle-kit@0.19.13/node_modules/drizzle-kit/index.cjs:43596:37)
    at __init (/workspaces/reserve-power/node_modules/.pnpm/drizzle-kit@0.19.13/node_modules/drizzle-kit/index.cjs:9:56)
    at node_modules/.pnpm/@libsql+client@0.1.6/node_modules/@libsql/client/lib-esm/index.js (/workspaces/reserve-power/node_modules/.pnpm/drizzle-kit@0.19.13/node_modules/drizzle-kit/index.cjs:51852:5)
    at __init (/workspaces/reserve-power/node_modules/.pnpm/drizzle-kit@0.19.13/node_modules/drizzle-kit/index.cjs:9:56)
    at src/cli/commands/sqliteIntrospect.ts (/workspaces/reserve-power/node_modules/.pnpm/drizzle-kit@0.19.13/node_modules/drizzle-kit/index.cjs:51875:5)
    at __init (/workspaces/reserve-power/node_modules/.pnpm/drizzle-kit@0.19.13/node_modules/drizzle-kit/index.cjs:9:56) {
  code: 'MODULE_NOT_FOUND',
  requireStack: [
    '/workspaces/reserve-power/node_modules/.pnpm/drizzle-kit@0.19.13/node_modules/drizzle-kit/index.cjs'
  ]
}

drizzle-kit: v0.19.13
drizzle-orm: v0.28.6

node:internal/modules/cjs/loader:1048
  const err = new Error(message);
              ^

Error: Cannot find module 'better-sqlite3'
Require stack:
- /workspaces/reserve-power/node_modules/.pnpm/drizzle-kit@0.19.13/node_modules/drizzle-kit/index.cjs
    at Module._resolveFilename (node:internal/modules/cjs/loader:1048:15)
    at Module._load (node:internal/modules/cjs/loader:901:27)
    at Module.require (node:internal/modules/cjs/loader:1115:19)
    at require (node:internal/modules/helpers:130:18)
    at node_modules/.pnpm/@libsql+client@0.1.6/node_modules/@libsql/client/lib-esm/sqlite3.js (/workspaces/reserve-power/node_modules/.pnpm/drizzle-kit@0.19.13/node_modules/drizzle-kit/index.cjs:43596:37)
    at __init (/workspaces/reserve-power/node_modules/.pnpm/drizzle-kit@0.19.13/node_modules/drizzle-kit/index.cjs:9:56)
    at node_modules/.pnpm/@libsql+client@0.1.6/node_modules/@libsql/client/lib-esm/index.js (/workspaces/reserve-power/node_modules/.pnpm/drizzle-kit@0.19.13/node_modules/drizzle-kit/index.cjs:51852:5)
    at __init (/workspaces/reserve-power/node_modules/.pnpm/drizzle-kit@0.19.13/node_modules/drizzle-kit/index.cjs:9:56)
    at src/cli/commands/sqliteIntrospect.ts (/workspaces/reserve-power/node_modules/.pnpm/drizzle-kit@0.19.13/node_modules/drizzle-kit/index.cjs:51875:5)
    at __init (/workspaces/reserve-power/node_modules/.pnpm/drizzle-kit@0.19.13/node_modules/drizzle-kit/index.cjs:9:56) {
  code: 'MODULE_NOT_FOUND',
  requireStack: [
    '/workspaces/reserve-power/node_modules/.pnpm/drizzle-kit@0.19.13/node_modules/drizzle-kit/index.cjs'
  ]
}

Am I not pushing the schema properly with the command drizzle-kit push:sqlite? Thank you!

3 replies

DTDrizzle Team

•Created by Nicolas on 9/27/2023 in #help

How to make sql.placeholder() type safe

I have the following prepared statement:

export const listDeployments = db
  .select()
  .from(deployments)
  .where(eq(deployments.organizationId, sql.placeholder("orgId")))
  .prepare("listDeployments");

export const listDeployments = db
  .select()
  .from(deployments)
  .where(eq(deployments.organizationId, sql.placeholder("orgId")))
  .prepare("listDeployments");

However, when I use this like below: const response = await listDeployments.execute({ orgId: input }); The orgId is not type safe, I can enter whatever string I want, and there is no way to protect this call from receiving something other than orgId Is there a way to make it such that the caller must pass in orgId? Thank you!

4 replies

DTDrizzle Team

•Created by Nicolas on 9/23/2023 in #help

Neon and Drizzle ORM: Can my schema.ts create my tables in Neon?

As the title suggests, I was wondering if Drizzle ORM can create my tables in Neon from my schema.ts, or if I need to first create my tables in Neon with SQL, and then just create my schema.ts to reflect those tables. Thank you!

7 replies

Gaming

Programming