3 replies

@effect/sql Inconsistent Casing

It seems like the casing (snake_case, camelCase) is inconsistent across migrations and actual queries.

Here is a simple example

Migration

CREATE TABLE users (
  id text PRIMARY KEY,
  username text NOT NULL
);

CREATE TABLE sessions (
  id text PRIMARY KEY,
  userId text NOT NULL,
  FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE users (
  id text PRIMARY KEY,
  username text NOT NULL
);

CREATE TABLE sessions (
  id text PRIMARY KEY,
  userId text NOT NULL,
  FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE
);

The

session

session

table now looks like this:

id    text    
userid    text

id    text    
userid    text

Relevant Code

export class Session extends Schema.Class<Session>('Session')({
    id: StringSchema,
    userId: StringSchema
}) {}


export const InsertSessionSchema = Schema.Struct({
    id: StringSchema ,
    userId: StringSchema
});

const InsertSession = yield* SqlResolver.ordered('InsertSession', {
  Request: InsertSessionSchema,
  Result: Session,
  execute: (requests) => {
    // Generates this SQL Query
    // INSERT INTO sessions (\"id\",\"userId\",\"expiresAt\") VALUES ($1,$2,$3) RETURNING *
    return sql`INSERT INTO sessions ${sql.insert(requests)} RETURNING *`;
  }
});

const insert = InsertSession.execute;

export class Session extends Schema.Class<Session>('Session')({
    id: StringSchema,
    userId: StringSchema
}) {}


export const InsertSessionSchema = Schema.Struct({
    id: StringSchema ,
    userId: StringSchema
});

const InsertSession = yield* SqlResolver.ordered('InsertSession', {
  Request: InsertSessionSchema,
  Result: Session,
  execute: (requests) => {
    // Generates this SQL Query
    // INSERT INTO sessions (\"id\",\"userId\",\"expiresAt\") VALUES ($1,$2,$3) RETURNING *
    return sql`INSERT INTO sessions ${sql.insert(requests)} RETURNING *`;
  }
});

const insert = InsertSession.execute;

The insert query fails because the user id column is called

userid

userid

and NOT

userId

userId

as expected by the migrations when calling it like this:

const sess = yield* sessionService.insert({
  id: generateId(12),
  userId: "whatever"
});

// PostgresError: column "userId" of relation "sessions" does not exist

const sess = yield* sessionService.insert({
  id: generateId(12),
  userId: "whatever"
});

// PostgresError: column "userId" of relation "sessions" does not exist

It works when chaning the migration and schema column to

user_id

user_id

, but it's common for database columns to use snake_case, but not in typescript. I'm wondering if there is some transformation happening before.

@effect/sql Inconsistent Casing

It seems like the casing (snake_case, camelCase) is inconsistent across migrations and actual queries.

Here is a simple example

Migration

CREATE TABLE users (
  id text PRIMARY KEY,
  username text NOT NULL
);

CREATE TABLE sessions (
  id text PRIMARY KEY,
  userId text NOT NULL,
  FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE users (
  id text PRIMARY KEY,
  username text NOT NULL
);

CREATE TABLE sessions (
  id text PRIMARY KEY,
  userId text NOT NULL,
  FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE
);

The

session

session

table now looks like this:

id    text    
userid    text

id    text    
userid    text

Relevant Code

export class Session extends Schema.Class<Session>('Session')({
    id: StringSchema,
    userId: StringSchema
}) {}


export const InsertSessionSchema = Schema.Struct({
    id: StringSchema ,
    userId: StringSchema
});

const InsertSession = yield* SqlResolver.ordered('InsertSession', {
  Request: InsertSessionSchema,
  Result: Session,
  execute: (requests) => {
    // Generates this SQL Query
    // INSERT INTO sessions (\"id\",\"userId\",\"expiresAt\") VALUES ($1,$2,$3) RETURNING *
    return sql`INSERT INTO sessions ${sql.insert(requests)} RETURNING *`;
  }
});

const insert = InsertSession.execute;

export class Session extends Schema.Class<Session>('Session')({
    id: StringSchema,
    userId: StringSchema
}) {}


export const InsertSessionSchema = Schema.Struct({
    id: StringSchema ,
    userId: StringSchema
});

const InsertSession = yield* SqlResolver.ordered('InsertSession', {
  Request: InsertSessionSchema,
  Result: Session,
  execute: (requests) => {
    // Generates this SQL Query
    // INSERT INTO sessions (\"id\",\"userId\",\"expiresAt\") VALUES ($1,$2,$3) RETURNING *
    return sql`INSERT INTO sessions ${sql.insert(requests)} RETURNING *`;
  }
});

const insert = InsertSession.execute;

The insert query fails because the user id column is called

userid

userid

and NOT

userId

userId

as expected by the migrations when calling it like this:

const sess = yield* sessionService.insert({
  id: generateId(12),
  userId: "whatever"
});

// PostgresError: column "userId" of relation "sessions" does not exist

const sess = yield* sessionService.insert({
  id: generateId(12),
  userId: "whatever"
});

// PostgresError: column "userId" of relation "sessions" does not exist

It works when chaning the migration and schema column to

user_id

user_id

, but it's common for database columns to use snake_case, but not in typescript. I'm wondering if there is some transformation happening before.

@effect/sql Inconsistent Casing

Similar Threads

@effect/sql Inconsistent Casing

Similar Threads

Similar Threads

Similar Threads