Replace Post Content using SQL Query

Latest version on DO droplet with updated NodeJS using modified Dawn theme.

If I were to replace a small part that is available in all posts using an SQL-query, how would I do that? For example: replace hello_ with goodbye_ in all posts.

When connected to the droplet and changed into ghost-mgr, could I run something like this →

update posts set post_content = replace( post_content, 'hello_', 'goodbye_' );

Or is the command different? If someone could please help out, I would appreciate it. Sorry for tagging directly, but could you please help out @Kevin ? I have abut 5 000 posts in total (local and product env) that I need to alter.

It’s always preferable to replace content using the API than via SQL as it ensures the whole system is in sync and it’s always up-to-date with the internal workings of Ghost. There’s an API demos repo that contains an example find-and-replace script for this exact use-case.

If there’s no other choice but to use SQL there are currently three columns you’d need to update, mobiledoc (the source format) plus html and plaintext which are generated when saving via the API.

Thanks Kevin. Not sure I understand how to edit the code and from where I run it. Would you mind editing the below code by inserting my hello_ and goodbye_ as above?

/**
 * Find and replace in all posts
 *
 * TODO: you must edit this script to adjust what is being found and replaced!
 *
 * Usage:
 *
 * node all-posts-find-and-replace.js https://blah.ghost.io ADMIN_API_KEY
 */

if (process.argv.length < 4) {
    console.error('Missing an argument');
    process.exit(1);
}

const url = process.argv[2];
const key = process.argv[3];

const Promise = require('bluebird');
const GhostAdminAPI = require('@tryghost/admin-api');
const api = new GhostAdminAPI({
    url,
    key,
    version: 'v2'
});

(async function main() {
    try {
        // Admin API automatically includes tags and authors
        // WARNING: If the site is really big (1000s of posts) maybe do this paginated
        const allPosts = await api.posts.browse({limit: 'all'});

        // convert our list of posts, to a list of promises for requests to the api
        const result = await Promise.mapSeries(allPosts, async (post) => {
            // Edit mobiledoc
            // @NOTE: if you're editing a string that might appear in mobiledoc structure, edit the HTML instead!
            // E.g. "sections" or "markups"
            // See Edit the HTML below
            post.mobiledoc = post.mobiledoc.replace(/github/gmi, 'GitHub');

            console.log('Updating', post.slug);
            // Call the API
            let result = await api.posts.edit(post);

            // Edit the HTML
            // post.html = post.html.replace(/github/gmi, 'GitHub');

            // console.log('Updating', post.slug);
            // // Call the API
            // // @NOTE: source HTML forces the mobiledoc to get overridden here!
            // let result = await api.posts.edit(post, {source: 'html'});

            // Add a delay but return the original result
            return Promise.delay(50).return(result);
        });

        console.log(`Updated ${result.length} posts`);
    } catch (err) {
        console.error('There was an error', require('util').inspect(err, false, null));
        process.exit(1);
    }
}());

This is the line you need to edit:

post.mobiledoc = post.mobiledoc.replace(/github/gmi, 'GitHub');

For your example it would be changed to:

post.mobiledoc = post.mobiledoc.replace(/hello_/gmi, 'goodbye_');

Awesome. Thanks. From where do I run it? As an injection within <script> tags or?

Docs are in the repo https://github.com/TryGhost/api-demos

Hi I find many of the docs a bit sparse. If you haven’t been dealing with this before it’s far from obvious how to actually use the scripts. I wish there was a simple step by step how-to. And the link in the doc you refer to (“ Learn more about the Core API in our docs” lead to a 404.

1 Like

Sorry, but I am non the wiser. I would appreciate some sort of guide / instruction other than what’s available in the description.

git clone git@github.com:TryGhost/api-demos.git
cd api-demos
yarn
(...modify file as needed...)
node all-posts-find-and-replace.js "https://blah.ghost.io" ADMIN_API_KEY

Not too sure what could be clearer :confused:

Then in the script file there’s the individual script instructions:

/**
 * Find and replace in all posts
 *
 * TODO: you must edit this script to adjust what is being found and replaced!
 *
 * Usage:
 *
 * node all-posts-find-and-replace.js https://blah.ghost.io ADMIN_API_KEY
 */

Thank you Kevin! Appreciate it. I’ll run a test on my local install to test it out.

On a side note:
Considering the fact that Ghost targets publishers and user like it, which in my understanding are users who write and post content, there are a lot of things that could be made easier and more relatable / understandable.

I come from WordPress and I’ve been working with WordPress and WooCommerce for many years. Most things about Ghost come relatively easy to me, while some do not.

I think you should consider having users who are trying to understand what Ghost is, users who wants to test it and install it for the first time – have them read the docs and, as an example, the information you gave me about the “replace post content”. See if they understand it.

I’m just happy that I do come from a “techie” world and because of that, at least relate to most of what users here talk about.

Hi again,

I just tried to do follow the steps you posted and I got an error on my localhost saying that the repo was denied access…? Something with the publickey?

git@github.com: Permission denied (publickey).
fatal: Could not read from remote repository.

Please make sure you have the correct access rights
and the repository exists.

So, I then tried to copy the js file into my theme folder. I changed what should be replaced and took the token from Staff and my user. It then said that bluebird was missing.

Error: Cannot find module 'bluebird'

Any suggestions on how to proceed?

Your git setup isn’t configured to use ssh. Try git clone https://github.com/TryGhost/api-demos.git instead.

That wouldn’t work, the scripts use the node modules defined in the repo’s package.json and installed locally with yarn so they need to be run from the same directory.

I think you should consider having users who are trying to understand what Ghost is, users who wants to test it and install it for the first time – have them read the docs and, as an example, the information you gave me about the “replace post content”. See if they understand it.

Those are not the intended audience for the API demos, the demos are intended for developers as technical examples of how to use the API.

Tried again and the git got cloned. I then ran the yarn command. Now trying to figure out the rest as all I get are errors. I’ll get back to you with info on how I progress.

While I understand where you’re coming from and what you mean; I feel that you took that part a bit out of context. Maybe I should have been clearer.

I was and I am, referring to the docs as a whole. They are not, in my opinion, very clear or easy to understand on occasion. I have seen many posts here, asking for basic things because they did not understand the documentation.

This is a general thing from what I’ve seen and witnessed. In other words; it is the same with WordPress and other CMS platforms. It is easy to “spot” that developers wrote the docs, not someone who wants to make the information easy to understand, follow and / or use.

All I’m saying is this; I don’t think that I am alone in wanting “easier” docs for most parts related to Ghost.

I had to spend well over a week getting started before I felt comfortable using the system on an actual website.

With all of that said; if I can be of any help making it better - please let me know.

1 Like

Hi again Kevin,

Created a new local install and ran all the commands. All worked fine until the last step, whereof I am not sure I made a mistake or not?

This is the output:

node all-posts-find-and-replace.js "http://localhost:2369" 609a50b3cba44036bdece83b:a16ffb6ecb4dfc7cc5c348d0668cad9fb5172ec181c7fee0c129c183a8886312
Updating welcome
There was an error Error [ValidationError]: Validation error, cannot edit post.
    at /home/bear/Documents/GD/api-demos/node_modules/@tryghost/admin-api/lib/index.js:347:33
    at processTicksAndRejections (internal/process/task_queues.js:93:5)
    at async /home/bear/Documents/GD/api-demos/all-posts-find-and-replace.js:43:26 {
  context: 'Validation failed for posts[0].',
  type: 'ValidationError',
  details: [
    {
      keyword: 'additionalProperties',
      dataPath: '.posts[0]',
      schemaPath: '#/additionalProperties',
      params: { additionalProperty: 'frontmatter' },
      message: 'should NOT have additional properties'
    }
  ],
  property: 'posts[0]',
  help: null,
  code: null,
  id: '27b7acb0-b23d-11eb-9483-2b644ba560bc'
}

If I’m doing it wrong, please let me know.

Can you try changing v2 to v3 on this line?

Output:

node all-posts-find-and-replace.js "http://localhost:2369" 609a50b3cba44036bdece83b:a16ffb6ecb4dfc7cc5c348d0668cad9fb5172ec181c7fee0c129c183a8886312
Updating welcome
There was an error Error [ValidationError]: Validation error, cannot edit post.
    at /home/bear/Documents/GD/api-demos/node_modules/@tryghost/admin-api/lib/index.js:347:33
    at processTicksAndRejections (internal/process/task_queues.js:93:5)
    at async /home/bear/Documents/GD/api-demos/all-posts-find-and-replace.js:43:26 {
  context: 'Validation failed for posts[0].',
  type: 'ValidationError',
  details: [
    {
      keyword: 'additionalProperties',
      dataPath: '.posts[0]',
      schemaPath: '#/additionalProperties',
      params: { additionalProperty: 'frontmatter' },
      message: 'should NOT have additional properties'
    }
  ],
  property: 'posts[0]',
  help: null,
  code: null,
  id: 'ead29620-b241-11eb-8715-c97c25865ff2'
}

Any other suggestions?

Looks like there’s a bug in the v2 and v3 APIs, they shouldn’t be returning the recently-added frontmatter field in fetch requests or ignoring it if it’s included in an update request. It’s been reported internally and is being tracked.

You can change the version to v4 or canary which should both work.

1 Like

All versions including canary returns the same output as posted above.

Hey @thebear.dev . The fix for this bug landed in the upstream. It should be available with next release.

For now the workaround can be removing this field from the PUT request in your script. For for example with the all-posts-find-and-replace.js script you can do:

post.mobiledoc = post.mobiledoc.replace(/github/gmi, 'GitHub');
delete post.frontmatter;
console.log('Updating', post.slug);

The delete will get rid of the field in the request and the API should not be complaining any longer :wink:

2 Likes