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