Our problem
With the quite recent launch of solid's own meta framework "start" we got a new (very performant) way to create MVCs. There is one issue with that: there is no (native) way to couple that to a database, which is why we are here.
Getting started
We will start by creating a new project with the setup wizard. First we create a new folder with mkdir psql-with-solid-start
and cd psql-with-solid-start
to change into the folder. There we start the wizard by typing npm init solid
. I picked the todomvc
template, so we already have something that we can connect to a database:
create-solid version 0.1.2
Welcome to the SolidStart setup wizard!
There are definitely bugs and some feature might not work yet.
If you encounter an issue, have a look at https://github.com/solidjs/solid-start/issues and open a new one, if it is not already tracked.
? Which template do you want to use? › - Use arrow-keys. Return to submit.
bare
hackernews
❯ todomvc
with-auth
with-mdx
with-prisma
with-solid-styled
with-tailwindcss
with-vitest
with-websocket
I picked server side rendering and typescript to setup the project.
Then you also need to install all the packages with a package manager of your choice. I started using yarn since I code rails, so I'll stick to it. We just type yarn
into our console.
What do we have?
If you start the server with npm run dev -- --open
a browser window will open up with our page, which already has a functional ToDo app.
More interesting for us is the file structure and especially the /src
folder:
If you open up the db.ts
folder you should see, that that is where all the magic happens. All of our ToDos get stored in an array, which we want to persist.
Let's persist!
Because the framework is so new we don't have any native database connections or ORMs yet. What we do have is a huge JavaScript ecosystem at our disposal, which we will use in our case.
Creating the database
We will be using PostgreSQL, so you need to have that pre-installed on your machine. You can either use docker or just have it on your machine.
If you have it on your machine, you can enter the service by typing psql -U <username> -d postgres
into your terminal. There you can list all databases with \l
. We will want to create a new one with SQL
CREATE DATABASE psql_with_solid_start;
\c psql_with_solid_start
There we create our table for the ToDos. If you want to check out what attributes you exactly need, you can console.log
them:
We can see that it has three attributes: completed
, id
, title
. That means, that our table will also need those attributes, so it functions properly:
psql_with_solid_start=# create table todos (
id serial primary key,
title varchar(255),
completed boolean default false
);
Connecting the application to it
Next, we need to actually connect our application to the database. For that we will use the db.ts
file. If we look into it, we can see that that is where our TODOs already get stored (into an array at the moment):
Here you can also access the code
Now we start off by installing the postgres
npm package. Because I word with yarn
I execute yarn add postgres
in our terminal.
The next step is to connect a client to the database. The postgres
npm package is actually very well documented, if you want to read trough that yourself.
We will do all the connection in our db.ts
file, because we already started doing it in there.
First we import postgres and then use it to create a connection instance into a const
var:
import postgres from "postgres";
const sql= postgres({
host: "localhost",
port: 5432,
database: "psql_with_solid_start",
username: "<USERNAME>"
});
This should already yield us our connection to the PostgreSQL database.
Querying the DB
Next we want to connect our application to CRUD with the database instead of the Array.
For that we need to rewrite the methods in our default export inside of db.ts
. With sql'<Query Here>'
we can query our database. If we now rewrite all the queries we should get something as follows:
export default {
getTodos() {
return sql`SELECT * FROM todos`;
// return delay(() => TODOS);
},
addTodo(title: string) {
return sql`INSERT INTO todos (title, completed) VALUES (${title}, false)`;
// return delay(() => TODOS.push({ id: COUNTER++, title, completed: false }));
},
removeTodo(id: number) {
return sql`DELETE FROM todos WHERE id = (${id})`;
// return delay(() => (TODOS = TODOS.filter((todo) => todo.id !== id)));
},
toggleTodo(id: number) {
return sql`UPDATE todos SET completed = NOT completed WHERE id = (${id})`;
// return delay(() =>
// TODOS.forEach(
// (todo) => todo.id === id && (todo.completed = !todo.completed)
// )
// );
},
editTodo(id: number, title: string) {
return sql`UPDATE todos SET title = (${title}) WHERE id = (${id})`;
// return delay(() =>
// TODOS.forEach((todo) => {
// if (todo.id === id) todo.title = title;
// })
// );
},
clearCompleted() {
return sql`DELETE FROM todos WHERE completed = true`;
// return delay(() => (TODOS = TODOS.filter((todo) => !todo.completed)));
},
toggleAll(completed: boolean) {
return sql`UPDATE todos SET completed = (${completed})`;
// return delay(() => TODOS.forEach((todo) => (todo.completed = !completed)));
},
};
You should make sure, that you also have brackets around all the user inputs, so we sanitize the queries. The repository README promises to parameterize the queries which would make it secure, but it still may be injectable, so use with caution.
Now if we check if our application does what it should we quickly see that it doesn't work:
To fix that we add a return type any
to our getTodos()
function:
getTodos():any {
return (sql`SELECT * FROM todos`);
// return delay(() => TODOS);
},
That will only fix our Typescript issue. Next we also need to add a db.getTodos
below the createServerData
on line 23 in our src/routes/index.tsx
:
createServerData$(db.getTodos, { initialValue: [] });
db.getTodos
Now we can reload and finally see our webpage:
We can also query in our database and see that it works:
Adressing the console issue
In our console we still see error messages:
That is because the library was written for deno. Vite, which is used by start, tries to optimize it and throws this error. To prevent it from doing that we can exclude it in our vite.config.ts
file:
import solid from "solid-start/vite";
import { defineConfig } from "vite";
export default defineConfig({
plugins: [solid()],
optimizeDeps: {
exclude: ['postgres']
}
});
After restarting the server we shouldn't see any errors anymore.
Conclusion
Creating a few things with solid start was very fun, but it is still very young (less than a month old), so bugs and missing features are expected.
As of now, this is the best method I found to connect to PostgreSQL, but there probably are better ones. There is also the chance that this is still SQL-inject-able. PRs are (as always) open!
The source code is here
Top comments (6)
Yes, this code is indeed vulnerable to SQL injection. Long story short: use parameter queries for user input in order to ensure correct escaping.
I actually found out, that the library promises to handle that by itself. Here you can read more about it.
Ah, they are using the direct template literal call to ensure correct escaping. That's quite ingenious, I must concede.
Yes, if we log our insert query we can see what it does:
Thanks so far..
where to put this code-snippet?
createServerData$(db.getTodos, { initialValue: [] });
db.getTodos
Werner
My bad, I forgot to mention that. You put it on
line 23
inside thesrc/routes/index.tsx
file. Maybe this helps