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 { UserRegistrationModel } from '@server/models/user/user-registration'
9 import { UserVideoHistoryModel } from '@server/models/user/user-video-history'
10 import { VideoChannelSyncModel } from '@server/models/video/video-channel-sync'
11 import { VideoJobInfoModel } from '@server/models/video/video-job-info'
12 import { VideoLiveSessionModel } from '@server/models/video/video-live-session'
13 import { VideoSourceModel } from '@server/models/video/video-source'
14 import { LocalVideoViewerModel } from '@server/models/view/local-video-viewer'
15 import { LocalVideoViewerWatchSectionModel } from '@server/models/view/local-video-viewer-watch-section'
16 import { isTestOrDevInstance } from '../helpers/core-utils'
17 import { logger } from '../helpers/logger'
18 import { AbuseModel } from '../models/abuse/abuse'
19 import { AbuseMessageModel } from '../models/abuse/abuse-message'
20 import { VideoAbuseModel } from '../models/abuse/video-abuse'
21 import { VideoCommentAbuseModel } from '../models/abuse/video-comment-abuse'
22 import { AccountModel } from '../models/account/account'
23 import { AccountBlocklistModel } from '../models/account/account-blocklist'
24 import { AccountVideoRateModel } from '../models/account/account-video-rate'
25 import { ActorModel } from '../models/actor/actor'
26 import { ActorFollowModel } from '../models/actor/actor-follow'
27 import { ActorImageModel } from '../models/actor/actor-image'
28 import { ApplicationModel } from '../models/application/application'
29 import { OAuthClientModel } from '../models/oauth/oauth-client'
30 import { OAuthTokenModel } from '../models/oauth/oauth-token'
31 import { VideoRedundancyModel } from '../models/redundancy/video-redundancy'
32 import { PluginModel } from '../models/server/plugin'
33 import { ServerModel } from '../models/server/server'
34 import { ServerBlocklistModel } from '../models/server/server-blocklist'
35 import { UserNotificationSettingModel } from '../models/user/user-notification-setting'
36 import { ScheduleVideoUpdateModel } from '../models/video/schedule-video-update'
37 import { TagModel } from '../models/video/tag'
38 import { ThumbnailModel } from '../models/video/thumbnail'
39 import { VideoModel } from '../models/video/video'
40 import { VideoBlacklistModel } from '../models/video/video-blacklist'
41 import { VideoCaptionModel } from '../models/video/video-caption'
42 import { VideoChangeOwnershipModel } from '../models/video/video-change-ownership'
43 import { VideoChannelModel } from '../models/video/video-channel'
44 import { VideoCommentModel } from '../models/video/video-comment'
45 import { VideoFileModel } from '../models/video/video-file'
46 import { VideoImportModel } from '../models/video/video-import'
47 import { VideoLiveModel } from '../models/video/video-live'
48 import { VideoPlaylistModel } from '../models/video/video-playlist'
49 import { VideoPlaylistElementModel } from '../models/video/video-playlist-element'
50 import { VideoShareModel } from '../models/video/video-share'
51 import { VideoStreamingPlaylistModel } from '../models/video/video-streaming-playlist'
52 import { VideoTagModel } from '../models/video/video-tag'
53 import { VideoViewModel } from '../models/view/video-view'
54 import { CONFIG } from './config'
56 require('pg').defaults.parseInt8 = true // Avoid BIGINT to be converted to string
58 const dbname = CONFIG.DATABASE.DBNAME
59 const username = CONFIG.DATABASE.USERNAME
60 const password = CONFIG.DATABASE.PASSWORD
61 const host = CONFIG.DATABASE.HOSTNAME
62 const port = CONFIG.DATABASE.PORT
63 const poolMax = CONFIG.DATABASE.POOL.MAX
65 let dialectOptions: any = {}
67 if (CONFIG.DATABASE.SSL) {
70 rejectUnauthorized: false
75 const sequelizeTypescript = new SequelizeTypescript({
86 benchmark: isTestOrDevInstance(),
87 isolationLevel: Transaction.ISOLATION_LEVELS.SERIALIZABLE,
88 logging: (message: string, benchmark: number) => {
89 if (process.env.NODE_DB_LOG === 'false') return
91 let newMessage = 'Executed SQL request'
92 if (isTestOrDevInstance() === true && benchmark !== undefined) {
93 newMessage += ' in ' + benchmark + 'ms'
96 logger.debug(newMessage, { sql: message, tags: [ 'sql' ] })
100 function checkDatabaseConnectionOrDie () {
101 sequelizeTypescript.authenticate()
102 .then(() => logger.debug('Connection to PostgreSQL has been established successfully.'))
105 logger.error('Unable to connect to PostgreSQL database.', { err })
110 async function initDatabaseModels (silent: boolean) {
111 sequelizeTypescript.addModels([
121 AccountVideoRateModel,
125 VideoCommentAbuseModel,
128 VideoChangeOwnershipModel,
137 ScheduleVideoUpdateModel,
140 VideoRedundancyModel,
141 UserVideoHistoryModel,
143 VideoLiveSessionModel,
144 AccountBlocklistModel,
145 ServerBlocklistModel,
146 UserNotificationModel,
147 UserNotificationSettingModel,
148 VideoStreamingPlaylistModel,
150 VideoPlaylistElementModel,
151 LocalVideoViewerModel,
152 LocalVideoViewerWatchSectionModel,
157 ActorCustomPageModel,
159 VideoChannelSyncModel,
160 UserRegistrationModel
163 // Check extensions exist in the database
164 await checkPostgresExtensions()
166 // Create custom PostgreSQL functions
167 await createFunctions()
169 if (!silent) logger.info('Database %s is ready.', dbname)
172 // ---------------------------------------------------------------------------
176 checkDatabaseConnectionOrDie,
180 // ---------------------------------------------------------------------------
182 async function checkPostgresExtensions () {
184 checkPostgresExtension('pg_trgm'),
185 checkPostgresExtension('unaccent')
188 return Promise.all(promises)
191 async function checkPostgresExtension (extension: string) {
192 const query = `SELECT 1 FROM pg_available_extensions WHERE name = '${extension}' AND installed_version IS NOT NULL;`
194 type: QueryTypes.SELECT as QueryTypes.SELECT,
198 const res = await sequelizeTypescript.query<object>(query, options)
200 if (!res || res.length === 0) {
201 // Try to create the extension ourselves
203 await sequelizeTypescript.query(`CREATE EXTENSION ${extension};`, { raw: true })
206 const errorMessage = `You need to enable ${extension} extension in PostgreSQL. ` +
207 `You can do so by running 'CREATE EXTENSION ${extension};' as a PostgreSQL super user in ${CONFIG.DATABASE.DBNAME} database.`
208 throw new Error(errorMessage)
213 function createFunctions () {
214 const query = `CREATE OR REPLACE FUNCTION immutable_unaccent(text)
217 SELECT public.unaccent('public.unaccent', $1::text)
218 $func$ LANGUAGE sql IMMUTABLE;`
220 return sequelizeTypescript.query(query, { raw: true })