PostgreSQL on Replit

What is PostgreSQL?

PostgreSQL is a powerful, open source object-relational database system that is widely used in web applications and other software development projects. We use PostgreSQL version 15 in our integration, which allows you to easily create a production-ready PostgreSQL database directly within Replit. You can run queries and connect to the database using our provided environment variables. For more information on PostgreSQL, visit the official documentation.

Why use PostgreSQL?

Simplicity: With our integration, you can easily set up a PostgreSQL database without having to install any additional software or configure any settings. All you need to do is click a button and you'll have a fully-functional database ready to go.
Seamless integration: Our integration is designed to work seamlessly with Replit, so you can easily access your database and run queries within the Replit environment or within your code with minimal configuration.
Production-ready: The database created with our integration is production-ready, so you can use it for real-world applications and projects.
Convenience: With our provided environment variables, you can easily connect to the database from your code, without having to worry about setting up the connection manually. This saves you time and makes it easier to get your project up and running.
Neon documentation: Provides guides on how to connect various frameworks, such as Django, to your PostgreSQL database in the neon documentation.
Connection pooling: If you need to enable connection pooling for any reason, you can contact us and we'll do it manually for you. More information on connection pooling can be found in the neon documentation.
Compute lifecycle: The database will go to sleep after 5 minutes of no queries, so you may experience disconnects. More information on the compute lifecycle can be found in the Neon documentation

Setup

Open a new tab in Replit and type "PostgreSQL"
In the "PostgreSQL" panel, click "Buy Cycles & create a DB" or "Use Cycles to create a DB"
In the env sectino, you can view all of the relevant connection information about your database.

Note: At this time, we do not offer usage based billing for PostgreSQL.

SQL Explorer

We provide a SQL explorer that you can use to create tables and manage your database. And if you have purchased Ghostwriter, you can use that within the SQL explorer to help you write queries.

Usage (NodeJS)

Preparation

Use the Packager to install knex and pg

Connect to the DB with Knex using the URL secret

const knex = require("knex")({
  // We are using PostgreSQL
  client: "postgres",
  // Use the `DATABASE_URL` environment variable we provide to connect to the Database
  // It is included in your Replit environment automatically (no need to set it up)
  connection: process.env.DATABASE_URL,

  // Optionally, you can use connection pools to increase query performance
  pool: { min: 0, max: 80 },
});

Use an Async IIFE so we can use await

(async () => {
  // Leave this empty for now
})();

Code

Create a table

await knex.schema.createTable("users", (table) => {
  table.increments("id");
  table.string("username");
  table.integer("points");
});

Insert a row

await knex("users").insert({
  username: "alice",
  points: 0,
});

Update the row

await knex("users")
  .where("username", "alice")
  .update({
    points: knex.raw("points + 1"),
  });

Get the row

const user = await knex('users')
    .where('username', 'alice')
    .first();
    ```

Print the row to the console
```
console.log(user);
```

Delete the row

await knex("users").where("username", "alice").delete();

Final Code

// Connect to the Database
const knex = require("knex")({
  // We are using PostgreSQL
  client: "postgres",
  // Use the `DATABASE_URL` environment variable we provide to connect to the Database
  // It is included in your Replit environment automatically (no need to set it up)
  connection: process.env.DATABASE_URL,

  // Optionally, you can use connection pools to increase query performance
  pool: { min: 0, max: 80 },
});

(async () => {
  // Create a demo table called "users"
  await knex.schema.createTable("users", (table) => {
    table.increments("id");
    table.string("username");
    table.integer("points");
  });

  // Add a demo user to the table with 0 points
  await knex("users").insert({
    username: "alice",
    points: 0,
  });

  // Increment the points by 1
  await knex("users")
    .where("username", "alice")
    .update({
      points: knex.raw("points + 1"),
    });

  // Get the user
  const user = await knex("users").where("username", "alice").first();

  // Print the user
  console.log(user);

  // Delete the user
  await knex("users").where("username", "alice").delete();
})();

Usage (Python)

Preparation

Use the Packager to install psycopg2

Connect to the DB with psycopg2 using the URL secret

import psycopg2.pool

# Create a connection pool with a min_size of 0 and a max_size of 80
# Use the `DATABASE_URL` environment variable we provide to connect to the Database
# It is included in your Replit environment automatically (no need to set it up)
pool = psycopg2.pool.SimpleConnectionPool(0, 80, process.env.DATABASE_URL)

# Get a connection from the pool
conn = pool.getconn()

# Create a cursor using the connection
cur = conn.cursor()

# Do your database operations using the cursor
# <Your code goes here>

# Close the cursor and return the connection to the pool
cur.close()
pool.putconn(conn)

# When you are done using the pool, close it to release the resources
pool.closeall()

Code

Create a table

cur.execute(
    """
    CREATE TABLE users (
        id SERIAL PRIMARY KEY,
        username VARCHAR(255),
        points INTEGER
    )
    """
)

Insert a row

cur.execute(
    """
    INSERT INTO users (username, points)
    VALUES (%s, %s)
    """,
    ('alice', 0)
)

Update the row

cur.execute(
    """
    UPDATE users
    SET points = points + 1
    WHERE username = %s
    """,
    ('alice',)
)

Get the row

cur.execute(
    """
    SELECT *
    FROM users
    WHERE username = %s
    """,
    ('alice',)
)
user = cur.fetchone()

Print the row to the console
```
print(user)
```

Delete the row

cur.execute(
    """
    DELETE FROM users
    WHERE username = %s
    """,
    ('alice',)
)

Final Code

import psycopg2.pool


# Create a connection pool with a min_size of 0 and a max_size of 80
# Use the `DATABASE_URL` environment variable we provide to connect to the Database
# It is included in your Replit environment automatically (no need to set it up)
pool = psycopg2.pool.SimpleConnectionPool(0, 80, process.env.DATABASE_URL)

# Get a connection from the pool
conn = pool.getconn()

# Create a cursor using the connection
cur = conn.cursor()

# Do your database operations using the cursor

# Create a demo table called "users"
cur.execute(
    """
    CREATE TABLE users (
        id SERIAL PRIMARY KEY,
        username VARCHAR(255),
        points INTEGER
    )
    """
)

# Add a demo user to the table with 0 points
cur.execute(
    """
    INSERT INTO users (username, points)
    VALUES (%s, %s)
    """,
    ('alice', 0)
)

# Increment the points by 1
cur.execute(
    """
    UPDATE users
    SET points = points + 1
    WHERE username = %s
    """,
    ('alice',)
)

# Get the user
cur.execute(
    """
    SELECT *
    FROM users
    WHERE username = %s
    """,
    ('alice',)
)

user = cur.fetchone()

# Print the user
print(user)

# Delete the user
cur.execute(
    """
    DELETE FROM users
    WHERE username = %s
    """,
    ('alice',)
)

# Close the cursor and return the connection to the pool
cur.close()
pool.putconn(conn)

# When you are done using the pool, close it to release the resources
pool.closeall()

PostgreSQL on Replit

What is PostgreSQL?​

Why use PostgreSQL?​

Setup​

SQL Explorer​

Usage (NodeJS)​

Preparation​

Code​

Final Code​

Usage (Python)​

Preparation​

Code​

Final Code​

What is PostgreSQL?

Why use PostgreSQL?

Setup

SQL Explorer

Usage (NodeJS)

Preparation

Code

Final Code

Usage (Python)

Preparation

Code

Final Code