Plugin

If you want to extend Platformatic DB features, it is possible to register a plugin, which will be in the form of a standard Fastify plugin.

The config file will specify where the plugin file is located as the example below:

{
  ...
  "plugins": {
    "paths": ["./plugin/index.js"]
  }
}

The paths are relative to the config file path.

Once the config file is set up, you can write your plugin to extend Platformatic DB API or write your custom business logic.

You should export an async function which receives a parameters:

• app (FastifyInstance) the main Fastify instance running Platformatic DB.
• opts all the options specified in the config file after path.

You can always access Platformatic data mapper through app.platformatic property.

Context Integration in Plugin Operations

To ensure robust authorization and data management, it's important to pass the context object to the entity mapper. This context includes user-specific data, permissions, and other parameters that influence how data operations are executed.

Here's how you can integrate context into your plugin:

app.post('/', async (req, reply) => {
  const ctx = req.platformaticContext

  await app.platformatic.entities.movies.find({
    where: {
      /*...*/
    },
    ctx
  })
})

Accessing the Authenticated User

When authorization is configured (JWT, webhook or the development headers), custom routes can access the authenticated user's metadata by populating it explicitly with request.setupDBAuthorizationUser():

export default async function (app) {
  app.get('/whoami', async (req, reply) => {
    await req.setupDBAuthorizationUser()

    // req.user contains the metadata extracted from the JWT claims
    // or returned by the webhook
    return {
      userId: req.user['X-PLATFORMATIC-USER-ID'],
      roles: req.user['X-PLATFORMATIC-ROLE']
    }
  })
}

Unauthenticated requests leave req.user undefined, so remember to handle that case. When accessing entities on behalf of the user, prefer passing ctx (see above): the authorization rules are then enforced automatically.

Running Custom SQL Queries

Besides the generated entities, plugins can run any SQL query through app.platformatic.db and the app.platformatic.sql template tag, which safely interpolates values as query parameters:

export default async function (app) {
  app.get('/most-quoted-movies', async (req, reply) => {
    const { db, sql } = app.platformatic
    const limit = 10

    return db.query(sql`
      SELECT movies.title, COUNT(quotes.id) AS quotes
      FROM movies
      JOIN quotes ON quotes.movie_id = movies.id
      GROUP BY movies.title
      ORDER BY quotes DESC
      LIMIT ${limit}
    `)
  })
}

Never concatenate user input into the query string: always interpolate values with the sql tag (or use sql.ident for identifiers) so they are passed as bound parameters.

Queries can also participate in transactions through app.platformatic.db.tx:

await app.platformatic.db.tx(async tx => {
  await tx.query(sql`UPDATE accounts SET balance = balance - 100 WHERE id = ${from}`)
  await tx.query(sql`UPDATE accounts SET balance = balance + 100 WHERE id = ${to}`)
})

For the complete query API, see the @databases documentation, which Platformatic DB uses under the hood.

Handling File Uploads

The generated CRUD APIs work with JSON, not multipart payloads. To accept file uploads, register @fastify/multipart in a plugin and store the file wherever fits your deployment, saving only its reference through the entities:

import multipart from '@fastify/multipart'
import { createWriteStream } from 'node:fs'
import { pipeline } from 'node:stream/promises'
import { join } from 'node:path'

export default async function (app) {
  await app.register(multipart)

  app.post('/users/:id/avatar', async (req, reply) => {
    const file = await req.file()
    const path = join('uploads', `${req.params.id}-${file.filename}`)
    await pipeline(file.file, createWriteStream(path))

    const ctx = req.platformaticContext
    await app.platformatic.entities.user.save({
      input: { id: req.params.id, avatarPath: path },
      ctx
    })

    return { uploaded: path }
  })
}

For production workloads, prefer uploading files directly to object storage (e.g. S3 with pre-signed URLs generated by a custom route) and storing only the resulting URL in the database — the upload traffic then bypasses your API entirely.

Hot Reload

Plugin files are monitored by the fs.watch function.

You don't need to reload Platformatic DB server while working on your plugin. Every time you save, the watcher will trigger a reload event and the server will auto-restart and load your updated code.

tip

At this time, on Linux, file watch in subdirectories is not supported due to a Node.js limitation (documented here).

Directories

The path can also be a directory. In that case, the directory will be loaded with @fastify/autoload.

Consider the following directory structure:

├── routes
│   ├── foo
│   │   ├── something.js
│   │   └── bar
│   │       └── baz.js
│   ├── single-plugin
│   │   └── utils.js
│   └── another-plugin.js
└── platformatic.service.json

By default, the folder will be added as a prefix to all the routes defined within them. See the autoload documentation for all the options to customize this behavior.

Multiple plugins

Multiple plugins can be loaded in parallel by specifying an array:

{
  ...
  "plugins": {
    "paths": [{
      "path": "./plugin/index.js"
    }, {
      "path": "./routes/"
    }]
  }
}

TypeScript and autocompletion

If you want to access any of the types provided by Platformatic DB, generate them using the Watt's $db:types command (where $db is the id of your Platformatic DB service). This will create a plt-env.d.ts file that add Platformatic types to fastify instances. This file is included automatically by Typescript, unless you have the includes option set in your tsconfig.json. In that case you need to add plt-env.d.ts to the include list manually.

Plugin definition with TypeScript

Here is an example of writing a plugin in TypeScript:

import { type FastifyInstance, type FastifyPluginOptions } from 'fastify'

export default async function  (fastify: FastifyInstance, opts: FastifyPluginOptions) {}

Issues

If you run into a bug or have a suggestion for improvement, please raise an issue on GitHub or join our Discord feedback channel.