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