DEV Community 👩‍💻👨‍💻

Cover image for Solid Start with PostgreSQL
anes
anes

Posted on • Updated on

Solid Start with PostgreSQL

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
Enter fullscreen mode Exit fullscreen mode

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:
A screenshot of our file system with the src folder already open
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
Enter fullscreen mode Exit fullscreen mode

There we create our table for the ToDos. If you want to check out what attributes you exactly need, you can console.log them:
Screenshot of a todo item when you console log it
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
    );
Enter fullscreen mode Exit fullscreen mode

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):
Screenshot of the base
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>"
});
Enter fullscreen mode Exit fullscreen mode

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)));
  },
};
Enter fullscreen mode Exit fullscreen mode

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:
An error being displayed
To fix that we add a return type any to our getTodos() function:

getTodos():any {
  return (sql`SELECT * FROM todos`);
  // return delay(() => TODOS);
},
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Now we can reload and finally see our webpage:
Two todos with one being crossed out
We can also query in our database and see that it works:
A select * from todos showing that we have 2 entries

Adressing the console issue

In our console we still see error messages:
An error message in the console saying "Big Integer literals are not available"
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']
  }
});
Enter fullscreen mode Exit fullscreen mode

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)

Collapse
 
lexlohr profile image
Alex Lohr

Yes, this code is indeed vulnerable to SQL injection. Long story short: use parameter queries for user input in order to ensure correct escaping.

Collapse
 
aneshodza profile image
anes • Edited on

I actually found out, that the library promises to handle that by itself. Here you can read more about it.

Collapse
 
lexlohr profile image
Alex Lohr

Ah, they are using the direct template literal call to ensure correct escaping. That's quite ingenious, I must concede.

Thread Thread
 
aneshodza profile image
anes

Yes, if we log our insert query we can see what it does:
Screenshot of a query, showing that it uses parameterized queries by default

Collapse
 
wernerlaude profile image
Werner Laude

Thanks so far..
where to put this code-snippet?
createServerData$(db.getTodos, { initialValue: [] });
db.getTodos

Werner

Collapse
 
aneshodza profile image
anes

My bad, I forgot to mention that. You put it on line 23 inside the src/routes/index.tsx file. Maybe this helps

We want your help! Become a Tag Moderator.
Fill out this survey and help us moderate our community by becoming a tag moderator here at DEV.