server/initializers/database.ts

   1 import { QueryTypes, Transaction } from 'sequelize'
   2 import { Sequelize as SequelizeTypescript } from 'sequelize-typescript'
   3 import { AbuseModel } from '@server/models/abuse/abuse'
   4 import { AbuseMessageModel } from '@server/models/abuse/abuse-message'
   5 import { VideoAbuseModel } from '@server/models/abuse/video-abuse'
   6 import { VideoCommentAbuseModel } from '@server/models/abuse/video-comment-abuse'
   7 import { isTestInstance } from '../helpers/core-utils'
   8 import { logger } from '../helpers/logger'
   9 import { AccountModel } from '../models/account/account'
  10 import { AccountBlocklistModel } from '../models/account/account-blocklist'
  11 import { AccountVideoRateModel } from '../models/account/account-video-rate'
  12 import { UserModel } from '../models/account/user'
  13 import { UserNotificationModel } from '../models/account/user-notification'
  14 import { UserNotificationSettingModel } from '../models/account/user-notification-setting'
  15 import { UserVideoHistoryModel } from '../models/account/user-video-history'
  16 import { ActorModel } from '../models/activitypub/actor'
  17 import { ActorFollowModel } from '../models/activitypub/actor-follow'
  18 import { ApplicationModel } from '../models/application/application'
  19 import { AvatarModel } from '../models/avatar/avatar'
  20 import { OAuthClientModel } from '../models/oauth/oauth-client'
  21 import { OAuthTokenModel } from '../models/oauth/oauth-token'
  22 import { VideoRedundancyModel } from '../models/redundancy/video-redundancy'
  23 import { PluginModel } from '../models/server/plugin'
  24 import { ServerModel } from '../models/server/server'
  25 import { ServerBlocklistModel } from '../models/server/server-blocklist'
  26 import { ScheduleVideoUpdateModel } from '../models/video/schedule-video-update'
  27 import { TagModel } from '../models/video/tag'
  28 import { ThumbnailModel } from '../models/video/thumbnail'
  29 import { VideoModel } from '../models/video/video'
  30 import { VideoBlacklistModel } from '../models/video/video-blacklist'
  31 import { VideoCaptionModel } from '../models/video/video-caption'
  32 import { VideoChangeOwnershipModel } from '../models/video/video-change-ownership'
  33 import { VideoChannelModel } from '../models/video/video-channel'
  34 import { VideoCommentModel } from '../models/video/video-comment'
  35 import { VideoFileModel } from '../models/video/video-file'
  36 import { VideoImportModel } from '../models/video/video-import'
  37 import { VideoPlaylistModel } from '../models/video/video-playlist'
  38 import { VideoPlaylistElementModel } from '../models/video/video-playlist-element'
  39 import { VideoShareModel } from '../models/video/video-share'
  40 import { VideoStreamingPlaylistModel } from '../models/video/video-streaming-playlist'
  41 import { VideoTagModel } from '../models/video/video-tag'
  42 import { VideoViewModel } from '../models/video/video-view'
  43 import { CONFIG } from './config'
  44
  45 require('pg').defaults.parseInt8 = true // Avoid BIGINT to be converted to string
  46
  47 const dbname = CONFIG.DATABASE.DBNAME
  48 const username = CONFIG.DATABASE.USERNAME
  49 const password = CONFIG.DATABASE.PASSWORD
  50 const host = CONFIG.DATABASE.HOSTNAME
  51 const ssl = CONFIG.DATABASE.SSL
  52 const port = CONFIG.DATABASE.PORT
  53 const poolMax = CONFIG.DATABASE.POOL.MAX
  54
  55 const sequelizeTypescript = new SequelizeTypescript({
  56   database: dbname,
  57   dialect: 'postgres',
  58   host,
  59   port,
  60   username,
  61   password,
  62   ssl,
  63   pool: {
  64     max: poolMax
  65   },
  66   benchmark: isTestInstance(),
  67   isolationLevel: Transaction.ISOLATION_LEVELS.SERIALIZABLE,
  68   logging: (message: string, benchmark: number) => {
  69     if (process.env.NODE_DB_LOG === 'false') return
  70
  71     let newMessage = message
  72     if (isTestInstance() === true && benchmark !== undefined) {
  73       newMessage += ' | ' + benchmark + 'ms'
  74     }
  75
  76     logger.debug(newMessage)
  77   }
  78 })
  79
  80 function checkDatabaseConnectionOrDie () {
  81   sequelizeTypescript.authenticate()
  82     .then(() => logger.debug('Connection to PostgreSQL has been established successfully.'))
  83     .catch(err => {
  84
  85       logger.error('Unable to connect to PostgreSQL database.', { err })
  86       process.exit(-1)
  87     })
  88 }
  89
  90 async function initDatabaseModels (silent: boolean) {
  91   sequelizeTypescript.addModels([
  92     ApplicationModel,
  93     ActorModel,
  94     ActorFollowModel,
  95     AvatarModel,
  96     AccountModel,
  97     OAuthClientModel,
  98     OAuthTokenModel,
  99     ServerModel,
 100     TagModel,
 101     AccountVideoRateModel,
 102     UserModel,
 103     AbuseMessageModel,
 104     AbuseModel,
 105     VideoCommentAbuseModel,
 106     VideoAbuseModel,
 107     VideoModel,
 108     VideoChangeOwnershipModel,
 109     VideoChannelModel,
 110     VideoShareModel,
 111     VideoFileModel,
 112     VideoCaptionModel,
 113     VideoBlacklistModel,
 114     VideoTagModel,
 115     VideoCommentModel,
 116     ScheduleVideoUpdateModel,
 117     VideoImportModel,
 118     VideoViewModel,
 119     VideoRedundancyModel,
 120     UserVideoHistoryModel,
 121     AccountBlocklistModel,
 122     ServerBlocklistModel,
 123     UserNotificationModel,
 124     UserNotificationSettingModel,
 125     VideoStreamingPlaylistModel,
 126     VideoPlaylistModel,
 127     VideoPlaylistElementModel,
 128     ThumbnailModel,
 129     PluginModel
 130   ])
 131
 132   // Check extensions exist in the database
 133   await checkPostgresExtensions()
 134
 135   // Create custom PostgreSQL functions
 136   await createFunctions()
 137
 138   if (!silent) logger.info('Database %s is ready.', dbname)
 139 }
 140
 141 // ---------------------------------------------------------------------------
 142
 143 export {
 144   initDatabaseModels,
 145   checkDatabaseConnectionOrDie,
 146   sequelizeTypescript
 147 }
 148
 149 // ---------------------------------------------------------------------------
 150
 151 async function checkPostgresExtensions () {
 152   const promises = [
 153     checkPostgresExtension('pg_trgm'),
 154     checkPostgresExtension('unaccent')
 155   ]
 156
 157   return Promise.all(promises)
 158 }
 159
 160 async function checkPostgresExtension (extension: string) {
 161   const query = `SELECT 1 FROM pg_available_extensions WHERE name = '${extension}' AND installed_version IS NOT NULL;`
 162   const options = {
 163     type: QueryTypes.SELECT as QueryTypes.SELECT,
 164     raw: true
 165   }
 166
 167   const res = await sequelizeTypescript.query<object>(query, options)
 168
 169   if (!res || res.length === 0) {
 170     // Try to create the extension ourselves
 171     try {
 172       await sequelizeTypescript.query(`CREATE EXTENSION ${extension};`, { raw: true })
 173
 174     } catch {
 175       const errorMessage = `You need to enable ${extension} extension in PostgreSQL. ` +
 176         `You can do so by running 'CREATE EXTENSION ${extension};' as a PostgreSQL super user in ${CONFIG.DATABASE.DBNAME} database.`
 177       throw new Error(errorMessage)
 178     }
 179   }
 180 }
 181
 182 function createFunctions () {
 183   const query = `CREATE OR REPLACE FUNCTION immutable_unaccent(text)
 184   RETURNS text AS
 185 $func$
 186 SELECT public.unaccent('public.unaccent', $1::text)
 187 $func$  LANGUAGE sql IMMUTABLE;`
 188
 189   return sequelizeTypescript.query(query, { raw: true })
 190 }