Since there were several posts related to importing old db data into PocketBase, this post attempts to combine in a single place the previously discussed possible approaches for doing that.

As mentioned in the recently closed related issue, at least for me, import/export of data is a difficult topic to generalize because the desired behavior usually would vary from one project to another.

I'm not sure if the below instructions would be of any help but they could be at least used as pseudo-code examples and a research starting point to create your own custom import/export scripts.

With that said - while it may be strange coming from the author of the project, if you have an existing and working application that you are not planning further developing you might be better sticking with your existing stack instead of migrating to PocketBase because the migration most likely won't be as straightforward as you think, especially if you expect a direct 1:1 migration from PostgreSQL or other database to work out of the box.

v1: Using the PocketBase Web APIs.

Based on the past discussions, this seems to be one of the most popular way for importing/exporting external data.

While pottentially slow, it is a simple and reasonable approach (especially for data sets < 100k records).

It usually requires the collection(s) that you are going to import the data into to be created beforehand either via the UI or progmatically via Go or JS pb_hooks/pb_migrations.

Here is what a minimal Node.js import script could look like:

// node index.js

import { readFile } from "fs/promises";
import PocketBase from "pocketbase";

const pb = new PocketBase("http://127.0.0.1:8090");

// authenticate as superuser
await pb.collection("_superusers").authWithPassword("[email protected]", "1234567890");

// disable autocancellation so that we can send multiple create requests concurrently
pb.autoCancellation(false);

// load the import from a JSON file that has a content like:
// ```
// [
//     {"title": "a"},
//     {"title": "b"},
//     ...
// ]
// ```
// (alternatively you can also load it with import with `{assert: { type: "json" }}`)
const data = JSON.parse(await readFile("./data.json"));

// process the data on chunks per 1k items to prevent exhausting the file descriptors limit
while (data.length) {
  const chunk = data.splice(0, 1000)
  const promises = [];

  for (let item of chunk) {
    promises.push(pb.collection("example").create(item));
  }

  await Promise.all(promises);
}

For exporting collection data, you could simply call const data = pb.collection("example").getFullList().

v2: Custom PocketBase console commands

This is similar to the previous approach but instead of using the Web APIs we can register custom import/export console commands to interact directly with the database.

The below examples are using the JS pb_hooks because they are not documented that well at the moment but you can do the same (and more) with Go.

A minimal JS pb_hooks/import.pb.js import command could look like:

/// <reference path="../pb_data/types.d.ts" />

// ./pocketbase import /path/to/your/data.json YOUR_COLLECTION
$app.rootCmd.addCommand(new Command({
    use: "import",
    run: (cmd, args) => {
        const data = require(args[0])

        const collection = $app.findCollectionByNameOrId(args[1])

        $app.runInTransaction((txApp) => {
            let record;
            for (let item of data) {
                // check https://pocketbase.io/docs/js-records/
                record = new Record(collection)

                // bulk set the item field values
                //
                // note: if you want to set predefined values for "autodate" type fields
                // you'll need to manually load them with record.setRaw("created", val)
                record.load(item)

                // call txApp.saveNoValidate for better performance
                // but be aware that the fields validators will be skipped and could result in data integrity issues
                // if you haven't checked and verified the imported data beforehand
                txApp.save(record)
            }
        })
    },
}))

For export we can do something like:

/// <reference path="../pb_data/types.d.ts" />

// ./pocketbase export YOUR_COLLECTION data.json
$app.rootCmd.addCommand(new Command({
    use: "export",
    run: (cmd, args) => {
        const collectionIdOrName = args[0];
        const outputFile = args[1];

        // note: for simplicity this assume that the data can fit into memory;
        // if that's not the case then you can try loading it on batches
        // and perform a file write for each record (aka. 1 line write per record)
        const data = $app.findAllRecords(collectionIdOrName)

        $os.writeFile(outputFile, JSON.stringify(data), 0644);
    },
}))

v3: Mini Go program that connects to both pb_data and old PostgreSQL/MySQL/etc. database

Because PocketBase is distributed as a regular Go package, you could create a mini Go program that connects to both the PocketBase pb_data and your old external database within the same process and perform the import/export programmatically from there using the PocketBase APIs and dbx abstraction.

This is a little more advanced and a lot more involving from the previous 2 options but it is also more flexible and allow performing the import while the old application is still running which could potentially minimize the downtime.

It is a little outdated but one such example you can find in https://github.com/presentator/v2tov3migrate.

v4: Using external tools

If your exported data is in CSV, one possible solution for example could be to use the sqlite3 .import/.excel/.dump commands, something like:

# it requires the collections to be created upfront
$: sqlite3 pb_data/data.db
$(sqlite): .import --csv --skip 1 /path/to/data.csv yourCollection

Another option could be to use a comminity maintained import tool, such as https://github.com/michal-kapala/pocketbase-import (I haven't personally tested it and I'm not sure how well it works in practice).

v5: RAW SQLite scripts

This is another more traditional approach but if you are migrating from non-SQLite database it requires to manually adjust your PostgreSQL/MySQL/etc. dump to be SQLite syntax compatible.

This approach also requires the related _collections table entries to be created beforehand so that the inserted data to be accessible in the PocketBase dashboard and Web APIs (I recommend creating the collections from the UI or programmatically with the Collection model helpers via Go or JS pb_hooks/pb_migrations).

Note that while we use and store the collections data in plain SQLite tables, PocketBase has its own nuances what column type and how the data is structured/used (e.g. multivalued fields are stored as serialized JSON array). Therefore my recommendation if you are going with the raw SQLite scripts approach is to first create and play with a test PocketBase application and explore the structure of the generated pb_data/data.db to get a general idea in what format and constraints your imported data are expected to be.