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 { VideoSourceModel } from '@server/models/video/video-source'
12 import { LocalVideoViewerModel } from '@server/models/view/local-video-viewer'
13 import { LocalVideoViewerWatchSectionModel } from '@server/models/view/local-video-viewer-watch-section'
14 import { isTestOrDevInstance } from '../helpers/core-utils'
15 import { logger } from '../helpers/logger'
16 import { AbuseModel } from '../models/abuse/abuse'
17 import { AbuseMessageModel } from '../models/abuse/abuse-message'
18 import { VideoAbuseModel } from '../models/abuse/video-abuse'
19 import { VideoCommentAbuseModel } from '../models/abuse/video-comment-abuse'
20 import { AccountModel } from '../models/account/account'
21 import { AccountBlocklistModel } from '../models/account/account-blocklist'
22 import { AccountVideoRateModel } from '../models/account/account-video-rate'
23 import { ActorModel } from '../models/actor/actor'
24 import { ActorFollowModel } from '../models/actor/actor-follow'
25 import { ActorImageModel } from '../models/actor/actor-image'
26 import { ApplicationModel } from '../models/application/application'
27 import { OAuthClientModel } from '../models/oauth/oauth-client'
28 import { OAuthTokenModel } from '../models/oauth/oauth-token'
29 import { VideoRedundancyModel } from '../models/redundancy/video-redundancy'
30 import { PluginModel } from '../models/server/plugin'
31 import { ServerModel } from '../models/server/server'
32 import { ServerBlocklistModel } from '../models/server/server-blocklist'
33 import { UserNotificationSettingModel } from '../models/user/user-notification-setting'
34 import { ScheduleVideoUpdateModel } from '../models/video/schedule-video-update'
35 import { TagModel } from '../models/video/tag'
36 import { ThumbnailModel } from '../models/video/thumbnail'
37 import { VideoModel } from '../models/video/video'
38 import { VideoBlacklistModel } from '../models/video/video-blacklist'
39 import { VideoCaptionModel } from '../models/video/video-caption'
40 import { VideoChangeOwnershipModel } from '../models/video/video-change-ownership'
41 import { VideoChannelModel } from '../models/video/video-channel'
42 import { VideoCommentModel } from '../models/video/video-comment'
43 import { VideoFileModel } from '../models/video/video-file'
44 import { VideoImportModel } from '../models/video/video-import'
45 import { VideoLiveModel } from '../models/video/video-live'
46 import { VideoPlaylistModel } from '../models/video/video-playlist'
47 import { VideoPlaylistElementModel } from '../models/video/video-playlist-element'
48 import { VideoShareModel } from '../models/video/video-share'
49 import { VideoStreamingPlaylistModel } from '../models/video/video-streaming-playlist'
50 import { VideoTagModel } from '../models/video/video-tag'
51 import { VideoViewModel } from '../models/view/video-view'
52 import { CONFIG } from './config'
53 import { VideoChannelSyncModel } from '@server/models/video/video-channel-sync'
55 require('pg').defaults.parseInt8 = true // Avoid BIGINT to be converted to string
57 const dbname = CONFIG.DATABASE.DBNAME
58 const username = CONFIG.DATABASE.USERNAME
59 const password = CONFIG.DATABASE.PASSWORD
60 const host = CONFIG.DATABASE.HOSTNAME
61 const port = CONFIG.DATABASE.PORT
62 const poolMax = CONFIG.DATABASE.POOL.MAX
64 let dialectOptions: any = {}
66 if (CONFIG.DATABASE.SSL) {
69 rejectUnauthorized: false
74 const sequelizeTypescript = new SequelizeTypescript({
85 benchmark: isTestOrDevInstance(),
86 isolationLevel: Transaction.ISOLATION_LEVELS.SERIALIZABLE,
87 logging: (message: string, benchmark: number) => {
88 if (process.env.NODE_DB_LOG === 'false') return
90 let newMessage = 'Executed SQL request'
91 if (isTestOrDevInstance() === true && benchmark !== undefined) {
92 newMessage += ' in ' + benchmark + 'ms'
95 logger.debug(newMessage, { sql: message, tags: [ 'sql' ] })
99 function checkDatabaseConnectionOrDie () {
100 sequelizeTypescript.authenticate()
101 .then(() => logger.debug('Connection to PostgreSQL has been established successfully.'))
104 logger.error('Unable to connect to PostgreSQL database.', { err })
109 async function initDatabaseModels (silent: boolean) {
110 sequelizeTypescript.addModels([
120 AccountVideoRateModel,
124 VideoCommentAbuseModel,
127 VideoChangeOwnershipModel,
136 ScheduleVideoUpdateModel,
139 VideoRedundancyModel,
140 UserVideoHistoryModel,
142 VideoLiveSessionModel,
143 AccountBlocklistModel,
144 ServerBlocklistModel,
145 UserNotificationModel,
146 UserNotificationSettingModel,
147 VideoStreamingPlaylistModel,
149 VideoPlaylistElementModel,
150 LocalVideoViewerModel,
151 LocalVideoViewerWatchSectionModel,
156 ActorCustomPageModel,
158 VideoChannelSyncModel
161 // Check extensions exist in the database
162 await checkPostgresExtensions()
164 // Create custom PostgreSQL functions
165 await createFunctions()
167 if (!silent) logger.info('Database %s is ready.', dbname)
170 // ---------------------------------------------------------------------------
174 checkDatabaseConnectionOrDie,
178 // ---------------------------------------------------------------------------
180 async function checkPostgresExtensions () {
182 checkPostgresExtension('pg_trgm'),
183 checkPostgresExtension('unaccent')
186 return Promise.all(promises)
189 async function checkPostgresExtension (extension: string) {
190 const query = `SELECT 1 FROM pg_available_extensions WHERE name = '${extension}' AND installed_version IS NOT NULL;`
192 type: QueryTypes.SELECT as QueryTypes.SELECT,
196 const res = await sequelizeTypescript.query<object>(query, options)
198 if (!res || res.length === 0) {
199 // Try to create the extension ourselves
201 await sequelizeTypescript.query(`CREATE EXTENSION ${extension};`, { raw: true })
204 const errorMessage = `You need to enable ${extension} extension in PostgreSQL. ` +
205 `You can do so by running 'CREATE EXTENSION ${extension};' as a PostgreSQL super user in ${CONFIG.DATABASE.DBNAME} database.`
206 throw new Error(errorMessage)
211 function createFunctions () {
212 const query = `CREATE OR REPLACE FUNCTION immutable_unaccent(text)
215 SELECT public.unaccent('public.unaccent', $1::text)
216 $func$ LANGUAGE sql IMMUTABLE;`
218 return sequelizeTypescript.query(query, { raw: true })