server/initializers/database.ts

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