oauth user id's and data types

[avnav0]

hey thanks for this library!

i'm trying to setup my db (turso) in a way that conforms to my previous approaches:

there is a primary key that is unique and auto incrementing - and acts as the id on the table. i recognize that lucia uses provider id's as a primary key, so i'm wondering if there's a way to work this. my goal is to have everyone in one table, but i also don't like that i am forced to use text.

here is the way i have it setup:

// Users Table
export const authUsersTable = sqliteTable(
    'auth_users',
    {
        id: integer('id', { mode: 'number' }).primaryKey({ autoIncrement: true }),
        provider: text('provider').notNull(),
        providerId: text('provider_id').notNull(),
        email: text('email').notNull(), // make this a list incase they use others?
        firstName: text('first_name').notNull(),
        lastName: text('last_name').notNull(),
        fullName: text('full_name').notNull(),
        locale: text('locale'),
        emailVerified: integer('email_verified').notNull(),
    },
    (table) => {
        return {
            authUserId: primaryKey(table.id),
            authUserIdKey: unique("authUser_id_key").on(table.providerId),
            authUserEmailKey: unique("authUser_email_key").on(table.email),
        };
    }
);


// User Keys Table
export const authKeys = sqliteTable(
    'auth_keys',
    {
        id: integer('id', { mode: 'number' }).primaryKey({ autoIncrement: true })
        ,userId: integer("user_id", { mode: 'number' })
            .notNull()
            .references(() => authUsersTable.id)
        ,hashedPassword: text("hashed_password")
    },
    (table) => {
        return {
            authKeyId: primaryKey(table.id),
            authKeyIdKey: unique("authKey_id_key").on(table.userId),
        };
    }
);


// User Sessions Table
export const authSessionsTable = sqliteTable(
    'auth_sessions',
    {
        id: integer('id', { mode: 'number' }).primaryKey({ autoIncrement: true })
        ,userId: integer("user_id", { mode: 'number' })
            .notNull()
            .references(() => authUsersTable.id)
        ,activeExpires: blob('active_expires', { mode: 'bigint' }).notNull()
        ,idleExpires: blob('idle_expires', { mode: 'bigint' }).notNull(),
    },
    (table) => {
        return {
            authSessionId: primaryKey(table.id),
            authSessionUserIdKey: unique("authSession_id_key").on(table.userId),
        };
    }
);

but i'm unsure of how to implement so that the user id is actually id and not (for example with google) the sub:

		const { existingUser, googleUser, createUser } =
			await providerGoogle.validateCallback(code);
		const getUser = async () => {
			if (existingUser) return existingUser;
			try {
				const user = await createUser({
					userId: googleUser.sub,
					attributes: {
						provider: 'google',
						provider_id: googleUser.sub,
						email: googleUser.email,
						first_name: googleUser.given_name,
						last_name: googleUser.family_name,
						full_Name: googleUser.name,
						locale: googleUser.locale,
						email_verified: googleUser.email_verified ? 1 : 0
					}
				});
				return user;
			}

basically, wondering if it's possible to have the google id (googleUser.sub) be a column but not the primary key on the table. so lucia would check on that field, but the sessions table and the auth key table would be tied to the auto incrementing user id.

[avnav0]

just to be clear it works this way:

// Users Table
export const authUsersTable = sqliteTable(
    'auth_users',
    {
        // id: integer('id', { mode: 'number' }).primaryKey({ autoIncrement: true }),
        id: text('id').primaryKey(),
        provider: text('provider').notNull(),
        // providerId: text('provider_id').notNull(),
        email: text('email').notNull(), // make this a list incase they use others?
        firstName: text('first_name').notNull(),
        lastName: text('last_name').notNull(),
        fullName: text('full_name').notNull(),
        locale: text('locale'),
        emailVerified: integer('email_verified').notNull(),
    },
    (table) => {
        return {
            authUserId: primaryKey(table.id),
            authUserEmailKey: unique("authUser_email_key").on(table.email),
        };
    }
);


// User Keys Table
export const authKeys = sqliteTable(
    'auth_keys',
    {
        // id: integer('id', { mode: 'number' }).primaryKey({ autoIncrement: true })
        id: text("id").primaryKey()
        // ,userId: integer("user_id", { mode: 'number' })
        ,userId: text("user_id")
            .notNull()
            .references(() => authUsersTable.id)
        ,hashedPassword: text("hashed_password")
    },
    (table) => {
        return {
            authKeyId: primaryKey(table.id),
            authKeyIdKey: unique("authKey_id_key").on(table.userId),
        };
    }
);


// User Sessions Table
export const authSessionsTable = sqliteTable(
    'auth_sessions',
    {
        // id: integer('id', { mode: 'number' }).primaryKey({ autoIncrement: true })
        id: text("id").primaryKey()
        // ,userId: integer("user_id", { mode: 'number' })
        ,userId: text("user_id")
            .notNull()
            .references(() => authUsersTable.id)
        ,activeExpires: blob('active_expires', { mode: 'bigint' }).notNull()
        ,idleExpires: blob('idle_expires', { mode: 'bigint' }).notNull(),
    },
    (table) => {
        return {
            authSessionId: primaryKey(table.id),
            // authSessionUserIdKey: unique("authSession_id_key").on(table.userId),
        };
    }
);

but i would like to have an auto incremented field, which in sqlite i believe gets replaced by the primary key?

[oscarhermoso]

@avnav0 - How did you go with this one? I am approaching the same problem, just with postgres.

I'm considering moving an existing project to Lucia auth, but changing the primary key to text would be quite difficult because all of the foreign key relations

[avnav0]

its kind of a bummer but i just embraced it - will deal with that stuff at some other point later on...