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

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