How to use a database with Replit

1CREATE TABLE IF NOT EXISTS users (
2  id SERIAL PRIMARY KEY,
3  email TEXT UNIQUE NOT NULL,
4  name TEXT NOT NULL,
5  created_at TIMESTAMP DEFAULT NOW()
6);
7
8CREATE TABLE IF NOT EXISTS posts (
9  id SERIAL PRIMARY KEY,
10  user_id INTEGER REFERENCES users(id),
11  title TEXT NOT NULL,
12  content TEXT,
13  published BOOLEAN DEFAULT FALSE,
14  created_at TIMESTAMP DEFAULT NOW()
15);
16
17-- Verify tables were created
18SELECT table_name FROM information_schema.tables
19WHERE table_schema = 'public';

1// Install in Shell: npm install pg
2
3// src/db.js
4const { Pool } = require('pg');
5
6const pool = new Pool({
7  connectionString: process.env.DATABASE_URL,
8  ssl: false  // Not needed for Replit's internal connection
9});
10
11// Test connection on startup
12pool.query('SELECT NOW()', (err, res) => {
13  if (err) console.error('Database connection failed:', err.message);
14  else console.log('Database connected at:', res.rows[0].now);
15});
16
17// Export query helper
18module.exports = {
19  query: (text, params) => pool.query(text, params),
20  pool
21};

1// src/routes/users.js
2const express = require('express');
3const db = require('../db');
4const router = express.Router();
5
6// Create a user
7router.post('/', async (req, res) => {
8  try {
9    const { email, name } = req.body;
10    const result = await db.query(
11      'INSERT INTO users (email, name) VALUES ($1, $2) RETURNING *',
12      [email, name]
13    );
14    res.status(201).json(result.rows[0]);
15  } catch (err) {
16    if (err.code === '23505') {
17      res.status(409).json({ error: 'Email already exists' });
18    } else {
19      res.status(500).json({ error: err.message });
20    }
21  }
22});
23
24// Get all users
25router.get('/', async (req, res) => {
26  const result = await db.query(
27    'SELECT id, email, name, created_at FROM users ORDER BY created_at DESC'
28  );
29  res.json(result.rows);
30});
31
32module.exports = router;

1// Node.js: npm install @replit/database
2const Database = require('@replit/database');
3const db = new Database();
4
5// Set a value
6await db.set('settings', { theme: 'dark', language: 'en' });
7
8// Get a value
9const settings = await db.get('settings');
10console.log(settings); // { theme: 'dark', language: 'en' }
11
12// List all keys
13const keys = await db.list();
14console.log(keys);
15
16// Delete a key
17await db.delete('settings');

1// Initialize tables on startup (works in both dev and prod)
2const db = require('./db');
3
4async function initDatabase() {
5  await db.query(`
6    CREATE TABLE IF NOT EXISTS users (
7      id SERIAL PRIMARY KEY,
8      email TEXT UNIQUE NOT NULL,
9      name TEXT NOT NULL,
10      created_at TIMESTAMP DEFAULT NOW()
11    )
12  `);
13  console.log('Database tables initialized');
14}
15
16initDatabase().catch(console.error);