server/initializers/database.ts

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