vnext: initial database connection

7 files changed, 44 insertions, 3 deletions

diff --git a/docker-compose.yml b/docker-compose.yml
index ce9d409f..10152551 100644
--- a/docker-compose.yml
+++ b/docker-compose.yml
@@ -8,9 +8,12 @@ services:
     restart: unless-stopped
     ports:
       - "127.0.0.1:8081:8081"
+    extra_hosts:
+      - "host.docker.internal:host-gateway"
     environment:
       JUICK_FCM_SERVICE_ACCOUNT_FILE: ${JUICK_FCM_SERVICE_ACCOUNT_FILE}
       DEBUG: sender,durov,http,event,hms,android
       NODE_CONFIG_DIR: /etc/juick
+      PG_HOST: host.docker.internal
     volumes:
       - /etc/juick:/etc/juick
diff --git a/package-lock.json b/package-lock.json
index 7350e614..ac060d88 100644
--- a/package-lock.json
+++ b/package-lock.json
@@ -21,6 +21,7 @@
         "mailparser": "^3.7.1",
         "node-pushnotifications": "^3.1.1",
         "node-telegram-bot-api": "^0.66.0",
+        "postgres": "^3.4.4",
         "react": "^18.3.1",
         "react-content-loader": "^7.0.2",
         "react-dom": "^18.3.1",
@@ -16816,6 +16817,18 @@
       "resolved": "https://registry.npmjs.org/postcss-value-parser/-/postcss-value-parser-4.2.0.tgz",
       "integrity": "sha512-1NNCs6uurfkVbeXG4S8JFT9t19m45ICnif8zWLd5oPSZ50QnwMfK+H3jv408d4jw/7Bttv5axS5IiHoLaVNHeQ=="
     },
+    "node_modules/postgres": {
+      "version": "3.4.4",
+      "resolved": "https://registry.npmjs.org/postgres/-/postgres-3.4.4.tgz",
+      "integrity": "sha512-IbyN+9KslkqcXa8AO9fxpk97PA4pzewvpi2B3Dwy9u4zpV32QicaEdgmF3eSQUzdRk7ttDHQejNgAEr4XoeH4A==",
+      "engines": {
+        "node": ">=12"
+      },
+      "funding": {
+        "type": "individual",
+        "url": "https://github.com/sponsors/porsager"
+      }
+    },
     "node_modules/prebuild-install": {
       "version": "7.1.2",
       "resolved": "https://registry.npmjs.org/prebuild-install/-/prebuild-install-7.1.2.tgz",
diff --git a/package.json b/package.json
index fcbc3acf..aad48c1f 100644
--- a/package.json
+++ b/package.json
@@ -79,6 +79,7 @@
     "mailparser": "^3.7.1",
     "node-pushnotifications": "^3.1.1",
     "node-telegram-bot-api": "^0.66.0",
+    "postgres": "^3.4.4",
     "react": "^18.3.1",
     "react-content-loader": "^7.0.2",
     "react-dom": "^18.3.1",
diff --git a/vnext/server/Dockerfile b/vnext/server/Dockerfile
index 26e962fd..5c08a968 100644
--- a/vnext/server/Dockerfile
+++ b/vnext/server/Dockerfile
@@ -12,7 +12,8 @@ ENV NODE_ENV=production
 ENV NPM_CONFIG_LOGLEVEL=warn
 ENV DEBUG=http
 
-# Expose the listening port of your app
-EXPOSE 8081
+RUN useradd juick
+USER juick
 
-CMD [ "node", "server.js" ]
+EXPOSE 8081
+CMD ["node", "server.js"]
diff --git a/vnext/server/db/index.js b/vnext/server/db/index.js
new file mode 100644
index 00000000..7a029627
--- /dev/null
+++ b/vnext/server/db/index.js
@@ -0,0 +1,5 @@
+import postgres from 'postgres'
+
+const sql = postgres({ database: 'juick', host: process.env.PG_HOST })
+
+export default sql
diff --git a/vnext/server/db/users.js b/vnext/server/db/users.js
new file mode 100644
index 00000000..2201b1ef
--- /dev/null
+++ b/vnext/server/db/users.js
@@ -0,0 +1,14 @@
+import sql from './index'
+
+/**
+ *  Count users
+ */
+export async function get_count() {
+    const count = await sql`
+      select
+        count(*)
+      from users
+      where banned=0
+    `
+    return count
+}
diff --git a/vnext/server/index.js b/vnext/server/index.js
index eaf1ae8a..d58fe4c7 100644
--- a/vnext/server/index.js
+++ b/vnext/server/index.js
@@ -15,6 +15,7 @@ import { instance } from './middleware/mastodon'
 const PORT = process.env.LISTEN_PORT || 8081
 import path from 'path'
 import { webhook, webhookPath } from './durov'
+import { get_count } from './db/users'
 
 // initialize the application and create the routes
 const app = express()
@@ -49,6 +50,9 @@ router.use('*', serverRenderer)
 
 app.use(router)
 
+get_count().then(count => {
+    log(`Registered users: ${JSON.stringify(count)}`)
+}).catch(log)
 
 // start the app
 app.listen(PORT, (error) => {