Add postgres data via drizzle

default discord avatar
wiesson8 months ago
10

I have like 18k images that I'd like to import (just into the db, images are stored within the cdn), therefore basically like to create 18k db entries only.



I'm wondering how I use

drizzle

or

payload.db.tables

to do that



I have seen

https://payloadcms.com/docs/database/postgres

but I don't get it because drizzle says sth like



await db.insert(users).values([{ name: 'Andrew' }, { name: 'Dan' }]);

(example from here:

https://orm.drizzle.team/docs/insert#insert-multiple-rows

)



So where do I get the users model? (or in my case the media model?)

  • discord user avatar
    denolfe
    8 months ago

    It looks like we may be missing some type inference, but you should be able to access the table by passing

    payload.db.tables['media'])
  • default discord avatar
    wiesson8 months ago

    Yes! That was the missing part!



    const chunkArray = (array, chunkSize) => {
      const chunks = [];
      for (let i = 0; i < array.length; i += chunkSize) {
        chunks.push(array.slice(i, i + chunkSize));
      }
      return chunks;
    };
    
    const doAction = async () => {
      await payload.init({
        secret: PAYLOAD_SECRET,
        local: true,
      });
    
      const toImport = images.map((image) => ({
        createdAt: image.created_at,
        updatedAt: image.updated_at || image.created_at,
        alt: image.alt || "",
        caption: image.caption || "",
        url: image.url || "",
        filename: image.image || "",
        mime_type: image.type || "",
      }));
    
      const chunks = chunkArray(toImport, 500);
    
      for (const chunk of chunks) {
        await payload.db.drizzle.insert(payload.db.tables["media"]).values(chunk);
      }
    };
    
    doAction();
  • discord user avatar
    denolfe
    8 months ago

    @wiesson Amazing 🙌

  • default discord avatar
    wiesson8 months ago

    If I have time, I could do a PR with extended documentation how to import data

  • discord user avatar
    denolfe
    8 months ago

    That would be much appreciated 👍 Probably in this area of the docs

    https://github.com/payloadcms/payload/blob/main/docs/database/postgres.mdx#L46
  • default discord avatar
    wiesson8 months ago

    I'm basically replacing my old Django app with Payload and I have exportet all data as JSON and I'm now writing migration scripts to add the data to the DB

  • discord user avatar
    denolfe
    8 months ago

    Wow, sounds like a good amount of work. Looks like you're well on your way, though.

  • default discord avatar
    wiesson8 months ago

    btw, I wanted to use

    await payload.create({ collection: "media", data: { ... data here ... } })

    but I don't want to upload the file, because it's already stored inside the CDN

  • discord user avatar
    denolfe
    8 months ago

    Hmm, yeah that's tricky.



    Could do an

    update

    after the fact, but on creation I believe you need to pass the asset

  • default discord avatar
    wiesson8 months ago

    One question - I'm using the

    url

    field as my CDN url. But it gets overwritten somehow when I try to display the thumbnailImage



    I assume that payload tries to create the URL for me (based on my static url + filename?)



    Besides that, 18532 images imported ✅ 🙂

Star on GitHub

Star

Chat on Discord

Discord

online

Can't find what you're looking for?

Get help straight from the Payload team with an Enterprise License.