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