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 import { VideoChannelSyncModel } from '@server/models/video/video-channel-sync'
  54
  55 require('pg').defaults.parseInt8 = true // Avoid BIGINT to be converted to string
  56
  57 const dbname = CONFIG.DATABASE.DBNAME
  58 const username = CONFIG.DATABASE.USERNAME
  59 const password = CONFIG.DATABASE.PASSWORD
  60 const host = CONFIG.DATABASE.HOSTNAME
  61 const port = CONFIG.DATABASE.PORT
  62 const poolMax = CONFIG.DATABASE.POOL.MAX
  63
  64 let dialectOptions: any = {}
  65
  66 if (CONFIG.DATABASE.SSL) {
  67   dialectOptions = {
  68     ssl: {
  69       rejectUnauthorized: false
  70     }
  71   }
  72 }
  73
  74 const sequelizeTypescript = new SequelizeTypescript({
  75   database: dbname,
  76   dialect: 'postgres',
  77   dialectOptions,
  78   host,
  79   port,
  80   username,
  81   password,
  82   pool: {
  83     max: poolMax
  84   },
  85   benchmark: isTestOrDevInstance(),
  86   isolationLevel: Transaction.ISOLATION_LEVELS.SERIALIZABLE,
  87   logging: (message: string, benchmark: number) => {
  88     if (process.env.NODE_DB_LOG === 'false') return
  89
  90     let newMessage = 'Executed SQL request'
  91     if (isTestOrDevInstance() === true && benchmark !== undefined) {
  92       newMessage += ' in ' + benchmark + 'ms'
  93     }
  94
  95     logger.debug(newMessage, { sql: message, tags: [ 'sql' ] })
  96   }
  97 })
  98
  99 function checkDatabaseConnectionOrDie () {
 100   sequelizeTypescript.authenticate()
 101     .then(() => logger.debug('Connection to PostgreSQL has been established successfully.'))
 102     .catch(err => {
 103
 104       logger.error('Unable to connect to PostgreSQL database.', { err })
 105       process.exit(-1)
 106     })
 107 }
 108
 109 async function initDatabaseModels (silent: boolean) {
 110   sequelizeTypescript.addModels([
 111     ApplicationModel,
 112     ActorModel,
 113     ActorFollowModel,
 114     ActorImageModel,
 115     AccountModel,
 116     OAuthClientModel,
 117     OAuthTokenModel,
 118     ServerModel,
 119     TagModel,
 120     AccountVideoRateModel,
 121     UserModel,
 122     AbuseMessageModel,
 123     AbuseModel,
 124     VideoCommentAbuseModel,
 125     VideoAbuseModel,
 126     VideoModel,
 127     VideoChangeOwnershipModel,
 128     VideoChannelModel,
 129     VideoShareModel,
 130     VideoFileModel,
 131     VideoSourceModel,
 132     VideoCaptionModel,
 133     VideoBlacklistModel,
 134     VideoTagModel,
 135     VideoCommentModel,
 136     ScheduleVideoUpdateModel,
 137     VideoImportModel,
 138     VideoViewModel,
 139     VideoRedundancyModel,
 140     UserVideoHistoryModel,
 141     VideoLiveModel,
 142     VideoLiveSessionModel,
 143     AccountBlocklistModel,
 144     ServerBlocklistModel,
 145     UserNotificationModel,
 146     UserNotificationSettingModel,
 147     VideoStreamingPlaylistModel,
 148     VideoPlaylistModel,
 149     VideoPlaylistElementModel,
 150     LocalVideoViewerModel,
 151     LocalVideoViewerWatchSectionModel,
 152     ThumbnailModel,
 153     TrackerModel,
 154     VideoTrackerModel,
 155     PluginModel,
 156     ActorCustomPageModel,
 157     VideoJobInfoModel,
 158     VideoChannelSyncModel
 159   ])
 160
 161   // Check extensions exist in the database
 162   await checkPostgresExtensions()
 163
 164   // Create custom PostgreSQL functions
 165   await createFunctions()
 166
 167   if (!silent) logger.info('Database %s is ready.', dbname)
 168 }
 169
 170 // ---------------------------------------------------------------------------
 171
 172 export {
 173   initDatabaseModels,
 174   checkDatabaseConnectionOrDie,
 175   sequelizeTypescript
 176 }
 177
 178 // ---------------------------------------------------------------------------
 179
 180 async function checkPostgresExtensions () {
 181   const promises = [
 182     checkPostgresExtension('pg_trgm'),
 183     checkPostgresExtension('unaccent')
 184   ]
 185
 186   return Promise.all(promises)
 187 }
 188
 189 async function checkPostgresExtension (extension: string) {
 190   const query = `SELECT 1 FROM pg_available_extensions WHERE name = '${extension}' AND installed_version IS NOT NULL;`
 191   const options = {
 192     type: QueryTypes.SELECT as QueryTypes.SELECT,
 193     raw: true
 194   }
 195
 196   const res = await sequelizeTypescript.query<object>(query, options)
 197
 198   if (!res || res.length === 0) {
 199     // Try to create the extension ourselves
 200     try {
 201       await sequelizeTypescript.query(`CREATE EXTENSION ${extension};`, { raw: true })
 202
 203     } catch {
 204       const errorMessage = `You need to enable ${extension} extension in PostgreSQL. ` +
 205         `You can do so by running 'CREATE EXTENSION ${extension};' as a PostgreSQL super user in ${CONFIG.DATABASE.DBNAME} database.`
 206       throw new Error(errorMessage)
 207     }
 208   }
 209 }
 210
 211 function createFunctions () {
 212   const query = `CREATE OR REPLACE FUNCTION immutable_unaccent(text)
 213   RETURNS text AS
 214 $func$
 215 SELECT public.unaccent('public.unaccent', $1::text)
 216 $func$  LANGUAGE sql IMMUTABLE;`
 217
 218   return sequelizeTypescript.query(query, { raw: true })
 219 }