Solid Start with PostgreSQL
Tags: postgres, solidjs, webdev, tutorial
Published: 2022-11-21 16:01:56

## 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:
```bash
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:
![A screenshot of our file system with the src folder already open](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ztp1xs027ml5y7pq0vtv.png)
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
```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:
![Screenshot of a todo item when you console log it](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fesvz93es4l6y4s5deyz.png)
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:
```sql
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):
![Screenshot of the base ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r9xlkowmaivbk89hnznu.png)
[Here you can also access the code](https://github.com/aneshodza/psql-with-solid-start/tree/56771bfeb3c3f124f2a5cd3e66add16e661af6b7)
Now we start off by installing the [`postgres` npm package](https://www.npmjs.com/package/postgres). 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:
```js
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:
```typescript
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](https://dev.to/aneshodza/solid-start-with-postgresql-459p) 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wevzc8kxfdjdl0lsa370.png)
To fix that we add a return type `any` to our `getTodos()` function:
```typescript
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`](https://github.com/aneshodza/psql-with-solid-start/blob/main/src/routes/index.tsx#L23):
```typescript
createServerData$(db.getTodos, { initialValue: [] });
db.getTodos
```
Now we can reload and finally see our webpage:
![Two todos with one being crossed out](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f1dejmllwai8sn9gbs5y.png)
We can also query in our database and see that it works:
![A select * from todos showing that we have 2 entries](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jpriiem60mk3qt0i34oe.png)
#### 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"](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/js15qw7ffnmf6fflflev.png)
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:
```typescript
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](https://github.com/aneshodza/psql-with-solid-start)