database.ts 8.8 KB

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