server/initializers/database.ts

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