strong-tie/inbound-calls

1<h1 align="center">Fastify</h1>2 3## Database4 5Fastify's ecosystem provides a handful of6plugins for connecting to various database engines.7This guide covers engines that have Fastify8plugins maintained within the Fastify organization.9 10> If a plugin for your database of choice does not exist11> you can still use the database as Fastify is database agnostic.12> By following the examples of the database plugins listed in this guide,13> a plugin can be written for the missing database engine.14 15> If you would like to write your own Fastify plugin16> please take a look at the [plugins guide](./Plugins-Guide.md)17 18### [MySQL](https://github.com/fastify/fastify-mysql)19 20Install the plugin by running `npm i @fastify/mysql`.21 22*Usage:*23 24```javascript25const fastify = require('fastify')()26 27fastify.register(require('@fastify/mysql'), {28  connectionString: 'mysql://root@localhost/mysql'29})30 31fastify.get('/user/:id', function(req, reply) {32  fastify.mysql.query(33    'SELECT id, username, hash, salt FROM users WHERE id=?', [req.params.id],34    function onResult (err, result) {35      reply.send(err || result)36    }37  )38})39 40fastify.listen({ port: 3000 }, err => {41  if (err) throw err42  console.log(`server listening on ${fastify.server.address().port}`)43})44```45 46### [Postgres](https://github.com/fastify/fastify-postgres)47Install the plugin by running `npm i pg @fastify/postgres`.48 49*Example*:50 51```javascript52const fastify = require('fastify')()53 54fastify.register(require('@fastify/postgres'), {55  connectionString: 'postgres://postgres@localhost/postgres'56})57 58fastify.get('/user/:id', function (req, reply) {59  fastify.pg.query(60    'SELECT id, username, hash, salt FROM users WHERE id=$1', [req.params.id],61    function onResult (err, result) {62      reply.send(err || result)63    }64  )65})66 67fastify.listen({ port: 3000 }, err => {68  if (err) throw err69  console.log(`server listening on ${fastify.server.address().port}`)70})71```72 73### [Redis](https://github.com/fastify/fastify-redis)74Install the plugin by running `npm i @fastify/redis`75 76*Usage:*77 78```javascript79'use strict'80 81const fastify = require('fastify')()82 83fastify.register(require('@fastify/redis'), { host: '127.0.0.1' })84// or85fastify.register(require('@fastify/redis'), { url: 'redis://127.0.0.1', /* other redis options */ })86 87fastify.get('/foo', function (req, reply) {88  const { redis } = fastify89  redis.get(req.query.key, (err, val) => {90    reply.send(err || val)91  })92})93 94fastify.post('/foo', function (req, reply) {95  const { redis } = fastify96  redis.set(req.body.key, req.body.value, (err) => {97    reply.send(err || { status: 'ok' })98  })99})100 101fastify.listen({ port: 3000 }, err => {102  if (err) throw err103  console.log(`server listening on ${fastify.server.address().port}`)104})105```106 107By default `@fastify/redis` doesn't close108the client connection when Fastify server shuts down.109To opt-in to this behavior, register the client like so:110 111```javascript112fastify.register(require('@fastify/redis'), {113  client: redis,114  closeClient: true115})116```117 118### [Mongo](https://github.com/fastify/fastify-mongodb)119Install the plugin by running `npm i @fastify/mongodb`120 121*Usage:*122```javascript123const fastify = require('fastify')()124 125fastify.register(require('@fastify/mongodb'), {126  // force to close the mongodb connection when app stopped127  // the default value is false128  forceClose: true,129 130  url: 'mongodb://mongo/mydb'131})132 133fastify.get('/user/:id', async function (req, reply) {134  // Or this.mongo.client.db('mydb').collection('users')135  const users = this.mongo.db.collection('users')136 137  // if the id is an ObjectId format, you need to create a new ObjectId138  const id = this.mongo.ObjectId(req.params.id)139  try {140    const user = await users.findOne({ id })141    return user142  } catch (err) {143    return err144  }145})146 147fastify.listen({ port: 3000 }, err => {148  if (err) throw err149})150```151 152### [LevelDB](https://github.com/fastify/fastify-leveldb)153Install the plugin by running `npm i @fastify/leveldb`154 155*Usage:*156```javascript157const fastify = require('fastify')()158 159fastify.register(160  require('@fastify/leveldb'),161  { name: 'db' }162)163 164fastify.get('/foo', async function (req, reply) {165  const val = await this.level.db.get(req.query.key)166  return val167})168 169fastify.post('/foo', async function (req, reply) {170  await this.level.db.put(req.body.key, req.body.value)171  return { status: 'ok' }172})173 174fastify.listen({ port: 3000 }, err => {175  if (err) throw err176  console.log(`server listening on ${fastify.server.address().port}`)177})178```179 180### Writing plugin for a database library181We could write a plugin for a database182library too (e.g. Knex, Prisma, or TypeORM).183We will use [Knex](https://knexjs.org/) in our example.184 185```javascript186'use strict'187 188const fp = require('fastify-plugin')189const knex = require('knex')190 191function knexPlugin(fastify, options, done) {192  if(!fastify.knex) {193    const knex = knex(options)194    fastify.decorate('knex', knex)195 196    fastify.addHook('onClose', (fastify, done) => {197      if (fastify.knex === knex) {198        fastify.knex.destroy(done)199      }200    })201  }202 203  done()204}205 206export default fp(knexPlugin, { name: 'fastify-knex-example' })207```208 209### Writing a plugin for a database engine210 211In this example, we will create a basic Fastify MySQL plugin from scratch (it is212a stripped-down example, please use the official plugin in production).213 214```javascript215const fp = require('fastify-plugin')216const mysql = require('mysql2/promise')217 218function fastifyMysql(fastify, options, done) {219  const connection = mysql.createConnection(options)220 221  if (!fastify.mysql) {222    fastify.decorate('mysql', connection)223  }224 225  fastify.addHook('onClose', (fastify, done) => connection.end().then(done).catch(done))226 227  done()228}229 230export default fp(fastifyMysql, { name: 'fastify-mysql-example' })231```232 233### Migrations234 235Database schema migrations are an integral part of database management and236development. Migrations provide a repeatable and testable way to modify a237database's schema and prevent data loss.238 239As stated at the beginning of the guide, Fastify is database agnostic and any240Node.js database migration tool can be used with it. We will give an example of241using [Postgrator](https://www.npmjs.com/package/postgrator) which has support242for Postgres, MySQL, SQL Server and SQLite. For MongoDB migrations, please check243[migrate-mongo](https://www.npmjs.com/package/migrate-mongo).244 245#### [Postgrator](https://www.npmjs.com/package/postgrator)246 247Postgrator is Node.js SQL migration tool that uses a directory of SQL scripts to248alter the database schema. Each file in a migrations folder need to follow the249pattern: ` [version].[action].[optional-description].sql`.250 251**version:** must be an incrementing number (e.g. `001` or a timestamp).252 253**action:** should be `do` or `undo`. `do` implements the version, `undo`254reverts it. Think about it like `up` and `down` in other migration tools.255 256**optional-description** describes which changes migration makes. Although257optional, it should be used for all migrations as it makes it easier for258everyone to know which changes are made in a migration.259 260In our example, we are going to have a single migration that creates a `users`261table and we are going to use `Postgrator` to run the migration.262 263> Run `npm i pg postgrator` to install dependencies needed for the264> example.265 266```sql267// 001.do.create-users-table.sql268CREATE TABLE IF NOT EXISTS users (269  id SERIAL PRIMARY KEY NOT NULL,270  created_at DATE NOT NULL DEFAULT CURRENT_DATE,271  firstName TEXT NOT NULL,272  lastName TEXT NOT NULL273);274```275```javascript276const pg = require('pg')277const Postgrator = require('postgrator')278const path = require('node:path')279 280async function migrate() {281  const client = new pg.Client({282    host: 'localhost',283    port: 5432,284    database: 'example',285    user: 'example',286    password: 'example',287  });288 289  try {290    await client.connect();291 292    const postgrator = new Postgrator({293      migrationPattern: path.join(__dirname, '/migrations/*'),294      driver: 'pg',295      database: 'example',296      schemaTable: 'migrations',297      currentSchema: 'public', // Postgres and MS SQL Server only298      execQuery: (query) => client.query(query),299    });300 301    const result = await postgrator.migrate()302 303    if (result.length === 0) {304      console.log(305        'No migrations run for schema "public". Already at the latest one.'306      )307    }308 309    console.log('Migration done.')310 311    process.exitCode = 0312  } catch(err) {313    console.error(err)314    process.exitCode = 1315  }316 317  await client.end()318}319 320migrate()321```322