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