server/initializers/database.ts

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