1 import { QueryTypes, Transaction } from 'sequelize'
2 import { Sequelize as SequelizeTypescript } from 'sequelize-typescript'
3 import { ActorCustomPageModel } from '@server/models/account/actor-custom-page'
4 import { TrackerModel } from '@server/models/server/tracker'
5 import { VideoTrackerModel } from '@server/models/server/video-tracker'
6 import { UserModel } from '@server/models/user/user'
7 import { UserNotificationModel } from '@server/models/user/user-notification'
8 import { UserVideoHistoryModel } from '@server/models/user/user-video-history'
9 import { VideoJobInfoModel } from '@server/models/video/video-job-info'
10 import { VideoLiveSessionModel } from '@server/models/video/video-live-session'
11 import { LocalVideoViewerModel } from '@server/models/view/local-video-viewer'
12 import { LocalVideoViewerWatchSectionModel } from '@server/models/view/local-video-viewer-watch-section'
13 import { isTestInstance } from '../helpers/core-utils'
14 import { logger } from '../helpers/logger'
15 import { AbuseModel } from '../models/abuse/abuse'
16 import { AbuseMessageModel } from '../models/abuse/abuse-message'
17 import { VideoAbuseModel } from '../models/abuse/video-abuse'
18 import { VideoCommentAbuseModel } from '../models/abuse/video-comment-abuse'
19 import { AccountModel } from '../models/account/account'
20 import { AccountBlocklistModel } from '../models/account/account-blocklist'
21 import { AccountVideoRateModel } from '../models/account/account-video-rate'
22 import { ActorModel } from '../models/actor/actor'
23 import { ActorFollowModel } from '../models/actor/actor-follow'
24 import { ActorImageModel } from '../models/actor/actor-image'
25 import { ApplicationModel } from '../models/application/application'
26 import { OAuthClientModel } from '../models/oauth/oauth-client'
27 import { OAuthTokenModel } from '../models/oauth/oauth-token'
28 import { VideoRedundancyModel } from '../models/redundancy/video-redundancy'
29 import { PluginModel } from '../models/server/plugin'
30 import { ServerModel } from '../models/server/server'
31 import { ServerBlocklistModel } from '../models/server/server-blocklist'
32 import { UserNotificationSettingModel } from '../models/user/user-notification-setting'
33 import { ScheduleVideoUpdateModel } from '../models/video/schedule-video-update'
34 import { TagModel } from '../models/video/tag'
35 import { ThumbnailModel } from '../models/video/thumbnail'
36 import { VideoModel } from '../models/video/video'
37 import { VideoBlacklistModel } from '../models/video/video-blacklist'
38 import { VideoCaptionModel } from '../models/video/video-caption'
39 import { VideoChangeOwnershipModel } from '../models/video/video-change-ownership'
40 import { VideoChannelModel } from '../models/video/video-channel'
41 import { VideoCommentModel } from '../models/video/video-comment'
42 import { VideoFileModel } from '../models/video/video-file'
43 import { VideoImportModel } from '../models/video/video-import'
44 import { VideoLiveModel } from '../models/video/video-live'
45 import { VideoPlaylistModel } from '../models/video/video-playlist'
46 import { VideoPlaylistElementModel } from '../models/video/video-playlist-element'
47 import { VideoShareModel } from '../models/video/video-share'
48 import { VideoStreamingPlaylistModel } from '../models/video/video-streaming-playlist'
49 import { VideoTagModel } from '../models/video/video-tag'
50 import { VideoViewModel } from '../models/view/video-view'
51 import { CONFIG } from './config'
53 require('pg').defaults.parseInt8 = true // Avoid BIGINT to be converted to string
55 const dbname = CONFIG.DATABASE.DBNAME
56 const username = CONFIG.DATABASE.USERNAME
57 const password = CONFIG.DATABASE.PASSWORD
58 const host = CONFIG.DATABASE.HOSTNAME
59 const port = CONFIG.DATABASE.PORT
60 const poolMax = CONFIG.DATABASE.POOL.MAX
62 let dialectOptions: any = {}
64 if (CONFIG.DATABASE.SSL) {
67 rejectUnauthorized: false
72 const sequelizeTypescript = new SequelizeTypescript({
83 benchmark: isTestInstance(),
84 isolationLevel: Transaction.ISOLATION_LEVELS.SERIALIZABLE,
85 logging: (message: string, benchmark: number) => {
86 if (process.env.NODE_DB_LOG === 'false') return
88 let newMessage = 'Executed SQL request'
89 if (isTestInstance() === true && benchmark !== undefined) {
90 newMessage += ' in ' + benchmark + 'ms'
93 logger.debug(newMessage, { sql: message, tags: [ 'sql' ] })
97 function checkDatabaseConnectionOrDie () {
98 sequelizeTypescript.authenticate()
99 .then(() => logger.debug('Connection to PostgreSQL has been established successfully.'))
102 logger.error('Unable to connect to PostgreSQL database.', { err })
107 async function initDatabaseModels (silent: boolean) {
108 sequelizeTypescript.addModels([
118 AccountVideoRateModel,
122 VideoCommentAbuseModel,
125 VideoChangeOwnershipModel,
133 ScheduleVideoUpdateModel,
136 VideoRedundancyModel,
137 UserVideoHistoryModel,
139 VideoLiveSessionModel,
140 AccountBlocklistModel,
141 ServerBlocklistModel,
142 UserNotificationModel,
143 UserNotificationSettingModel,
144 VideoStreamingPlaylistModel,
146 VideoPlaylistElementModel,
147 LocalVideoViewerModel,
148 LocalVideoViewerWatchSectionModel,
153 ActorCustomPageModel,
157 // Check extensions exist in the database
158 await checkPostgresExtensions()
160 // Create custom PostgreSQL functions
161 await createFunctions()
163 if (!silent) logger.info('Database %s is ready.', dbname)
166 // ---------------------------------------------------------------------------
170 checkDatabaseConnectionOrDie,
174 // ---------------------------------------------------------------------------
176 async function checkPostgresExtensions () {
178 checkPostgresExtension('pg_trgm'),
179 checkPostgresExtension('unaccent')
182 return Promise.all(promises)
185 async function checkPostgresExtension (extension: string) {
186 const query = `SELECT 1 FROM pg_available_extensions WHERE name = '${extension}' AND installed_version IS NOT NULL;`
188 type: QueryTypes.SELECT as QueryTypes.SELECT,
192 const res = await sequelizeTypescript.query<object>(query, options)
194 if (!res || res.length === 0) {
195 // Try to create the extension ourselves
197 await sequelizeTypescript.query(`CREATE EXTENSION ${extension};`, { raw: true })
200 const errorMessage = `You need to enable ${extension} extension in PostgreSQL. ` +
201 `You can do so by running 'CREATE EXTENSION ${extension};' as a PostgreSQL super user in ${CONFIG.DATABASE.DBNAME} database.`
202 throw new Error(errorMessage)
207 function createFunctions () {
208 const query = `CREATE OR REPLACE FUNCTION immutable_unaccent(text)
211 SELECT public.unaccent('public.unaccent', $1::text)
212 $func$ LANGUAGE sql IMMUTABLE;`
214 return sequelizeTypescript.query(query, { raw: true })