Possible to bulk publish content drafts

System Information
  • Strapi Version: 3.6.6
  • Operating System: Windows 10
  • Database: MySQL
  • Node Version: node v14.16.0
  • NPM Version: ^6.0.0
  • Yarn Version:

Hi guys,

I am studying to be a front-end developer, and for my exam
project we are using Strapi to create an api that we are to fetch on our
website.

I am combining the exam with making a new website for my uncles who have
an online antique bookstore.

I have managed to import the old MySQL database for their store
containing all of the books (more than 15thousand titles) to strapi, and
have them all in a content-type list.
My problem is that all of the titles are saved as drafts… And I cannot
manage to find any way to bulk publish them. (Needless to say -
publishing <15000 titles one by one will be rather time consuming).

Is there any way to publish several drafts at once in strapi?

Hope, and crossing my fingers, there is some way to do this, and that
maybe someone here can help me with how.

Hi There!

I’ve faced this same problem. And yes 15000 records is alot to edit one by one! Currently you can’t bulk update via the Admin UI but you can do it programatically. The solution is to set the published_at field to a date for all the records you want to publish. Those records will then appear as ‘published’ in the Admin UI and API.

I’m assuming you’ve already got a good handle on SQL already since you were able to import 15000 records. If not, have a look at a database admin tool to help you bulk update the published_at field in your “books” table. DBeaver is an example of one and with it you can perform SQL operations via a GUI or you can use SQL directly.

If you want to do it within your strapi code instead, you can write a small function that runs an SQL update query once within your strapi app just before it starts. You can use knex to build your SQL query (it’s already packaged with strapi) e.g.

async function bulkPublishBooks() {
  const knex = strapi.connections.default
  const updated = await knex.query('books')
    .update('published_at', new Date().toISOString())
    .whereNull('published_at')
}

where ‘books’ is the name of the MySQL table that has all your book records. Place the above code inside bootstrap.js and restart the server.

NB: I haven’t tested the code snippet above as I wrote it from memory but the general idea is there.

Hope that helps!

Hi there, thank you so, so much for this!
I messed things up trying to go about this on my own, so now I have deleted the whole project, and will try to start everything all over tomorrow from scratch… I tried updating strapi and ended up with user/permissions js errors which I couldn’t manage to solve (hopefully deleting absolutely everything and starting a completely new project will help me avoid some of my previous mistakes :laughing:).

Can I ask you, maybe you know this: If I disable the draft/publish setting in content types in my new project before I import the books, will all the books then be set to published by default when I import them (so I kind of avoid the whole issue with draft/publish?)

Regardless, I am really so very grateful for you taking the time to try to help me whit my problem!

Wishing you a very good Friday onwards! :blush:

I can’t say I’ve tried, but I would suggest you delete the published_at column from your export before you import it again. (After you disable the draft/publish)

Hi @DMehaffy
Thank you for the advice - I really appreciate that! :blush:
(Going to try doing everything again now, so here goes nothing I guess :flushed::upside_down_face:)

Just a quick update - when I created the new strapi project I disabled the draft/publish function when I created the content type, and this made all the items in the database appear as published. Thus this has so far worked as a workaround so that I not had to publish the drafts one by one.
I did not try @jpizzle34’s solution from above, so I cannot say if that works (but I saw a video tutorial with strapi suggesting a similar solution to his - using published_at; Schedule publication - YouTube) so I would believe that might be a solution as well.

1 Like

Thanks for the amazing solution and info about the bulk update. I don’t use SQL a lot (learned well but haven’t used it in the last 2 years) but I can manage my way around. I am use Jetbrains DataGrip. Can you provide a little more elaboration on updating it via SQL ?

I’m probably a bit late to the party, but I just used this method to bulk update in Strapi v4. There’s a bootstrap function in index.js. I created a new field (“htmlId”) in my faq content-type (for search functionality) so that I can add an anchor tag to each of my faq articles. I used the entityService available in the api controllers to generate an “htmlId” programmatically:

async bootstrap({ strapi }) {
      const items = await strapi.entityService.findMany("api::faq.faq");
      console.log("items: ", items);
      try {
        items.forEach((item) => {
          strapi.entityService.update("api::faq.faq", item.id, {
            data: {
              htmlId: `Faq_${item.id}`,
            },
          });
        });
      } catch (err) {
        console.log("some error");
   }
}

I’m just a rookie so I’m pretty chuffed I got it to work :grin:
I’m positive the same approach would work for publishing drafts.

it’s not a bulk, it’s just a loop with multiple queries, overloading the DB server

1 Like

Hi, you can show more than 100 pages and after you can publish more than 100.

localhost:1337/admin/content-manager/collectionType/api::hauptartikel.hauptartikel?page=1&pageSize=500&sort=publishedAt:ASC

page= 1 //the number of the page
pageSize=500 //amount of the page, you can use more than 500, but the server might go wild.
sort=“columnName”: asc or desc. //sorting the table.