# Replace Post Content using SQL Query

**URL:** <https://forum.ghost.org/t/replace-post-content-using-sql-query/22549>\
**Category:** Developer help\
**Created:** [May 8, 2021, 5:34am UTC](https://forum.ghost.org/t/replace-post-content-using-sql-query/22549 "2021-05-08T05:34:53Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![thebear.dev](https://sea1.discourse-cdn.com/flex015/user_avatar/forum.ghost.org/thebear.dev/32/13956_2.png) [@thebear.dev](https://forum.ghost.org/u/thebear.dev)\
**Post date:** [May 8, 2021, 5:34am UTC](https://forum.ghost.org/t/replace-post-content-using-sql-query/22549/1 "2021-05-08T05:34:53Z")

</div>

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 →

```auto
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.

---

<div class="post-metadata">

**Author:** ![Kevin](https://sea1.discourse-cdn.com/flex015/user_avatar/forum.ghost.org/kevin/32/32_2.png) [@Kevin](https://forum.ghost.org/u/Kevin)\
**Post date:** [May 10, 2021, 7:30am UTC](https://forum.ghost.org/t/replace-post-content-using-sql-query/22549/2 "2021-05-10T07:30:05Z")

</div>

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](https://github.com/TryGhost/api-demos) repo that contains an example [find-and-replace script](https://github.com/TryGhost/api-demos/blob/master/all-posts-find-and-replace.js) 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.

---

<div class="post-metadata">

**Author:** ![thebear.dev](https://sea1.discourse-cdn.com/flex015/user_avatar/forum.ghost.org/thebear.dev/32/13956_2.png) [@thebear.dev](https://forum.ghost.org/u/thebear.dev)\
**Post date:** [May 10, 2021, 9:28am UTC](https://forum.ghost.org/t/replace-post-content-using-sql-query/22549/3 "2021-05-10T09:28:51Z")

</div>

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?

```auto
/**
 * 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);
    }
}());

```

---

<div class="post-metadata">

**Author:** ![Kevin](https://sea1.discourse-cdn.com/flex015/user_avatar/forum.ghost.org/kevin/32/32_2.png) [@Kevin](https://forum.ghost.org/u/Kevin)\
**Post date:** [May 10, 2021, 9:40am UTC](https://forum.ghost.org/t/replace-post-content-using-sql-query/22549/4 "2021-05-10T09:40:41Z")

</div>

This is the line you need to edit:

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

```

For your example it would be changed to:

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

```

---

<div class="post-metadata">

**Author:** ![thebear.dev](https://sea1.discourse-cdn.com/flex015/user_avatar/forum.ghost.org/thebear.dev/32/13956_2.png) [@thebear.dev](https://forum.ghost.org/u/thebear.dev)\
**Post date:** [May 10, 2021, 9:50am UTC](https://forum.ghost.org/t/replace-post-content-using-sql-query/22549/5 "2021-05-10T09:50:43Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Kevin](https://sea1.discourse-cdn.com/flex015/user_avatar/forum.ghost.org/kevin/32/32_2.png) [@Kevin](https://forum.ghost.org/u/Kevin)\
**Post date:** [May 10, 2021, 10:56am UTC](https://forum.ghost.org/t/replace-post-content-using-sql-query/22549/6 "2021-05-10T10:56:27Z")

</div>

Docs are in the repo [https://github.com/TryGhost/api-demos](https://github.com/TryGhost/api-demos)

---

<div class="post-metadata">

**Author:** ![Caucasus-and-Mercury](https://sea1.discourse-cdn.com/flex015/user_avatar/forum.ghost.org/caucasus-and-mercury/32/2467_2.png) [@Caucasus-and-Mercury](https://forum.ghost.org/u/Caucasus-and-Mercury)\
**Post date:** [May 10, 2021, 11:21am UTC](https://forum.ghost.org/t/replace-post-content-using-sql-query/22549/7 "2021-05-10T11:21:41Z")

</div>

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](https://ghost.org/docs/concepts/core/)” lead to a 404.

---

<div class="post-metadata">

**Author:** ![thebear.dev](https://sea1.discourse-cdn.com/flex015/user_avatar/forum.ghost.org/thebear.dev/32/13956_2.png) [@thebear.dev](https://forum.ghost.org/u/thebear.dev)\
**Post date:** [May 10, 2021, 11:25am UTC](https://forum.ghost.org/t/replace-post-content-using-sql-query/22549/8 "2021-05-10T11:25:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Kevin](https://sea1.discourse-cdn.com/flex015/user_avatar/forum.ghost.org/kevin/32/32_2.png) [@Kevin](https://forum.ghost.org/u/Kevin)\
**Post date:** [May 10, 2021, 11:35am UTC](https://forum.ghost.org/t/replace-post-content-using-sql-query/22549/9 "2021-05-10T11:35:54Z")

</div>

```auto
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 😕

 ![Screenshot 2021-05-10 at 12.37.26](https://us1.discourse-cdn.com/flex015/uploads/ghost2/original/2X/3/3ebff6e9bcf8f0f8e52baa1579b08d0bfc1daf4b.png)

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

```auto
/**
 * 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
 */

```

---

<div class="post-metadata">

**Author:** ![thebear.dev](https://sea1.discourse-cdn.com/flex015/user_avatar/forum.ghost.org/thebear.dev/32/13956_2.png) [@thebear.dev](https://forum.ghost.org/u/thebear.dev)\
**Post date:** [May 11, 2021, 7:17am UTC](https://forum.ghost.org/t/replace-post-content-using-sql-query/22549/10 "2021-05-11T07:17:07Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![thebear.dev](https://sea1.discourse-cdn.com/flex015/user_avatar/forum.ghost.org/thebear.dev/32/13956_2.png) [@thebear.dev](https://forum.ghost.org/u/thebear.dev)\
**Post date:** [May 11, 2021, 8:04am UTC](https://forum.ghost.org/t/replace-post-content-using-sql-query/22549/11 "2021-05-11T08:04:29Z")

</div>

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`?

```auto
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.

```auto
Error: Cannot find module 'bluebird'

```

Any suggestions on how to proceed?

---

<div class="post-metadata">

**Author:** ![Kevin](https://sea1.discourse-cdn.com/flex015/user_avatar/forum.ghost.org/kevin/32/32_2.png) [@Kevin](https://forum.ghost.org/u/Kevin)\
**Post date:** [May 11, 2021, 8:17am UTC](https://forum.ghost.org/t/replace-post-content-using-sql-query/22549/12 "2021-05-11T08:17:10Z")

</div>

> [@thebear.dev](#):
>
> ```auto
> git@github.com: Permission denied (publickey).
> fatal: Could not read from remote repository.
> 
> ```

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

> [@thebear.dev](#):
>
> 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.

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.

---

<div class="post-metadata">

**Author:** ![thebear.dev](https://sea1.discourse-cdn.com/flex015/user_avatar/forum.ghost.org/thebear.dev/32/13956_2.png) [@thebear.dev](https://forum.ghost.org/u/thebear.dev)\
**Post date:** [May 11, 2021, 9:31am UTC](https://forum.ghost.org/t/replace-post-content-using-sql-query/22549/13 "2021-05-11T09:31:43Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![thebear.dev](https://sea1.discourse-cdn.com/flex015/user_avatar/forum.ghost.org/thebear.dev/32/13956_2.png) [@thebear.dev](https://forum.ghost.org/u/thebear.dev)\
**Post date:** [May 11, 2021, 9:45am UTC](https://forum.ghost.org/t/replace-post-content-using-sql-query/22549/14 "2021-05-11T09:45:22Z")

</div>

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:**

```auto
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.

---

<div class="post-metadata">

**Author:** ![Kevin](https://sea1.discourse-cdn.com/flex015/user_avatar/forum.ghost.org/kevin/32/32_2.png) [@Kevin](https://forum.ghost.org/u/Kevin)\
**Post date:** [May 11, 2021, 9:53am UTC](https://forum.ghost.org/t/replace-post-content-using-sql-query/22549/15 "2021-05-11T09:53:32Z")

</div>

Can you try changing `v2` to `v3` on [this line](https://github.com/TryGhost/api-demos/blob/master/all-posts-find-and-replace.js#L24)?

---

<div class="post-metadata">

**Author:** ![thebear.dev](https://sea1.discourse-cdn.com/flex015/user_avatar/forum.ghost.org/thebear.dev/32/13956_2.png) [@thebear.dev](https://forum.ghost.org/u/thebear.dev)\
**Post date:** [May 11, 2021, 10:17am UTC](https://forum.ghost.org/t/replace-post-content-using-sql-query/22549/16 "2021-05-11T10:17:35Z")

</div>

Output:

```auto
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'
}

```

---

<div class="post-metadata">

**Author:** ![thebear.dev](https://sea1.discourse-cdn.com/flex015/user_avatar/forum.ghost.org/thebear.dev/32/13956_2.png) [@thebear.dev](https://forum.ghost.org/u/thebear.dev)\
**Post date:** [May 11, 2021, 2:08pm UTC](https://forum.ghost.org/t/replace-post-content-using-sql-query/22549/17 "2021-05-11T14:08:35Z")

</div>

Any other suggestions?

---

<div class="post-metadata">

**Author:** ![Kevin](https://sea1.discourse-cdn.com/flex015/user_avatar/forum.ghost.org/kevin/32/32_2.png) [@Kevin](https://forum.ghost.org/u/Kevin)\
**Post date:** [May 11, 2021, 2:28pm UTC](https://forum.ghost.org/t/replace-post-content-using-sql-query/22549/18 "2021-05-11T14:28:22Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![thebear.dev](https://sea1.discourse-cdn.com/flex015/user_avatar/forum.ghost.org/thebear.dev/32/13956_2.png) [@thebear.dev](https://forum.ghost.org/u/thebear.dev)\
**Post date:** [May 11, 2021, 2:32pm UTC](https://forum.ghost.org/t/replace-post-content-using-sql-query/22549/19 "2021-05-11T14:32:12Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![naz](https://sea1.discourse-cdn.com/flex015/user_avatar/forum.ghost.org/naz/32/30666_2.png) [@naz](https://forum.ghost.org/u/naz)\
**Post date:** [May 13, 2021, 8:17am UTC](https://forum.ghost.org/t/replace-post-content-using-sql-query/22549/20 "2021-05-13T08:17:38Z")

</div>

Hey @thebear.dev . The fix for this bug [landed in the upstream](https://github.com/TryGhost/Ghost/commit/eb7e4bb8158484f27f46bc6c60de832fdb2dd4ee). 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:

```plaintext
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 ;)

[Next page](https://forum.ghost.org/t/replace-post-content-using-sql-query/22549.md?page=2)