server/initializers/database.ts

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