Drizzle Team•5mo ago

Storing Images

Hi everyone, I just want to know what is the best way to store images according to these info about the system I'm building. - a user needs to have a profile picture. - I need to store a number of images for each user. - a user can create a post, and the post may have an image. - The hero section of the app may have a dynamic image that the admin can change. - I need to store a number of images for each activity 'activity is a table to hold some info about an activity' created.

7 Replies

Mario564•4mo ago

Hello Ans. You shouldn't store images (blobs) in a relational database, what you want to do is upload them to something like S3 and store the references/links to that in the DB

404•4mo ago

you can use uploadthing https://uploadthing.com/

uploadthing

An easier way to upload files.

Anas BadranOP•4mo ago

You are right, I already doing that, I'm asking about the best approach to properly store images for multiple things, like persons images, posts images, activites images, should I put them all in one talbe, or each one needs it's own table As a second question

// the schema
export const post = pgTable('posts', {
    id: uuid('id').primaryKey().defaultRandom(),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    updatedAt: timestamp('updated_at')
        .defaultNow()
        .$onUpdate(() => sql`(CURRENT_TIMESTAMP)`),
    name: varchar('name', { length: 255 }).notNull()
})
// the demo code
import { eq } from 'drizzle-orm';
import { db } from '.';
import { post } from './schema';

(async () => {
    try {

        console.log('demo started')
        console.log('||||||||||||||||||||||||||||||||||||||||||||||||||||||');
        // inserting
        // await db.insert(post).values({
        //     name: 'Hello, world!'
        // })
        // updating
        await db.update(post).set({ name: 'Hello, updated1' }).where(eq(post.id, '305115f6-82b1-447c-b869-76741b9e4058'))
        console.log('demo finished')
        console.log('||||||||||||||||||||||||||||||||||||||||||||||||||||||');
    } catch (error) {
        console.log('||||||||||||||||||||||||||||||||||||||||||||||||||||||');
        console.log('bad happened', error)
    }
})()

// the schema
export const post = pgTable('posts', {
    id: uuid('id').primaryKey().defaultRandom(),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    updatedAt: timestamp('updated_at')
        .defaultNow()
        .$onUpdate(() => sql`(CURRENT_TIMESTAMP)`),
    name: varchar('name', { length: 255 }).notNull()
})
// the demo code
import { eq } from 'drizzle-orm';
import { db } from '.';
import { post } from './schema';

(async () => {
    try {

        console.log('demo started')
        console.log('||||||||||||||||||||||||||||||||||||||||||||||||||||||');
        // inserting
        // await db.insert(post).values({
        //     name: 'Hello, world!'
        // })
        // updating
        await db.update(post).set({ name: 'Hello, updated1' }).where(eq(post.id, '305115f6-82b1-447c-b869-76741b9e4058'))
        console.log('demo finished')
        console.log('||||||||||||||||||||||||||||||||||||||||||||||||||||||');
    } catch (error) {
        console.log('||||||||||||||||||||||||||||||||||||||||||||||||||||||');
        console.log('bad happened', error)
    }
})()

I get this error

demo started
||||||||||||||||||||||||||||||||||||||||||||||||||||||
||||||||||||||||||||||||||||||||||||||||||||||||||||||
bad happened TypeError: value.toISOString is not a function
    at PgTimestamp.mapToDriverValue (D:\code\developing\remix\badran\node_modules\.pnpm\[email protected]_@[email protected][email protected][email protected]\node_modules\src\pg-core\columns\timestamp.ts:67:16)
    at <anonymous> (D:\code\developing\remix\badran\node_modules\.pnpm\[email protected]_@[email protected][email protected][email protected]\node_modules\src\sql\sql.ts:210:69)
    at Array.map (<anonymous>)
    at SQL.buildQueryFromSourceParams (D:\code\developing\remix\badran\node_modules\.pnpm\[email protected]_@[email protected][email protected][email protected]\node_modules\src\sql\sql.ts:144:30)
    at <anonymous> (D:\code\developing\remix\badran\node_modules\.pnpm\[email protected]_@[email protected][email protected][email protected]\node_modules\src\sql\sql.ts:170:17)
    at Array.map (<anonymous>)
    at SQL.buildQueryFromSourceParams (D:\code\developing\remix\badran\node_modules\.pnpm\[email protected]_@[email protected][email protected][email protected]\node_modules\src\sql\sql.ts:144:30)
    at <anonymous> (D:\code\developing\remix\badran\node_modules\.pnpm\[email protected]_@[email protected][email protected][email protected]\node_modules\src\sql\sql.ts:170:17)
    at Array.map (<anonymous>)
    at SQL.buildQueryFromSourceParams (D:\code\developing\remix\badran\node_modules\.pnpm\[email protected]_@[email protected][email protected][email protected]\node_modules\src\sql\sql.ts:144:30)

demo started
||||||||||||||||||||||||||||||||||||||||||||||||||||||
||||||||||||||||||||||||||||||||||||||||||||||||||||||
bad happened TypeError: value.toISOString is not a function
    at PgTimestamp.mapToDriverValue (D:\code\developing\remix\badran\node_modules\.pnpm\[email protected]_@[email protected][email protected][email protected]\node_modules\src\pg-core\columns\timestamp.ts:67:16)
    at <anonymous> (D:\code\developing\remix\badran\node_modules\.pnpm\[email protected]_@[email protected][email protected][email protected]\node_modules\src\sql\sql.ts:210:69)
    at Array.map (<anonymous>)
    at SQL.buildQueryFromSourceParams (D:\code\developing\remix\badran\node_modules\.pnpm\[email protected]_@[email protected][email protected][email protected]\node_modules\src\sql\sql.ts:144:30)
    at <anonymous> (D:\code\developing\remix\badran\node_modules\.pnpm\[email protected]_@[email protected][email protected][email protected]\node_modules\src\sql\sql.ts:170:17)
    at Array.map (<anonymous>)
    at SQL.buildQueryFromSourceParams (D:\code\developing\remix\badran\node_modules\.pnpm\[email protected]_@[email protected][email protected][email protected]\node_modules\src\sql\sql.ts:144:30)
    at <anonymous> (D:\code\developing\remix\badran\node_modules\.pnpm\[email protected]_@[email protected][email protected][email protected]\node_modules\src\sql\sql.ts:170:17)
    at Array.map (<anonymous>)
    at SQL.buildQueryFromSourceParams (D:\code\developing\remix\badran\node_modules\.pnpm\[email protected]_@[email protected][email protected][email protected]\node_modules\src\sql\sql.ts:144:30)

how to properly set the updatedAt to be automatically updated when updaing the documnet in the database. thanks for sharing

Mario564•4mo ago

Oh, this TypeError is a known issue that will be addressed soon As for this, every project has different needs. This is more of a general DB question rather than Drizzle specific so you could do some research online about how others handle what you're describing. I personally store the reference of the images into the same table, so for example, if I have a post table, I'll have a field like imageLink/imageMetadata/imageRef

Anas BadranOP•4mo ago

Thanks for your help

Luis Serota•4mo ago

I'm getting this error as well! same syntax and with postgres. To get by it, im just diong doing () => new Date() which seems to work, though would prefer to let the DB do the work with SQL

Mario564•4mo ago

Yeah, using the DB handle it is better. We'll get to patching this soon

Gaming

Programming

Storing Images