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