Drizzle Team•10mo ago

sqlite encode

I'm trying to encode some data in the schema. I thought I could use the sql command, but I think that's only for doing default values Like for this table:

export const contacts = sqliteTable(
    'contacts',
    {
        contact_id: integer('contact_id').primaryKey(),
        user_id: text('user_id')
            .notNull()
            .references(() => user.id),
        name: text('name').notNull(),
        relationship: text('relationship'),
        birthday: integer('birthday', { mode: 'timestamp' }),

export const contacts = sqliteTable(
    'contacts',
    {
        contact_id: integer('contact_id').primaryKey(),
        user_id: text('user_id')
            .notNull()
            .references(() => user.id),
        name: text('name').notNull(),
        relationship: text('relationship'),
        birthday: integer('birthday', { mode: 'timestamp' }),

I'd like to use the sqlean encode function for sensitive information like the birthday but don't know where to add that or if I can 🫠

5 Replies

Andrii Sherman•10mo ago

so you want to have some sort of interceptor to catch the inserted value and then encode it? so you can specify it once and then in any insert to database it will be encoded? I guess you can use it with customTypes or do you need to do it on database level? if it should be on database level, you can try using

.$defaultFn(sql`encode()`)

.$defaultFn(sql`encode()`)

Scott•10mo ago

Like this?

birthday: integer('birthday', {'timestamp'}).$defaultFn(
  () => sql`encode('%s', 'base64')`,
),

birthday: integer('birthday', {'timestamp'}).$defaultFn(
  () => sql`encode('%s', 'base64')`,
),

doesn't seem to do the thing

Andrii Sherman•10mo ago

ok, now I see, you don't have an access to a value on insert I'll think about this usecase and will get back to you

Angelelz•10mo ago

@Andrew Sherman When I saw the way the new .$default() / .$defaultFn were implemented I did some testing but I couldn't migrate from my custom type as it was implemented because of this issue Right now, I'm manually creating an id on every insert just because I need the created id to return it to the front-end We might want to look into patching the return from the driver with the result of the function passed to $default()

Andrii Sherman•10mo ago

yeah, the purpose of $default() is only to have a default on insert, to simulate custom id creation, etc. I guess for such purposes we will need tome other function

Gaming

Programming

sqlite encode