server/initializers/database.ts

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