Im currently trying to create a simple todo using express, node, postgresql and react. Im trying to post a basic description via postman but im getting syntax error at or near “-“
here’s my code:
//db.js
const Pool = require("pg").Pool;
const pool = new Pool({
user: 'postgres',
host: 'localhost',
port: 5432,
database: 'crud',
password: 'password'
});
module.exports = pool
//index.js
const express = require("express")
const app = express()
const cors = require("cors")
const pool = require("./db")
//middleware
app.use(cors());
app.use(express.json())
//routes
app.post("/todos", async (req, res) => {
try {
const { description } = req.body
const newTodo = await pool.query(
`INSERT INTO todo-list (description) VALUES($1) RETURNING *`,
[description]
);
res.json(newTodo.rows[0])
} catch (err) {
console.error(err.message)
}
})
//get all todos
app.get("/todos", async (req, res) => {
try {
const allTodos = await pool.query("SELECT * FROM todo-list")
res.json(allTodos.rows)
} catch (err) {
console.log(err.message)
}
})
app.listen(8001, () => {
console.log("server is running on port 8001")
})
//.env
DATABASE_URL=postgres://postgres:password@localhost:5432/crud
1
Per:
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($). Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable. The SQL standard will not define a key word that contains digits or starts or ends with an underscore, so identifiers of this form are safe against possible conflict with future extensions of the standard.
Further down:
There is a second kind of identifier: the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes (“). A delimited identifier is always an identifier, never a key word. So “select” could be used to refer to a column or table named “select”, whereas an unquoted select would be taken as a key word and would therefore provoke a parse error when used where a table or column name is expected. The example can be written with quoted identifiers like this:
UPDATE “my_table” SET “a” = 5;
Quoted identifiers can contain any character, except the character with code zero. (To include a double quote, write two double quotes.) This allows constructing table or column names that would otherwise not be possible, such as ones containing spaces or ampersands. The length limitation still applies.
If you actually have a table named todo-list then it needs to double quoted when creating it and referring to it:
create table "todo-list"(id integer);
select * from "todo-list";
id
----
(0 rows)
--Versus what you are seeing.
select * from todo-list;
ERROR: syntax error at or near "-"
I would stick with _ and eliminate the whole double quoting issue.
1